The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

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.

In this article, you are going to learn various ways to register SQL functions with JPA and Hibernate.

DTO projection with GROUP_CONCAT

As already explained in this article, DTO projections are ideal for reports and analytics, and for this reason, we are using the following PostSummaryDTO bean to store each record of our post summary report:

SQLFunctionPostSummary DTO class

Now, since our application runs on MySQL, we can use GROUP_CONCAT to join multiple String-based values belonging to the same group.

The JPQL query that builds the DTO projection looks as follows:

List<PostSummaryDTO> postSummaries = entityManager
.createQuery(
    "select " +
    "   p.id as id, " +
    "   p.title as title, " +
    "   group_concat(t.name) as tags " +
    "from Post p " +
    "left join p.tags t " +
    "group by p.id, p.title")
.unwrap(Query.class)
.setResultTransformer(
    Transformers.aliasToBean(PostSummaryDTO.class)
)
.getResultList();

Or, we could use the JPA Criteria API instead:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<PostSummaryDTO> cq = cb.createQuery(
    PostSummaryDTO.class
);

Root<Post> post = cq.from(Post.class);
Join tags = post.join("tags", JoinType.LEFT);
cq.groupBy(post.get("id"), post.get("title"));

cq.select(
    cb
    .construct(
        PostSummaryDTO.class,
        post.get("id"),
        post.get("title"),
        cb.function(
            "group_concat", 
            String.class, 
            tags.get("name")
        )
    )
);

List<PostSummaryDTO> postSummaries = entityManager
.createQuery(cq)
.getResultList();

Writing JPA Criteria API queries is not very easy. The Codota IDE plugin can guide you on how to write such queries, therefore increasing your productivity.

For more details about how you can use Codota to speed up the process of writing Criteria API queries, check out this article.

However, if we try to execute the following JPQL or Criteria API query, by default, Hibernate will throw the following QueryException while parsing the JPQL query:

java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'group_concat' {originalText=group_concat}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[DOT] DotNode: 'groupconca2_.name' {propertyName=name,dereferenceType=PRIMITIVE,getPropertyPath=name,path=t.name,tableAlias=groupconca2_,className=com.vladmihalcea.book.hpjp.hibernate.query.function.GroupConcatFunctionTest$Tag,classAlias=t}
          +-[ALIAS_REF] IdentNode: 'groupconca2_.id' {alias=t, className=com.vladmihalcea.book.hpjp.hibernate.query.function.GroupConcatFunctionTest$Tag, tableAlias=groupconca2_}
          \-[IDENT] IdentNode: 'name' {originalText=name}
 [select    p.id as id,    p.title as title,    group_concat(t.name) as tags from com.vladmihalcea.book.hpjp.hibernate.query.function.GroupConcatFunctionTest$Post p left join p.tags t group by p.id, p.title]

The problem is that Hibernate does not recognize the group_concat SQL function, hence the JPQL query cannot be parsed.

Now, there are several ways to register such SQL function, and we are going to go through all of them as follows.

In Hibernate 5, Criteria API is parsed to JPQL, so everything that applies to JPQL query parsing also applies to Criteria API queries.

Registering the SQL function with the Hibernate Dialect

The most well-known way of registering a SQL function is via a custom Hibernate Dialect.

public class CustomMySQLDialect 
        extends MySQL57Dialect {
    public CustomMySQLDialect() {
        super();

        registerFunction(
            "group_concat",
            new StandardSQLFunction(
                "group_concat", 
                StandardBasicTypes.STRING
            )
        );
    }
}

And using the CustomMySQLDialect when bootstrapping Hibernate:

<property>
    name="hibernate.dialect" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.CustomMySQLDialect"
</property>

However, this method has a big disadvantage. Every time we need to upgrade the Dialect, we have to remember to modify the CustomMySQLDialect class to extend the new Dialect. So, it’s much more convenient if we could just register the SQL function without having to override Hibernate classes.

Registering the SQL function with JPA and MetadataBuilderContributor

Since Hibernate 5.2.18, you can use the MetadataBuilderContributor utility to customize the MetadataBuilder even if you are bootstrapping via JPA.

The MetadataBuilderContributor interface can be implemented like this:

public class SqlFunctionsMetadataBuilderContributor 
        implements MetadataBuilderContributor {
		
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "group_concat",
            new StandardSQLFunction(
                "group_concat", 
                StandardBasicTypes.STRING
            )
        );
    }
}

And, we can provide the SqlFunctionsMetadataBuilderContributor via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

Conclusion

While you can always run a native SQL query if you want to benefit from database-specific features that are not available in JPQL, if you are building the JPQL dynamically using Criteria API, you can call any SQL function as long as Hibernate knows about it.

Transactions and Concurrency Control eBook

7 Comments on “The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate

  1. Simple use the below property:

    spring.jpa.properties.hibernate.metadata_builder_contributor = com.inn.core.generic.utils.SqlFunctionsMetadataBuilderContributor
    
  2. I’m using GROUP_CONCAT in Criteria without problem

    List seleccio = new ArrayList();
    seleccio.add(cb.function(“group_concat”, String.class, cb.function(“distinct”, String.class, emails.get(“email”))));

    But I can’t find how to put ‘ORDER BY’ inside the ‘GROUP_CONCAT’

    Can you help, please? Thank you

      • Hi,

        Is there a way to specify the DISTINCT, ORDER BY and SEPARATOR values for the registered function of group_concat without the CriteriaBuilder? Say I have a dynamic JPQL query that I build from some data I get from the database and I can’t really use the CriteriaBuilder because I have multiple inner select queries.

        Is there a way to register the function in such way that it would be possible to pass the above-mentioned criterias as a parameter?

        Thanks.

      • Say I have a dynamic JPQL query that I build from some data I get from the database and I can’t really use the CriteriaBuilder because I have multiple inner select queries.

        That doesn’t sound right. If you are building the JPQL query dynamically, without Criteria API, then you might risk an SQL Injection attack.

        Criteria API can support multiple INNER JOINs too.

        Is there a way to register the function in such way that it would be possible to pass the above-mentioned criterias as a parameter?

        There’s always a way. That’s why I’m offering consulting. If your company is interested in my services, let me know.

  3. Hi I followed this example to use group_concat in my hql. I want to map the result set from group_concat to a DTO with three fields – date, String (result of group_concat) , String(result of group_concat). But query.resultSet() is giving error.
    Caused by: java.sql.SQLDataException: Cannot determine value type from string ‘-65.51,-407.09’

    Same error comes even without mapping too when I run
    List rows = query.getResultList();

    Can you please help me. Thanks

    • Group_concat returns a String, so you can’t map that to a DTO. You need to revise your requirements.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.