Querying with JdbcTemplate

This tutorial is about the JdbcTemplate querying type. Generally, there are 3 types of query, which is SimpleTypes, Generic Maps and Domain Objects.

Simple Types

The sample code below are using queryForObject() method with no binding variable. This example show that queryForObject method accepts 2 arguments, first argument is String value (we passed in the SQL statement), the second argument is Class (result expected to match). This kind of query is useful when expected to be single row/column query and the result will auto map to the object type.

public Date getLastJoinDate() {
     String sql = "select max(join_date) from contact";
     return jdbcTemplate.queryForObject(sql, Date.class);
}

public long countTotalContact() {
     String sql = "select count(*) from contact";
     return jdbcTemplate.queryForObject(sql, Long.class);
}

Example queryForObject with binding parameter

public String getUsernameById(long id) {
     String sql = "select username from user where id = ?";
     return jdbcTemplate.queryForObject(sql, 
                 new Object[] {id}, String.class);
}

public String findByAgeAndFirstName(int age, String firstname) {
     String sql = "select username from contact where age = ? and firstname = ?";
     return jdbcTemplate.queryForObject(sql, String.class, age, firstname);
}

Generic Query

When talking about generic query, we remember to these 2 methods:

• queryForMap – Expect single row result
• queryForList – Expect list of results

The sample code below is using queryForMap() to query a single result from the database. As you can see from the method name, it map the result in key and value form and return as a Map.

public Map getContactById(long id) 
{
     String sql = "select * from contact where id = ?";
     return jdbcTemplate.queryForMap(sql, id);
}

result output format:
{
    id = 1,
    first_name = "xx",
    lastname = "yy",
    birth_date = "yyyy-mm-dd"
}

// convert to object
public Contact getContactById(long id)
{
    String sql = "select * from contact where id = ?";
    Map map = jdbcTemplate.queryForMap(sql, id);
    Contact contact = new Contact();
		
    contact.setId(Long.valueOf((Integer)map.get("id")));
    contact.setBirthDate((Date)map.get("birth_date"));
    contact.setFirstName((String)map.get("first_name"));
    contact.setLastName((String)map.get("last_name"));
    return contact;
}

The sample code below is using queryForList() to query multiple rows result from the database. As you can see from the method name, it return a list of the map result in key and value form.

public List getContacts() 
{
     String sql = "select * from contact";
     return jdbcTemplate.queryForList(sql);
}

result output format:
List : {
   0 : {
       id = 1,
       first_name = "xx",
       lastname = "yy",
       birth_date = "yyyy-mm-dd"
   },
   1 : {
       id = 2,
       first_name = "aa",
       lastname = "bb",
       birth_date = "yyyy-mm-dd"
   }
}

Domain Objects

Similiar to ORM concept, map the ResultSet into domain object with the following approach.

• RowMapper – Use this approach when query one or more rows and map with corresponding object.
• RowCallbackHandler – Use this approach when no return object, such as write into report, xml, file and etc.
• ResultSetExtractor – Use this approach when multiple rows map into a single object.

RowMapper

public class ContactRowMapper implements RowMapper<Contact> {

  public Contact mapRow(ResultSet rs, int rowNum) throws SQLException 
  {
      Contact contact = new Contact();
      contact.setId(rs.getLong("id"));
      contact.setFirstName(rs.getString("first_name"));
      contact.setLastName(rs.getString("last_name"));
      contact.setBirthDate(rs.getDate("birth_date"));
      return contact;
  }
}

DAO

/*
* When using RowMapper with single object query, 
* Use JdbcTemplate method's queryForObject()
*/
public Contact findById(Long id) {
   String sql = "select * from contact where id = ?";
   Contact contact = jdbcTemplate.queryForObject(sql, new ContactRowMapper(), id);
   return contact;
}

/*
* When using RowMapper with muliple object query, 
* Use JdbcTemplate method's queryForObject()
*/
public List<Contact> findAll() {
    String sql = "select * from contact";
    List<Contact> list = jdbcTemplate.query(sql, new ContactRowMapper());
    return list;
}

RowCallbackHandler

RowCallBackHandler is the primary interface, it process the current row of the ResultSet. The query() method loop the result for you and call RowCallbackHandler for each row. The processRow() will be called once for each row of the return ResultSet.

public List<Contact> findAll() {
    String sql = "select * from contact";
    final List<Contact> list = new ArrayList<Contact>();
		
    jdbcTemplate.query(sql, new Object[]{}, 
        new RowCallbackHandler()	{
            public void processRow(ResultSet rs) throws SQLException {
                Contact contact = new Contact();
                contact.setId(rs.getLong("id"));
                contact.setFirstName(rs.getString("first_name"));
                contact.setLastName(rs.getString("last_name"));
                contact.setBirthDate(rs.getDate("birth_date"));
                list.add(contact);
            }
        });
        return list;
}


public Contact findById(Long id) {
      String sql = "select * from contact where id = ?";
      final Contact contact = new Contact();
      jdbcTemplate.query(sql,new Object[] {id}, 
            new RowCallbackHandler() {
                 public void processRow(ResultSet rs) throws SQLException {
                       contact.setId(rs.getLong("id"));
                       contact.setFirstName(rs.getString("first_name"));
                       contact.setLastName(rs.getString("last_name"));
                       contact.setBirthDate(rs.getDate("birth_date"));
                }
	   });
      return contact;
}

ResultSetExtractor

Batch process entire ResultSet at once and require manual iterate the ResultSet to map into single object. Example, when we have one to many or one to one relationship mapping.

public class CountryWithStateExtractor implements ResultSetExtractor<List<Country>> {

    public List<Country> extractData(ResultSet rs) throws SQLException {
		
        Map<String, Country> map = new HashMap<String, Country>();
        Country country = null;
		
	while(rs.next()) {
			
	   String countryCode = rs.getString("country_code");
	   country = map.get(countryCode);
			
	   if ( country == null ) {
	        country = new Country();
		country.setCode(countryCode);
		country.setName(rs.getString("name"));
		country.setStates(new ArrayList<State>());
				
		map.put(id, country);
	   }
			
	   //continue process
	   Long stateCode = rs.getLong("state_code");
	   if ( stateCode > 0 ) {
		 State state = new State();
		 state.setCode(stateCode);
		 state.setCountryCode(countryCode);
		 state.setName(rs.getString("state_name"));
				
		 country.getStates().add(state);
	   }
      }
      return new ArrayList<Country> (map.values());
   }
}

DAO

public List<Country> findCountryWithStates() {
		
    String sql = " select c.code, c.name " + 
	         " s.code as state_id, s.state_name " +
		 " from country c left join state s " + 
                 " on c.code = s.country_code";
		
    return getJdbcTemplate().query(sql, 
           new CountryWithStateExtractor());
}