How to model One-To-One relationship with Spring Data JDBC?

Question

I want to model One-To-One relationship using Spring Data JDBC and PostgreSQL but I'm having trouble setting up root aggregates the right way.

There's following scenario: Picture, SQL
Each engine is unique, car has unique column engine_id which is foreign key of engine.id, same goes for truck. Therefore car and truck should be root aggregates so when car or truck is deleted, referenced row from engine table should be deleted as well.

From my understanding of Spring Data JDBC Aggregates

If multiple aggregates reference the same entity, that entity can’t be part of those aggregates referencing it since it only can be part of exactly one aggregate.

So the questions are:

Is workaround possible due to explanation above so that by performing CRUD operations on car and truck changes are reflected to engine as well ?
What's the best way to implement this relationship in java using Spring Data JDBC ?

Here's my take which does not work but should clarify what I'm trying to accomplish.

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

score 6 · Accepted Answer · answered Dec 05 '18 at 09:03

I see four options to model this in Java. Note that most of them require tweaking your database schema.

The general problem is that Spring Data JDBC assumes that the referenced entity (Engine) has a column in its table that references the owning entity (Car/Vehicle). There is an issue for this: https://jira.spring.io/browse/DATAJDBC-128 Starting from this you have the following options:

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.

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.

Have two separate Engine classes and tables. One for Cars and one for Trucks.
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.

Thanks for the very useful explanation, I really like what you're doing with Spring Data JDBC btw. Since the original problem has about 10 tables referencing `"engine"` table, 1) and 3) are off the menu. I'll most likely opt for 4) with variation of doing the cascade delete on database side instead of calling the event listener. In that case is the best option to load engine into car when querying for car by putting `@Transient` on Engine field in Car and then adding custom mapper to the query I wrote ? — lolo92, Dec 05 '18 at 09:38
Thanks for the kind feedback. Yes, that approach should work as far as I can tell from the short comment. If you encounter problems open a new question. — Jens Schauder, Dec 05 '18 at 10:27
The 4th solution seems the best in a situation, albeit a work-around when you are working on an existing schema. I have a similar issue with Address Entity, being referenced by User, Company and School Entities, in which more referencing Entities might be introduced in future. It wouldn't be ideal to keep creating more classes and tables to achieve that ... Any Persistence layer shouldn't really dictate how an RDBMS schema is structured ... it should just work regardless. — Motolola, Feb 07 '19 at 21:49
The one way one to one mapping is more important, Most of schema follows this, I think this must be addressed first. — Gaurav, Jun 17 '21 at 12:04

lolo92 · Answer 2 · 2018-12-05T09:47:49.630

Managed to find the solution and the problem was that I couldn't get my head around referencing Car and Truck classes from Engine, when in database the model is different.

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

However this solution does not meet the requirement - CRUD operations performed on Engine.java are reflected on Car.java and Truck.java. And I'd want to achieve that CRUD operations performed on Car.java and Truck.java are reflected on Engine.java.

If anyone has a better solution, post please.

How to model One-To-One relationship with Spring Data JDBC?

2 Answers2