Batch updates

25 December 2007

Updating a record from Java using executeUpdate() method of Statement object is used commonly by JDBC programmers. Thing to note is that this is a costly activity in terms of time, as a database trip is made. As a programmer/developer, our aim should be to write efficient Java code. In this post, I will write about how you can efficiently make several updates.

Batch update allows us to execute several update statements in a batch. It means only one database trip will be make, thus saving processing time. Batch updates won’t work on select statements.

Time for an example. I want to update 3 records in a Postge SQL database. So 3 update statements are require. I will use batch to execute there.

Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
System.out.println("getConnection: url="+url);
conn = DriverManager.getConnection(url, dbuser, dbpass);
 
Statement stmt = conn.createStatement();
stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 1);
stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 2);
stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 3);
int[] updateCount = stmt.executeBatch();

Things look simple here. I used addBatch(…) method of Statement object to create the batch. A batch can have as many as you like statements. Once the batch is ready, executeBatch() method is use to execute it. The method executeBAtch() return int array which contains update count of each command. For example, at zero index, you will find the no of records updated by first update statement in the batch and so on.

Happy coding.

Related Posts: