Hibernate 6 - Criteria 'hour' is not a recognized built-in function name

Hibernate ORM
1

Hi,

I’m migrating from hibernate 5.3.24 to hibernate 6.2.13.

In a criteria query, i have a predicate

Predicate pred = cb.equal(cb. function("hour", Integer.class, root.get(MyTable_.startDateTime)),
				cb.function("hour", Integer.class, otherRoot.get(MyTable2_.startTime)));

With this version it throwing the following exception

Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select …)] [‘hour’ is not a recognized built-in function name.] [n/a]

com.microsoft.sqlserver.jdbc.SQLServerException: ‘hour’ is not a recognized built-in function name.

I notice that the generated query is not generating “datepart(hour, mytable_.startDateTime)”

Same code in hibernate 5 works fine.

Do i miss something?

2

Just tested with Hibernate ORM core version 6.4.1.Final and the issue remains.

3

I have found that in version 5.6 the function “hour” is being registered in the SQLServerDialect constructor.

public SQLServerDialect() {
		...
		registerFunction( "hour", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "datepart(hour, ?1)" ) );
		...
	}

But since version 6.0 this function is not registered in the constructor or in the initializeFunctionRegistry function.
While in some other dialects, for example MySQLDialect, the function “hour” is registered using functionFactory.hourMinuteSecond() from the CommonFunctionFactory. This does not happen in the SQLServerDialect.

Could the “hour” function be added to the SQLServerDialect?

4

Hibernate 6 now supports extracting datetime fields natively through the extract function, so you should use that. The HibernateCriteriaBuilder interface also exposes the convenience method hour() which you can use by simply passing it the datetime path expression and it should work like expected.

5

How could one use the extract function, extract(year from datetime), with jpa criteria?

6

You can use the Hibernate Criteria extended APIs interface, like I mentioned in my previous reply. For example, to extract the year you could do something like:

HibernateCriteriaBuilder cb = entityManager.unwrap( Session.class ).getCriteriaBuilder();
cb.year( root.get( "datetime_field" ) );

For more details see the User Guide chapter about Crtiera extensions.