Andrew Eells dot com

Hibernate foreign-key collections and database best practice

I came across the following situation having noticed we were not enforcing database NOT NULL column constraints, even though all column entries were non-null. This is not best-practice from a database perspective and so I thought I’d look into it.

So, I have 2 tables, ENQUIRY and ELEMENT with a One-to-Many relationship such that an Enquiry has many Elements. And I wanted to enforce the NOT NULL constraint on foreign key column ELEMENT.ENQUIRY_ID. This relationship looks like so when modeling the Enquiry object with Hibernate:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "ENQUIRY_ID", referencedColumnName = "ID")
private Set elements = new HashSet();


When I enforced the NOT NULL constraint at the database level I received the following stacktrace however:

Caused by: java.sql.BatchUpdateException: ORA-01400: cannot insert NULL into ("ELEMENT"."ENQUIRY_ID")


So Hibernate is obviously persisting the collection of elements before the parent enquiry and then going back and doing an UPDATE on the foreign key field afterwards (so INSERT collection items with NULL foreign key, INSERT parent, UPDATE collection item foreign keys) as it doesn’t realise the foreign key column will always be non-null.

You must explicitly tell Hibernate that the foreign key column is NOT NULL. It can then persist the parent enquiry first followed by the collection items. To do this, add the following clause to the @JoinColumn annotation:

@JoinColumn(name = "ENQUIRY_ID", referencedColumnName = "ID", nullable = false)


This way, not only are you ensuring database best-practice, but Hibernate will also have to issue less statements (so INSERT parent, INSERT collection items) which should be a more efficient transaction statement.

The javax.persistence.JoinColumn API only states that the nullable clause indicates whether the foreign key column is nullable but nowhere could I find how this nuance affects Hibernate and the queries it issues – it’s probably out there someplace but I hope this post can help anyone with a similar problem.

Thanks to axtavt on stackoverflow for pointing out the obvious!

Hibernate’s @FilterJoinTable

Couldn’t find a completely articulate example or full documentation of how to add a filter to a Hibernate Many-to-Many collection association mapping, so thought I’d would post a complete example here.

It’s possible to apply the filter to the target entity table which is reasonably well documented, or at least you can have an educated guess, but also to the association table of the Many-to-Many relationship where the docs were a little less clear.

I have the following relational association table:

CREATE TABLE departure_point_resort_association (
  departure_point_id      VARCHAR(36) NOT NULL,
  resort_id               VARCHAR(36) NOT NULL,
  site_id                 ENUM('30', '31'...) NOT NULL,
  PRIMARY KEY (departure_point_id, resort_id),
  CONSTRAINT assoc_departure_point_fk FOREIGN KEY (departure_point_id)
    REFERENCES departure_point (id),
  CONSTRAINT assoc_resort_fk FOREIGN KEY (resort_id) REFERENCES resort (id)
);

which is mapping a ski resort to potential departure points or airports. There was no real requirement to persist an actual SITE table (which would be entirely static data) and to map to it’s foreign key value, so instead am using a simple MySQL ENUM column above, and wanted to apply a filter at the application level to the collection of Resorts based on this site_id.

@Entity @Table
@FilterDef(name = "siteFilter", parameters = {
    @ParamDef(name = "siteId", type = "string")
})
public final class Resort extends BasePersistentObject {
    @ManyToMany(fetch = FetchType.EAGER)
    @FilterJoinTable(name = "siteFilter", condition = "site_id = :siteId")
    @JoinTable(name = "DEPARTURE_POINT_RESORT_ASSOCIATION",
            joinColumns = {
                    @JoinColumn(name = "RESORT_ID")
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "DEPARTURE_POINT_ID")
            }
    )
    private Set departurePoints;

Then it’s simply a case of enabling the filter in the client (DAO or whatever your framework dictates):

session.enableFilter("siteFilter").setParameter("siteId",  String.valueOf(siteId));

Tags: Hibernate