PolarDB:Import big data through a program

Understand JDBC batch insert behavior

By default, MySQL Connector/J sends each addBatch() call as a separate INSERT statement, even when you call executeBatch(). Setting rewriteBatchedStatements=true rewrites those into a single multi-row INSERT, which significantly reduces network round trips.

Without rewriteBatchedStatements=true, the driver sends one statement per row:

INSERT INTO post (postingType, id, ...) VALUES (1, 101, ...);
INSERT INTO post (postingType, id, ...) VALUES (2, 102, ...);
INSERT INTO post (postingType, id, ...) VALUES (3, 103, ...);

With rewriteBatchedStatements=true, the driver combines them:

INSERT INTO post (postingType, id, ...) VALUES (1, 101, ...), (2, 102, ...), (3, 103, ...);

The following table describes the JDBC connection parameters used in this example:

Run the import program

Add stackoverflow.csv to your project's classpath resources directory, then run the following Java program:

import java.io.*;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.*;

public class Main {

    public static void main(String[] args)
            throws IOException, URISyntaxException, ClassNotFoundException, SQLException {

        // Load the CSV file from the classpath
        URL url = Main.class.getClassLoader().getResource("stackoverflow.csv");
        File dataFile = new File(url.toURI());

        String sql = "INSERT INTO post (postingType, id, acceptedAnswer, parentId, score, tags) "
                   + "VALUES (?, ?, ?, ?, ?, ?)";
        int batchSize = 10000;

        try (
            Connection connection = getConnection(
                "<drds-host>",       // Replace with your DRDS endpoint
                3306,
                "<database-name>",   // Replace with your database name
                "<username>",        // Replace with your username
                "<password>"         // Replace with your password
            );
            BufferedReader br = new BufferedReader(new FileReader(dataFile))
        ) {
            PreparedStatement st = connection.prepareStatement(sql);
            long startTime = System.currentTimeMillis();
            int batchCount = 0;
            String line;

            while ((line = br.readLine()) != null) {
                String[] data = line.split(",");

                st.setInt(1, Integer.valueOf(data[0]));
                st.setInt(2, Integer.valueOf(data[1]));
                st.setObject(3, "".equals(data[2]) ? null : Integer.valueOf(data[2]));
                st.setObject(4, "".equals(data[3]) ? null : Integer.valueOf(data[3]));
                st.setObject(5, "".equals(data[4]) ? null : Integer.valueOf(data[4]));
                if (data.length >= 6) {
                    st.setObject(6, data[5]);
                }

                st.addBatch();

                // Flush every batchSize rows to keep memory usage bounded
                if (++batchCount % batchSize == 0) {
                    st.executeBatch();
                    System.out.printf("Inserted %d rows%n", batchCount);
                }
            }

            // Flush remaining rows
            if (batchCount % batchSize != 0) {
                st.executeBatch();
            }

            long elapsed = System.currentTimeMillis() - startTime;
            System.out.printf(
                "Done: %d rows in %d seconds (~%d TPS)%n",
                batchCount, elapsed / 1000, batchCount / (elapsed / 1000)
            );
        }
    }

    /**
     * Creates a JDBC connection to the DRDS database.
     *
     * @param host     DRDS endpoint (for example, xxxxx.drds.aliyuncs.com)
     * @param port     Port number (default: 3306)
     * @param database Database name
     * @param username Username
     * @param password Password
     */
    private static Connection getConnection(
            String host, int port, String database, String username, String password)
            throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        String url = String.format(
            "jdbc:mysql://%s:%d/%s?autoReconnect=true&socketTimeout=600000&rewriteBatchedStatements=true",
            host, port, database
        );
        return DriverManager.getConnection(url, username, password);
    }
}

Replace the following placeholders with your actual values:

Tune batch size for your workload

The example uses a batch size of 10,000 rows. A larger batch size reduces the number of round trips but increases memory usage. Start with 5,000–10,000 rows per batch and adjust based on observed TPS and heap usage.