title: How to create a one-to-many relationship with JDBI SQL object API?
body: <p>I'm creating a simple REST application with dropwizard using JDBI. The next step is to integrate a new resource that has a one-to-many relationship with another one. Until now I couldn't figure out how to create a method in my DAO that retrieves a single object that holds a list of objects from another table.</p>
  
  <p>The POJO representations would be something like this:</p>
  
  <p>User POJO:</p>
  
  <pre><code>public class User {
  
      private int id;
      private String name;
  
      public User(int id, String name) {
          this.id = id;
          this.name = name;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  }
  </code></pre>
  
  <p>Account POJO:</p>
  
  <pre><code>public class Account {
  
      private int id;
      private String name;
      private List&lt;User&gt; users;
  
      public Account(int id, String name, List&lt;User&gt; users) {
          this.id = id;
          this.name = name;
          this.users = users;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public List&lt;User&gt; getUsers() {
          return users;
      }
  
      public void setUsers(List&lt;User&gt; users) {
          this.users = users;
      }
  }
  </code></pre>
  
  <p>The DAO should look something like this</p>
  
  <pre><code>public interface AccountDAO {
  
      @Mapper(AccountMapper.class)
      @SqlQuery("SELECT Account.id, Account.name, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")
      public Account getAccountById(@Bind("id") int id);
  
  }
  </code></pre>
  
  <p>But when the method has a single object as return value (<em>Account</em> instead of <em>List&lt;Account&gt;</em>) there seems to be no way to access more than one line of the resultSet in the Mapper class. The only solution that comes close I could find is described at <a href="https://groups.google.com/d/msg/jdbi/4e4EP-gVwEQ/02CRStgYGtgJ" rel="noreferrer">https://groups.google.com/d/msg/jdbi/4e4EP-gVwEQ/02CRStgYGtgJ</a> but that one also only returns a Set with a single object which does not seem very elegant. (And can't be properly used by the resouce classes.)</p>
  
  <p>There seems to be a way using a <em>Folder2</em> in the fluent API. But I don't know how to integrate that properly with dropwizard and I'd rather stick to JDBI's SQL object API as recommended in the dropwizard documentation.</p>
  
  <p>Is there really no way to get a one-to-many mapping using the SQL object API in JDBI? That is such a basic use case for a database that I think I must be missing something.</p>
  
  <p>All help is greatly appreciated,
  <br/>&nbsp;&nbsp;Tilman</p>