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

本文介绍了具有一对多和多对多关系的JOOQ pojos的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我努力了解如何与JOOQ建立一对多和多对多的关系来处理pojos.

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;

在我的Java应用程序中，所有这些信息都存储在一个pojo中.请注意，可以从应用程序中更新播放器和受邀播放器的列表，并且还需要在数据库中对其进行更新:

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

我可以使用JOOQ的pojo映射将这三个记录映射到单个pojo吗?我可以使用此pojo中的JOOQ的CRUD功能来更新一对多和多对多关系吗?如果无法使用pojo映射，除了可以使用JOOQ编写SQL语句外，我是否可以以其他任何方式利用JOOQ?

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?

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

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

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")
    )
   .from(LOCATION)
   .fetchInto(Location.class);

在某些数据库产品(例如PostgreSQL)中，您甚至可以使用ARRAY_AGG()使用SQL数组类型，而跳过使用中间XML或JSON格式.

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.

jOOQ还没有开箱即用地进行这种POJO映射，但是您可以利用 ModelMapper ，其中具有专用的 jOOQ集成，该功能在一定程度上适用于这些情况

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.

本质上，ModelMapper可以插入jOOQ的 RecordMapper API.此处有更多详细信息:

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/

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/

这篇关于具有一对多和多对多关系的JOOQ pojos的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

一对多和多对多关系