具有一对多和多对多关系的JOOQ pojos

Question

我正在努力理解如何处理与JOOQ的一对多和多对多关系的Pojo。

我存储玩家创建的位置（一对多关系）。一个位置可以容纳多个可能访问它的其他玩家（多对多）。数据库布局可归结为以下内容：

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;

在我的java应用程序中，所有这些信息都存储在一个pojo中。请注意，玩家和受邀玩家列表可以从应用程序中更新，也需要在数据库中更新：

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

我可以使用JOOQ的pojo映射将这三个记录映射到单个pojo吗？我可以使用这个pojo中JOOQ的CRUD特性来更新一对多和多对多关系吗？如果不能使用pojo映射，除了用它来编写SQL语句外，我还能以任何方式利用JOOQ吗？

Answer 1

您可以在查询的结果集上使用SimpleFlatMapper。

创建以player为键的映射器

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

然后使用fetchResultSet获取结果集并将其传递给映射器。请注意，按（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 2

从jOOQ 3.15开始，您可以使用标准的SQLMULTISET运算符来嵌套集合，并抽象以下SQL /XML或SQL /JSON序列化格式。您的查询如下所示：

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

如果DTO是不可变的（例如Java 16记录），您甚至可以避免使用反射进行映射，并使用构造函数引用和新的jOOQ 3.15即席转换功能将类型安全地映射到DTO构造函数中。

java prettyprint-override">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));

另请参阅这篇博客文章，了解有关MULTISET的更多详细信息

从jOOQ 3.14开始，如果您的RDBMS支持SQL /XML或SQL /JSON，则可以嵌套集合。然后，您可以使用Jackson、Gson或JAXB从文本格式映射回Java类。例如：

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

在一些数据库产品中，例如PostgreSQL，您甚至可以使用SQL数组类型使用array\u AGG（），并使用中间XML或JSON格式跳过。

请注意，JSON\u ARRAYAGG（）将空集聚合为空集，而不是空集。如果这是一个问题，请使用COALESCE（）

jOOQ还没有开箱即用地进行这种POJO映射，但是您可以利用ModelMapper之类的东西，它具有专用的jOOQ集成，在一定程度上适用于这些场景。

本质上，ModelMapper挂钩到jOOQ的RecordMapperAPI。这里有更多细节：

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