如何使用Spring Data JDBC建模一对一关系？

Question

我想使用Spring Data JDBC和PostgreSQL建模一对一关系，但我在正确设置根聚合时遇到了问题。

以下是场景：Picture, SQL
每个引擎都是独特的，car具有唯一列engine_id，该列是engine.id的外键，truck同样如此。因此，汽车和卡车应该是根聚合，因此当删除汽车或卡车时，引用的引擎表中的行也应该被删除。

从我对Spring Data JDBC Aggregates的理解来看，

如果多个聚合引用相同的实体，则该实体不能是引用它的那些聚合的一部分，因为它只能是恰好一个聚合的一部分。

所以问题是：

• 由于上述解释，是否可以通过绕过来进行CRUD操作 car 和 truck 以便将更改反映到 engine 中？
• 使用Spring Data JDBC在Java中实现这种关系的最佳方法是什么？

下面是我的想法，虽然不起作用，但应该可以澄清我想要实现的内容。

Car.java

package com.example.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

import java.util.UUID;

@Table("car")
public class Car implements Persistable<UUID> {

    @Id
    private UUID id;

    String brand;

    String model;

    @Column("engine_id")
    Engine engine;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }
}

Engine.java

package com.example.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Table;

import java.time.LocalDateTime;
import java.util.UUID;

@Table("engine")
public class Engine implements Persistable<UUID> {

    @Id
    private UUID id;

    String name;

    LocalDateTime dateCreated;

    String type;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }
}

Truck.java

package com.example.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

import java.util.UUID;

@Table("truck")
public class Truck implements Persistable<UUID> {

    @Id
    private UUID id;

    String brand;

    String model;

    Integer cargoMaxWeight;

    String truckType;

    @Column("engine_id")
    Engine engine;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }
}

- lolo92

Answer 1

我可以翻译这段内容。这是关于Java建模的四个选项，但大多数需要调整数据库模式。问题在于Spring Data JDBC假设被引用的实体（Engine）在其表中有一列引用拥有实体（Car/Vehicle）。对此有一个问题：https://jira.spring.io/browse/DATAJDBC-128。因此，你有以下选项：

1. add to columns to the engine table resulting in entities and schema like the following (all entities are reduced to their minimum relevant to the problem):

   public class Car {
   
       @Id
       Long id;
       String name;
   
       Engine engine;
   }
   
   public class Truck {
   
       @Id
       Long id;
       String name;
   
       Engine engine;
   }
   
   public class Engine {
   
       String name;
   }
   
   CREATE TABLE CAR (
     id   BIGINT IDENTITY,
     NAME VARCHAR(200)
   );
   
   CREATE TABLE TRUCK (
     ID   BIGINT IDENTITY,
     NAME VARCHAR(200)
   );
   
   CREATE TABLE ENGINE (
     TRUCK BIGINT,
     CAR   BIGINT,
     NAME  VARCHAR(200),
     FOREIGN KEY (TRUCK) REFERENCES TRUCK (ID),
     FOREIGN KEY (CAR) REFERENCES CAR (ID)
   );
   

   I provided a complete example on GitHub: https://github.com/schauder/so-sd-jdbc-multipleonetoone.

2. If you don't like the two columns you can modify the mapping to use the same column for both references. But then you have to make sure that the ids of Car and Vehicle are distinct. Even then there is a big problem with this approach:

   deleteAll on either the Car repository or the Truck vehicle will delete ALL the engines!!! Therefore this approach is not recommended!

   If you still want to use it here is the code for schema and entities.

   public class Car {
   
       @Id
       Long id;
       String name;
   
       @Column(value = "vehicle")
       Engine engine;
   }
   
   public class Truck {
   
       @Id
       Long id;
       String name;
   
       @Column(value = "vehicle")
       Engine engine;
   }
   
   public class Engine {
   
       String name;
   }
   
   
   CREATE TABLE CAR (
     id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
     NAME VARCHAR(200)
   );
   
   CREATE TABLE TRUCK (
     ID   BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH -1, INCREMENT BY -1) PRIMARY KEY ,
     NAME VARCHAR(200)
   );
   
   CREATE TABLE ENGINE (
     VEHICLE   BIGINT,
     NAME  VARCHAR(200),
   );
   

   And the complete example is on this commit: https://github.com/schauder/so-sd-jdbc-multipleonetoone/tree/5570979ef85e30fe7a17a8ce48d867fdb79e212a.

3. Have two separate Engine classes and tables. One for Cars and one for Trucks.

4. If you don't want or can't change your database schema you can consider Engine, Car, and Truck three separate aggregates. you would have a Long engineId in Car and in Truck. The cascading delete could then be done using an event listener for AfterDeleteEvent.

- Jens Schauder

---

感谢您提供非常有用的解释，顺便说一下，我真的很喜欢您在Spring Data JDBC中所做的工作。由于原始问题涉及到约10个引用“engine”表的表，因此1）和3）不适用。我最有可能选择4），并在数据库端执行级联删除而不是调用事件侦听器的变体。在这种情况下，最好的选择是通过在Car中的Engine字段上放置@Transient来在查询汽车时将引擎加载到汽车中，然后添加自定义映射器到我编写的查询中吗？ - lolo92

感谢您的友善反馈。从简短的评论中可以看出，那种方法应该是可行的。如果您遇到问题，请开一个新的问题。 - Jens Schauder

第四个解决方案似乎是在现有模式下工作时最好的解决方法。我有一个类似的问题，地址实体被用户、公司和学校实体引用，未来可能会引入更多的引用实体。为了实现这一点，创建更多的类和表并不理想...任何持久层都不应该真正决定关系型数据库模式的结构...它只需要正常工作即可。 - Motolola

一对一映射是更重要的，大多数模式都遵循这个规则，我认为这应该首先解决。 - Gaurav

Answer 2

我终于找到了解决方案，问题在于我无法理解如何从数据库中不同的模型中引用Car和Truck类到Engine中。

Car.java

package com.backend.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;

import java.util.Objects;
import java.util.UUID;

public class Car implements Persistable<UUID> {

    @Id
    private UUID id;

    private String brand;

    private String model;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof Car)) {
            return false;
        }
        Car car = (Car) o;
        return Objects.equals(id, car.id) &&
            Objects.equals(brand, car.brand) &&
            Objects.equals(model, car.model);
    }

    @Override
    public int hashCode() {

        return Objects.hash(id, brand, model);
    }
}

Truck.java

package com.backend.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Table;

import java.util.Objects;
import java.util.UUID;

@Table("truck")
public class Truck implements Persistable<UUID> {

    @Id
    private UUID id;

    private String brand;

    private String model;

    private Integer cargoMaxWeight;

    private String truckType;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public Integer getCargoMaxWeight() {
        return cargoMaxWeight;
    }

    public void setCargoMaxWeight(Integer cargoMaxWeight) {
        this.cargoMaxWeight = cargoMaxWeight;
    }

    public String getTruckType() {
        return truckType;
    }

    public void setTruckType(String truckType) {
        this.truckType = truckType;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof Truck)) {
            return false;
        }
        Truck truck = (Truck) o;
        return Objects.equals(id, truck.id) &&
            Objects.equals(brand, truck.brand) &&
            Objects.equals(model, truck.model) &&
            Objects.equals(cargoMaxWeight, truck.cargoMaxWeight) &&
            Objects.equals(truckType, truck.truckType);
    }

    @Override
    public int hashCode() {

        return Objects.hash(id, brand, model, cargoMaxWeight, truckType);
    }
}

Engine.java

package com.backend.dao.model;

import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

import java.time.LocalDateTime;
import java.util.Objects;
import java.util.UUID;

@Table("engine")
public class Engine implements Persistable<UUID> {

    @Id
    private UUID id;

    private String name;

    private LocalDateTime dateCreated;

    private String type;

    @Column("engine_id")
    private Car car;

    @Column("engine_id")
    private Truck truck;

    public void setId(UUID id) {
        this.id = id;
    }

    @Override
    public UUID getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public LocalDateTime getDateCreated() {
        return dateCreated;
    }

    public void setDateCreated(LocalDateTime dateCreated) {
        this.dateCreated = dateCreated;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    @Override
    public boolean isNew() {
        return id == null;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof Engine)) {
            return false;
        }
        Engine engine = (Engine) o;
        return Objects.equals(id, engine.id) &&
            Objects.equals(name, engine.name) &&
            Objects.equals(dateCreated, engine.dateCreated) &&
            Objects.equals(type, engine.type);
    }

    @Override
    public int hashCode() {

        return Objects.hash(id, name, dateCreated, type);
    }

    public Car getCar() {
        return car;
    }

    public void setCar(Car car) {
        this.car = car;
    }

    public Truck getTruck() {
        return truck;
    }

    public void setTruck(Truck truck) {
        this.truck = truck;
    }
}

然而，这个解决方案不符合要求 - 在 Engine.java 上执行的CRUD操作会反映在 Car.java 和 Truck.java 上。而我想要实现的是，在 Car.java 和 Truck.java 上执行的CRUD操作能够反映在 Engine.java 上。

如果有更好的解决方案，请发帖分享。

- lolo92