How to use database specific (Postgres) functions and operations?

Hibernate ORM
1

Is it still possible to use Postgres functions in JPQL with Hibernate 6?

Example:

SELECT * FROM dog d WHERE d.dayOfDeath - d.birthDay > make_interval(days => 1000)

dayOfDeath and birthDay are both of type LocalDateTime
Hibernate doesn’t know what make_interval is.

SELECT AVG(d.dayOfDeath - d.birthDay) FROM dog d

Hibernate doesn’t allow AVG() with interval. Postgres allows it. Looks like AvgFunction always expects a numeric. This is wrong for Postgres.

SELECT new ClassWithIntConstructor(ROUND(AVG(d.dayOfDeath - d.birthDay))) FROM dog d

Hibernate fails with an error because the constructor parameter is an integer. It expects something different.

All these queries worked perfectly fine with Hibernate 5. The main problem is that Hibernate 6 doesn’t know about Postgres types, despite using the PostgreSQLDialect.

Is all this functionality still missing in Hibernate 6 or am I missing something?
Is it possible to deactivate the query parser?

2

Hello, while Hibernate 5 mostly let unknown expressions through to let the database handle them, version 6 introduced a lot more runtime type-checks to fail early when possible.

If you want to use custom db-specific functionality, like custom types for the avg function or entirely new expressions, you should tell Hibernate about them with the FunctionContributor contract. You can implement this interface and use the contributeFunctions method to plug in any additional operations or override existing ones. Hibernate will load your implementation through the service loader when available in the meta-inf/services folder of your application.

3

So that means that the Postgres support in Hibernate is rather limited. Do I understand it correctly that every developer that uses Hibernate with Postgres and wants to use e.g. the round function has to provide his own implementation because the existing one is not correct, despite the name of it has Postgres in it (PostgreSQLTruncRoundFunction)? See third example where contructor cannot be found because Hibernate maps the return type to double although it should be integer.

Is there a library that adds all these Postgres specific functions already? I really can’t believe that all this functionality is missing.

Is it possible to disable these type-checks? Implementing them for every function call would be a huge development effort.

4

Hibernate is community project, driven by voluntary contributions. We support many DBMS vendors, and one of the main selling points is portability of most functionality across each and every one of them.

Feel free to open an improvement request in our issue tracker and contribute a pull request against our repository that adds support for “all this functionality” if you want native support.

5

I thought, given that Hibernate 6 is out for some time now, that more developers stumbled about this limitation in Hibernate 6. Since you didn’t answer my question whether the type checks can be disabled, I assume that it is not possible. Please correct me, if I’m wrong.

I think this is a really big step backward. Many DBMS’ offer a variety of functions that provide crucial and enhanced functionality. I completely understand that the Hibernate team cannot provide type and syntax check support for a majority of them.

However, it blows my mind that it’s not possible to deactivate these checks so that you can “just use” Hibernate as before. Now, as it seems, you have to implement types and functions yourself for nearly every vendor specific function and type, just for Hibernate to start up. Why is this forced? Can you help me understand this requirement better?

This is also not an easy task You have to dig into the new Hibernate Semantic Query Model classes. Inline documentation is missing on a lot of classes and methods.
I couldn’t find any examples that showcase how to create an implementation for non trivial functions (like round, which can have different return types depending on its parameter).

6

I assume that it is not possible

Indeed it isn’t.

I think this is a really big step backward. Many DBMS’ offer a variety of functions that provide crucial and enhanced functionality

You can always use native queries, or even plain JDBC to take advantage of these “variety of functions”. They are beyond Hibernate ORM’s scope and objective, which is to provide an easy to use and cross-functional interface from Java to all supported databases.

I couldn’t find any examples that showcase how to create an implementation for non trivial functions (like round , which can have different return types depending on its parameter).

The Hibernate source code has plenty of these examples, here’s the return type resolver for one, or the PostgreSQLTruncRoundFunction implementation for a complete function support.

Note that you don’t need to re-implement everything, through the function contributor serivce you can simply write something like:

functionContributions.getFunctionRegistry().namedDescriptorBuilder( "make_interval" )
    .setExactArgumentCount( 1 )
    .setParameterTypes( ... )
    .setInvariantType(functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.DURATION))
   .register()
7

Thanks for clarification and the example. That helps.

I’ll use this post to add more findings to illustrate how insane this change in Hibernate 6 is and so people find a workaround for certain problems:

Date arithmetric no longer possible

SELECT current_date + 1

Operand of + is of type 'java.lang.Integer' which is not a temporal amount (it is not an instance of 'java.time.TemporalAmount')

Workaround:
Register custom function make_interval

SELECT CAST(current_date + make_interval(0, 0, 0 , 1) AS DATE)

Brackets around in statements required

SELECT ... FROM country c WHERE :language IN c.languages

Workaround:

SELECT ... FROM country c WHERE :language IN (c.languages)
8

Since I can’t edit my last post any more because the forum doesn’t allow it after a certain time, I’ll continue with new posts.

Comparison with true/false

SELECT ... FROM ... WHERE foo IS FALSE

mismatched input 'IS' expecting {<EOF>, ',', '+', '-', '/', '||', '[', '.', AND, '*', BY, DAY, EPOCH, EXCEPT, GROUP, HOUR, INTERSECT, MINUTE, MONTH, NANOSECOND, OR, ORDER, QUARTER, SECOND, UNION, WEEK, YEAR}; Bad JPQL grammar

Workaround:

SELECT ... FROM ... WHERE foo = false

or

SELECT ... FROM ... WHERE NOT foo
9

It is possible, you just have to read the Hibernate ORM documentation. Adding an integer to a temporal does not have any meaning. The very fact that you construct an interval manually as workaround should tell you that PostgreSQL just assumes something.

The proper way to do this is by using a temporal amount / duration literal, just like the exception says i.e. select current_date + 1 day

This was never supported nor documented and just happened to work by luck. The supported and documented way to check if an expression is contained in a collection association is to use the member of predicate i.e. SELECT ... FROM country c WHERE :language MEMBER OF c.languages

This was also never supported nor documented and just happened to work.