循环中执行sql时，一步教你怎么优化

对于insert语句的情况

1. 单个插入示例

首先，假设你有一个循环，每次插入一条记录：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class SingleInsertExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        String insertSQL = "INSERT INTO table_name (column1, column2) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            for (Record record : records) {
                pstmt.setString(1, record.getValue1());
                pstmt.setString(2, record.getValue2());
                pstmt.executeUpdate(); // 单个执行
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. 批量插入示例

将上述单个插入改为批量插入：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchInsertExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        String insertSQL = "INSERT INTO table_name (column1, column2) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            conn.setAutoCommit(false); // 关闭自动提交

            for (Record record : records) {
                pstmt.setString(1, record.getValue1());
                pstmt.setString(2, record.getValue2());
                pstmt.addBatch(); // 添加到批处理

                // 每1000条执行一次
                if (++count % 1000 == 0) {
                    pstmt.executeBatch(); // 执行批处理
                }
            }
            pstmt.executeBatch(); // 执行剩余的批处理
            conn.commit(); // 提交事务
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback(); // 回滚事务
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
        }
    }
}

3. 代码分析

数据库连接：使用DriverManager.getConnection连接到数据库。
PreparedStatement：使用PreparedStatement来预编译SQL语句，提供更好的性能和安全性。
事务管理：通过conn.setAutoCommit(false)关闭自动提交，手动控制事务。这样可以将多个插入操作作为一个事务处理，以提高性能和一致性。
批处理操作：使用addBatch()方法将每条插入语句添加到批处理。然后使用executeBatch()执行所有的插入操作。
控制批次大小：在示例中，每1000条记录执行一次批处理。根据你的数据量和数据库性能，可以调整这个值。
错误处理：在catch块中捕获SQLException，并在出现错误时回滚事务。

4. 性能考虑

合适的批量大小：批量大小的选择可能影响性能。一般来说，1000到5000条记录的批处理是一个较为理想的范围，但具体取决于你的数据库和网络性能。
监控和调优：执行批量插入后，可以监控数据库的性能，使用数据库的日志和工具进行性能分析和调优。

5. 示例的记录类

你可能会使用一个简单的Record类来代表待插入的数据：

public class Record {
    private String value1;
    private String value2;

    // 构造函数
    public Record(String value1, String value2) {
        this.value1 = value1;
        this.value2 = value2;
    }

    // Getter方法
    public String getValue1() {
        return value1;
    }

    public String getValue2() {
        return value2;
    }
}

总结

通过使用Java的PreparedStatement结合批处理操作，可以显著提高数据插入的性能。批量插入不仅减少了数据库的交互次数，还能提高数据处理的效率。在实现时，请注意事务管理和错误处理，以确保数据的一致性和完整性。

二、对于update语句同样可以这么处理：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchUpdateExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        String updateSQL = "UPDATE table_name SET column1 = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {

            conn.setAutoCommit(false); // 关闭自动提交

            for (Record record : records) {
                pstmt.setString(1, record.getNewValue());
                pstmt.setInt(2, record.getId());
                pstmt.addBatch(); // 添加到批处理

                // 每1000条执行一次
                if (++count % 1000 == 0) {
                    pstmt.executeBatch(); // 执行批处理
                }
            }
            pstmt.executeBatch(); // 执行剩余的批处理
            conn.commit(); // 提交事务
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback(); // 回滚事务
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
        }
    }
}