Create many-to-one (n:1) / one-to-one (1:1) relationship with JDBI 3

Question

Topic says it all: I have a 1:1 relationship, which is technically a many-to-one relationship, but I would expect both to be handled the same. The relationship is a status column that holds primary keys of another table with status codes. The SQL query could not be more simple:

SELECT * FROM User LEFT OUTER JOIN status USING (status_id)

All the JDBI examples I could find with regards to join seem to expect considerably more complicated joins with CollectionS, whereas I simply want to get back UserS that look like this:

class User {
    String name;
    Status status;
}

class Status {
    int code;
}

In this example, the tables would be User(VARCHAR name, INT status_id), Status(INT status_id, INT code).

As a bonus follow up question, the status codes are (relatively) static, so it seems like a good idea to generate an enum class matching the Status table. I saw JDBI comes with an EnumMapper, but I have no idea how to use it.

Answer 1

The solution turned out to be the @Nested annotation. For future reference I'm posting a working version of the example:

class User {
    String name;

    @Nested("status")
    Status status;
}

class Status {
    int code;
}

interface Dao {
    @SqlQuery("SELECT user.*, status.code AS `status_code` FROM user LEFT OUTER JOIN Status USING (status_id)
    @RegisterBeanMapper(User.class)
    List<User> getUsers();
}

Note that while in this example, I have been explicit with the name of the joined table, but I didn't have to be. If you have no overlapping column names, then the example can be simplified to:

class User {
    String name;

    @Nested
    Status status;
}

class Status {
    int code;
}

interface Dao {
    @SqlQuery("SELECT * FROM user LEFT OUTER JOIN Status USING (status_id)
    @RegisterBeanMapper(User.class)
    List<User> getUsers();
}

Lastly, if you use Lombok for your data classes, the correct syntax is one of the following (depending on whether you need the explicit column renaming):

@Data
class User {
    String name;

    @Setter(onMethod = @__(@Nested))
    Status status;
}

or

@Data
class User {
    String name;

    @Setter(onMethod = @__(@Nested("status")))
    Status status;
}