Wednesday, May 25, 2011

Spring – SimpleJdbcTemplate batchUpdate() example

In some cases, you may required to insert a batch of records into the database. If you call a single insert method for every record, the SQL create statement will be compiled repeatedly and causing your system very slow.
In Spring JDBC framework, you can use JdbcTemplate class batchUpdate() template to perform the batch insert operations. With this method, the statement is compiled only once and executed multiple times.
Now consider the function insertBatch() present in your dao, called FlightDao, which takes list of flights as parameter and batch inserts them in database.
Click here to know about this flight database and its corresponding pojo Flight class.
This DAO has simpleJdbcTemplate as its field, on which batchUpdate() function is called. See here for such DAO creation in database. This link will tell you how to use SimpleJdbcTemplate to create dao and use it for insert, update or retrieve operations.
In the dao, initialize the field SimpleJdbcTemplate :
SimpleJdbcTemplate simpleJdbcTemplate = new 
SimpleJdbcTemplate(dataSource);

Now use this field in this function:
 

public void insertBatch(final List<Flight> flightList){
String sql = "INSERT INTO flights_test " +
"(flight_no, carrier, kahase,kahatak) VALUES (?, ?, ?,?)";

List<Object[]> paramList = new ArrayList<Object[]>();

for (Flight flight : flightList) {
paramList.add(new Object[] {flight.getFlightNo(),
flight.getCarrier(), flight .getFrom(), flight.getTo()}
);
}
simpleJdbcTemplate.batchUpdate(sql, parameters);
}


Calling the above function:


Flight flight1 = new Flight("JL-220", "Jet 
Airways"
,"Mumbai","Jaipur");
Flight flight2= new Flight("KL-
202", "Kingfisher","Jaipur","Agra");
Flight flight3= new Flight("AI-220", "Air
India"
,"Agra","Delhi");

List<Flight> flights = new ArrayList<Flight>();
flights.add(flight1);
flights.add(flight2);
flights.add(flight3);

flightDao.insertBatch(flights);

1 comment:

  1. Thanks for your post, I have one question what is "parameters" argument?

    ReplyDelete

Chitika