Java PreparedStatement与批处理

PreparedStatement预编译SQL，提升性能并防止SQL注入；批处理批量执行SQL，大幅提升效率。

PreparedStatement优势

与Statement对比

防止SQL注入

// Statement危险：SQL注入
Statement stmt = conn.createStatement();
String sql = "SELECT * FROM users WHERE name = '" + name + "'";
// name = "admin' OR '1'='1" 可注入！

ResultSet rs = stmt.executeQuery(sql);

// PreparedStatement安全：参数化
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM users WHERE name = ?");
pstmt.setString(1, name);  // 参数安全设置
ResultSet rs = pstmt.executeQuery();

预编译性能优势

// Statement：每次编译SQL
for (int i = 0; i < 1000; i++) {
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("INSERT INTO users VALUES(" + i + ", 'user')");
    stmt.close();
}

// PreparedStatement：SQL预编译，复用
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO users VALUES(?, ?)");
for (int i = 0; i < 1000; i++) {
    pstmt.setInt(1, i);
    pstmt.setString(2, "user" + i);
    pstmt.executeUpdate();
}
pstmt.close();

PreparedStatement使用

基本用法

// 查询
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT id, name, age FROM users WHERE age > ?");
pstmt.setInt(1, 18);
ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");
}

// 插入
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO users(name, age) VALUES(?, ?)");
pstmt.setString(1, "张三");
pstmt.setInt(2, 25);
int rows = pstmt.executeUpdate();

// 更新
PreparedStatement pstmt = conn.prepareStatement(
    "UPDATE users SET age = ? WHERE name = ?");
pstmt.setInt(1, 26);
pstmt.setString(2, "张三");
int rows = pstmt.executeUpdate();

// 删除
PreparedStatement pstmt = conn.prepareStatement(
    "DELETE FROM users WHERE id = ?");
pstmt.setInt(1, 100);
int rows = pstmt.executeUpdate();

参数类型设置

PreparedStatement pstmt = conn.prepareStatement(sql);

// 基本类型
pstmt.setInt(1, 100);
pstmt.setLong(2, 1000L);
pstmt.setDouble(3, 99.5);
pstmt.setFloat(4, 88.5f);
pstmt.setBoolean(5, true);
pstmt.setShort(6, 10);
pstmt.setByte(7, 1);

// 字符串与日期
pstmt.setString(8, "张三");
pstmt.setDate(9, new java.sql.Date(System.currentTimeMillis()));
pstmt.setTime(10, new java.sql.Time(System.currentTimeMillis()));
pstmt.setTimestamp(11, new java.sql.Timestamp(System.currentTimeMillis()));

// 二进制
pstmt.setBlob(12, inputStream);
pstmt.setBytes(13, byteData);

// NULL值
pstmt.setNull(14, Types.VARCHAR);
pstmt.setObject(15, null);

批处理操作

addBatch批量执行

PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO users(name, age) VALUES(?, ?)");

for (int i = 0; i < 1000; i++) {
    pstmt.setString(1, "user" + i);
    pstmt.setInt(2, i % 100);
    pstmt.addBatch();  // 添加到批处理队列

    // 每100条执行一次
    if (i % 100 == 0) {
        pstmt.executeBatch();  // 执行批处理
        pstmt.clearBatch();     // 清空队列
    }
}

pstmt.executeBatch();  // 执行剩余
pstmt.close();

executeBatch返回值

int[] results = pstmt.executeBatch();

// 返回值含义
// -2：成功，影响行数未知
// >0：成功，影响行数
// -3：执行失败

for (int result : results) {
    if (result >= 0) {
        System.out.println("成功，影响" + result + "行");
    } else if (result == -3) {
        System.out.println("执行失败");
    }
}

MySQL批处理优化

// MySQL默认不启用批处理优化，需添加参数
// rewriteBatchedStatements=true

String url = "jdbc:mysql://localhost:3306/db?rewriteBatchedStatements=true";

// 效果：将多条INSERT合并成一条
// INSERT INTO users VALUES(?,?),(?,?),(?,?)...
// 大幅提升批量插入性能

批处理最佳实践

public void batchInsert(List<User> users) throws SQLException {
    Connection conn = dataSource.getConnection();
    try {
        conn.setAutoCommit(false);  // 关闭自动提交

        PreparedStatement pstmt = conn.prepareStatement(
            "INSERT INTO users(name, age) VALUES(?, ?)");

        int count = 0;
        for (User user : users) {
            pstmt.setString(1, user.getName());
            pstmt.setInt(2, user.getAge());
            pstmt.addBatch();

            if (++count % 100 == 0) {
                pstmt.executeBatch();
                pstmt.clearBatch();
            }
        }

        pstmt.executeBatch();
        conn.commit();  // 提交事务

    } catch (SQLException e) {
        conn.rollback();  // 回滚
        throw e;
    } finally {
        conn.setAutoCommit(true);
        conn.close();
    }
}

批处理性能对比

// 测试代码
// 单条执行
for (User user : users) {
    pstmt.setString(1, user.getName());
    pstmt.setInt(2, user.getAge());
    pstmt.executeUpdate();
}

// 批处理
for (User user : users) {
    pstmt.setString(1, user.getName());
    pstmt.setInt(2, user.getAge());
    pstmt.addBatch();
}
pstmt.executeBatch();

// rewriteBatchedStatements=true后
// MySQL将批处理重写为多值INSERT，性能提升10倍+

常见问题

参数索引错误

// 错误：索引从0开始
pstmt.setString(0, "张三");  // 异常！

// 正确：索引从1开始
pstmt.setString(1, "张三");

类型不匹配

// 错误：类型不匹配
pstmt.setInt(1, Integer.parseInt("abc"));  // 异常！

// 正确：类型匹配
pstmt.setString(1, "abc");

批处理失败处理

// 批处理可能部分失败
try {
    int[] results = pstmt.executeBatch();
} catch (BatchUpdateException e) {
    int[] successResults = e.getUpdateCounts();  // 成功的部分
    // 处理失败部分
}

注意事项

PreparedStatement参数索引从1开始

批处理配合rewriteBatchedStatements大幅提升性能

大批量操作应分批执行，避免内存溢出

批处理应在事务中执行，失败可回滚

关闭Statement/PreparedStatement释放资源

要点总结

1. PreparedStatement防止SQL注入，参数化安全
2. PreparedStatement预编译SQL，复用提升性能
3. addBatch批量添加，executeBatch批量执行
4. MySQL rewriteBatchedStatements=true重写批处理，性能提升10倍+
5. 批处理应在事务中执行，失败可回滚