JOOQ pojos with one-to-many and many-to-many relations

Question

I am struggling to understand how to handle pojos with one-to-many and many-to-many relationships with JOOQ.

I store locations that are created by players (one-to-many relation). A location can hold multiple additional players who may visit it (many-to-many). The database layout comes down to the following:

CREATE TABLE IF NOT EXISTS `Player` (
  `player-id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `player` BINARY(16) NOT NULL,
  PRIMARY KEY (`player-id`),
  UNIQUE INDEX `U_player` (`player` ASC))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Location` (
  `location-id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `player-id` INT UNSIGNED NOT NULL COMMENT '
  UNIQUE INDEX `U_name` (`name` ASC),
  PRIMARY KEY (`location-id`),
  INDEX `Location_Player_fk` (`player-id` ASC),
  CONSTRAINT `fk_location_players1`
    FOREIGN KEY (`player-id`)
    REFERENCES `Player` (`player-id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `location2player` (
  `location-id` INT UNSIGNED NOT NULL,
  `player-id` INT UNSIGNED NOT NULL,
  INDEX `fk_ location2player_Location1_idx` (`location-id` ASC),
  INDEX `fk_location2player_Player1_idx` (`player-id` ASC),
  CONSTRAINT `fk_location2player_Location1`
    FOREIGN KEY (`location-id`)
    REFERENCES `Location` (`location-id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_location2player_Player1`
    FOREIGN KEY (`player-id`)
    REFERENCES `Player` (`player-id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Within my java application, all these informations are stored within one pojo. Note that the player and the list of invited players can be updated from within the application and need to be updated in the database as well:

public class Location {

    private final String name;
    private UUID player;
    private List<UUID> invitedPlayers;

    public void setPlayer(UUID player) {
        this.player = player;
    }

    public void invitePlayer(UUID player) {
        invitedPlayers.add(player);
    }

    public void uninvitePlayer(UUID player) {
        invitedPlayers.remove(player);
    }

    //additional methods…
}

Can I use JOOQ’s pojo mapping to map these three records into the single pojo? Can I use JOOQ’s CRUD feature from this pojo to update the one-to-many and many-to-many relations? If the pojo mapping cannot be used, can I take advantage of JOOQ in any way except using it to write my SQL statements?

Answer 1

Using MULTISET for nested collections with jOOQ 3.15

Starting from jOOQ 3.15, you can use the standard SQL MULTISET operator to nest collections, and to abstract over the below SQL/XML or SQL/JSON serialisation format. Your query would look like this:

List<Location> locations
ctx.select(
      LOCATION.NAME,
      LOCATION.PLAYER,
      multiset(
        select(LOCATION2PLAYER.PLAYER_ID)
        .from(LOCATION2PLAYER)
        .where(LOCATION2PLAYER.LOCATION_ID.eq(LOCATION.LOCATION_ID))
      ).as("invitedPlayers")
    )
   .from(LOCATION)
   .fetchInto(Location.class);

If your DTOs were immutable (e.g. Java 16 records), you can even avoid using reflection for mapping, and map type safely into your DTO constructors using constructor references and the new jOOQ 3.15 ad-hoc conversion feature.

List<Location> locations
ctx.select(
      LOCATION.NAME,
      LOCATION.PLAYER,
      multiset(
        select(LOCATION2PLAYER.PLAYER_ID)
        .from(LOCATION2PLAYER)
        .where(LOCATION2PLAYER.LOCATION_ID.eq(LOCATION.LOCATION_ID))
      ).as("invitedPlayers").convertFrom(r -> r.map(Record1::value1))
    )
   .from(LOCATION)
   .fetch(Records.mapping(Location::new));

See also this blog post for more details about MULTISET

Using SQL/XML or SQL/JSON for nested collections with jOOQ 3.14

Starting from jOOQ 3.14, it's possible to nest collections using SQL/XML or SQL/JSON, if your RDBMS supports that. You can then use Jackson, Gson, or JAXB to map from the text format back to your Java classes. For example:

List<Location> locations
ctx.select(
      LOCATION.NAME,
      LOCATION.PLAYER,
      field(
        select(jsonArrayAgg(LOCATION2PLAYER.PLAYER_ID))
        .from(LOCATION2PLAYER)
        .where(LOCATION2PLAYER.LOCATION_ID.eq(LOCATION.LOCATION_ID))
      ).as("invitedPlayers")
        .convertFrom(r -> r.map(Records.mapping(Pla)
    )
   .from(LOCATION)
   .fetch(Records.mapping(Location::new));

In some database products, like PostgreSQL, you could even use SQL array types using ARRAY_AGG() and skip using the intermediate XML or JSON format.

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

Historic answer (pre jOOQ 3.14)

jOOQ doesn't do this kind of POJO mapping out of the box yet, but you can leverage something like ModelMapper which features a dedicated jOOQ integration, which works for these scenarios to a certain extent.

Essentially, ModelMapper hooks into jOOQ's RecordMapper API. More details here:

• http://www.jooq.org/doc/latest/manual/sql-execution/fetching/recordmapper/
• http://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos-with-recordmapper-provider/

Answer 2

You can use SimpleFlatMapper on the ResultSet of the query.

create a mapper with player as the key

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

Then use fetchResultSet to get the ResultSet and pass it to the mapper. Note that it is important to orderBy(LOCATION.PLAYER_ID) otherwise you might end up with split Locations.

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

}

then do what you need to do on the stream, you can also get an iterator.