JDBC实现简单的增删改查

//创建分类表
CREATE TABLE sort(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(100),
sprice DOUBLE,
sdesc VARCHAR(500)
);

//初始化数据
insert into sort(sname,sprice,sdesc) values(‘家电‘,2000, ‘优惠的促销‘);
insert into sort(sname,sprice,sdesc) values(‘家具‘,8900, ‘家具价格上调,原材料涨价‘);
insert into sort(sname,sprice,sdesc) values(‘儿童玩具‘,290, ‘赚家长的钱‘);
insert into sort(sname,sprice,sdesc) values(‘生鲜‘,500.99, ‘生鲜商品‘);
insert into sort(sname,sprice,sdesc) values(‘服装‘,24000, ‘换季销售‘);
insert into sort(sname,sprice,sdesc) values(‘洗涤‘,50, ‘洗发水促销‘);

通过查询语句SELECT * FROM sort;  可以看到数据插入成功了

2.insert

package harbin.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo {
    
    public static void main(String []args) throws ClassNotFoundException, SQLException{
        
        Class.forName("com.mysql.jdbc.Driver");//注册驱动
        String url  = "jdbc:mysql://localhost:3306/mybase";//获得数据库连接
        String username = "root"; //登陆数据库的用户名和密码
        String password = "682436498";
        Connection con = DriverManager.getConnection(url,username,password);
        
        String sql = "INSERT INTO sort(sname,sprice,sdesc) VALUES(?,?,?)";
        PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql);
        pst.setString(1, "汽车用品");
        pst.setInt(2, 50000);
        pst.setString(3, "疯狂涨价");
        int row = pst.executeUpdate();
        System.out.println(row);  //打印结果为1，表示插入成功了

　　　　　　con.close();
　　　　　　pst.close();

      
    }
}

在数据库中可以看到插入成功了

3.update

package harbin.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo {
    
    public static void main(String []args) throws ClassNotFoundException, SQLException{
        
        Class.forName("com.mysql.jdbc.Driver");//注册驱动
        String url  = "jdbc:mysql://localhost:3306/mybase";//获得数据库连接
        String username = "root"; //登陆数据库的用户名和密码
        String password = "682436498";
        Connection con = DriverManager.getConnection(url,username,password);
        
        String sql = "UPDATE sort SET sname=?,sprice=? WHERE sid=?";
        PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql);
        pst.setString(1, "汽车美容");
        pst.setInt(2, 49888);
        pst.setInt(3, 7);
        int row = pst.executeUpdate();
        System.out.println(row);        
        con.close();
        pst.close();
    }
}

数据库中结果已经更新

 4.delete

package harbin.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo {
    
    public static void main(String []args) throws ClassNotFoundException, SQLException{
        
        Class.forName("com.mysql.jdbc.Driver");//注册驱动
        String url  = "jdbc:mysql://localhost:3306/mybase";//获得数据库连接
        String username = "root"; //登陆数据库的用户名和密码
        String password = "682436498";
        Connection con = DriverManager.getConnection(url,username,password);
        
        String sql = "DELETE FROM sort WHERE sid =?";
        PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql);
        pst.setInt(1, 7);

        int row = pst.executeUpdate();
        System.out.println(row);        
        con.close();
        pst.close();
    }
}

数据库中可以看到sid=7的已经被删除了

5.select

package harbin.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo {
    
    public static void main(String []args) throws ClassNotFoundException, SQLException{
        
        Class.forName("com.mysql.jdbc.Driver");//注册驱动
        String url  = "jdbc:mysql://localhost:3306/mybase";//获得数据库连接
        String username = "root"; //登陆数据库的用户名和密码
        String password = "682436498";
        Connection con = DriverManager.getConnection(url,username,password);
        
        String sql = "SELECT * FROM sort WHERE sname=? AND sprice=?";
        PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql);
        pst.setString(1,"家电");
        pst.setInt(2,2000);
        
        ResultSet rs = pst.executeQuery();
        int col = rs.getMetaData().getColumnCount();
        while(rs.next()){
            for(int i=1;i<=col;i++){
                System.out.println(rs.getString(i));
            }
        }
    
        con.close();
        pst.close();
    }
}

输出结果为：

可以看到与数据库中的一致

JDBC实现简单的增删改查

标签：manager   localhost   pst   into   pat   9.png   system   查询语句   bcd