title: JDBCTemplate set nested POJO with BeanPropertyRowMapper
body: <p>Given the following example POJO's: (Assume Getters and Setters for all properties)</p>
  
  <pre><code>class User {
      String user_name;
      String display_name;
  }
  
  class Message {
      String title;
      String question;
      User user;
  }
  </code></pre>
  
  <p>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). </p>
  
  <pre><code>NamedParameterDatbase.query("SELECT * FROM message", new BeanPropertyRowMapper(Message.class));
  </code></pre>
  
  <p>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.</p>
  
  <p>That is, Some syntatical magic where each result row in the query:</p>
  
  <pre><code>SELECT * FROM message, user WHERE user_id = message_id
  </code></pre>
  
  <p>Produce a list of Message with the associated User populated</p>
  
  <hr>
  
  <p>Use Case:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <hr>
  
  <p>Update : Solution Used - </p>
  
  <p>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.</p>
  
  <p>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):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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).</p>
  
  <pre><code>public Object mapRow(ResultSet rs, int i) throws SQLException {
  
      HashMap&lt;String, BeanMap&gt; 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 &lt;= 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;
  }
  </code></pre>
  
  <p>Again, this might seem like overkill for a two class join but the IRL use case involves multiple tables with tens of fields.</p>