[FIXED] Many to many relation not persisting in Spring JPA, Kotlin

Issue

Alright, after reading the whole of last night I am stuck at this supposed to be very simple issue.

I have a Driver and Race entity in JPA:

@Entity
@Table(name = "rt_driver")
data class Driver(
  @Id @GeneratedValue
  var id: Long,
  val firstName: String?,
  val lastName: String?,
  val team: String?,
  val age: Int?,

  @ManyToMany(
    cascade =
    [
      CascadeType.DETACH,
      CascadeType.MERGE,
      CascadeType.REFRESH
    ],
    fetch = FetchType.LAZY
  )
  @JoinTable(
    name = "rt_driver_race",
    joinColumns = [JoinColumn(name = "race_id")],
    inverseJoinColumns = [JoinColumn(name = "driver_id")]
  )
  val races: List<Race>

@Entity
@Table(name = "rt_race")
data class Race(
  @Id @GeneratedValue
  var id: Long,
  val raceNumber: Int,
  val year: Int,

  @OneToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "circuit_id")
  val circuit: Circuit,

//  Not the owning side of relationship
  @ManyToMany(
    mappedBy = "races",
    fetch = FetchType.EAGER,
    cascade = [
      CascadeType.DETACH,
      CascadeType.MERGE,
      CascadeType.REFRESH
    ]
  )
  val drivers: List<Driver>
)

I have a simple sql script that creates these tables for me (including the join table because something is wrong with JPA it seems and refuses to create the table)

        create table if not exists rt.public.rt_race
    (
        id bigint not null
            constraint rt_race_pkey
                primary key,
        year int,
        race_number int,
        circuit_id bigint
            constraint rt_result_circuit_id_fkey
                references public.rt_circuit
    )

    create table if not exists rt.public.rt_driver
    (
        id bigint not null
            constraint rt_driver_pkey
                primary key,
        first_name varchar(255),
        last_name varchar(255),
        team varchar(255),
        age int
    );

create table if not exists rt.public.rt_driver_race
(
    id bigint not null
        constraint rt_driver_race_pkey
            primary key,
    race_id bigint
        constraint rt_driver_race_race_id_fkey
            references public.rt_race,
    driver_id bigint
        constraint rt_driver_race_driver_id_fkey
            references public.rt_driver
)

I understand that you can only persist via the owning side which is the driver. So I wrote the following on the driver side:

//    TODO instead of null return notfound error
    val driverToUpdate = driverRepository.findById(driverView.id)
    return if (driverToUpdate.isPresent) {
      driverRepository.save(
        Driver(
          driverView.id,
          driverView.firstName ?: driverToUpdate.get().firstName,
          driverView.lastName ?: driverToUpdate.get().lastName,
          driverView.team ?: driverToUpdate.get().team,
          driverView.age ?: driverToUpdate.get().age,
          driverView.races?.let { raceService.findAllById(it.map { it.id }) } ?: driverToUpdate.get().races
        )
      )
    } else {
      null
    }
  }

and

  fun updateRace(raceView: RaceView): Race? {
//    TODO instead of null return notfound error
    val raceToUpdate = raceRepository.findById(raceView.id)
    return if (raceToUpdate.isPresent) {
      raceView.drivers?.map { driverService.updateDriver(it.copy(id =raceView.id)) }
      raceRepository.save(
        Race(
          raceView.id,
          raceView.raceNumber ?: raceToUpdate.get().raceNumber,
          raceView.year ?: raceToUpdate.get().year,
          circuitService.getCircuit(raceView.circuitId) ?: raceToUpdate.get().circuit,
          raceView.drivers?.let { driverService.findAllById(it.map { it.id }) } ?: raceToUpdate.get().drivers
        )
      )
    } else {
      null
    }
  }

On the race side.

1. If I update on the driver side, the json returned is what I would expect, except the change is not persisted in the database
2. If I update on the racing side I get the message "Failing row contains (null, 3, 2)." Which I think means that the id in the joining table is not automatically generated. (I know I can create a composite primary key of the other two columns but this still doesn't fix the problem:)) If I'm not wrong JPA usually adds this id itself right?

I'm at a loss, I must be missing something very obvious but can't seem to find it. Any help would be greatly appreciated :).

Solution

After hours of not getting it to work I gave up on using JPA and used a direct query to solve the problem

Answered By - Yvan Stemmerik