JPA: @OneToMany mapping and composite primary key - Logical column not found error

Question

I have issues with mapping OneToMany foreign key to a composite primary key. I have already tried many solutions, including this post @OneToMany and composite primary keys?.

So the situation is:

I have two entities, say, Box & Color and the composite primary key is on the child side (Color).

@Entity
@Table(name = "box")
data class Box(
        @Id
        var id: Int = 0,
        ...
       @OneToMany(cascade = [CascadeType.ALL])
        @JoinColumns(
                JoinColumn(name = "box_id", referencedColumnName = "id"),
                JoinColumn(name = "locale", referencedColumnName = "locale"))
        val colors: List<Color> = emptyList(),
)

@Entity
@Table(name = "color")
data class Color(
        
        @EmbeddedId
        var colorId: ColorId? = null,
) : Serializable

@Embeddable
data class ColorId(
        var id: Int = 0,
        @Column(name = "locale", insertable = false, updatable = false)
        var locale: Locale = Locale.Germany
) : Serializable

So, in the Box entity, I am trying to create an OneToMany mapping between Box & Color entities. For this should I use the composite primary key of the Color entity? If I try to join columns to the composite primary key(as I have done in the Box entity) I do get an error saying - unable to locate the logical column "locale".

How can I resolve this?

Or what is the neat solution for this problem?

Answer 1

OneToMany mapping will connect one Box with many Colors. Therefore, each Color needs to point to a Box, so each Color has a foreign key referencing the primary key of Box (of Int type).

@OneToMany as an owning side

In your mapping, you should use a single join column since this join column will be placed in the color table:

@Entity
@Table(name = "box")
data class Box(
        @Id
        var id: Int = 0,
        ...

        @OneToMany(cascade = [CascadeType.ALL])
        @JoinColumn(name = "box_id", referencedColumnName = "id")
        var colors: MutableList<Color> = mutableListOf()
)

You can check-out the working sample (Kotlin and Java) in my github repo.

@OneToMany as an inverse (mapped-by) side

Database perspective

@OneToMany as an owning side and @OneToMany as an inverse side (with a complimentary mandatory @ManyToOne) produce the same database schema - a foreign key on the color table. That's why @JoinColumn annotation looks the same in both cases, no matter which side you place it on - the goal is to produce a foreign key on the many side.

Application perspective

The difference in 'owning-side' is in an application perspective. JPA/hibernate saves the relationships only from the owning side.

So, if you change the owning side, you must set up this side (attribute) in your application code. In this case, you must set up Box in Color, otherwise, JPA/hibernate will not create a relationship (even though you added your Colors to Box). Moreover, it will not raise any exception, just the relationship will not be created. Next time, you will retrieve your Box from a database, the Color list will be empty.

You can check-out the working samples and see the difference in ownership in my github repo.

@Entity
@Table(name = "box")
data class Box(
        @Id
        var id: Int = 0,
        ...

        @OneToMany(cascade = [CascadeType.ALL], mappedBy="box")
        var colors: MutableList<Color> = mutableListOf(),
)

@Entity
@Table(name = "color")
data class Color(
        
        @EmbeddedId
        var colorId: ColorId? = null,
        
        // the owning side is changed, therefore you MUST set the box in Color
        // otherwise the relationship in a database will not be saved (!)
        @ManyToOne
        @JoinColumn(name = "box_id")
        var box: Box? = null
) : Serializable

Other possible problems

Since you struggle with a OneToMany relationship, it might be useful for you to consider some other possible problems in this model.

Locale non-insertable and non-updatable

Another problem, you may encounter, is saving locale in ColorId as you marked it as non-insertable and non-updatable. If this is on purpose that's fine (in that case all your colors must be pre-inserted into the database, or they will be inserted without Locale).

Please, keep in mind, that setting Locale.GERMAN has no effect on a database in this case. It will be silently ignored and if you don't have such a color in a database, it will be inserted with null.

Color assigned to only one box

If you model this relationship, you make a single Color (like Black in German) be assigned to only one Box. It sounds a bit unnatural. Usually, I would assume Black can be assigned to many Boxes. So, it would be a ManyToMany relationship. Again, if this is on purpose, that's fine!

ColorId as a primary key

It's also a bit unnatural to have Locale as a part of a primary key in Color -- Black in German and Black in English as different colors? The colors themselves are locale-independent. The name of a color is locale-dependent, but it's more a matter of UI. Again, if this is on purpose, that's fine! After all, it's your business model!

Answer 2

Before providing you a solution, i would like to give you more info on the @OneToMany and the @JoinColumns annotation.

A @OneToManymapping symbolizes the fact that the Entity which has that mapping applied has many references to the other entity.

i.e. A Box has many references to Color(s) in your case.

Now to model this, I would suggest you reverse the mapping and use @ManyToOne on the Color entity.

@Entity
@Table(name = "color")
data class Color(
        
        @EmbeddedId
        var colorId: ColorId? = null,
        @ManyToOne(fetch = LAZY)
        @JoinColumn(name = "box_id"), //referencedColumnName is not needed here as it is inferred as "id"
        var box: Box? = null
) : Serializable

Because the foreign key is supposed to be in Color. You cannot have foreign keys from Box to Color as there may be more than one Color.

Also, note that it is not @JoinColumns, it is the singular @JoinColumn. You need the plural version if the Box entity contains a Composite Primary Key.

With that being said, you can all together ignore the @OneToMany mapping, because if you need to get all Colors of a Box, you can and should in my opinion, simply use the Query API.

A @OneToMany mapping in this case would only be a convenience. But if you insist on having the @OneToMany mapping then you can use the following.

@Entity
@Table(name = "box")
data class Box(
        @Id
        var id: Int = 0,
        ...
       @OneToMany(cascade = [CascadeType.ALL], mappedBy="box") // mappedBy will use the "box" reference from the Color Class
        val colors: List<Color> = emptyList(),
)

Answer 3

You have two options. Either define a join table @JoinTable(name = "color_box_assignment", joinColumns = ...) or use an inverse mapping:

@Entity
@Table(name = "color")
data class Color(
        
        @EmbeddedId
        var colorId: ColorId? = null,
        @ManyToOne(fetch = LAZY)
        @JoinColumns(
            JoinColumn(name = "box_id", referencedColumnName = "id"),
            JoinColumn(name = "locale", referencedColumnName = "locale"))
        var box: Box? = null
) : Serializable