3.7. JDBC Batch Processing

Execute multiple statements together to improve performance:

JDBC Batch Processing allows you to combine several SQL statements into a single batch execution. By doing this, you reduce the number of network calls between your application and the database, which improves performance. Instead of executing individual SQL commands one by one, which can be slow, you group them together and execute them all at once.

Think of this like sending a package to a friend. Instead of sending multiple small envelopes over time (which is slower and costs more), you bundle everything into one larger box and send it all at once for efficiency.

PreparedStatement ps = con.prepareStatement("INSERT INTO logs VALUES (?, ?)");

The first step in batch processing is creating a PreparedStatement. This statement is compiled and ready to execute multiple times with different parameters each time. The placeholders (?) in the SQL statement allow us to set values dynamically as we add to the batch.

Imagine you are creating a recipe that requires the same set of ingredients multiple times. You prepare your ingredients in bulk once (like a batch) and use them as needed rather than preparing them from scratch each time you want to cook a dish.

for(int i=1; i<=100; i++) {
  ps.setInt(1, i);
  ps.setString(2, "Log " + i);
  ps.addBatch();
}

In this code snippet, we are using a loop to set parameters for the PreparedStatement and adding each complete set of parameters to the batch. The setInt method assigns an integer value to the first placeholder, and the setString method assigns a string to the second. addBatch() is called to indicate that this set of values should be included in the batch execution.

Consider a factory assembly line where each worker assembles parts into a product. Instead of processing each product individually, they prepare multiple products simultaneously and send them for final inspection all at once.

ps.executeBatch();

Finalizing the batch processing involves calling executeBatch(), which sends all the batched statements to the database at once. This method executes all of the commands you have added in the batch, making it efficient as it minimizes the interaction with the database server.

Think of it as a teacher collecting assignment papers at the end of the class. Instead of taking each paper one by one throughout the session, the teacher waits until everyone is done and collects all papers in one go. This saves time and effort.

--