Handy RowMapper base class for Spring JDBC

RowMappers are needed all over the place in your Spring JDBC DAO classes. One challenge that I kept running into was that when I wanted to reuse a particular RowMapper class for numerous queries, there was an ever-present threat of an underlying SQLException if certain columns were not present in the ResultSet. Obviously, a reusable RowMapper will set every field on the object it maps for, however not every ResultSet will include every field. Calling rs.getString("column_name"); will result in an exception being thrown if column_name is not present in the particular ResultSet.

So to solve this problem, I wrote this base RowMapper class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

public abstract class BaseRowMapper<T extends Object> implements RowMapper<T> {
    private Set setAvailableColumns;
    private ResultSet rs;
    private final String prefix;
 
    public BaseRowMapper() {
        prefix = "";
    }
 
    public BaseRowMapper(String prefix) {
        this.prefix = prefix;
    }
 
    private void init(ResultSet rs) throws SQLException {
        this.rs = rs;
        setAvailableColumns = new HashSet();
        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1, n = meta.getColumnCount() + 1; i < n; i++)
            setAvailableColumns.add(meta.getColumnName(i));
    }
 
    public T mapRow(ResultSet rs, int rowNum) throws SQLException {
        if (setAvailableColumns == null)
            init(rs);
        return mapRowImpl(rs, rowNum);
    }
 
    public abstract T mapRowImpl(ResultSet rs, int rowNum) throws SQLException;
 
    public boolean column(String sName) {
        return (setAvailableColumns.contains(sName));
    }
 
    public Long getLong(String sName) throws SQLException {
        if (column(prefix + sName))
            return rs.getLong(prefix + sName);
        else
            return new Long(0l);
    }
 
    public Integer getInteger(String sName) throws SQLException {
        if (column(prefix + sName))
            return rs.getInt(prefix + sName);
        else
            return new Integer(0);
    }
 
    // the rest of the class is omitted.

There is one abstract method to implement, mapRowImpl(ResultSet rs, int rowNum). There are a number of getter methods wrapping those of the ResultSet object, which I’ll talk about in a minute.

This class must be initialized, however there is no concept of a RowMapper initializer in Spring JDBC. So, I check (Line 23) to see if the setAvailableColumns member variable has not yet been initialized in the mapRow method. This is not the most elegant code, but there is little overhead cost to do a null check.

The init method interrogates the ResultSet metadata to acquire the set of column names present. Now, if you look again at the getter methods, I’m checking whether or not the requested column is present in the ResultSet. If it is, then call the getter on the ResultSet object. If not, return a default value.

Careful here. I could return null, for instance in the getInteger method, instead to indicate a value not present. Returning null would actually be more correct. However, this might result in NullPointerExceptions elsewhere in your code. In particular, autounboxing a null Integer to an int primitive will result in a NullPointerException. On the flip side, defaulting an Integer to zero rather than null may cause other subtle bugs. However it is already the case that an uninitialized int primitive defaults to zero in Java, so there is precedent to go with this choice.

What is the “prefix” for?  Consider you have a state table and country table, each containing columns named “name” and “abbreviation”.  You have a RowMapper class for each.  You have a case where you are selecting state data and country data in the same query.  Both your StateRowMapper and CountryRowMapper will try to map the columns “name” and “abbreviation” to their respective internal variables, but in this case those columns will appear twice.  How to sort this out?  One way is to use a prefix, so that in your select statement you do like so: “SELECT state.name AS state_name, state.abbreviation AS state_abbreviation, …” Then you would initialize your StateRowMapper with the prefix “state_”, and the getter methods in the BaseRowMapper will translate getString("name") into rs.getString("state_name").

Yes, the String concatenation in the getters should be refactored out.

So what would an implementing class look like? I frequently define them as static inner classes inside my DAOs, like so:

1
2
3
4
5
6
7
8
9
10
11

private static final class StateRowMapper extends BaseRowMapper<State> {
 
    @Override
    public State mapRowImpl(ResultSet rs, int i) throws SQLException {
        State state = new State();
        state.setStateId(getInteger("state_id"));
        state.setName(getString("name"));
        state.setAbbreviation(getString("abbreviation"));
        return state;
    }
}

Note, you’ve still got access to the ResultSet object if needed. Usage of this class would look like:

1
2
3

jdbcTemplate().query("SELECT state_id, name, abbreviation FROM state WHERE country_id = ?",
    new StateRowMapper(),
    72);

You could expand the base class with any sort of getter methods that would be convenient to you. For example, I make use of the fantastic Joda Time library, so these getters are useful:

1
2
3
4
5
6
7
8
9
10
11
12
13

    public DateTime getDate(String name) throws SQLException {
        if (column(prefix + name))
            return new DateTime(rs.getTimestamp(prefix + name).getTime());
        else
            return null;
    }
 
    public DateMidnight getDateMidnight(String name) throws SQLException {
        if (column(prefix + name))
            return new DateMidnight(rs.getDate(prefix + name).getTime());
        else
            return null;
    }

So, this was pretty simple. But hopefully it was useful to someone considering a handy base class for their RowMappers in Spring JDBC. If you’ve got any ideas to share on this topic, don’t hesitate to comment.