Spring JdbcTemplate Queries and Batches

Original chapter: JdbcTemplate. The text and examples below are preserved from the historical source; read the corrections above before using them.

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Instances of the JdbcTemplate class are threadsafe once configured so it can be safely inject this shared reference into multiple DAOs.

Section 15.1: Basic Query methods

Some of the queryFor* methods available in JdbcTemplate are useful for simple sql statements that perform CRUD operations.

Querying for Date

Source example · PDF page 60

String sql = "SELECT create_date FROM customer WHERE customer_id = ?";
int storeId = jdbcTemplate.queryForObject(sql, java.util.Date.class, customerId);

Querying for Integer

Source example · PDF page 60

String sql = "SELECT store_id FROM customer WHERE customer_id = ?";
int storeId = jdbcTemplate.queryForObject(sql, Integer.class, customerId);

Source example · PDF page 60

String sql = "SELECT store_id FROM customer WHERE customer_id = ?";
int storeId = jdbcTemplate.queryForInt(sql, customerId);                 //Deprecated in spring-
jdbc 4

Querying for String

Source example · PDF page 60

String sql = "SELECT first_Name FROM customer WHERE customer_id = ?";
String firstName = jdbcTemplate.queryForObject(sql, String.class, customerId);

Querying for List

Source example · PDF page 60

String sql = "SELECT first_Name FROM customer WHERE store_id =  ?";
List<String> firstNameList = jdbcTemplate.queryForList(sql, String.class, storeId);

Section 15.2: Query for List of Maps

Source example · PDF page 60

int storeId = 1;
DataSource dataSource = ... //
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "SELECT * FROM customer WHERE store_id = ?";
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql, storeId);

for(Map<String, Object> entryMap : mapList)
{
 for(Entry<String, Object> entry : entryMap.entrySet())
 {
      System.out.println(entry.getKey() + " / " + entry.getValue());
 }
 System.out.println("---");
}

Section 15.3: SQLRowSet

Source example · PDF page 61

DataSource dataSource = ... //
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "SELECT * FROM customer";
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);

while(rowSet.next())
{
 String firstName = rowSet.getString("first_name");
 String lastName = rowSet.getString("last_name");
 System.out.println("Vorname: " + firstName);
 System.out.println("Nachname: " + lastName);
 System.out.println("---”);
}

Source example · PDF page 61

String sql = "SELECT * FROM customer";
List<Customer> customerList = jdbcTemplate.query(sql, new RowMapper<Customer>()    {

 @Override
 public Customer mapRow(ResultSet rs, int rowNum) throws SQLException
 {
   Customer customer = new Customer();
   customer.setFirstName(rs.getString("first_Name"));
   customer.setLastName(rs.getString("first_Name"));
   customer.setEmail(rs.getString("email"));

   return customer;
 }

});

Section 15.4: Batch operations

JdbcTemplate also provides convenient methods to execute batch operations.

Batch Insert

Source example · PDF page 61

final ArrayList<Student> list = // Get list of students to insert..
String sql = "insert into student (id, f_name, l_name, age, address) VALUES (?, ?, ?, ?, ?)"
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {
       Student s = l.get(i);
       ps.setString(1, s.getId());
       ps.setString(2, s.getF_name());
       ps.setString(3, s.getL_name());
       ps.setInt(4, s.getAge());
       ps.setString(5, s.getAddress());
   }

   @Override
   public int getBatchSize() {
       return l.size();
   }
});

Batch Update

Source example · PDF page 62

final ArrayList<Student> list = // Get list of students to update..
String sql = "update student set f_name = ?, l_name = ?, age = ?, address = ? where id = ?"
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {
       Student s = l.get(i);
       ps.setString(1, s.getF_name());
       ps.setString(2, s.getL_name());
       ps.setInt(3, s.getAge());
       ps.setString(4, s.getAddress());
       ps.setString(5, s.getId());
   }

   @Override
   public int getBatchSize() {
       return l.size();
   }
});

There are further batchUpdate methods which accept List of object array as input parameters. These methods internally use BatchPreparedStatementSetter to set the values from the list of arrays into sql statement.

Section 15.5: NamedParameterJdbcTemplate extension of JdbcTemplate

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ( '?') arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

Source example · PDF page 62

DataSource dataSource = ... //
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

String sql = "SELECT count(*) FROM customer WHERE city_name=:cityName";
Map<String, String> params = Collections.singletonMap("cityName", cityName);
int count = jdbcTemplate.queryForObject(sql, params, Integer.class);