View RSS Feed

My Java Tips

Batch updates

Rate this Entry
by , 11-11-2011 at 05:41 PM (1209 Views)
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 Postgre SQL database. So 3 update statements are require. I will use batch to execute there.

Java Code:
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.

Submit "Batch updates" to Facebook Submit "Batch updates" to Digg Submit "Batch updates" to del.icio.us Submit "Batch updates" to StumbleUpon Submit "Batch updates" to Google

Categories
JDBC

Comments