JOOQ POJO带有一对多和多对多关系

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映射，是否可以以任何方式利用JOOQ，而不是仅用它来编写SQL语句？

- thee

Answer 1

使用 jOOQ 3.15 嵌套集合时的 MULTISET

从 jOOQ 3.15 开始，您可以使用标准 SQL 的 MULTISET 操作符来嵌套集合，并抽象出下面的 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构造函数中。

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 的更多详细信息的博客文章

使用 SQL/XML 或 SQL/JSON 在 jOOQ 3.14 中处理嵌套集合

从 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_AGG()，而不必使用中间的XML或JSON格式。

请注意，JSON_ARRAYAGG()将空集合聚合为NULL，而不是一个空的[]。如果这是个问题，可以使用COALESCE()

历史回答（jOOQ 3.14之前）

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/

- Lukas Eder

---

谢谢。但是我该如何将多对多的数据提取到JOOQ的逻辑中呢？在JDBC中，我会使用JOIN子句获取数据，并将每个附加行添加到POJO中的集合中，但是在JOOQ中，RecordMapper无法访问先前的对象。当创建相应的对象时，我是否需要在RecordMapper中执行另一个查询以获取多对多关系？还是有其他方法可以做到这一点？ - thee

是的，RecordMapper 是一个函数式接口，因此实现一些“有状态”的分组算法并不容易。但我们也有各种 Result.intoGroups()。也许这些会有所帮助？另一个选择是利用像 ModelMapper 这样的工具，它本身就支持 jOOQ。无论如何，您永远不应该执行两个查询，只是因为 ResultSet 到对象映射算法变得有点复杂。 - Lukas Eder

在其他答案（以及您的博客）中，您已经提到Java 8的流可以用于将数据库中的复杂关系正确匹配到POJO中。从这个角度来看，流是否可以用于解决这个问题？ - thee

@thee：当然可以。甚至比使用Streams更容易，你可以使用jOOλ。我的回答并不详尽。我会在不久的将来添加更多示例，感谢你指出这一点。 - Lukas Eder

有什么新消息吗？我目前正在重新评估这个问题，一些详细的例子会极大地帮助我，因为我目前不确定是否应该坚持使用 JOOQ 还是转向其他解决方案。 - thee

@thee：你有兴趣在用户组中详细说明你的用例吗？https://groups.google.com/forum/#!forum/jooq-user。如果我知道你的具体期望，我或许可以更好地回答你的问题，而不是在这里。当然，你可以使用我提到的工具进行这些映射，但很难说（并在Stack Overflow上讨论），是否对你来说足够好。 - Lukas Eder

Answer 2

您可以在查询的ResultSet上使用SimpleFlatMapper。

创建一个以player为键的映射器。

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

使用 fetchResultSet 获取 ResultSet 并将其传递给 mapper。注意，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);

}

然后在流上执行您需要执行的操作，您还可以获取一个迭代器。

- user3996996

---

需要注意的是，此解决方案绕过了Jooq的记录映射器并直接访问结果集。如果您有任何Jooq强制类型，则会出现问题-您将不得不重新定义它们以适用于SFM。 - Amit Goldstein

我的理解是，强制类型用于生成Record对象。您可以使用sfm将其映射到jOOQ记录对象，使用强制类型。然后问题就是如何映射与Record对象的连接？您需要使用Tuple2<Record1, List<RecordMany>>。 - user3996996

我通过创建一个ConverterFactoryProducer并通过遍历jooq转换器列表并将它们的方法传递给constantConverter()来解决了强制类型问题。如果您感兴趣，我可以添加一个gist。虽然我还没有彻底测试过它。 - Amit Goldstein

有趣，您介意开个工单并提供更多细节吗？我似乎没有真正理解这个问题。 - user3996996