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!

Oracle 11g UCP with Tomcat

Ensure you have the below tnsnames.ora file in TNS_ADMIN so as to connect to the server from client using connect string: sqlplus <username>@xyz

xyz =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(PORT = <port>)
(HOST = <server_name>)
)
(CONNECT_DATA = (SID = <SID>))
)