Field that is Primary key and part of composite foreign key it is not working in SpringBoot Hibernate

Question

I am trying to create a composite foreign key to another table in java JPA.

This is the SQL querys for both tables:

CREATE TABLE Ticket_Type (
    event_id int REFERENCES Event(id) ON DELETE CASCADE,
    ticket_type_id smallint,
    name varchar(15),
    price int, 
    total_ticket_quantity int, 
    number_remaining int,
    PRIMARY KEY(event_id, ticket_type_id)
);

CREATE TABLE Attendee_Event (
    person_id varchar(10) REFERENCES Person(id) ON DELETE CASCADE,
    event_id int REFERENCES Event(id) ON DELETE CASCADE,
    ticket_number int,
    ticket_type smallint,
    FOREIGN KEY (event_id, ticket_type) REFERENCES Ticket_Type(event_id, ticket_type_id) ON DELETE RESTRICT,
    PRIMARY KEY (person_id, event_id)
);

I already have that database, and I'm tryng to map it to the database, so in my application.properties I setted up spring.jpa.hibernate.ddl-auto=validate, so I do not change in any way the database.

But I am getting an error while trying to start the application. I have searched every place for an answer, but I find nothing :(

This is my Ticket Type entity

@Entity
@Table(name = "Ticket_Type")
public class TicketType {
    @Id
    private TicketTypeCompositeKey id;
    @Column(name = "name")
    @Size(min = 1, max = 15)
    private String name;
    @Column(name = "price")
    private int price;
    @Column(name = "total_ticket_quantity")
    private int totalTicketQuantity;
    @Column(name = "number_remaining")
    private int numberRemaining;

    @OneToMany(mappedBy = "ticketType", fetch = FetchType.LAZY)
    private List<AttendeeEvent> ticketBuyers;
}

This is my Ticket Type Composite Key

@Embeddable
@EqualsAndHashCode
public class TicketTypeCompositeKey implements Serializable {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "event_id", referencedColumnName = "id")
    private Event event;

    @Column(name = "ticket_type_id")
    private short ticketTypeId;
}

This is my Attendee Event entity

@Entity
@Table(name = "Attendee_Event")
@Data
public class AttendeeEvent {
    @Id
    private AttendeeEventCompositeKey id;

    @Column(name = "ticket_number")
    private int ticketNumber;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumns({
            @JoinColumn(name = "ticket_type", referencedColumnName = "id.ticketTypeId"),
            @JoinColumn(name = "event_id", referencedColumnName = "id.eventId")
    })
    private TicketType ticketType;  // FIXME This relationship is not working
}

This is the error I am getting:

Failed to initialize JPA EntityManagerFactory: A '@JoinColumn' references a column named 'id.ticketTypeId' but the target entity 'com.implementacioneintegracion.Parcial.Ticket.Entity.TicketType' has no property which maps to this column

Answer 1

Turns out that the referencedColumnName attribute in the @JoinColumn annotation, should hold the actual name of the column in the database (or if you are creating it with Hibernate, the name you defined in the @Column or default name if you didn't specify it, in any case, it should have the name that the column will have in the database).

After doing this, I had this error message:

Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: Column 'event_id' is duplicated in mapping for entity 'com.implementacioneintegracion.Parcial.Event.Entity.MiddleTables.AttendeeEvent.AttendeeEvent' (use '@Column(insertable=false, updatable=false)' when mapping multiple properties to the same column)

To solve this, you have to do like the error says, put insertable=false, updatable=false in the @JoinColumn annotation. This properties are meant so that JPA will not create or update into existance those columns in the actual database.

But there is a catch, if you do it with only the column that is repeated, it will throw this error:

Failed to initialize JPA EntityManagerFactory: Column mappings for property 'null' mix insertable with 'insertable=false'

All this means is that you are mixing a column that cannot be inserted or updated with a column that can inside a same @JoinColumns, so to solve this, you also have to add this properties to the other column in the foreign key.

In summary, my foreign key in the AttendeeEvent entity ended up looking like this:

@ManyToOne(fetch = FetchType.EAGER)
// Turns out the @JoinColumn should reference the actual name in the table, not the variable name in the entity
@JoinColumns(value = {
        @JoinColumn(name = "ticket_type", referencedColumnName = "ticket_type_id", insertable = false, updatable = false),
        @JoinColumn(name = "event_id", referencedColumnName = "event_id", insertable = false, updatable = false)
    })
private TicketType ticketType;

Thanks to: Thanks to this post for answering the question: https://discourse.hibernate.org/t/how-to-map-a-column-to-a-jpa-property-if-it-was-already-mapped-in-an-manytoone-association-when-using-hibernate/2013

Extra Notes: I also found out that you can create a composite primary key by annotating multiple fields with @Id. GitHub copilot led me to believe that this was not possible, and it was necessary to create an embeddable class, so in the end I changed my embedded primary key to this.

@Id
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "person_id", referencedColumnName = "id")
private Person person;

@Id
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "EVENT_ID", referencedColumnName = "id")
private Event event;

If someone knows if this is good practice or not, I would be super thankful.

Anyways, thanks for reading, and hope I helped you with your issue.

Note 2: Just found out why the composite key is used in another class, when you are going to make your DAO, and extend the JpaRepository<> you are expected to give two generic types, the entity, and the type of the primary key. Other thing is that if you try to make JPQL querys with entities that have composite keys, but you didn't make an own composite key class by itself, it the query will not work. I tried thoroughly but it did not work, the moment I created the composite key class once again, it worked. So in resume use the embedded composite key class. At least in my experience.