DB2JsonStreaming.java

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import javax.json.Json;
import javax.json.stream.JsonGenerator;

public class DatabaseToJson {

    private static final int BATCH_SIZE = 1000;

    public static void main(String[] args) throws SQLException, IOException {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "mypassword";
        String query = "SELECT * FROM mytable";

        Connection conn = DriverManager.getConnection(url, username, password);
        PreparedStatement stmt = conn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = stmt.executeQuery();

        ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

        try (Writer writer = new BufferedWriter(new FileWriter("data.json"))) {
            JsonGenerator gen = Json.createGenerator(writer);
            gen.writeStartArray();

            while (rs.next()) {
                executor.execute(() -> {
                    try {
                        gen.writeStartObject();
                        gen.write("id", rs.getInt("id"));
                        gen.write("name", rs.getString("name"));
                        // add more columns here
                        gen.writeEnd();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                });
            }

            executor.shutdown();
            while (!executor.isTerminated()) {
                // wait for all tasks to finish
            }

            gen.writeEnd();
            gen.close();
        }

        rs.close();
        stmt.close();
        conn.close();
    }
}