Calling User Defined SQL function in JPQL with hibernate

Question

I need to migrate My Spring Data JPA/Hibernate Application to

MS SQL Server (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor))

from

Oracle(Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production).

Environment:

org.springframework.data:spring-data-jpa:2.0.9.RELEASE
org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final
org.hibernate:hibernate-entitymanager:5.2.17.Final
Java version: 1.8.0_91, vendor: Oracle Corporation
OS name: "windows 10", version: "10.0", arch: "amd64", family: "windows"

Now in many places in orm.xml, we have used to_char. For example, to generate monthly aggregated reports etc.

In the SQL Server schema, we have defined to_char expecting either Hibernate will register this or will leave some hook(s) to get user defined functions registered:

CREATE FUNCTION to_char(@dateVal DATE, @dateFormat varchar) RETURNS VARCHAR(20) 
  BEGIN       
    RETURN CONVERT(varchar, date, 1)
  END

I have studied Write & Call user-defined function in JPQL?. But unfortunately, I am not allowed to re-define one dialect.

For the JPQL,

<query>
         select new com.entity.MyEntity(
            c.Id,
            to_char(c.transactionDate,'Mon/yyyy'),
            sum(c.disputeAmount))
            FROM   ChargeBack c join c.chargebackReason rcd
            GROUP BY c.Id, to_char(c.transactionDate,'Mon/yyyy'),
</query>

I am getting the following error as the SQL function is not registered.

Caused by: java.lang.NullPointerException
at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27)
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:434)
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeColumnNames(SelectClause.java:269)
at org.hibernate.hql.internal.ast.tree.SelectClause.finishInitialization(SelectClause.java:259)
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:254)
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1013)
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:781)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:677)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
at org.hibernate.query.spi.NamedQueryRepository.checkNamedQueries(NamedQueryRepository.java:157)
at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:533)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:323)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
... 67 more

Simon Martinelli · Accepted Answer · 2018-09-05 12:42:14Z

2

Your reference is very old an no longer valid. Starting from JPA 2.1 you can call your customer function like:

function('to_char', c.transactionDate,'Mon/yyyy')

So your query should look like

<query>
     select new com.entity.MyEntity(
        c.Id,
        function('to_char', c.transactionDate,'Mon/yyyy'),
        sum(c.disputeAmount))
        FROM   ChargeBack c join c.chargebackReason rcd
        GROUP BY c.Id, function('to_char', c.transactionDate,'Mon/yyyy'),
 </query>

answered Sep 5, 2018 at 12:42

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lab bhattacharjee Over a year ago

Thanks for your quick response. I will definitely try this out ASAP. In the meantime, could you please site some reference where it is documented?

Simon Martinelli Over a year ago

You can read about this here thoughts-on-java.org/database-functions or here mastertheboss.com/jboss-frameworks/hibernate-jpa/…

lab bhattacharjee Over a year ago

After some debugging, I have observed that Hibernate assigns org.hibernate.hql.internal.ast.tree.MethodNode for each function and then tries to resolve the return data type in org.hibernate.hql.internal.ast.tree.MethodNode.dialectFunction(AST) from the selected Dialect. But to_char is not registered in SQLServer2008Dialect. So, the datatype for MethodNode remains null. This causes NullPointer as shown in the SatckTrace of the Question.

Collectives™ on Stack Overflow

Calling User Defined SQL function in JPQL with hibernate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related