Error caused while migrating from Spring boot 2.x to 3.x and Hibernate 6.x version
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:936) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1171) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1100) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1425) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1308) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3854) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1097) ~[ojdbc8-21.9.0.0.jar:21.9.0.0.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246) ~[hibernate-core-6.5.3.Final.jar:6.5.3.Final]
... 172 common frames omitted
Solution:
Hibernate 6 HibernateCriteriaBuilder adds operation needed to express features of HQL which are not available in standard JPQL. So it is recommended to migrate to HibernateCriteriaBuilder method like year, month, minute, seconds etc to support more HQL features.
Replace
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
Expression<Integer> yearExp = cb.function("year", Integer.class, root.get(modelData));
With
HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
Expression<Integer> yearExp = cb.year(root.get(modelData));
year
Extracts the TemporalUnit.YEAR of a date, time, or datetime expression.
EntityManager – unwrap
Java Persistence 2.0 introduces unwrap method to return an object of the specified type to allow access to the provider-specific API
