How to create a one-to-many relationship with JDBI SQL object API?

Question

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.

The POJO representations would be something like this:

User POJO:

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;
    }
}

Account POJO:

public class Account {

    private int id;
    private String name;
    private List<User> users;

    public Account(int id, String name, List<User> 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<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }
}

The DAO should look something like this

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);

}

But when the method has a single object as return value (Account instead of List<Account>) 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 https://groups.google.com/d/msg/jdbi/4e4EP-gVwEQ/02CRStgYGtgJ 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.)

There seems to be a way using a Folder2 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.

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.

All help is greatly appreciated,
  Tilman

Answer 1

OK, after a lot of searching, I see two ways dealing with this:

The first option is to retrieve an object for each column and merge it in the Java code at the resource (i.e. do the join in the code instead of having it done by the database). This would result in something like

@GET @Path("/{accountId}") public Response getAccount(@PathParam("accountId") Integer accountId) {     Account account = accountDao.getAccount(accountId);     account.setUsers(userDao.getUsersForAccount(accountId));     return Response.ok(account).build(); } 

This is feasible for smaller join operations but seems not very elegant to me, as this is something the database is supposed to do. However, I decided to take this path as my application is rather small and I did not want to write a lot of mapper code.

The second option is to write a mapper, that retrieves the result of the join query and maps it to the object like this:

public class AccountMapper implements ResultSetMapper<Account> {      private Account account;      // this mapping method will get called for every row in the result set     public Account map(int index, ResultSet rs, StatementContext ctx) throws SQLException {          // for the first row of the result set, we create the wrapper object         if (index == 0) {             account = new Account(rs.getInt("id"), rs.getString("name"), new LinkedList<User>());         }          // ...and with every line we add one of the joined users         User user = new User(rs.getInt("u_id"), rs.getString("u_name"));         if (user.getId() > 0) {             account.getUsers().add(user);         }          return account;     } } 

The DAO interface will then have a method like this:

public interface AccountDAO {      @Mapper(AccountMapper.class)     @SqlQuery("SELECT Account.id, Account.name, User.id as u_id, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")     public List<Account> getAccountById(@Bind("id") int id);  } 

Note: Your abstract DAO class will quietly compile if you use a non-collection return type, e.g. public Account getAccountById(...);. However, your mapper will only receive a result set with a single row even if the SQL query would have found multiple rows, which your mapper will happily turn into a single account with a single user. JDBI seems to impose a LIMIT 1 for SELECT queries that have a non-collection return type. It is possible to put concrete methods in your DAO if you declare it as an abstract class, so one option is to wrap up the logic with a public/protected method pair, like so:

public abstract class AccountDAO {      @Mapper(AccountMapper.class)     @SqlQuery("SELECT Account.id, Account.name, User.id as u_id, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")     protected abstract List<Account> _getAccountById(@Bind("id") int id);      public Account getAccountById(int id) {         List<Account> accountList = _getAccountById(id);         if (accountList == null || accountList.size() < 1) {             // Log it or report error if needed             return null;         }         // The mapper will have given a reference to the same value for every entry in the list         return accountList.get(accountList.size() - 1);     } } 

This still seems a little cumbersome and low-level to me, as there are usually a lot of joins in working with relational data. I would love to see a better way or having JDBI supporting an abstract operation for this with the SQL object API.

Answer 2

In JDBI v3, you can use @UseRowReducer to achieve this. The row reducer is called on every row of the joined result which you can "accumulate" into a single object. A simple implementation in your case would look like:

public class AccountUserReducer implements LinkedHashMapRowReducer<Integer, Account> {      @Override     public void accumulate(final Map<Integer, Account> map, final RowView rowView) {         final Account account = map.computeIfAbsent(rowView.getColumn("a_id", Integer.class),             id -> rowView.getRow(Account.class));         if (rowView.getColumn("u_id", Integer.class) != null) {             account.addUser(rowView.getRow(User.class));         }     } } 

You can now apply this reducer on a query that returns the join:

@RegisterBeanMapper(value = Account.class, prefix = "a") @RegisterBeanMapper(value = User.class, prefix = "u") @SqlQuery("SELECT a.id a_id, a.name a_name, u.id u_id, u.name u_name FROM " +     "Account a LEFT JOIN User u ON u.accountId = a.id WHERE " +     "a.id = :id") @UseRowReducer(AccountUserReducer.class) Account getAccount(@Bind("id") int id); 

Note that your User and Account row/bean mappers can remain unchanged; they simply know how to map an individual row of the user and account tables respectively. Your Account class will need a method addUser() that is called each time the row reducer is called.

Answer 3

I have a small library which will be very useful to maintain one to many & one to one relationship. It also provide more feature for default mappers.

https://github.com/Manikandan-K/jdbi-folder

Answer 4

There's an old google groups post where Brian McAllistair (One of the JDBI authors) does this by mapping each joined row to an interim object, then folding the rows into the target object.

See the discussion here. There's test code here.

Personally this seems a little unsatisfying since it means writing an extra DBO object and mapper for the interim structure. Still I think this answer should be included for completeness!