Example Of Using Spring JDBC Execute Batch To Insert Multiple Rows Into A Database Table
Introduction
I recently needed to read several thousand objects from an XML file, create a collection of those objects, and then insert each object in the collection into a database table. Since my application uses Spring JDBC to interact with the database, I researched how to use the SimpleJDBCInsert class method executeBatch. The Spring online documentation for SimpleJDBCInsert does not have a good example of using this executeBatch, so I wrote this article to provide one.
SimpleJDBCInsert
Class SimpleJDBCInsert provides handy methods to easily insert records into a table using the meta data provided by the JDBC driver. I'd previously used this class when inserting a single Object's state into the database table. For this project I needed to insert many Objects into the database table.
Method executeBatch
The SimpleJDBCInsert class has a executeBatch method. This method is overloaded, with one version taking a parameter of type Map<String,Object>[] and the other version taking a parameter of type SqlParameterSource []. The example application shows how to use the executeBatch version that takes a parameter of type SqlParameterSource.
Interface SqlParameterSource
Type SqlParameterSource is an interface implemented by BeanPropertySqlParameterSource and MapSqlParameterSource. If the Object you want to insert into the table has instance fields that match the table column names then you can use the BeanPropertySqlParameterSource to tell the executeBatch method which instance field values to use for which table columns. In the example application the Person class has a personFirstName instance field which matches the table's personFirstName column. So executeBatch will use the value from that instance field when inserting the Object's state into the table.
The MapSqlParameterSource is used when you need to map the Object's instance fields to the table column names. For example, if the Person object had an instance field of firstName and the table column name was personFirstName, then I'd need to map the instance field firstName to the table column personFirstName.
Example Application
You can download the example application. It was created using Eclipse 3.6 and the m2 Maven Eclipse plugin. It is a standard Maven application. You should be able to import the zipped project file into Eclipse. If you don't use Eclipse, unzip the file and import it into your Java IDE as a Maven project (assuming your Java IDE supports Maven).
After downloading and unzipping the file open a command window and navigate to the unzipped folder. In that folder is the pom.xml file. You should be able to run mvn test and all tests should pass. You should see the log output in the JVM console. NOTE: If you don't have Maven installed see http://maven.apache.org
The example application uses an embedded HSQL database when running the test. If you're not familiar with using Spring 3's support for creating an embedded database, you may want to read this article.
In the example application is PersonDaoImpl class which demonstrates how to use the executeBatch method that takes a SqlParameterSource array as an argument. In PersonDaoImpl is method saveAllPersonsMap which uses a MapSqlParameterSource when calling executeBatch and method saveAllPersonsBean which uses a BeanPropertySqlParameterSource when calling executeBatch. Since the Person class's instance fields match the table column names, the example can use the BeanPropertySqlParameterSource.
The methods saveAllPersonsMap and saveAllPersonsBean are well-commented (see the JavaDoc comments in PersonDao interface) and should be easy to follow. If something's not clear post a comment below.
Summary
The SimpleJDBCInsert class's executeBatch method provides a handy way to insert multiple rows into a database table. Be sure to consult the online Spring 3.0 documentation and the Spring 3.0 API for more information.
