Schema generation with EclipseLink and Hibernate (JPA2.1) - @ForeignKey ignored

I am testing JPA2.1 and the new "schema generation" feature. For that, I tested two implementations under the HyperSQL database :

  • EclipseLink 2.5.2-M1 which is the reference implementation.
  • Hibernate 4.3

I don't have any preference for an implementation (not even for performance). I tested EclipseLink because it was the first JPA2.1 implementation available, but now, Hibernate 4.3 is here and JPA2.1 compliant. The only thing I want is to get something independent from the JPA provider - except for the configuration (like cache, logging, static weaving, ...).

As for schema generation, several aspect are interesting me :

  • I'm not satisfied with the default naming strategy of EclipseLink : upper casing the entity class name, or the field/property name, make it unreadable. And I don't to extends the session customizer (I tried once and I thought it was easier, less dangerous to put annotations...).
  • I don't like Upper-case in queries; well that's a personal preference but I also had problems with mySQL on linux, because they don't follow the identifier and keywords are case insensitive unless you double-quote them SQL idiom.
  • Hibernate is, by default, not much better, but the naming strategy can be improved using ImprovedNamingStrategy. Sadly, it does not name the foreign key with an explicit name : FK_8gc2pk9u5bsbhpi91x93c77o is not explicit, while fk_foobar_sample is.

Thus, I added @Table, @JoinColumn and @Column annotation to force my naming strategy and now I'm blocked with foreign key generation, whose support seems to be pretty poor (when complying to JPA2.1) :

  • Hibernate wants me to use the @org.hibernate.annotations.ForeignKey, and for that I need to annotate the field or property. But it is an hibernate annotation, hence it is not JPA2.1 compliant.
  • EclipseLink never generate foreign key constraits, unless I put a @JoinColumn and a foreignKey with @ForeignKey(name="..."). But while it generate a foreign key, the definition is missing (ie: ALTER TABLE foobar ADD CONSTRAINT fk_foobar_zorg FOREIGN KEY () REFERENCES ()) while it should be present according to the Javadoc.
  • @ManyToOne is required for Hibernate to generate the foreign key (an exception is thrown), while it is not for EclipseLink. I don't understand why it is required : if the referenced type is annotated @Entity then why should I put @OneToOne, @ManyToOne, @OneToMany and @ManyToMany when it can perfectly be deduced from the referenced classes.
  • EclipseLink do not generate foreign key constraints for field annotated with @JoinColumn without a foreignKey set.

What should I do to the get the job done in a pure JPA2.1 way ?

Sources

Because my sample case is somehow too big to be put on Stackoverflow, you will find an archive with 4 maven 3 project :

  • jpa2.1-parent : parent project (POM)
  • jpa2.1-eclipselink : EclipseLink 2.5.2-M1 with @Table and @JoinColumn annotations.
  • jpa2.1-eclipselink-default : EclipseLink 2.5.2-M1 without @Table and @JoinColumn annotations and default naming strategy
  • jpa2.1-hibernate : Hibernate 4.3 with custom names.
  • jpa2.1-hibernate-default : Hibernate 4.3 without @Table and @JoinColumn annotations and default naming strategy

Simply run mvn test on the root project, and run whatever diff tool you like :

colordiff ./jpa2.1-eclipselink/database-create.sql ./jpa2.1-hibernate/database-create.sql
vimdiff ./jpa2.1-eclipselink/database-create.sql ./jpa2.1-hibernate/database-create.sql
diff ./jpa2.1-eclipselink/database-create.sql ./jpa2.1-hibernate/database-create.sql
kdiff3 ./jpa2.1-eclipselink/database-create.sql ./jpa2.1-hibernate/database-create.sql

The POM can also be imported into Eclipse (tested on Kepler), using the Maven Plugin for Eclipse.

Generated SQL

For reference, here is the result of different SQL file (the drop file are wrong too, but only because the constraint name is not overloaded).

EclipseLink

As you can see, the foreign key constraints is syntaxically invalid; and there are two missing constraints on foobar (one to zorg, the other to grumph).

    CREATE TABLE foobar (ID BIGINT NOT NULL, grumph_id BIGINT, sample_id BIGINT, zorg_id BIGINT, PRIMARY KEY (ID))
    CREATE TABLE foobar_getter (ID BIGINT NOT NULL, grumph_id BIGINT, sample_id BIGINT, zorg_id BIGINT, PRIMARY KEY (ID))
    CREATE TABLE sample (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    CREATE TABLE zorg (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    CREATE TABLE grumph (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    ALTER TABLE foobar ADD CONSTRAINT fk_foobar_zorg FOREIGN KEY () REFERENCES  ()
    ALTER TABLE foobar_getter ADD CONSTRAINT fk_foobar_getter_zorg FOREIGN KEY () REFERENCES  ()
    ALTER TABLE foobar_getter ADD CONSTRAINT fk_foobar_getter_sample FOREIGN KEY () REFERENCES  ()

EclipseLink (no @Table, @JoinColumn)

No surprise here : EclipseLink would not be used if it was not able to generate something at least correct.

    CREATE TABLE FOOBAR (ID BIGINT NOT NULL, GRUMPH_ID BIGINT, SAMPLE_ID BIGINT, ZORG_ID BIGINT, PRIMARY KEY (ID))
    CREATE TABLE FOOBARGETTER (ID BIGINT NOT NULL, GRUMPH_ID BIGINT, SAMPLE_ID BIGINT, ZORG_ID BIGINT, PRIMARY KEY (ID))
    CREATE TABLE SAMPLE (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    CREATE TABLE ZORG (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    CREATE TABLE GRUMPH (ID BIGINT NOT NULL, PRIMARY KEY (ID))
    ALTER TABLE FOOBAR ADD CONSTRAINT FK_FOOBAR_SAMPLE_ID FOREIGN KEY (SAMPLE_ID) REFERENCES SAMPLE (ID)
    ALTER TABLE FOOBAR ADD CONSTRAINT FK_FOOBAR_GRUMPH_ID FOREIGN KEY (GRUMPH_ID) REFERENCES GRUMPH (ID)
    ALTER TABLE FOOBAR ADD CONSTRAINT FK_FOOBAR_ZORG_ID FOREIGN KEY (ZORG_ID) REFERENCES ZORG (ID)
    ALTER TABLE FOOBARGETTER ADD CONSTRAINT FK_FOOBARGETTER_SAMPLE_ID FOREIGN KEY (SAMPLE_ID) REFERENCES SAMPLE (ID)
    ALTER TABLE FOOBARGETTER ADD CONSTRAINT FK_FOOBARGETTER_ZORG_ID FOREIGN KEY (ZORG_ID) REFERENCES ZORG (ID)
    ALTER TABLE FOOBARGETTER ADD CONSTRAINT FK_FOOBARGETTER_GRUMPH_ID FOREIGN KEY (GRUMPH_ID) REFERENCES GRUMPH (ID)

Hibernate

Hibernate ignore my custom foreign key name, unless I use their annotation.

    create table foobar (id bigint not null, grumph_id bigint, sample_id bigint, zorg_id bigint, primary key (id))
    create table foobar_getter (id bigint not null, grumph_id bigint, sample_id bigint, zorg_id bigint, primary key (id))
    create table grumph (id bigint not null, primary key (id))
    create table sample (id bigint not null, primary key (id))
    create table zorg (id bigint not null, primary key (id))
    alter table foobar add constraint FK_45nw30c81ae209hokgmrs9gyy foreign key (grumph_id) references grumph
    alter table foobar add constraint FK_b3pwyt79opfaopbjwaf23h11f foreign key (sample_id) references sample
    alter table foobar add constraint hb_foobar_zorg foreign key (zorg_id) references zorg
    alter table foobar_getter add constraint FK_1s2755a8mgvune6lhpfw8cifr foreign key (grumph_id) references grumph
    alter table foobar_getter add constraint FK_slbhet4s26lh9ma4vh63ltctj foreign key (sample_id) references sample
    alter table foobar_getter add constraint hb_foobar_getter_zorg foreign key (zorg_id) references zorg

Answers


Seems I'm running into bugs of both Hibernate HHH-8783 (I will comment it again, because this does not work with @JoinTable and @OneToMany/@ManyToMany) and EclipseLink 425656, so my question is not longer relevant.

Last thing I still need to understand (but it is outside the scope of my original question) is why I have an unique key generated for @OneToMany in Hibernate, and not in EclipseLink.


Need Your Help

XTEXT: Controlling when whitespace is allowed

whitespace dsl xtext

I have a custom scripting language, that I am attempting to use XTEXT for syntax checking. It boils down to single line commands in the format

Fastest way to read a text file

java java-io

What's the fastest way to read a text file for processing?