4. Case-Use Statement to complete JDBC addition, deletion, and modification check

4. Case-Use Statement to complete JDBC addition, deletion, and modification check

Preface

In the previous chapters, we have already understood how to obtain database connections and unit tests. Let's use Statement to implement the addition, deletion, and modification of JDBC.

Case-Additions, deletions, revisions and inspection exercises

1. Demand

Use JDBC to complete the practice of adding, deleting, modifying and checking

2. Steps

Register the driver

Get connected

Create and execute SQL statement object

Execute sql statement, process the result

Release resources

3. Realize

3.1 Prepare the database table for the operation

First, prepare the testdb database and user data table to provide operations, as follows:

mysql> use testdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| t_user           || user             |+------------------+2 rows in set (0.00 sec) mysql> mysql> select * from t_user;+----+-------+------+------+| id | uname | age  | sex  |+----+-------+------+------+|  1 | zs    |   18 |    1 ||  2 | ls    |   20 |    0 ||  3 | ww    |   23 |    1 ||  4 | zl    |   24 |    1 ||  5 | lq    |   15 |    0 ||  6 | hh    |   12 |    0 ||  7 | wzx   |   60 | NULL ||  8 | lb    | NULL | NULL |+----+-------+------+------+8 rows in set (0.00 sec) mysql> 

3.2 Create the CRUDDemo class and write a unit test: get database connection

image-20210119223053506

public class CRUDDemo {     /**     * 获取数据库连接(使用ResourceBundle读取配置文件)     */    @Test    public void test01() throws Exception {        //1.使用ResourceBundle读取jdbc.properties文件中的内容        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀         //2.读取配置信息        String username = bundle.getString("jdbc.username");        String password = bundle.getString("jdbc.password");        String url = bundle.getString("jdbc.url");        String driverClass = bundle.getString("jdbc.driverClass");         System.out.println("username=" + username + ", password=" + password + ", url=" + url + ", driverClass=" + driverClass);         //3.加载驱动        Class.forName(driverClass);         //4.获取连接        Connection conn = DriverManager.getConnection(url,username,password);        System.out.println(conn);    } }

Wherein the configuration file jdbc.propertiesas follows:

jdbc.username=rootjdbc.password=L***********0jdbc.url=jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=falsejdbc.driverClass=com.mysql.jdbc.Driver

3.3 Use Statement to complete data insertion

image-20210119223805860

/** * 使用 Statement  完成数据插入 * @throws Exception */@Testpublic void test02() throws Exception {    //1.使用ResourceBundle读取jdbc.properties文件中的内容    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀     //2.读取配置信息    String username = bundle.getString("jdbc.username");    String password = bundle.getString("jdbc.password");    String url = bundle.getString("jdbc.url");    String driverClass = bundle.getString("jdbc.driverClass");     System.out.println("username=" + username + ", password=" + password + ", url=" + url + ", driverClass=" + driverClass);     //3.加载驱动    Class.forName(driverClass);     //4.获取连接    Connection conn = DriverManager.getConnection(url,username,password);    System.out.println(conn);     //5.使用 Statement  完成数据插入    //5.1 创建执行sql语句对象    Statement statement = conn.createStatement(); // 获取 Statement    //5.2 编写SQL语句:插入一条user数据    String sql = " insert into t_user values(null,'特朗普','68','0')";    int rows = statement.executeUpdate(sql);    System.out.println("几行收影响=" + rows);     //5.释放资源    statement.close();    conn.close();}

Next we go to the database and query to see the inserted data, as follows:

mysql> select * from t_user;+----+-----------+------+------+| id | uname     | age  | sex  |+----+-----------+------+------+|  1 | zs        |   18 |    1 ||  2 | ls        |   20 |    0 ||  3 | ww        |   23 |    1 ||  4 | zl        |   24 |    1 ||  5 | lq        |   15 |    0 ||  6 | hh        |   12 |    0 ||  7 | wzx       |   60 | NULL ||  8 | lb        | NULL | NULL ||  9 | 特朗普    |   68 |    0 |  --- 插入的数据+----+-----------+------+------+9 rows in set (0.00 sec) mysql> 

3.4 Use Statement to complete data update

image-20210119224619248

    /**     * 使用 Statement  完成数据更新     * @throws Exception     */    @Test    public void test03() throws Exception {        //1.使用ResourceBundle读取jdbc.properties文件中的内容        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀         //2.读取配置信息        String username = bundle.getString("jdbc.username");        String password = bundle.getString("jdbc.password");        String url = bundle.getString("jdbc.url");        String driverClass = bundle.getString("jdbc.driverClass");//        System.out.println("username=" + username + ", password=" + password + ", url=" + url + ", driverClass=" + driverClass);         //3.加载驱动        Class.forName(driverClass);         //4.获取连接        Connection conn = DriverManager.getConnection(url,username,password);        System.out.println(conn);         //5.使用 Statement  完成数据插入        //5.1 创建执行sql语句对象        Statement statement = conn.createStatement(); // 获取 Statement        //5.2 编写SQL语句:更新user数据        String sql = " update t_user set uname='拜登', age=68 where id = 9 ";        int rows = statement.executeUpdate(sql);        System.out.println("几行收影响=" + rows);         //5.释放资源        statement.close();        conn.close();    }

Go back to the database and check the updated data as follows:

mysql> select * from t_user;+----+--------+------+------+| id | uname  | age  | sex  |+----+--------+------+------+|  1 | zs     |   18 |    1 ||  2 | ls     |   20 |    0 ||  3 | ww     |   23 |    1 ||  4 | zl     |   24 |    1 ||  5 | lq     |   15 |    0 ||  6 | hh     |   12 |    0 ||  7 | wzx    |   60 | NULL ||  8 | lb     | NULL | NULL ||  9 | 拜登   |   68 |    0 | --- 更新的数据+----+--------+------+------+9 rows in set (0.00 sec) mysql> 

3.5 Use Statement to complete data deletion

image-20210119225154893

    /**     * 使用 Statement  完成数据删除     * @throws Exception     */    @Test    public void test04() throws Exception {        //1.使用ResourceBundle读取jdbc.properties文件中的内容        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀         //2.读取配置信息        String username = bundle.getString("jdbc.username");        String password = bundle.getString("jdbc.password");        String url = bundle.getString("jdbc.url");        String driverClass = bundle.getString("jdbc.driverClass");//        System.out.println("username=" + username + ", password=" + password + ", url=" + url + ", driverClass=" + driverClass);         //3.加载驱动        Class.forName(driverClass);         //4.获取连接        Connection conn = DriverManager.getConnection(url,username,password);        System.out.println(conn);         //5.使用 Statement  完成数据插入        //5.1 创建执行sql语句对象        Statement statement = conn.createStatement(); // 获取 Statement        //5.2 编写SQL语句:删除user数据        String sql = " delete from t_user where id = 9 ";        int rows = statement.executeUpdate(sql);        System.out.println("几行收影响=" + rows);         //5.释放资源        statement.close();        conn.close();    }

Go to the database and view the deleted data as follows:

mysql> select * from t_user;+----+--------+------+------+| id | uname  | age  | sex  |+----+--------+------+------+|  1 | zs     |   18 |    1 ||  2 | ls     |   20 |    0 ||  3 | ww     |   23 |    1 ||  4 | zl     |   24 |    1 ||  5 | lq     |   15 |    0 ||  6 | hh     |   12 |    0 ||  7 | wzx    |   60 | NULL ||  8 | lb     | NULL | NULL ||  9 | 拜登   |   68 |    0 | -- 删除之前的数据+----+--------+------+------+9 rows in set (0.00 sec) mysql> mysql> mysql> select * from t_user;+----+-------+------+------+| id | uname | age  | sex  |+----+-------+------+------+|  1 | zs    |   18 |    1 ||  2 | ls    |   20 |    0 ||  3 | ww    |   23 |    1 ||  4 | zl    |   24 |    1 ||  5 | lq    |   15 |    0 ||  6 | hh    |   12 |    0 ||  7 | wzx   |   60 | NULL ||  8 | lb    | NULL | NULL |+----+-------+------+------+ -- 数据被删除了8 rows in set (0.00 sec) mysql> 

3.6 Use Statement to complete a data query

image-20210119225922790

/** * 使用 Statement  完成一条数据的查询 * * @throws Exception */@Testpublic void test05() throws Exception {    //1.使用ResourceBundle读取jdbc.properties文件中的内容    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀     //2.读取配置信息    String username = bundle.getString("jdbc.username");    String password = bundle.getString("jdbc.password");    String url = bundle.getString("jdbc.url");    String driverClass = bundle.getString("jdbc.driverClass");     //3.加载驱动    Class.forName(driverClass);     //4.获取连接    Connection conn = DriverManager.getConnection(url, username, password);    System.out.println(conn);     //5.使用 Statement  完成数据插入    //5.1 创建执行sql语句对象    Statement statement = conn.createStatement(); // 获取 Statement    //5.2 编写SQL语句:一条数据的查询    String sql = " select * from t_user where id = 8 ";    ResultSet resultSet = statement.executeQuery(sql);     while (resultSet.next()) {        //每遍历一次,就是一条数据        // 打印查询的数据        System.out.println("id=" + resultSet.getInt("id")                + ", uname=" + resultSet.getString("uname")                + ", age=" + resultSet.getString("age")                + ", sex=" + resultSet.getString("sex"));    }     //5.释放资源    statement.close();    conn.close();}

In the above code, we have queried the data, but there is no better way to save the data.

Generally, for this kind of data query, we will write a saved pojo class.

3.7 Write the user class under the pojo package to save the data queried by the t_user table

image-20210119231259396

package com.pojo; import java.io.Serializable; /** * @author Aron.li * @date 2021/1/19 23:04 */public class User implements Serializable {     /**     * 成员属性     * id     * uname     * age     * sex     */    private Integer id;    private String uname;    private String age;    private String sex;     public User() {    }     public User(Integer id, String uname, String age, String sex) {        this.id = id;        this.uname = uname;        this.age = age;        this.sex = sex;    }     @Override    public String toString() {        return "User{" +                "id=" + id +                ", uname='" + uname + '\'' +                ", age='" + age + '\'' +                ", sex='" + sex + '\'' +                '}';    }     public Integer getId() {        return id;    }     public void setId(Integer id) {        this.id = id;    }     public String getUname() {        return uname;    }     public void setUname(String uname) {        this.uname = uname;    }     public String getAge() {        return age;    }     public void setAge(String age) {        this.age = age;    }     public String getSex() {        return sex;    }     public void setSex(String sex) {        this.sex = sex;    }}

3.8 Use the user class to save a piece of data queried by Statement

image-20210119231417656

/** * 使用 Statement  完成一条数据的查询 * * @throws Exception */@Testpublic void test05() throws Exception {    //1.使用ResourceBundle读取jdbc.properties文件中的内容    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀     //2.读取配置信息    String username = bundle.getString("jdbc.username");    String password = bundle.getString("jdbc.password");    String url = bundle.getString("jdbc.url");    String driverClass = bundle.getString("jdbc.driverClass");     //3.加载驱动    Class.forName(driverClass);     //4.获取连接    Connection conn = DriverManager.getConnection(url, username, password);    System.out.println(conn);     //5.使用 Statement  完成数据插入    //5.1 创建执行sql语句对象    Statement statement = conn.createStatement(); // 获取 Statement    //5.2 编写SQL语句:一条数据的查询    String sql = " select * from t_user where id = 8 ";    ResultSet resultSet = statement.executeQuery(sql);     User user = null; // 创建 user 用来保存查询的数据    while (resultSet.next()) {        //每遍历一次,就是一条数据        user = new User(                resultSet.getInt("id"),                resultSet.getString("uname"),                resultSet.getString("age"),                resultSet.getString("sex")        );     }     // 打印结果    System.out.println(user.toString());     //5.释放资源    statement.close();    conn.close();}

In the above, we have successfully queried a piece of data and saved it as a User class object.

So when we need to query multiple pieces of data, what should we do?

3. 9 Use ArrayList to save multiple pieces of data queried by Statement

image-20210119231901721

/** * 使用 Statement  完成多条数据的查询 * * @throws Exception */@Testpublic void test06() throws Exception {    //1.使用ResourceBundle读取jdbc.properties文件中的内容    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 注意不用写 properties 后缀     //2.读取配置信息    String username = bundle.getString("jdbc.username");    String password = bundle.getString("jdbc.password");    String url = bundle.getString("jdbc.url");    String driverClass = bundle.getString("jdbc.driverClass");     //3.加载驱动    Class.forName(driverClass);     //4.获取连接    Connection conn = DriverManager.getConnection(url, username, password);    System.out.println(conn);     //5.使用 Statement  完成数据插入    //5.1 创建执行sql语句对象    Statement statement = conn.createStatement(); // 获取 Statement    //5.2 编写SQL语句:多条数据的查询    String sql = " select * from t_user";    ResultSet resultSet = statement.executeQuery(sql);     // 用来保存多个user查询数据    ArrayList<User> users = new ArrayList<>();    while (resultSet.next()) {        //每遍历一次,就是一条数据        User user = new User(                resultSet.getInt("id"),                resultSet.getString("uname"),                resultSet.getString("age"),                resultSet.getString("sex")        );        //将查询的数据加入到 ArrayList 中        users.add(user);    }     // 遍历查询结果    for (User user : users) {        System.out.println(user);    }     //5.释放资源    resultSet.close();    statement.close();    conn.close();}

4. Summary

step

Register the driver

Get connected

Create and execute SQL statement object

Execute sql statement, process the result

Release resources

Found that most of the code in the exercise is repetitive, so it should be extracted