[FIXED] Hibernate is not mapping object correctly ("Bad value for type" exception) when using compound primary keys in a junction table

Issue

I am getting the exception o.h.e.j.s.SqlExceptionHelper | Bad value for type int : 9dac4fd2-a04c-4be7-976b-d880a43ea25a. It seems to want to put a UUID in an Integer field here.

I have the following tables, which admittedly are a bit complex in terms of compound keys:

CREATE TABLE public.event (
    id uuid NOT NULL,
    ...
    CONSTRAINT event_pkey PRIMARY KEY (id)
);

CREATE TABLE public.condition_set (
    api_id uuid NOT NULL,
    version integer NOT NULL,
    ...,
    CONSTRAINT condition_set_pkey PRIMARY KEY (api_id, version)
);

CREATE TABLE public.condition_set_event (
    condition_set_api_id uuid NOT NULL,
    condition_set_version integer NOT NULL,
    event_id uuid NOT NULL,
    CONSTRAINT condition_set_event_pkey PRIMARY KEY (condition_set_api_id, condition_set_version, event_id),
    CONSTRAINT fk_condition_set FOREIGN KEY (condition_set_api_id, condition_set_version) REFERENCES public.condition_set(api_id, version) ON DELETE CASCADE,
    CONSTRAINT fk_event FOREIGN KEY (event_id) REFERENCES public.event(id) ON DELETE CASCADE
);

In my model I have the Event class which is fairly straightforward. The ConditionSet class has a compound primary key matching the database structure, as follows:

@Entity
public class ConditionSet {

    @EmbeddedId
    private ConditionSetId id;
}

which looks like:

@Embeddable
public class ConditionSetId implements Serializable {

    private static final long serialVersionUID = 8110138933878596476L;

    private UUID apiId;
    private Integer version;

}

The tricky part is the ConditionSetEvent junction table which ALSO consists of a compound key, of which one is the compound key of ConditionSet

@Entity
public class ConditionSetEvent {

    @EmbeddedId
    private ConditionSetEventId id;

    @ManyToOne(fetch = FetchType.LAZY)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @MapsId("conditionSetId")
    @JoinColumns(foreignKey = @ForeignKey(name = "fk_condition_set"), value = {
        @JoinColumn(nullable = false, name = "conditionSetApiId"),
        @JoinColumn(nullable = false, name = "conditionSetVersion")
    })
    private ConditionSet conditionSet;

    @ManyToOne(fetch = FetchType.LAZY)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @MapsId("eventId")
    @JoinColumn(foreignKey = @ForeignKey(name = "fk_event"))
    private Event event;

    public ConditionSetEvent(ConditionSet conditionSet, Event event) {
        this.conditionSet = conditionSet;
        this.event = event;
        this.id = new ConditionSetEventId(conditionSet.getId(), event.getId());
    }
}

with its EmbeddedId:

@Embeddable
public class ConditionSetEventId implements Serializable {

    private static final long serialVersionUID = -6269791751266804667L;

    private ConditionSetId conditionSetId;
    private UUID eventId;
}

However, if I try to query this junction table with this repository method:

public interface ConditionSetEventRepository extends JpaRepository<ConditionSetEvent, ConditionSetEventId> {

    @Query("select cse from ConditionSetEvent cse where cse.id.eventId = :eventId")
    List<ConditionSetEvent> findByEventId(UUID eventId);
}

then I get the error as mentioned on top (where the uuid in the exception is a valid ConditionSet.apiId, but that somehow seems to be re-used.

With trace logging:

DEBUG | org.hibernate.SQL | select conditions0_.condition_set_api_id as conditio0_8_, conditions0_.event_id as event_id1_8_, conditions0_.condition_set_api_id as conditio2_8_, conditions0_.condition_set_version as conditio3_8_ from condition_set_event conditions0_ where conditions0_.event_id=?
TRACE | o.h.t.d.sql.BasicBinder | binding parameter [1] as [OTHER] - [be1ec45d-6533-4e77-98b7-f9a357cda052]
TRACE | o.h.t.d.s.BasicExtractor | extracted value ([conditio0_8_] : [OTHER]) - [9dac4fd2-a04c-4be7-976b-d880a43ea25a]
WARN | o.h.e.j.s.SqlExceptionHelper | SQL Error: 0, SQLState: 22003
ERROR | o.h.e.j.s.SqlExceptionHelper | Bad value for type int : 9dac4fd2-a04c-4be7-976b-d880a43ea25a

So it does manage to extract that UUID value initially (the last trace line), but on the next step (for the Integer) it still is trying to use the UUID instead of the Integer.

Am I doing something wrong here?

Solution

I don't think you need the @JoinColumns and it is messing up which id column maps to which id field in ConditionSetEventId

    @Entity
    public class ConditionSetEvent {

      @EmbeddedId
      private ConditionSetEventId id;

      @ManyToOne(fetch = FetchType.LAZY)
      @OnDelete(action = OnDeleteAction.CASCADE)
      @MapsId("conditionSetId")
      private ConditionSet conditionSet;
      ....

Answered By - Kavithakaran Kanapathippillai