OrderColumn w/o nullable or default?

diathesis · February 22, 2019, 7:58pm

Hey,

Is it possible to use a JPA @OrderColumn with Hibernate without making the order column nullable or giving it a default?

I have an existing database with a sequence column mapped in a long-standing Hibernate 3.6-based non-JPA application, and the collection is mapped successfully as as follows:

                <list name="requirements" table="MEDICAL_REQUIREMENT" cascade="all-delete-orphan"
                        access="field">
                        <key column="applicationId" not-null="true" />
                        <list-index column="requirementIndex" />
                        <one-to-many class="MedicalRequirement" />
                </list>

We’re building out an API to access the same database which will likely eventually replace the back-end of existing application, but in the meantime, they need to share a schema. I tried to reproduce the mapping in the equivalent JPA with Kotlin:

	@OneToMany(fetch = LAZY, cascade = [ALL], orphanRemoval = true)
	@JoinColumn(name = "applicationId")
	@OrderColumn(name = "requirementIndex", nullable = false)
	private var _medicalRequirements = mutableListOf<MedicalRequirement>()

The mapping as a whole seems to work, but Hibernate (5.x) tries to write the medical requirement record without an order column, then (I assume, but I haven’t seen it yet because of the failure) update the order after the fact.

I can understand the simplicity of that – the entity itself is unaware of the order, only the parent’s collection knows the order. Having said that, I’d love to be able to use the same schema without modifications if I can. Is there any way around this?

I haven’t found much discussion on the topic, except in an EclipseLink bug – EclipseLink does the same thing, apparently, although I don’t really care, since I’m using Hibernate.

Suggestions welcome.

vlad · February 22, 2019, 8:18pm

Try mapping the association on the @ManyToOne side and use a bidirectional @OneToMany. That will allow Hibernate yo populate the FK more efficiently too. Check out this article for more details.

diathesis · February 22, 2019, 9:53pm

Ok, it was already bidirectional, but perhaps not properly. I’ve improved it somewhat, using your article as a reference point:

@OneToMany(mappedBy="application", cascade = [ALL], orphanRemoval = true)
private var _medicalRequirements = mutableListOf<MedicalRequirement>()

@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "applicationId")
@OrderColumn(name = "requirementIndex", nullable = false)
var application: Application? = null

Same result:

Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3072) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3663) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:359) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:292) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:200) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:131) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:192) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.firePersistOnFlush(SessionImpl.java:860) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.persistOnFlush(SessionImpl.java:853) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.spi.CascadingActions$8.cascade(CascadingActions.java:341) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:471) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:396) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:197) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:504) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:436) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:399) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:197) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:130) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:159) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:150) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:83) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:38) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:532) ~[spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
	... 74 common frames omitted
Caused by: java.sql.SQLException: Field 'requirementIndex' doesn't have a default value
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]

vlad · February 23, 2019, 11:55am

Try to replicate it with this test case template and I’ll take a look at it once you manage to reproduce the issue.

diathesis · February 23, 2019, 7:41pm

The template uses H2 with hbm2ddl schema generation, and, interestingly, doesn’t even seem to generate an order column in my initial test, which surprises me.

Options:

I can share the current template, and if you know why Hibernate isn’t generating the order column, we can fix that and get back to trying to reproduce the problem I’ve got …
I can convert the test template to using a custom schema of my own devising and include the schema in the template. I haven’t used H2 with hbm2ddl in a while, but it’s probably not that hard to go this route. It adds a bit more complexity to the “simple” test case though.

Which feels better to you? And how do people normally share these test cases with you?

vlad · February 23, 2019, 8:24pm

If the auto-generated ecample uses the order column, you can debug that a d compare to your application to see where they differ.

Just linking your GitHub fork is enough to share the replicating test case.

diathesis · February 23, 2019, 8:56pm

I can’t tell if it would use the order column because the generated table doesn’t contain the order column.

Here’s my repo:

If you know why Hibernate is not generating the order column, I can fix that and then see if it uses the order column.

vlad · February 24, 2019, 9:11am

@diathesis I created the HHH-13287 Jira issue for this. It is a problem indeed.

diathesis · February 25, 2019, 4:14pm

Updated my test case to move the @OrderColumn back. I moved it when I moved the rest, wasn’t sure if the mappedBy would result in OrderColumn being found there. Anyway, it’s back on Application, and as it seems like you’ve already agreed, the problem does persist.

Thanks for filing it. I’ll have alter the existing schema to use a default or a nullable column for now, I suspect.

Topic		Replies	Views
@OrderColumn broken order / null values Hibernate ORM	0	864	September 9, 2019
ManyToMany with OrderColumn in linking table Hibernate ORM	3	558	March 8, 2023
Hibernate not maintaining order of List of @OneToMany with @OrderColumn when list updated Hibernate ORM	4	3883	June 5, 2019
Null index column in bidirectionnal and hierarchical relationship Hibernate ORM	1	603	January 18, 2022
@JoinColumns should still join rows when one column is null Hibernate ORM	3	3114	October 1, 2018

OrderColumn w/o nullable or default?

Related Topics