JDBCTemplate set nested POJO with BeanPropertyRowMapper

Question

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 1

Perhaps you could pass in a custom RowMapper that could map each row of an aggregate join query (between message and user) to a Message and nested User. Something like this:

List<Message> messages = jdbcTemplate.query("SELECT * FROM message m, user u WHERE u.message_id = m.message_id", new RowMapper<Message>() {
    @Override
    public Message mapRow(ResultSet rs, int rowNum) throws SQLException {
        Message message = new Message();
        message.setTitle(rs.getString(1));
        message.setQuestion(rs.getString(2));

        User user = new User();
        user.setUserName(rs.getString(3));
        user.setDisplayName(rs.getString(4));

        message.setUser(user);

        return message;
    }
});