Should Hibernate be able to handle overlapping foreign keys?

Question

I have a table that has two foreign keys to two different tables with both foreign keys sharing one column:

CREATE TABLE ZipAreas
(
  country_code CHAR(2) NOT NULL,
  zip_code VARCHAR(10) NOT NULL,
  state_code VARCHAR(5) NOT NULL,
  city_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (country_code, zip_code, state_code, city_name),
  FOREIGN KEY (country_code, zip_code) REFERENCES Zips (country_code, code),
  FOREIGN KEY (country_code, state_code, city_name) REFERENCES Cities (country_code, state_code, name)
)

As you can see, there are two FKs sharing country_code (coincidentally referencing the same column at the end of the referentiation path). The entity class looks like (JPA 1.0 @IdClass):

@Entity
@Table(name = "ZipAreas")
@IdClass(value = ZipAreaId.class)
public class ZipArea implements Serializable
{
    @Id
    @Column(name = "country_code", insertable = false, updatable = false)
    private String countryCode;

    @Id
    @Column(name = "zip_code", insertable = false, updatable = false)
    private String zipCode;

    @Id
    @Column(name = "state_code", insertable = false, updatable = false)
    private String stateCode;

    @Id
    @Column(name = "city_name", insertable = false, updatable = false)
    private String cityName;

    @ManyToOne
    @JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "zip_code", referencedColumnName = "code")})
    private Zip zip = null;

    @ManyToOne
    @JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code", insertable = false, updatable = false), @JoinColumn(name = "state_code", referencedColumnName = "state_code"), @JoinColumn(name = "city_name", referencedColumnName = "name")})
    private City city = null;

    ...
}

As you can see I flagged the countryCode property and city's country_code @JoinColumn as read-only (insertable = false, updatable = false). Hibernate fails with this saying:

Exception in thread "main" javax.persistence.PersistenceException: [PersistenceUnit: geoinfo] Unable to configure EntityManagerFactory
    at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:374)
    at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:56)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:48)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:32)
    at tld.geoinfo.Main.main(Main.java:27)
Caused by: org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed: tld.geoinfo.model.ZipAreacity
    at org.hibernate.cfg.Ejb3Column.checkPropertyConsistency(Ejb3Column.java:563)
    at org.hibernate.cfg.AnnotationBinder.bindManyToOne(AnnotationBinder.java:2703)
    at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1600)
    at org.hibernate.cfg.AnnotationBinder.processIdPropertiesIfNotAlready(AnnotationBinder.java:796)
    at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:707)
    at org.hibernate.cfg.Configuration$MetadataSourceQueue.processAnnotatedClassesQueue(Configuration.java:3977)
    at org.hibernate.cfg.Configuration$MetadataSourceQueue.processMetadata(Configuration.java:3931)
    at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1368)
    at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1345)
    at org.hibernate.ejb.Ejb3Configuration.buildMappings(Ejb3Configuration.java:1477)
    at org.hibernate.ejb.EventListenerConfigurator.configure(EventListenerConfigurator.java:193)
    at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:1096)
    at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:278)
    at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:362)
    ... 4 more

This looks pretty basic to me honestly. "Mixing insertable and non insertable columns in a property is not allowed" is such a weak "excuse", isn't it?

Should Hibernate be able to handle this, e.g. according to the JPA spec? Is this a bug?

hibernate does dislike composite primary keys - in some situations like this one it messes up. try to refactor your database to use single column primary keys. — Andreas Petersson, May 15 '11 at 19:44
Yeah Hibernate sucks at composite keys. In any case, this has never been reported, now it is: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6221 — Kawu, May 15 '11 at 20:00

score 35 · Answer 1 · edited Jul 12 '23 at 13:46

35

There is a way to bypass the validation and get it to work, thus indicating the column is a @JoinColumnsOrFormulas then put the solution:

Error:

@ManyToOne
@JoinColumns(value = {
    @JoinColumn(name = "country_code", referencedColumnName = "country_code"),
    @JoinColumn(name = "zip_code", referencedColumnName = "code")
})
private Zip zip = null;
    
@ManyToOne
@JoinColumns(value = {
    @JoinColumn(name = "country_code", referencedColumnName = "country_code", insertable = false, updatable = false),
    @JoinColumn(name = "state_code", referencedColumnName = "state_code"), 
    @JoinColumn(name = "city_name", referencedColumnName = "name")
})
private City city = null;

OK:

@ManyToOne
@JoinColumns(value = {
    @JoinColumn(name = "country_code", referencedColumnName = "country_code"), 
    @JoinColumn(name = "zip_code", referencedColumnName = "code")
})
private Zip zip = null;

@ManyToOne
@JoinColumnsOrFormulas(value = {
    @JoinColumnOrFormula(formula = @JoinFormula(value = "country_code", referencedColumnName = "country_code")),
    @JoinColumnOrFormula(column = @JoinColumn(name = "state_code", referencedColumnName = "state_code")),
    @JoinColumnOrFormula(column = @JoinColumn(name = "city_name", referencedColumnName = "name"))
})
private City city = null;

edited Jul 12 '23 at 13:46

MC Emperor

22,334
15
80
130

answered Oct 30 '12 at 20:48

Manu Navarro

700
6
9

9

I believe this should be the accepted answer, because it provides a working solution right now and not somewhere in the future when hibernate 5 is GA. – azerole Jan 28 '13 at 12:49
@ManuNavarro Why are there more columns of city property at good sample than at bad sample? – banterCZ Jan 30 '13 at 16:01
Hello @banterCZ, moves to the left and see the third relation – Manu Navarro Feb 01 '13 at 16:11
In my case I get an impossible cast from formula to column. The formula would actually enforce the read-only state, but it seems not to work here. – Ben Jul 22 '16 at 12:27
3

From what I read is that the ClassCastException from Formula to Column does not work if a Formula is used on an @Id column. The Formula would cause it to be explicitly non-insertable, while Hibernate wants them to be insertable: http://stackoverflow.com/questions/19519781/hibernate-and-joinformula-org-hibernate-mapping-formula-cannot-be-cast-to-org – Ben Jul 25 '16 at 11:32

score 10 · Accepted Answer · edited Sep 25 '17 at 08:12

10

Will be supported with Hibernate 5, see https://hibernate.atlassian.net/browse/HHH-6221

edited Sep 25 '17 at 08:12

Jagger

10,350
9
51
93

answered Mar 05 '12 at 14:01

Kawu

13,647
34
123
195

3

It seems that this has been moved to Hibernate 6. – Daniel Scott Dec 22 '15 at 09:12
2

Here is the issue on the hibernate atlassian issues page: https://hibernate.atlassian.net/browse/HHH-6221 – Ben Jul 25 '16 at 11:26
Created in 2011. Updated late 2017. What is it that makes this such a difficult feature to include? – Jordan Mackie Aug 03 '18 at 08:59

score 5 · Answer 3 · answered Nov 15 '17 at 18:32

This is still not solved in Hibernate 5. However, if I use @JoinColumnsOrFormulas I get ClassCastException. Appending insertable = false, updatable = false on all join columns solved my problem:

Example:

@ManyToOne
@JoinColumns(value = {
    @JoinColumn(name = "country_code", referencedColumnName = "country_code", insertable = false, updatable = false),
    @JoinColumn(name = "state_code", referencedColumnName = "state_code", insertable = false, updatable = false), 
    @JoinColumn(name = "city_name", referencedColumnName = "name", insertable = false, updatable = false)})
private City city = null;

Ben · Answer 4 · 2016-07-26T12:44:39.870

I am using hibernate 5 and I still get this the exception. If you add insert="false", update="false" only to one, you will get an exception stating that you mixed insertable and non-insertable columns and that this is not allowed. This is an issue that is already in the tracker, but seems not to be resolved. Hibernate throws AnnotationException on column used by multiple overlapping foreign keys

In our case, this meant that we migrated to EclipseLink, which is in fact pretty easy given that you mainly need to replace the persistence.xml and rewrite the HSQL (Hibernate SQL) to JPQL (JPA SQL). Also you might need to replace custom naming strategies (Eclipse calls them SessionCustomizer). Of course it might be harder to do if you use special features of hibernate such as hibernate search etc. But in our case, we tried to fix overlapping foreign keys for weeks when the migration only took hours in the end.

Should Hibernate be able to handle overlapping foreign keys?

4 Answers4

Linked