JDBC批量执行SQL语句

为什么要批量执行SQL？

用Java操作数据库时，频繁执行单条SQL不仅拖慢性能，还可能破坏事务原子性。比如同时插入多条用户数据，如果中途出错，数据可能只存一半！

环境准备

1. 添加MySQL驱动：在pom.xml添加依赖👇

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

2. 建表语句：先创建用户表📑

CREATE DATABASE user_db;
USE user_db;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

3. 建立数据库连接🔧

public Connection getConnection() throws SQLException {
    String url = "jdbc:mysql://localhost:3306/user_db?allowMultiQueries=true";
    String username = "username";
    String password = "password";
    return DriverManager.getConnection(url, username, password);
}

✨方法一_：_Statement对象直接执行

适用场景：快速执行多条非查询语句（如INSERT/UPDATE）

public void 批量插入数据() throws SQLException {
    String sql = "INSERT INTO users (name, email) VALUES ('小红', 'xiaohong@example.com');"
               + "INSERT INTO users (name, email) VALUES ('小明', 'xiaoming@example.com');";
    
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(sql); // 一键执行两条插入！
    }
}

⚠️注意：MySQL需在连接URL加allowMultiQueries=true参数，否则会报错！

✨方法二：批处理模式

优点：性能更高，适合大批量操作💨

public void 批处理插入() throws SQLException {
    try (Statement stmt = connection.createStatement()) {
        connection.setAutoCommit(false); // 关闭自动提交
        
        stmt.addBatch("INSERT INTO users VALUES ('小美', 'mei@example.com')");
        stmt.addBatch("INSERT INTO users VALUES ('小帅', 'handsome@example.com')");
        
        int[] results = stmt.executeBatch(); // 返回每条SQL影响的行数
        connection.commit(); // 统一提交
    }
}

✅*_测试技巧：用`SELECT COUNT(_)`验证插入数量，轻松排查问题！

✨方法三：存储过程YYDS

适合场景：复杂业务逻辑封装，一次调用完成多步操作🔮

1. 先创建存储过程：

DELIMITER //
CREATE PROCEDURE 批量添加用户()
BEGIN
    INSERT INTO users (name,email) VALUES ('莉莉', 'lili@example.com');
    INSERT INTO users (name,email) VALUES ('大壮', 'dz@example.com');
END //
DELIMITER ;

2. Java调用超简单：

public void 调用存储过程() throws SQLException {
    try (CallableStatement cstmt = connection.prepareCall("{CALL 批量添加用户()}")) {
        cstmt.execute(); // 一键触发双倍快乐！
    }
}

3. 测试

@Test
public void 测试() throws SQLException {
    boolean result = 调用存储过程(connection);
    assertTrue(result, "The stored procedure should execute successfully.");
    try (Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
          "SELECT COUNT(*) AS count FROM users WHERE name IN ('Eve', 'Frank')");) {
        resultSet.next();
        int count = resultSet.getInt("count");
        assertEquals(2, count, "Stored procedure should have inserted two records.");
    }
}

---

🎯性能对比

方法	优点	缺点
Statement	简单直观	安全性较低
批处理	执行效率高	需手动管理事务
存储过程	复用性强，减少网络开销	需要数据库权限

---

💬常见问题

Q：Oracle能用方法一吗？

A：不行哦！Oracle不支持多语句执行，推荐用批处理或存储过程~

Q：怎么处理查询结果？

A：用getMoreResults()遍历多个结果集:

public List<User> 结果集() throws SQLException {
    String sql = "SELECT * FROM users WHERE email = 'alice@example.com';" +
      "SELECT * FROM users WHERE email = 'bob@example.com';";
    List<User> users = new ArrayList<>();
    try (Statement statement = connection.createStatement()) {
        statement.execute(sql);
        do {
            try (ResultSet resultSet = statement.getResultSet()) {
                while (resultSet != null && resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String email = resultSet.getString("email");
                    users.add(new User(id, name, email));
                }
            }
        } while (statement.getMoreResults());
    }
    return users;
}

测试：

@Test
public void 测试() 
  throws SQLException {
    MultipleSQLExecution execution = new MultipleSQLExecution(connection);
    execution.executeMultipleStatements();
    List<User> users = execution.executeMultipleSelectStatements();
    // Here we verify that exactly two users are fetched and their names match the expected ones
    assertThat(users)
      .hasSize(2)
      .extracting(User::getName)
      .containsExactlyInAnyOrder("Alice", "Bob");
}