JDBCTemplate set nested POJO with BeanPropertyRowMapper

Given the following example POJO’s: (Assume Getters and Setters for all properties)

class User {
    String user_name;
    String display_name;
}

class Message {
    String title;
    String question;
    User user;
}

One can easily query a database (postgres in my case) and populate a list of Message classes using a BeanPropertyRowMapper where the db field matched the property in the POJO: (Assume the DB tables have corresponding fields to the POJO properties).

NamedParameterDatbase.query("SELECT * FROM message", new BeanPropertyRowMapper(Message.class));

I’m wondering – is there a convenient way to construct a single query and / or create a row mapper in such a way to also populate the properties of the inner ‘user’ POJO within the message.

That is, Some syntatical magic where each result row in the query:

SELECT * FROM message, user WHERE user_id = message_id

Produce a list of Message with the associated User populated

---

Use Case:

Ultimately, the classes are passed back as a serialised object from a Spring Controller, the classes are nested so that the resulting JSON / XML has a decent structure.

At the moment, this situation is resolved by executing two queries and manually setting the user property of each message in a loop. Useable, but I imagine a more elegant way should be possible.

---

Update : Solution Used –

Kudos to @Will Keeling for inspiration for the answer with use of the custom row mapper – My solution adds the addition of bean property maps in order to automate the field assignments.

The caveat is structuring the query so that the relevant table names are prefixed (however there is no standard convention to do this so the query is built programatically):

SELECT title AS "message.title", question AS "message.question", user_name AS "user.user_name", display_name AS "user.display_name" FROM message, user WHERE user_id = message_id

The custom row mapper then creates several bean maps and sets their properties based on the prefix of the column: (using meta data to get the column name).

public Object mapRow(ResultSet rs, int i) throws SQLException {

    HashMap<String, BeanMap> beans_by_name = new HashMap();

    beans_by_name.put("message", BeanMap.create(new Message()));
    beans_by_name.put("user", BeanMap.create(new User()));

    ResultSetMetaData resultSetMetaData = rs.getMetaData();

    for (int colnum = 1; colnum <= resultSetMetaData.getColumnCount(); colnum++) {

        String table = resultSetMetaData.getColumnName(colnum).split("\.")[0];
        String field = resultSetMetaData.getColumnName(colnum).split("\.")[1];

        BeanMap beanMap = beans_by_name.get(table);

        if (rs.getObject(colnum) != null) {
            beanMap.put(field, rs.getObject(colnum));
        }
    }

    Message m = (Task)beans_by_name.get("message").getBean();
    m.setUser((User)beans_by_name.get("user").getBean());

    return m;
}

Again, this might seem like overkill for a two class join but the IRL use case involves multiple tables with tens of fields.

Answer

Spring introduced a new AutoGrowNestedPaths property into the BeanMapper interface.

As long as the SQL query formats the column names with a . separator (as before) then the Row mapper will automatically target inner objects.

With this, I created a new generic row mapper as follows:

QUERY:

SELECT title AS "message.title", question AS "message.question", user_name AS "user.user_name", display_name AS "user.display_name" FROM message, user WHERE user_id = message_id

---

ROW MAPPER:

package nested_row_mapper;

import org.springframework.beans.*;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.JdbcUtils;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class NestedRowMapper<T> implements RowMapper<T> {

  private Class<T> mappedClass;

  public NestedRowMapper(Class<T> mappedClass) {
    this.mappedClass = mappedClass;
  }

  @Override
  public T mapRow(ResultSet rs, int rowNum) throws SQLException {

    T mappedObject = BeanUtils.instantiate(this.mappedClass);
    BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);

    bw.setAutoGrowNestedPaths(true);

    ResultSetMetaData meta_data = rs.getMetaData();
    int columnCount = meta_data.getColumnCount();

    for (int index = 1; index <= columnCount; index++) {

      try {

        String column = JdbcUtils.lookupColumnName(meta_data, index);
        Object value = JdbcUtils.getResultSetValue(rs, index, Class.forName(meta_data.getColumnClassName(index)));

        bw.setPropertyValue(column, value);

      } catch (TypeMismatchException | NotWritablePropertyException | ClassNotFoundException e) {
         // Ignore
      }
    }

    return mappedObject;
  }
}