Can't call database function inside CriteriaBuilder construct method

Question

I simplified my DTO class to only have 2 properties: UUID and a Long:

public class MyDto implements Serializable {

    private UUID uuid;
    private Long sequential;

    // Constructor taking properties as parameters

    // Geters and setters
}

And my code works great when I try to construct in CriteriaBuilder like this:

// Assuming my constructor in MyDto take only UUID parameter

query.select(builder.construct(MyDto.class,
            root.get(MyDto_.uuid)));

But when I try to add the 'sequential' parameter in the constructor of my Dto, and change my select to retrieve the value from a db function as bellow:

// Assuming my constructor in MyDto take both UUID and Long parameters

query.select(builder.construct(MyDto.class,
            builder.function("nextval", Long.class, builder.literal("my_seq")),
            root.get(MyDto_.uuid)));

hibernate throw a exception:

java.lang.NullPointerException
at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27)
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:418)
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:991)
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:759)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:675)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:311)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:259)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
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.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:545)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:654)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3282)
at org.hibernate.query.criteria.internal.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:318)
at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:127)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3575)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:204)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.jboss.weld.bean.proxy.AbstractBeanInstance.invoke(AbstractBeanInstance.java:38)
at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:100)
at org.jboss.weld.proxies.AutoCloseable$EntityManager$HibernateEntityManager$QueryProducer$Serializable$Session$SharedSessionContract$1645305853$Proxy$_$$_WeldClientProxy.createQuery(Unknown Source)

This is an example of what I'm trying to do, and here I see the function being used in a where, but my need is to use in the constructor of CriteriaBuilder.

I have this working in the old way SELECT NEW MyDto(...) but Im strugling with CriteriaBuilder.

Leandro Cavalcante · Accepted Answer · 2020-07-01 20:10:18Z

For everyone facing the same problem, the answer is: register your function.

As mentioned in this article,

When executing an entity query (e.g. JPQL, HQL or Criteria API), you can use any SQL function without having to register it as long as the function is passed directly to the WHERE clause of the underlying SQL statement. However, if the SQL function is used in the SELECT clause, and Hibernate has not registered the SQL function (be it a database-specific or user-defined function), you will have to register the function prior to using it in an entity query.

So what I needed to do was first create a class extending my dialect, and registering my functions inside the constructor

public class MyCustomPostgreSQLDialect extends org.hibernate.dialect.PostgreSQLDialect {
    public MyCustomPostgreSQLDialect() {
        super();
        registerFunction("name_of_function",
                new StandardSQLFunction("name_of_function",
                        StandardBasicTypes.BIG_DECIMAL // Or the appropriated result of the function
                )
        );
    }
}

And after that, change my Dialect in the persistence.xml to my new custom dialect with registrated functions

<property name="hibernate.dialect" value="my.package.MyCustomPostgreSQLDialect"/>

Collectives™ on Stack Overflow

Can't call database function inside CriteriaBuilder construct method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related