Should Hibernate be able to handle overlapping foreign keys?

Question

I have a table that has two foreign keys for two different tables with two foreign keys sharing the same 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 referring to the same column at the end of the referencing 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 marked the countryCode and city country_code @JoinColumn property as read-only (insertable = false, updatable = false). Hibernate fails with this statement:

 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 

It looks pretty straightforward to me. “Mixing inserted and not inserted columns into a property is not allowed” is such a weak “excuse”, isn't it?

If Hibernate can handle it, for example. according to JPA specification? This is mistake?

Answer 1

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

Answer 2

There is a way around the check and make it work, indicating that the column is "@JoinColumnsOrFormulas", and then put the solution:

Mistake:

 @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; 

Hi,

Answer 3

I am using hibernate 5 and I am still getting this exception. If you add insert = "false", update = "false" to only one, you will get an exception, indicating that you are mixing inserted and non-inserted columns and that this is unacceptable. This is a problem that is already in the tracker, but it does not seem to solve it. Hibernate throws AnnotationException on a column used by multiple overlapping foreign keys

In our case, this meant that we switched to EclipseLink, which is actually quite easy, given that you basically need to replace persistence.xml and rewrite HSQL (Hibernate SQL) with JPQL (JPA SQL). You may also need to replace custom naming strategies (Eclipse calls them SessionCustomizer). Of course, this can be harder to do if you use special sleep mode features such as sleep search, etc. But in our case, we tried to fix the overlapping foreign keys for several weeks, when only a few hours passed at the end of the migration.

Answer 4

This is still not allowed in Hibernate 5. However, if I use @JoinColumnsOrFormulas , I get a ClassCastException. Adding insertable = false, updatable = false to all connection columns resolved 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;