Java JDBC 導出 MySQL 數據完整實戰：分批導出、斷點續傳、多條件查詢

功能特性

• 分批導出：每批 50 條，避免大結果集 OOM
• 斷點續傳：記錄最後導出 ID/時間戳，支援增量同步
• 多模式查詢：按 ID 範圍 / 按時間窗口 兩種策略
• 資源安全：Connection/Statement/ResultSet 全鏈路 try-with-resources 風格管理

核心代碼結構

public class MySqlDataExporter {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=GBK";
    private static final String USER = "root";
    private static final String PASS = "mysql";
    private static final int BATCH_SIZE = 50;

    public static void main(String[] args) {
        // exportByIdRange(0);      // 全量導出
        exportByTimeWindow();       // 最近 1 小時增量導出
    }

    // ========= 模式一：按 ID 範圍分批導出 =========
    public static void exportByIdRange(int startId) {
        String sql = "SELECT * FROM t_test WHERE id > ? ORDER BY id ASC LIMIT ?";
        int lastId = startId;
        boolean hasMore = true;

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            while (hasMore) {
                ps.setInt(1, lastId);
                ps.setInt(2, BATCH_SIZE);
                try (ResultSet rs = ps.executeQuery()) {
                    hasMore = false;
                    while (rs.next()) {
                        hasMore = true;
                        int id = rs.getInt("id");
                        String title = rs.getString("title");
                        lastId = id;
                        writeLog(id + "\t" + title);
                    }
                }
            }
            saveCheckpoint("last_id.txt", String.valueOf(lastId));
        } catch (Exception e) { e.printStackTrace(); }
    }

    // ========= 模式二：按時間窗口增量導出 =========
    public static void exportByTimeWindow() {
        long windowStart = System.currentTimeMillis() - 60 * 60 * 1000; // 1小時前
        String sql = "SELECT * FROM t_test WHERE createTime > ? ORDER BY createTime ASC LIMIT ?";
        long lastTime = windowStart;
        boolean hasMore = true;

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            while (hasMore) {
                ps.setLong(1, lastTime);
                ps.setInt(2, BATCH_SIZE);
                try (ResultSet rs = ps.executeQuery()) {
                    hasMore = false;
                    while (rs.next()) {
                        hasMore = true;
                        long createTime = rs.getLong("createTime");
                        String title = rs.getString("title");
                        lastTime = createTime;
                        writeLog(title + " @ " + new Date(createTime));
                    }
                }
            }
        } catch (Exception e) { e.printStackTrace(); }
    }

    // ========= 輔助方法 =========
    private static void writeLog(String line) {
        try (FileWriter fw = new FileWriter("export.log", true);
             BufferedWriter bw = new BufferedWriter(fw)) {
            bw.write(line);
            bw.newLine();
        } catch (IOException e) { e.printStackTrace(); }
    }

    private static void saveCheckpoint(String file, String content) {
        try (FileWriter fw = new FileWriter(file)) {
            fw.write(content);
        } catch (IOException e) { e.printStackTrace(); }
    }
}

關鍵技術點解析

1. PreparedStatement 防注入：佔位符 ? 綁定參數，自動處理轉義
2. 游標式分頁：WHERE id > ? ORDER BY id LIMIT ? 避免 OFFSET 深分頁性能劣化
3. 檢查點持久化：導出完成後寫入 last_id.txt / last_time.txt，下次啟動讀取實現斷點續傳
4. 字符集指定：URL 參數 characterEncoding=GBK 匹配數據庫編碼，防中文亂碼
5. 資源自動關閉：JDK 7+ try-with-resources 確保無洩漏

生產環境強化清單

總結

本方案以 JDBC 原生 API + 分批游標 + 檢查點機制 實現了穩健的 MySQL 數據導出。核心模式 WHERE pk > ? ORDER BY pk LIMIT N 兼顧性能與簡潔，適用於千萬級表的增量同步、異構遷移、數據歸檔等場景。生產化時請務必接入連接池、並行分片與監控體系。