java批量处理数据(单线程分批查询更新)

 

User.java

package org.hxl.rds;

public class User {
    private long id;
    private String name;
    private String email;



    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\''+
        '}';
    }


    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

 

BatchDataUpdater.java

package org.hxl.rds;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BatchDataUpdater {
    // 数据库配置
    private static final String URL = "jdbc:mysql://192.168.1.14:3306/db_admin?rewriteBatchedStatements=true&useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private static final int BATCH_SIZE = 5000; // 每批处理量

    public static void BatchUpdate(){
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement updateStmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            conn.setAutoCommit(false); // 关闭自动提交

            // 1. 分批查询（使用主键范围分页）
            String selectSQL = "select id, name from user where id > ? order by id limit ?";
            selectStmt = conn.prepareStatement(selectSQL);

            // 2. 更新语句
            String updateSQL = "update user set name = ? where id = ?";
            updateStmt = conn.prepareStatement(updateSQL);

            long lastId = 0; // 起始ID
            int totalUpdated = 0;

            while (true) {
                // 设置查询参数
                selectStmt.setLong(1, lastId);
                selectStmt.setInt(2, BATCH_SIZE);
                rs = selectStmt.executeQuery();


                List<User> myNewValues = new ArrayList<>();

                int batchCount = 0;

                // 读取当前批次
                while (rs.next()) {
                    User myUser = new User();
                    long id = rs.getLong("id");
                    String name = rs.getString("name") + "hxlupdated";
                    myUser.setId(id);
                    myUser.setName(name);
                    myNewValues.add(myUser);

                    lastId = id; // 更新当前批次最大ID
                    batchCount++;
                }

                // 无数据时退出循环
                if (batchCount == 0) break;

                // 批量更新当前批次
                for (int i = 0; i < myNewValues.size(); i++) {
                    updateStmt.setString(1, myNewValues.get(i).getName());
                    updateStmt.setLong(2, myNewValues.get(i).getId());
                    updateStmt.addBatch(); // 添加到批处理
                }

                int[] updateCounts = updateStmt.executeBatch();
                conn.commit(); // 提交当前批次事务
                totalUpdated += updateCounts.length;

                System.out.printf("已更新 %d 条记录, 当前最后ID: %d%n", updateCounts.length, lastId);
            }

            System.out.println("更新完成! 总计更新: " + totalUpdated + " 条记录");

        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback(); // 出错回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (selectStmt != null) selectStmt.close();
                if (updateStmt != null) updateStmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {

        java.util.Date startDate = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startTime = sdf.format(startDate);
        System.out.println("开始时间：" + startTime);

        //update
        BatchUpdate();

        Date endDate = new Date();
        String endTime = sdf.format(endDate);
        System.out.println("结束时间：" + endTime);
    }
}