Spring Note: RowMapper/MappingSqlQuery

There are a couple of interesting Spring classes which keep coming up in my daily work: RowMapper and MappingSqlQuery.

RowMapper

The RowMapper class is an interface which defines only one method which turns a single row from a ResultSet into a domain-useable object. While you can use a generic implementation of this class for producing column name/value Maps, my typical usage has been to create domain objects from the result of a specific set of queries.

MappingSqlQuery

The MappingSqlQuery class is intended to be used for SELECT queries and can use a RowMapper implementation to create a result List of the appropriate type, hiding the intermediate ResultSet from the outside world.

These classes, when taken together, provide excellent tools for creating the read methods for DAO classes which are concise and testable.

ITSpringJdbc.java

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
public class ITSpringJdbc {

  public class TestMapper implements RowMapper {
    @Override
    public Map<String, String> mapRow(final ResultSet rs, final int index)
        throws SQLException {
      final Map<String, String> m = new HashMap<String, String>();
      final ResultSetMetaData md = rs.getMetaData();
      for (int i = 0; i < md.getColumnCount(); i++) {
        m.put(md.getColumnName(i), rs.getString(i));
      }
      return m;
    }
  }

  public class TestSelect extends MappingSqlQuery {
    final TestMapper mapper = new TestMapper();

    @Override
    protected Object mapRow(final ResultSet rs, final int index)
        throws SQLException {
      return mapper.mapRow(rs, index);
    }
  }

  @Resource TestSelect selectBean;
  @Resource TestMapper mappingBean;
  @Resource JdbcTemplate jdbcTemplate;

  @Test public void testSelect() {
    final String[] args = { "X" };
    final Object result = selectBean.findObject(args);
    assertThat(result, is(notNullValue());
    assertThat(result, is(Map.class));
    final Map map = (Map) result;
    assertThat(map, hasSize(1));
    assertThat(map.containsKey("dummy"), is(true));
    assertThat(map.get("dummy"), is("X"));
  }

  @Test public void testMapper() {
    final SqlRowSet rs = jdbcTemplate.queryForRowSet(
      "select dummy from table");
    final Object o = mappingBean.mapRow(rs, 0);
    assertThat(o, is(notNullValue());
    assertThat(o, is(Map.class));
    final Map map = (Map) o;
    assertThat(map, hasSize(1));
    assertThat(map.containsKey("dummy"), is(true));
    assertThat(map.get("dummy"), is("X"));
  }
}

ITSpringJdbc-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:util="http://www.springframework.org/schema/util"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

<bean id="dataSource.properties"
  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  <property name="locations">
    <list>
      <value>classpath:connection.properties</value>
    </list>
  </property>
</bean>

<bean id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="${driverClassName}" />
  <property name="url" value="${uri}" />
  <property name="username" value="${username}" />
  <property name="password" value="${password}" />
</bean>

<bean id="selectBean"
  class="TestSelect">
  <property name="dataSource" ref="dataSource" />
  <property name="sql">
    <value>
      select dummy from table where dummy = ?
    </value>
  </property>
  <property name="types">
    <list value-type="java.lang.Integer">
      <util:constant
        static-field="java.sql.Types.VARCHAR" />
    </list>
  </property>
</bean>

<bean id="mappingBean"
  class="TestMapper"/>

<bean id="jdbcTemplate"
  class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="dataSource"/>
</bean>
</beans>