JDBC Batch INSERT and UPDATE example in Java with PreparedStatement

JDBC API in Java allows program to batch insert and update data into database, which tends to provide better performance by simple virtue of fact that it reduce lot of database round-trip which eventually improves overall performance. In fact it�s one of JDBC best practices to insert and update data in batches. For those who doesn�t know what is batch insert and update, Java provides several ways to execute SQL queries, one of them is JDBC batch insert and update, on which instead of executing sql query one by one using either Statement or PreparedSatement, you execute query in batch and send a batch of query to database for execution instead of single query. Since multiple queries are combined into batch and one batch is sent to database instead of individual queries, it reduce database round trip by factor of batch size. Batch size can be anything but needs to be decided carefully. JDBC specification supports upto 100 but individual database e.g. Oracle, MySQL, Sybase or SQL Server has there own limit on maximum batch size, , normal jdbc batch size ranges from 50 to 100. JDBC API provides addBatch() method to add queries into batch and than later execute them using executeBatch() method. Both Statement and PreparedStatement can be used to execute batch queries in Java. By the way batch insert and update also provide performance boost to Data access Object or DAO layer, as discussed in our last post 4 ways to improve Performance of JDBC applications.

Read more �