I have a mapped entity like this:

@OneToMany(targetEntity = Child.class)
@JoinColumn(name = "PARENT_ID", referencedColumnName = "PARENT_ID")
@OrderBy("orderNumber")
private List<Child> children;

I would like to specify NULLS LAST in @OrderBy annotation of my mapped collection.

I am using Oracle database, which considers NULL values larger than any non-NULL values. The problem is in my integration test, which uses h2 database and it seems the NULL values are evaluated differently.

So far, I came up with a hack to use nvl2() function inside of the @OrderNumber like this:

@OrderBy("nvl2(orderNumber, orderNumber, 100000000)")

This hack works, but it seems nasty and I don't like the idea that I have this code there just because of the integration tests. As I mentioned above, Oracle returns the rows in correct order by default, so the basic @OrderBy(orderNumber) without handling nulls works good. On the other hand, I want to have it tested in case the app will use different database.

Is there any way how to solve this issue in a better way?

CodePudding user response：

To enable Oracle compatibility mode in H2 you need to append ;MODE=Oracle;DEFAULT_NULL_ORDERING=HIGH to JDBC URL as suggested in documentation: https://h2database.com/html/features.html#compatibility

The DEFAULT_NULL_ORDERING setting changes the default ordering of NULL values (when neither NULLS FIRST nor NULLS LAST are used). There are four possible options, LOW is default.

This setting can be used separately without this compatibility mode if you don't need it.