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

Question

I'm struggling to figure out how to handle pojos with one-to-many and many-to-many relationships with JOOQ.

I store locations created by players (one-to-many relationship). A place may contain several additional players who can visit it (many to many). The structure of the database is as follows:

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;

In my Java application, all this information is stored in one Pojo. Please note that the player and the list of invited players can be updated from the application and must be updated in the database:

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

pojo JOOQ pojo? CROO JOOQs pojo, ? pojo , JOOQ , SQL?

Answer 1

jOOQ POJO , - ModelMapper, jOOQ, .

, ModelMapper jOOQ RecordMapper API. :

• 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

SimpleFlatMapper ResultSet .

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

fetchResultSet, ResultSet . , orderBy (LOCATION.PLAYER_ID), .

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

}

, , .

Answer 3

, , JOOQ .

When he is able to fill in the graph of objects, perhaps ...

the best review i have found so far:

http://teonos.com/blog/java/development/2014/11/10/experiences-with-jOOQ.html