MySQL中的JDBC编程最基础的四大场景即表的增删改查

一般使用DataSource的方式

一般步骤
1、获取数据源DataSource(设置MySQL地址)
2、得到连接 Connection
3、得到执行器（组装SQL）
4、执行SQL
5、关闭资源

导入外部驱动包

准备数据库驱动包，并添加到项目的依赖中
mysql-connector-java-5.1.47版本

场景1添加数据

 public static void main(String[] args) throws SQLException {
        //获取数据源DataSource(设置MySQL地址)
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/java33?characterEncoding=utf8&useSSL=true");
        //连接本机回环地址+数据库名称+编码格式+加密方式
        dataSource.setUser("root");//设置连接的用户名
        dataSource.setPassword("12345678");//   设置mysql密码
        //2.得到连接 Connection
        Connection connection = (Connection) dataSource.getConnection();//要抛出异常
        //得到执行器（组装SQL）
        String insertSQL="insert into books(book_name,book_classify) values(?,?)";//组装SQL，使用占位符
        PreparedStatement statement = connection.prepareStatement(insertSQL);
        statement.setString(1,"java技术");
        statement.setString(2,"计算机技术");
        //执行SQL
        int result = statement.executeUpdate();
        System.out.println("受影响行数"+result);
        //关闭资源
        statement.close();//关闭执行器
        connection.close();//关闭连接
    }

场景2删除数据

public static void main(String[] args) throws SQLException {
        //1.获取数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/java33?characterEncoding=utf8&useSSL=true");
        dataSource.setUser("root");
        dataSource.setPassword("12345678");
        //2.得到 Connection
        Connection connection = (Connection) dataSource.getConnection();
        //3.得到执行器(组装SQL)
        String delSQL = "delete from books where  book_name=?";
        PreparedStatement statement = connection.prepareStatement(delSQL);
        statement.setString(1, "java技术");
        int reuslt = statement.executeUpdate();
        System.out.println("影响行数"+reuslt);
        statement.close();
        connection.close();

    }

场景3修改数据

  public static void main(String[] args) throws SQLException {
        //1.获取数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/java33?characterEncoding=utf8&useSSL=true");
        dataSource.setUser("root");
        dataSource.setPassword("12345678");
        //2.得到  Connection
        Connection connection = (Connection) dataSource.getConnection();
        //得到执行器(组装SQL)
        String updSQl = "update books set  book_price=?";
        PreparedStatement statement = connection.prepareStatement(updSQl);
        statement.setInt(1,55);
        //4.执行SQL
        int date = statement.executeUpdate();
        System.out.println("影响行数"+date);
        //5.关闭资源
        statement.close();
        connection.close();

    }

场景4查询数据
这里设置了一个书的类
方便打印查询结果

public class selectbooks {
    public static void main(String[] args) throws SQLException {
        //1.获取数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/java33?characterEncoding=utf8&useSSL=true");
        dataSource.setUser("root");
        dataSource.setPassword("12345678");
        //2.获得Connection
        Connection connection = (Connection) dataSource.getConnection();
        //3.得到执行器（拼装SQL）
        String sql = "select * from books where book_price=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,55);
        //4.执行sql
        ResultSet resultSet = statement.executeQuery();
        //ResultSet resultSet = statement.executeQuery();//得到一个结果集
        while (resultSet.next()) { // 如果结果集的下一行有数据的话
            // 每次循环可以得到一行数据
            //City city = new City();
            Books books = new Books();
            books.setBook_price(resultSet.getInt("book_price"));//名称要和表里面列名相同
            books.setBook_name(resultSet.getString("book_name"));
//            books.setBook_name(resultSet.getInt("book_price"));
//            books.setBook_price(resultSet.getString("book_name"));



            // resultSet.getInt("id") ——> 查询当前行中列名为“id”的值
            // 打s印 city 对象
            System.out.println(books);
        }
        resultSet.close();
        statement.close();
        connection.close();

    }

书的类

public class Books {
    private int book_price;//图书名称
    private String book_name;//图书价格

    @Override
    public String toString() {
        return "Books{" +
                "book_price=" + book_price +
                ", book_name='" + book_name + '\'' +
                '}';
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }

    public void setBook_price(int book_price) {
        this.book_price = book_price;
    }

    public String getBook_name() {
        return book_name;
    }

    public int getBook_price() {
        return book_price;
    }
}