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

(Last Updated On: July 11, 2018)

Follow @vlad_mihalcea

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 inthis 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:

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

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

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.

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>

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 anative 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.