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

(Last Updated On: July 11, 2018)
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:

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

Download free ebook sample

Newsletter logo
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence,
  • a 10% discount coupon for my book.

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

  1. Is it possible in the JPA criteria to call a function with special words, for example, the function “extract(MONTH FROM table_name.date_field)” (PostgreSQL)?

    • If you wrap that query in a databae-specific function, you can then call CriteriaBuilder.function.

  2. Thanks! This is the only place where I found a good explanation of invoking SQL functions

  3. I have this query in MySQL that works fine:

    select c.id_contacte, c.nom_Mostrar, c.nif,
     group_concat(distinct e.email) as email, group_concat(distinct t.telefon) as telefon from contacte c
     left join email e on c.id_contacte = e.contacte_id
     left join  telefon t on c.id_contacte = t.contacte_id
      group by c.id_contacte
    

    But when I try to do it in JPQL I have the error: unexpected token: group_concat

    @Query(
    	"select new com.plataforma.aplicacio.basics.ContacteBasic (c.idContacte, c.nomMostrar, c.nif,"
    	+ " group_concat(distinct(e.email)) as email, group_concat(distinct(t.telefon) as telefon) from Contacte c"
    	+ " left join c.emails e"
    	+ " left join c.telefons t"
    	+ " where c.nif like :nif%"
    	+ " group by c.idContacte"
    )
    
    • What is the purpose of translating a valid native SQL to JPQL if you don’t even select entities, but a DTO projection?

      Just use the native SQL as I explained in this article.

      • So is it impossible to use DISTINCT along with GROUP_CONCAT using JPQL?

      • For that, you’d need to create a custom DB function to include the Distinct clause.

  4. Would it be possible to do the same thing with a custom operator ?

    For example, PostgreSQL ‘ILIKE’ is not supported by Hibernate ? I can’t use applySqlFunction() since it is not a function, and seemingly, registerKeyword() on the dialect does not work.

    • You can simply do lower(e.name) like lower(:name) and you would not need ilike.

      • This is true but :

        I want to take advantage of a GIN index. While LIKE and ILIKE can take advantage of the same one index, LIKE and lower(x) LIKE lower(y) need two different indexes. It is thus a little bit on the update and insert front.

        I might use your solution in the end but I wanted to know if registering custom operator was possible.

      • In your case, you need to use a native SQL query instead which, unfortunately, is totally underrated among JPA and Hibernate users.

        Or, you can use the legacy Hibernate Criteria Restrictions.ilike if you really need an entity query. JPQL and HQL don’t support it yet, but we might add support for that in Hibernate ORM 6. So, you have two alternatives until we do that.

    • Related to the placement of a SQL function in an entity query? I think it’s related to resolving types so that it can build the result properly. But, there might be other causes that don’t come to my mind.

      • I mean to create custom sql function in hibernate to concat as solution?

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.

Want to run your data access layer at warp speed?