A few years ago Marco Behler wrote JAVA PERSISTENCE GHETTO (AND HOW JOOQ MIGHT CHANGE THAT) talking about jOOQ strength and weaknesses.

One of the pain points he raised was many to many mapping:

  1. HOW DO I CONVERT BETWEEN DATABASE <–> OBJECTS?

After or before querying the database, the next question is: How do we go from database to objects and vice versa. And Hibernate et. al are really strong here. jOOQ has active records and yep, you can plug in stuff like objectmodelmapper, but mapping with jOOQ does not yet give you this “wow-effect” and feels clumsy at time. Try to get a more complex many-to-many relationship mapped and sooner or later you’ll end up writing some query DTOs and continue mapping to other objects. This is actually one of our main gripes with jOOQ at the moment.

As Lukas answered it is a deliberate choice, see Issue 1530. By design jOOQ is very open to other mappers allowing other libraries to solves those problems.

In this post, I will see how SimpleFlatMapper can help filling jOOQ mapping gaps.

StackOverflow

In this StackOverflow question a jOOQ user ask about the possibility of mapping a many-to-many in the following class

public class Location {
    private final String name;
    private UUID player;
    private List<UUID> invitedPlayers;

And a schema with 3 tables Location, Player, and location2player many-to-many join table between the first 2.

jOOQ has some mapping functionality but it expects one object per row. It is therefore not possible to return one Location object with a List of invited player with the RecordMapper. If a Location has 3 invited player all we can get is 3 identical Location object with 1 invited player each.

SimpleFlatMapper to the rescue

SimpleFlatMapper can integrate with jOOQ but as stated earlier it is not possible to map multiple rows with one object with the jOOQ RecordMapper integration. sfm-jdbc though can work at the ResultSet level and aggregate the join into the Location object. Fortunately, jOOQ provide access to the underlying ResultSet, so all we need to do is instantiate a Sfm JdbcMapper and we will be sorted.

Add sfm-jdbc as dependency

<dependency>
    <groupId>org.simpleflatmapper</groupId>
    <artifactId>sfm-jdbc</artifactId>
    <version>3.11.2</version>
</dependency>

Instantiate the JdbcMapper

For join aggregation, Sfm needs to know what are the column representing the id of the object.

assuming the following SQL Query would return the following fields, player is the id of the root object.

p.player-id as player, l.name as name, l1p.player-id as invited_players_player

to create the JdbcMapper just write the following code:

JdbcMapper<Location> jdbcMapper = 
    JdbcMapperFactory.addKeys("player").newMapper(Location.class);

The Mapper is thread-safe it is recommended to have only one instance per type, or type - columns for static mapper.

Execute your sqlQuery with jOOQ

For the break detection on the root object you need to order by the id of the root object, .orderBy(LOCATION.PLAYER_ID) here. Now we just need to execute the query, retrieve the ResultSet and use the jdbcMapper to map the rows to Location object.

UPDATE since 6.2.0 a new option unorderedJoin() allow for join mapping with a unordered ResultSet.

try (ResultSet rs = 
        dsl
            .select(
                    LOCATION.NAME.as("name"), 
                    LOCATION.PLAYER_ID.as("player"), 
                    LOCATION2PLAYER.PLAYERID.as("invited_players_player"))
            .from(LOCATION)
                .leftOuterJoin(LOCATION2PLAYER)
                    .on(LOCATION2PLAYER.LOCATION_ID.eq(LOCATION.LOCATION_ID))
            .orderBy(LOCATION.PLAYER_ID)
            .fetchResultSet()) { 
    Stream<Location> stream = jdbcMapper.stream(rs);
    
    // do something on the stream.
}

and here you go you will have a Stream of Location as describes in the original question. Simple.

More information :

PS: It should be possible to do all that without having to write any DTO, blog post to come. PS2: here it is jOOQ DTO-less one-to-many