Efficient way to do batch INSERTS with JDBC

java

jdbc-tuning

database-performance

transaction-management

byAlex Kataev·Jan 4, 2025

Boost JDBC INSERT efficiency with a combo move—batch processing. Group INSERTS using addBatch() and launch them off in one shot with executeBatch(). For instance:

String sql = "INSERT INTO myTable (col1, col2) VALUES (?, ?)";
try (Connection conn = myDataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {

    conn.setAutoCommit(false); // Yo estoy in control now, muchachos!
    for (DataItem item : dataList) {
        pstmt.setInt(1, item.getCol1()); // Set the first placeholder as item's Col1
        pstmt.setString(2, item.getCol2()); // The second one gets Col2
        pstmt.addBatch(); // Into the Batch-Pot you go, my little query!
    }
    pstmt.executeBatch(); // Release the Kraken, I mean Runtime!
    conn.commit(); // Seal the deal!
}

Retune the batch size to perfectly harmonize with your DB's sweet spot. And never forget, setAutoCommit(false) ensures atomicity—goes full Avengers: it's all or nothing.

Trimming time off transactions

Unleash the beast of power by reducing network latency. Transporting packets over network calling home is an expensive affair. Stick with one round-trip per batch, if possible—time is money!

Reuse, Recycle, PreparedStatement

Regenerating a PreparedStatement from nothingness every time can drain resources. Reuse them across batches to lighten the load. After the great executeBatch(), clean the slate with pstmt.clearParameters(). New batch, new beginnings.

Testing, tuning, triumph

Neither all batches nor all databases are created equal. Profile your database, twiddle with batch sizes until you hit the jackpot. Go in blind with 100 records per batch, but adjust it to your specific workload and environment.

Advanced JDBC tuning

For those looking to squeak out the last bit of performance juice, some JDBC drivers support driver-specific configurations:

MySQL: Rewrite those batches

MySQL can play with rewriteBatchedStatements=true, which gives the queries a makeover, potentially zapping them with a dose of quicksilver.

Oracle: Fine-grained control

Oracle users, you have specific methods like OraclePreparedStatement.setExecuteBatch(). Smoothen out the idiosyncrasies; it’s you and your database against the world!

All about transactions

Transaction management is more crucial than morning coffee; mess up and your day goes downhill! By using setAutoCommit(false) and transactions, you're in the driver's seat ─ the data will remain consistent and in a known state at all costs.

Exception handling: Choose your exits

Exceptions or errors are like sudden detours. Be ready to rollback the transaction. It'll safeguard your data like a bouncer at a nightclub—no partially committed data leaks here!

Set levels of isolation

Consider transaction isolation. It locks away dirty reads, non-repeatable reads, and phantom reads, ensuring transactions are smoother than a freshly waxed surfboard.

Extra tips

Make the most of your JDBC connection with these techniques:

Save on memory

Going to dump large Beverly Hills-style text or binary objects? Use stream-based methods like setBlob() or setClob() to prevent memory bloating.

Cache me if you can

Many JDBC pools have a trick up their sleeve—statement caching! Reusing statement objects minimizes the expensive business of statement creation and parsing.

Prevent empty trips

Before you insert, stop... and think! Are these queries redundant? If you need integrity checks, consider constraints or triggers within the database itself.

explain-codes / Java / Efficient way to do batch INSERTS with JDBC

Linked

How to do a batch insert in MySQL



Preparedstatement IN clause alternatives?



Running a .sql script using MySQL with JDBC



Increasing the Command Timeout for SQL command



Multiple queries executed in java in a single statement

