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

Last modified:

Are you struggling with performance issues in your Spring, Jakarta EE, or Java EE application?

What if there were a tool that could automatically detect what caused performance issues in your JPA and Hibernate data access layer?

Wouldn’t it be awesome to have such a tool to watch your application and prevent performance issues during development, long before they affect production systems?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, Micronaut, or Play Framework.

So, rather than fixing performance issues in your production system on a Saturday night, you are better off using Hypersistence Optimizer to help you prevent those issues so that you can spend your time on the things that you love!

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)
.setTupleTransformer(
    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();

Hibernate 6

On Hibernate 6, everything works by default, as demonstrated by this test case.

Hibernate 5

However, on Hibernate 5, 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 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:

properties.put(
    "hibernate.metadata_builder_contributor",
    SqlFunctionsMetadataBuilderContributor.class
);

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

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.

Follow @vlad_mihalcea