I am studying for the Spring Core certification and I have some doubts about how Spring handle the JDBC queries:
So I know that I can obtain data from my DB tables in various ways depending on the type of data that I expect to obtain:
1) Query for simple type (as an int, a long or a String): I use the queryForObject() method of the jdbcTemplate class, something like it:
String sql = "SELECT count(*) FROM T_REWARD";
int rowsNumber = jdbcTemplate.queryForObject(sql, Integer.class);
String sql = "select * from T_REWARD where CONFIRMATION_NUMBER = ?";
Map<String, Object> values = jdbcTemplate.queryForMap(sql,confirmation.getConfirmationNumber());
String sql = “select * from PERSON”;
return jdbcTemplate.queryForList(sql);
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum)
throws SQLException;
}
public Restaurant findByMerchantNumber(String merchantNumber) {
String sql = "select MERCHANT_NUMBER, NAME, BENEFIT_PERCENTAGE, BENEFIT_AVAILABILITY_POLICY from T_RESTAURANT where MERCHANT_NUMBER = ?";
return jdbcTemplate.queryForObject(sql, new RestaurantRowMapper(), merchantNumber);
class RestaurantRowMapper implements RowMapper<Restaurant> {
public Restaurant mapRow(ResultSet rs, int i) throws SQLException {
return mapRestaurant(rs);
}
}
private Restaurant mapRestaurant(ResultSet rs) throws SQLException {
// get the row column data
String name = rs.getString("NAME");
String number = rs.getString("MERCHANT_NUMBER");
Percentage benefitPercentage = Percentage.valueOf(rs.getString("BENEFIT_PERCENTAGE"));
// map to the object
Restaurant restaurant = new Restaurant(number, name);
restaurant.setBenefitPercentage(benefitPercentage);
restaurant.setBenefitAvailabilityPolicy(mapBenefitAvailabilityPolicy(rs));
return restaurant;
}
return jdbcTemplate.queryForObject(sql, new RestaurantRowMapper(), merchantNumber);
class RestaurantRowMapper implements RowMapper<Restaurant> {
public Restaurant mapRow(ResultSet rs, int i) throws SQLException {
return mapRestaurant(rs);
}
}
The queryForObject
method looks like this:
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
return DataAccessUtils.requiredSingleResult(results);
}
The queryForObject
-method internally invokes the method query
on the JdbcTemplate
object. The query
-method is defined as:
public <T> T query(
PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
throws DataAccessException;
As you can see, a ResultSetExtractor<T>
is passed to the query
-method and Spring conveniently converts your RowMapper<T>
to an object of type new RowMapperResultSetExtractor<T>(rowMapper, 1)
. The RowMapperResultSetExtractor
is the object that holds the key to the magic. When the object is invoked it iterates all rows as per this snippet:
public List<T> extractData(ResultSet rs) throws SQLException {
List<T> results = (this.rowsExpected > 0 ? new ArrayList<T>(this.rowsExpected) : new ArrayList<T>());
int rowNum = 0;
while (rs.next()) {
results.add(this.rowMapper.mapRow(rs, rowNum++));
}
return results;
}
So, your original RowMapper
is the callback that is called for each row. Furthermore, as you can see here your RowMapper
is invoked for all matching results and the Restaurant
-object that you created is added to the result list. However, since you query for only one object the following statement is finally used to return your single Restaurant
object.
return DataAccessUtils.requiredSingleResult(results);
So, to sum up: JdbcTempalte
implementes the Strategy Pattern (which is similar to the Template method pattern). And by providing a Strategy interface (the RowMapper
) you can let JdbcTemplate
do the heavy lifting for you (handling exceptions, connections etc). The RowMapper
maps each hit as a POJO (the Restaurant
) and all hits are collected to a List
. The method queryForObject
then takes the first row from that List
and returns it to the caller. The return value is based on the generic type of the RowMapper
which in your case is Restaurant
.