jdbc操作mysql

一、数据准备

说明: 本章基于mysql和maven演示

咱们先来回顾一下jdbc相关的操作

1.先准备一张学生表

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  age INT COMMENT '年龄',
  gender TINYINT DEFAULT 0 COMMENT '性别,0|女,1|男',
  birthday DATE NOT NULL COMMENT '出生日期',
  major int COMMENT '专业',
  phone VARCHAR(100) COMMENT '电话号码',
  deleted INT DEFAULT 0 COMMENT '是否有效,0|有效,1|无效',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT='学生表';

2.准备mysql环境

<dependency>  
<groupId>com.mysql</groupId>  
<artifactId>mysql-connector-j</artifactId>  
<version>9.0.0</version>  
<scope>test</scope>  
</dependency>

二、数据源

String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "123456";

/**
 * 获取数据库连接
 */
private Connection getConn() throws SQLException {  
    return DriverManager.getConnection(url, username, password);  
}

/**
 * 关闭数据库连接
 */
private void close(Statement statement, Connection connection) {  
    try (statement; connection) {  
    } catch (SQLException e) {  
    e.printStackTrace();  
    }  
}

简单的获取和关闭连接的公共方法

三、常规crud

1.insert

普通INSERT

@Test
  void testInsert() throws SQLException {
    Connection connection = this.getConn();

    // 自动生成的键
    String generatedKeyColumn = "id";
    String sql = "insert into student(name, gender, birthdate, major, phone) values(?,?,?,?,?)";
    PreparedStatement statement = connection.prepareStatement(sql, new String[]{generatedKeyColumn});
    
    // 封装参数
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("name", "小乔同学");
    paramMap.put("gender", 1);
    paramMap.put("birthdate", LocalDate.of(1999, 9, 9));
    paramMap.put("major", 1);
    paramMap.put("phone", "13400001814");

    // 设置参数
    statement.setString(1, paramMap.get("name").toString());
    statement.setInt(2, (Integer) paramMap.get("gender"));
    statement.setObject(3, paramMap.get("birthdate"));
    statement.setString(4, 1);
    statement.setString(5, "13400001814");

    // 执行sql
    statement.executeUpdate();
    int affectedRows = statement.getUpdateCount();

    // 返回值封装
    if (affectedRows > 0) {
      // 获取生成的键
      try (ResultSet rs = statement.getGeneratedKeys()) {
        ResultSetMetaData metaData = rs.getMetaData();
        String columnName = metaData.getColumnName(1);
        int columnType = metaData.getColumnType(1);
        System.out.println(columnName + ":" + columnType);
        if (rs.next()) {
          int id = rs.getInt(1);
          paramMap.put(generatedKeyColumn, id);
        }
      }
    }
    
    // 处理返回值
    System.out.println(paramMap);
    this.close(statement, connection);
  }

稍微解释以下

• 1.获取数据库连接
• 2构建sql语句
• 3.构建sql参数
• 4.预处理sql并赋值
• 5.执行插入
• 6.获取自动生成的主键并添加到参数中
  这里我们注意一下预处理对象的创建statement = connection.prepareStatement(sql, new String[]{generatedKeyColumn}); 这句代码, 第二个参数指定自动生成的列, 它是一个数组格式, 但是在mysql中只有主键字段可以设置自动生成标识, 也就是AUTO_INCREMENT。同时第二个参数还可以用Statement.RETURN_GENERATED_KEYS来指定返回自动生成的列(重载方法)。

批量插入

insert values

批量插入分两种, 先看第一种insert into … values(…),(…)

@Test
    void testBatchInsertValues() throws Exception {
        // 获取数据库连接
        Connection connection = this.getConn();

        // 参数处理
        List<Map<String, Object>> paramList = this.getParamList();
        String sql = "insert into student(name, gender, birthdate, major, phone) values(?,?,?,?,?),(?,?,?,?,?)";
        // 设置返回自动生成的键
        PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        // 设置参数
        for (int i = 0; i < paramList.size(); i++) {
            Map<String, Object> paramMap = paramList.get(i);
            statement.setString(1 + i * 5, paramMap.get("name").toString());
            statement.setInt(2 + i * 5, (Integer) paramMap.get("gender"));
            statement.setObject(3 + i * 5, paramMap.get("birthdate"));
            statement.setString(4 + i * 5, (String) paramMap.get("major"));
            statement.setString(5 + i * 5, (String) paramMap.get("phone"));
        }

        // 执行sql
        statement.executeUpdate();
        int updateCount = statement.getUpdateCount();
        System.out.println("影响行数:" + updateCount);

        // 获取生成的键
        ResultSet generatedKeys = statement.getGeneratedKeys();
        while (generatedKeys.next()) {
            int id = generatedKeys.getInt(1);
            // gerRow:获取行号, 从1开始
            paramList.get(generatedKeys.getRow() - 1).put("id", id);
        }

        // 返回值处理
        System.out.println(paramList);
        this.close(statement, connection);
    }
    
    private List<Map<String, Object>> getParamList() {
        List<Map<String, Object>> paramList = new ArrayList<>();

        Map<String, Object> paramMap1 = new HashMap<>();
        paramMap1.put("name", "小杜同学");
        paramMap1.put("gender", 0);
        paramMap1.put("birthdate", LocalDate.of(1998, 8, 8));
        paramMap1.put("major", 3);
        paramMap1.put("phone", "13500001815");

        Map<String, Object> paramMap2 = new HashMap<>();
        paramMap2.put("name", "小白同学");
        paramMap2.put("gender", 1);
        paramMap2.put("birthdate", LocalDate.of(1997, 7, 7));
        paramMap2.put("major", 1);
        paramMap2.put("phone", "13400001814");

        paramList.add(paramMap1);
        paramList.add(paramMap2);
        return paramList;
    }

相关步骤

• 1.获取链接
• 2.准备参数
• 3.准备sql insert into … values(…),(…) 的格式
• 4.预处理sql并创建statement
• 5.设置参数
• 6.使用statement.executeUpdate()方法执行sql
• 7.处理自动生成的主键
• 8.处理返回值
• 9.关闭连接

这里是拼接的sql, 虽然拼接了多个值, 最后也是一条sql语句,所以使用executeUpdate方法执行。 要注意数据库对单sql长度的限制, 避免sql过长导致报错

executeBatch

再看批处理的形式

@Test
void testBatchInsert() throws Exception {
    Connection connection = this.getConn();

    List<Map<String, Object>> paramList = this.getParamList();
    String sql = "insert into student(name, gender, birthdate, major, phone) values(?,?,?,?,?)";
    PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    
    for (Map<String, Object> paramMap : paramList) {
        statement.setString(1, paramMap.get("name").toString());
        statement.setInt(2, (Integer) paramMap.get("gender"));
        statement.setObject(3, paramMap.get("birthdate"));
        statement.setString(4, (String) paramMap.get("major"));
        statement.setString(5, (String) paramMap.get("phone"));
        statement.addBatch();
    }
    int[] ints = statement.executeBatch();
    System.out.println("影响行数:" + Arrays.toString(ints));

    ResultSet generatedKeys = statement.getGeneratedKeys();
    while (generatedKeys.next()) {
        int id = generatedKeys.getInt(1);
        paramList.get(generatedKeys.getRow() - 1).put("id", id);
    }

    System.out.println(paramList);
    this.close(statement, connection);
}

相关步骤

• 1.sql为单条数据的插入语句
• 2.每遍历一条参数数据, 就执行一次statement.addBatch()操作
• 3.最后使用statement.executeBatch()提交整个批次的任务
• 4.影响行返回的是一个数组

这里是将多条insert语句一次性提交到db, 在db中也是多次处理的

区别
1.INSERT INTO … VALUES (…), (…)
这种方式是通过 SQL 语句一次性插入多行数据，语法上通过逗号分隔多组值。

• 优点：相对简单，适合小批量数据插入。
• 缺点：不容易动态构建，尤其当插入的数据量较大时，SQL 语句会变得很长且不易管理。

性能

• 当插入的行数较少时，这种方式通常会比 executeBatch 更快，因为它只涉及一次网络交互和一次 SQL 解析。
• 但是，当插入行数较多时，由于 SQL 语句会变得非常长，可能会受到数据库最大 SQL 语句长度的限制，影响性能。

使用场景

• 适用于小规模、简单的批量插入场景，或插入数据量不大的情况下。
• 当批量数据来源于同一个数据源，且 SQL 语句不会过长时，这种方式比较适合

错误处理

• 如果插入过程中出现错误，整个 SQL 语句都会失败，无法部分成功。

2.executeBatch
这种方式是通过 JDBC 的批处理机制，一次性提交多条 SQL 语句。每条 SQL 语句都可以是单行的 INSERT INTO … VALUES (…)，然后通过 executeBatch 一次性执行这些语句。

• 优点：灵活性更高，适合大批量数据插入，可以动态构建和管理大量的 SQL 语句。
• 缺点：代码相对复杂，可能需要更多的内存来维护批处理中的 SQL 语句。

性能

• 对于大批量数据插入，executeBatch 通常性能更好，因为它将多个 SQL 语句组合成一个批次进行一次网络传输，并且可以通过批处理减少数据库交互次数。
• 由于每个 PreparedStatement 可以重复使用，因此减少了 SQL 解析的开销。

使用场景

• 更适用于大规模批量插入，尤其是当插入数据来源多样且需要动态构建 SQL 语句时。
• 适合处理复杂的批量操作，例如从不同的数据源插入不同的数据集。

错误处理

• 可以设置批处理的执行策略，如继续执行剩余部分或回滚整个批次。如果某条语句失败，可以对单个失败进行处理或排查。

总之，选择哪种方式要依据具体场景、数据量以及对性能的要求。如果是简单的小规模批量插入，可以使用 INSERT INTO … VALUES (…), (…)，而对于更复杂的大规模批量操作，executeBatch 更为合适。

2.update

普通数据更新

@Test
    void testUpdate() throws Exception {
        Connection connection = this.getConn();

        String sql = "update student set name = ?, phone = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "小李同学");
        statement.setInt(2, 1330004321);
        statement.setInt(3, 1);

        System.out.println("影响行数:" + statement.executeUpdate());

        this.close(statement, connection);
    }

1. 批量更新
   初始数据长这样

1,小乔同学,1,1999-09-09,1,13400001814,2024-08-21 14:55:52,2024-08-21 14:55:52
2,小杜同学,0,1998-08-08,3,13500001815,2024-08-21 14:55:52,2024-08-21 14:55:52
3,小白同学,1,1999-07-07,1,13400001814,2024-08-21 14:55:52,2024-08-21 14:55:52
4,小黑同学,0,2000-06-06,3,13600001815,2024-08-21 14:55:53,2024-08-21 14:55:53
5,小黄同学,0,2001-05-05,2,13600001815,2024-08-21 14:55:53,2024-08-21 14:55:53

批量处理代码

    @Test
    void batchUpdate() throws Exception {
        Connection connection = this.getConn();

        String sql = "update student set name = ?, gender = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);

        List<Map<String, Object>> paramList = new ArrayList<>();
        Map<String, Object> paramMap1 = new HashMap<>();
        paramMap1.put("name", "张三同学");
        paramMap1.put("gender", 0);
        paramMap1.put("id", 1);

        Map<String, Object> paramMap2 = new HashMap<>();
        paramMap2.put("name", "李四同学");
        paramMap2.put("gender", 1);
        paramMap2.put("id", 2);

        paramList.add(paramMap1);
        paramList.add(paramMap2);

        // 构建多个statement
        for (Map<String, Object> paramMap : paramList) {
            statement.setString(1, paramMap.get("name").toString());
            statement.setInt(2, (Integer) paramMap.get("gender"));
            statement.setInt(3, (Integer) paramMap.get("id"));
            statement.addBatch();
        }

        // 执行批量操作
        int[] ints = statement.executeBatch();
        System.out.println("影响行数:" + Arrays.toString(ints));
        this.close(statement, connection);
    }

批处理之后, 数据已经变化了

1,张三同学,0,1999-09-09,1,13400001814,2024-08-21 14:58:33,2024-08-21 14:58:43
2,李四同学,1,1998-08-08,3,13500001815,2024-08-21 14:58:34,2024-08-21 14:58:43

错误示范

原始数据

select id, gender, name from student;

1,1,小乔同学

错误sql处理

    @Test
    void errorUpdate() throws Exception {
        Connection connection = this.getConn();

        String sql = "update student set gender = ? and name = ?  where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, 2);
        statement.setString(2, "小李同学");
        statement.setInt(3, 1);

        System.out.println("影响行数:" + statement.executeUpdate());

        this.close(statement, connection);
    }

注意这里sql语句字段之间是用and连接的set name = ? and phone = ?
执行之后的数据

select id, gender, name from student;

1,0,小乔同学

是不是很神奇, 这里sqlupdate student set gender = ? and name = ? where id = ?本来是想修改id为1的同学的gender为2和name为"小李同学"
但是gender被修改成了0, 没有变化

原因是mysql解析的时候讲and作为逻辑连接符 其中的? and name = ?被当成一个与运算了, 相当于 2 and name = '小李同学' 很显然id为1的name是小乔同学返回false, 也就是0(1表示true), 这里也正好gender是个数字类型可以接受0/1的数据

3.delete

@Test
void testDelete() throws Exception {
    Connection connection = this.getConn();

    String sql = "delete from student where id = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setInt(1, 1);

    System.out.println("影响行数:" + statement.executeUpdate());

    this.close(statement, connection);
}

4.truncate

@Test
void testTruncate() throws Exception {
    Connection connection = this.getConn();

    // 使用TRUNCATE清空表
    String sql = "TRUNCATE TABLE student";
    Statement statement = connection.createStatement();

    // TRUNCATE TABLE 不返回影响行数
    statement.execute(sql);
    this.close(statement, connection);
}

在 MySQL 中，DELETE 和 TRUNCATE 都用于删除表中的数据，但它们之间有一些关键的区别。以下是它们的主要差异：

1. 基本功能

• DELETE: 用于删除表中的指定行或所有行。可以使用 WHERE 子句来指定要删除的行。
• TRUNCATE: 用于删除表中的所有行。TRUNCATE 是一个DDL（数据定义语言）操作，而不是DML（数据操作语言）。

1. 语法

• DELETE: DELETE FROM table_name [WHERE condition];
• TRUNCATE: TRUNCATE TABLE table_name;

1. 执行效率

• DELETE: DELETE 逐行删除数据，每删除一行都会记录在日志中（针对 InnoDB 引擎），因此相对较慢。
• TRUNCATE: TRUNCATE 删除所有行，通常不记录单行删除的日志（视引擎而定），因此比 DELETE 更快。

1. 日志记录

• DELETE: 记录每一行的删除操作到事务日志中，如果有 WHERE 子句，仅记录符合条件的行。
• TRUNCATE: 通常只记录表的数据页的释放，而不记录单行操作，因此日志量较少。

1. 事务处理

• DELETE: DELETE 操作是可以回滚的，前提是操作在事务中进行。
• TRUNCATE: TRUNCATE 通常不能回滚，特别是在 AUTOCOMMIT 模式下，它会立刻生效。对于支持 TRUNCATE 回滚的存储引擎（如 InnoDB），在事务中执行 TRUNCATE 是可以回滚的。

1. 触发器与外键约束

• DELETE: 可以触发触发器（BEFORE DELETE 和 AFTER DELETE），并且尊重外键约束。
• TRUNCATE: 不会触发任何触发器，不能在有外键约束的表上直接使用，除非先移除约束。

1. 自增列 (AUTO_INCREMENT)

• DELETE: 不会重置自增列的计数。删除操作后插入的新行将继续从上次的 AUTO_INCREMENT 值递增。
• TRUNCATE: 通常会重置自增列的计数，新插入的行会从初始值开始。

1. 表结构

• DELETE: 仅删除数据，表结构和索引都保持不变。
• TRUNCATE: 也仅删除数据，不会影响表结构和索引，但会重置表空间。

总结

• 使用场景：
  • DELETE 更灵活，适用于需要删除部分数据或依赖于触发器、外键约束的场景。
  • TRUNCATE 更高效，适用于需要快速删除所有数据并不依赖外键约束和触发器的场景。
• 性能：
  • TRUNCATE 通常比 DELETE 更快，因为它是基于表的操作，不逐行记录日志。

根据使用场景的不同，选择合适的操作以获得最佳性能和功能。

5.query

普通查询

@Test
void testQuery() throws Exception {
    Connection connection = this.getConn();
    String sql = "select * from student";
    try (Statement statement = connection.createStatement()) {
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id"));
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getInt("gender"));
            System.out.println(resultSet.getDate("birthdate"));
            System.out.println(resultSet.getString("major"));
            System.out.println(resultSet.getString("phone"));
        }
    }
    this.close(null, connection);
}

这里我们可以关注一下executeQuery方法
它有三个重载方法

1. Statement createStatement() throws SQLException;
   这个构造器默认使用的是 createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
2. Statement createStatement(int resultSetType, int resultSetConcurrency)
   throws SQLException;
3. Statement createStatement(int resultSetType, int resultSetConcurrency,
   int resultSetHoldability) throws SQLException;
   这个构造器要想resultSetHoldability字段生效, 需要在jdbc连接上加一个属性pedantic=true, 它表示在提交事务时关闭所有由该连接创建的结果集; 目前mysql中这个字段只能取值ResultSet.HOLD_CURSORS_OVER_COMMIT

resultSetType
ResultSet.TYPE_FORWARD_ONLY(默认): 表示一个 ResultSet 对象的游标只能向前移动，而不能向后移动或滚动。
ResultSet.TYPE_SCROLL_INSENSITIVE: 允许游标前后滚动，其它session修改db中的数据’不会影响’到当前session的数据
ResultSet.TYPE_SCROLL_SENSITIVE(mysql不支持): 允许游标前后滚动, 其它session修改db中的数据也’会影响’到当前session的数据

resultSetConcurrency
ResultSet.CONCUR_READ_ONLY: 示结果集是只读的。在ResultSet中的数据记录是只读的，不可以修改
ResultSet.CONCUR_UPDATABLE: 在ResultSet中的数据记录可以任意修改，然后更新到数据库，可以插入，删除，修改

resultSetHoldability
ResultSet.HOLD_CURSORS_OVER_COMMIT: 在事务commit或者rollback后, ResultSet仍然可以访问到数据(只要ResultSet还未关闭)。
ResultSet.CLOSE_CURSORS_AT_COMMIT: 在事务commit或者rollback后, ResultSet将会被关闭。

可以滚动的查询

@Test
    void testQuery3() throws Exception {
        Connection connection = this.getConn();
        String sql = "select * from student where id = 1";
        ResultSet resultSet = null;
        try {
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 移到首行
        resultSet.beforeFirst();
//        resultSet.afterLast();
        // absolute(1)相当于beforeFirst,absolute(-1) afterLast
//        resultSet.absolute(1);
        System.out.println("=========");
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
        }
        this.close(null, connection);
    }

这里咋们只需要设置resultSetType为ResultSet.TYPE_SCROLL_INSENSITIVE就可以达到游标前后移动了。
大家可以去试一下beforeFirst、afterLast、absolute等api, 注意absolute(1)相当于beforeFirst,absolute(-1) afterLast

查询db中已修改的数据

当前数据库中的数据为

1:小乔同学:1:2024-09-01:1:13400001814

查询动作

@Test
void testQuery4() throws Exception {
    Connection connection = this.getConn();
    // 开启事务
//        connection.setAutoCommit(false);
    String sql = "select * from student where id = 1";
    try {
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            // 手动刷新当前行，查看数据库中最新的数据, mysql需要ResultSet.TYPE_SCROLL_SENSITIVE和ResultSet.CONCUR_UPDATABLE的配合, 只有其中一个都不行
            resultSet.refreshRow();
            System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
//        connection.commit();
    this.close(null, connection);
}

@Test
void testQuery4Update() throws Exception {
    Connection connection = this.getConn();
    String sql = "update student set name = ? where id = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, "小李同学");
    statement.setInt(2, 1);

    System.out.println("影响行数:" + statement.executeUpdate());
    this.close(statement, connection);
}

步骤

1. 这里我们运行的时候把断点打在while, 执行testQuery4
2. 断点卡在了while, 然后执行testQuery4Update方法
3. 放开testQuery4的断点, 打印出来的会是方法testQuery4Update修改的小李同学

有的人可能会问了, 这不是违背了mysql可重复读的隔离级别吗, 这里注意隔离级别是在事务下生效的, 隔离级别用于定义一个事务如何与其他并发事务进行交互，以避免数据不一致的情况 但是testQuery4方法并没有开启事务connection.setAutoCommit(true)

所以jdbc在mysql下如果要查询到其它session修改的数据, 需要满足以下几点

1. 设置resultSetType为ResultSet.TYPE_SCROLL_INSENSITIVE
2. 设置resultSetConcurrency为ResultSet.CONCUR_UPDATABLE
3. 在获取数据(例如getInt,getString等)时, 先调用resultSet.refreshRow()方法
4. 当前查询不能开启事务(因为隔离级别mvcc的原因)

修改已查询的数据同步到db

/**
 * 修改已查询的数据同步到db
 */
@Test
void testQuery5() throws Exception {
    Connection connection = this.getConn();
    String sql = "select * from student where id = 1";
    try {
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = statement.executeQuery(sql);
        boolean flag = false;
        while (resultSet.next()) {
            // 手动刷新当前行，查看数据库中最新的数据, mysql需要ResultSet.TYPE_SCROLL_SENSITIVE和ResultSet.CONCUR_UPDATABLE的配合, 只有其中一个都不行
            resultSet.refreshRow();
            System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
            if (flag) {
                break;
            }
            resultSet.updateString("name", "小乔同学");
            resultSet.updateRow();
            resultSet.beforeFirst();
            flag = true;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    this.close(null, connection);
}

需要满足以下几点

1. 设置resultSetType为ResultSet.TYPE_SCROLL_INSENSITIVE
2. 设置resultSetConcurrency为ResultSet.CONCUR_UPDATABLE
3. 在使用resultSet更新数据后(这里是updateString), 需要调用resultSet.updateRow();才能将数据刷到db

6.rewriteBatchedStatements

rewriteBatchedStatements=true
它是MySQL JDBC 驱动程序中的一个连接属性，它用于优化批量更新操作的性能, 一般建议设置为true

当设置 rewriteBatchedStatements=true 时，MySQL JDBC 驱动程序会自动将批量操作的多个 SQL 语句合并为一个 SQL 语句，这样可以减少与数据库的通信次数，从而提高批量更新的性能。

例如

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name (column1, column2) VALUES (value3, value4);

重写为:

INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4);

7.allowMultiQueries

allowMultiQueries=true
它是 MySQL JDBC 驱动程序中的一个连接属性，它允许在一次数据库调用中执行多个 SQL 查询语句

当设置 allowMultiQueries=true 时，MySQL JDBC 驱动程序允许通过单个 SQL 语句执行多个查询。例如，可以在一个 Statement 对象的 execute 方法中传递多个用分号分隔的 SQL 语句，并且驱动程序会按顺序执行这些语句。
例如

@Test
void allowMultiQueriesTest() throws Exception {
    Connection connection = this.getConn();
    String sql = "select * from student where id = 1; select * from student where id = 2;";
    try {
        Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        boolean hasMoreResults = statement.execute(sql);
        while (hasMoreResults) {
            // 手动刷新当前行，查看数据库中最新的数据, mysql需要ResultSet.TYPE_SCROLL_SENSITIVE和ResultSet.CONCUR_UPDATABLE的配合, 只有其中一个都不行
            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
            }
            hasMoreResults = statement.getMoreResults();
            resultSet.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    this.close(null, connection);
}

8.流式查询

分批的从TCP通道中读取mysql服务返回的数据，每次读取的数据量并不是一行（通常是一个package大小），jdbc客户端在调用rs.next()方法时会根据需要从TCP流通道中读取部分数据。（并不是每次读区一行数据！）

@Test
void streamQuery() throws Exception {
    Connection conn = getConn();
    String sql = "select * from student";

    // 也就是不支持滚动结果集并且不支持更新ResultSet ResultSet.TYPE_FORWARD_ONLY:只能向后遍历, ResultSet.CONCUR_READ_ONLY:不可以更新ResultSet
    // 流式查询必须设置ResultSet.TYPE_FORWARD_ONLY和ResultSet.CONCUR_READ_ONLY属性, 并且fetchSize设置为Integer.MIN_VALUE
    PreparedStatement statement = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);

    // 查询阻塞, 把所有满足条件的数据加载到内存中
    // rowData为ResultSetRowsStreaming类型
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
    }
    this.close(statement, conn);
}

注意点

1. 设置ResultSet.TYPE_FORWARD_ONLY和ResultSet.CONCUR_READ_ONLY属性
2. 设置fetchSize为Integer.MIN_VALUE

优点: 大数据量时不会有OOM问题
缺点: 占用数据库时间更长，导致网络拥塞的可能性较大。

怎么证明当前是流式查询呢??
断点看一下当前返回的ResultSet的rowData类型是不是ResultSetRowsStreaming类型

9.游标查询

@Test
void cursorQuery() throws SQLException {
    Connection conn = getConn();
    String sql = "select * from student";
    PreparedStatement statement = conn.prepareStatement(sql);

    // 游标查询时, fetchSize控制每一次获取多少条数据
    statement.setFetchSize(10);

    // rowData为 ResultSetRowsCursor类型
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
    }
    this.close(statement, conn);
}

注意点

1. 游标查询要在连接参数上拼接useCursorFetch为true
2. 游标查询必须设置ResultSet.TYPE_FORWARD_ONLY和ResultSet.CONCUR_READ_ONLY属性
3. 必须设置fetchSize为一个大于0的数

怎么证明当前是游标查询呢??
断点看一下当前返回的ResultSet的rowData类型是不是ResultSetRowsCursor类型

10.执行函数

创建一个函数

DROP FUNCTION IF EXISTS test_func;
CREATE FUNCTION test_func (input_id INT)
RETURNS VARCHAR(64)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE result VARCHAR(64);
    SELECT name FROM student s WHERE s.id = input_id INTO result;
    RETURN result;
END;

注意: mysql的函数只能返回一个值
查看定义的函数

SHOW CREATE FUNCTION test_func;

调用函数

select test_func(1);

jdbc调用

@Test
void testFunc() throws Exception {
    Connection conn = getConn();
    // 准备调用函数的 SQL 语句
    String sql = "{ ? = CALL test_func(?) }";
    // 创建 CallableStatement
    try (CallableStatement stmt = conn.prepareCall(sql)) {
        // 注册返回值类型
        stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
        stmt.setInt(2, 1);
        // 执行函数
        stmt.execute();
        // 获取返回值
        String result = stmt.getString(1);
        // 打印结果
        System.out.println("Function result: " + result);
    }
}

11.执行存储过程

创建存储过程

DROP PROCEDURE IF EXISTS get_student_by_id;
DELIMITER //
CREATE PROCEDURE get_student_by_id(IN id INTEGER, OUT name VARCHAR(64))
BEGIN
    -- 确保返回的结果集是正确的
    SELECT s.name INTO name FROM student s WHERE s.id = id;
    -- 返回数据
    SELECT name, phone  FROM student s WHERE s.id = id;
    SELECT * FROM student s WHERE s.id = id;
END //
DELIMITER ;

简单的测试类

@Test
void testProcedure() throws Exception {
    Connection conn = getConn();
    CallableStatement cstmt = conn.prepareCall("{call get_student_by_id(?, ?)}");
    cstmt.setInt(1, 1);
    cstmt.registerOutParameter(2, Types.VARCHAR);
    cstmt.execute();

    // 获取输出参数
    String name = cstmt.getString(2);
    System.out.println(name);
    
    // 处理第一个结果集
    try (ResultSet resultSet = cstmt.getResultSet()) {
        while (resultSet.next()) {
            // 处理第一个结果集的数据
            System.out.println(resultSet.getString("name"));
        }
    }
    // 处理第二个结果集
    while (cstmt.getMoreResults()) {
        try (ResultSet resultSet = cstmt.getResultSet()) {
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id") + ":" + resultSet.getString("name") + ":" + resultSet.getInt("gender") + ":" + resultSet.getDate("birthdate") + ":" + resultSet.getString("major") + ":" + resultSet.getString("phone"));
            }
        }
    }
    cstmt.close();
}

小结

这篇文章可以学习到

1. insert values(…),(…)和executeBatch的区别
2. update时候and报错或者结果不达预期的原因
3. delete和truncate的区别
4. 查询其它session提交的数据
5. 修改当前session的数据同步到db
6. rewriteBatchedStatements对批量sql的优化
7. allowMultiQueries对多结果集的支持
8. 大数量使用流式查询和游标查询的区别

对jdbc的回顾就到这里, 以mysql数据库为例用一些简单的demo介绍了项目中常用和不常用的功能, 希望能唤起大家的一些记忆。接下来就进入mybatis环节。
参考:
https://www.jb51.net/database/294227l42.htm