您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java 實現 DbHelper 支持大數據量增刪改查

編輯：Java 相關知識

設計動機

項目早期常用 Hibernate/JPA 等全功能 ORM，但面對大數據量（百萬級+）批量寫入、複雜報表查詢時，ORM 存在：

實體管理開銷大（脏檢查、快照、一級緩存）
SQL 不可控，難以優化執行計劃
批量操作需手動 flush/clear，易 OOM

輕量級 DbHelper 直接封裝 JDBC，提供：參數化查詢、批量預編譯、流式結果集、事務模板，適合大數據量場景。

核心類設計

public class DbHelper {
    private String sql;
    private List<Object> params;           // 單條參數
    private List<List<Object>> batchParams; // 批量參數
    private Connection conn;

    public DbHelper() {
        this.conn = getConnection(); // 從配置文件讀取屬性構建連接
    }

    // 配置文件 db.properties
    // driver_class=com.mysql.cj.jdbc.Driver
    // driver_url=jdbc:mysql://host:3306/db?useSSL=false&serverTimezone=UTC
    // database_user=user
    // database_password=pwd
    private Connection getConnection() { ... }

    // 資源關閉
    private void closeAll(Connection c, Statement s, ResultSet rs) { ... }

    // 單條查詢
    public Result executeQuery() { ... }

    // 單條增刪改
    public int executeUpdate() { ... }

    // 批量增刪改（PreparedStatement.addBatch）
    public int[] executeBatch() { ... }

    // 批量執行任意 SQL 字符串（Statement.addBatch）
    public int[] executeBatchSql(List<String> sqls) { ... }
}

關鍵實現細節

1. 參數綁定

private void bindParams(PreparedStatement ps, List<Object> params) throws SQLException {
    for (int i = 0; i < params.size(); i++) {
        ps.setObject(i + 1, params.get(i));
    }
}

2. 批量預編譯執行

public int[] executeBatch() {
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (List<Object> paramSet : batchParams) {
            bindParams(ps, paramSet);
            ps.addBatch();
        }
        return ps.executeBatch(); // 返回各條影響行數
    } catch (SQLException e) {
        throw new RuntimeException("批量執行失敗", e);
    }
}

3. 流式結果集處理大查詢

public void executeLargeQuery(Consumer<ResultSet> handler) {
    try (Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
        stmt.setFetchSize(Integer.MIN_VALUE); // MySQL 流式讀取關鍵設置
        try (ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                handler.accept(rs); // 逐行處理，不佔用堆內存
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("大查詢失敗", e);
    }
}

4. 事務模板

public  T executeInTransaction(Function action) {
    boolean originalAutoCommit = conn.getAutoCommit();
    try {
        conn.setAutoCommit(false);
        T result = action.apply(conn);
        conn.commit();
        return result;
    } catch (Exception e) {
        conn.rollback();
        throw new RuntimeException("事務失敗", e);
    } finally {
        conn.setAutoCommit(originalAutoCommit);
    }
}

使用示例

單條插入

DbHelper helper = new DbHelper();
helper.setSql("INSERT INTO tb_log(type, content, create_time) VALUES (?, ?, NOW())");
helper.setParams(Arrays.asList("ERROR", "空指針異常"));
helper.executeUpdate();

批量插入 10 萬條

DbHelper helper = new DbHelper();
helper.setSql("INSERT INTO tb_user(name, email, status) VALUES (?, ?, ?)");
List> batch = new ArrayList<>();
for (int i = 0; i < 100000; i++) {
    batch.add(Arrays.asList("user" + i, "user" + i + "@example.com", 1));
    if (batch.size() == 1000) { // 分批提交，避免參數集過大
        helper.setBatchParams(new ArrayList<>(batch));
        helper.executeBatch();
        batch.clear();
    }
}
if (!batch.isEmpty()) {
    helper.setBatchParams(batch);
    helper.executeBatch();
}

大數據量導出

DbHelper helper = new DbHelper();
helper.setSql("SELECT id, name, email FROM tb_user WHERE status = 1");
helper.executeLargeQuery(rs -> {
    // 寫入 CSV、推送到消息隊列、增量同步 ES 等
    csvWriter.write(rs.getLong(1), rs.getString(2), rs.getString(3));
});

事務保證一致性

helper.executeInTransaction(conn -> {
    try (PreparedStatement ps1 = conn.prepareStatement("UPDATE account SET balance = balance - ? WHERE id = ?");
         PreparedStatement ps2 = conn.prepareStatement("UPDATE account SET balance = balance + ? WHERE id = ?")) {
        ps1.setBigDecimal(1, amount); ps1.setLong(2, fromId);
        ps2.setBigDecimal(1, amount); ps2.setLong(2, toId);
        ps1.executeUpdate();
        ps2.executeUpdate();
        return true;
    }
});

性能優化建議

分批提交：批量參數集控制在 500-2000 條，避免單次 executeBatch 過大導致網絡阻塞或數據庫鎖升級。
關閉自動提交：批量操作前 conn.setAutoCommit(false)，提交後恢復。
使用流式結果集：MySQL setFetchSize(Integer.MIN_VALUE)、PostgreSQL setFetchSize(1000) + 游標，避免全量載入內存。
預編譯復用：同一 SQL 重複執行時復用 PreparedStatement（需連接池支持 Statement Cache）。
索引與分區：大表必須按查詢模式建立覆蓋索引，時間序列表按月分區。