How to execute SQL functions with multiple parameters in a JPQL query with Hibernate

(Last Updated On: November 28, 2018)
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!


In this article, we are going to see how you can execute SQL functions with multiple parameters in JPQL queries with Hibernate. I recently stumbled on this requirement while answering this StackOverflow question which is a never-ending source of inspiration for articles on my blog.

Domain Model

Let’s consider we are using the following Post entity in our application:

How to execute SQL functions with multiple parameters in a JPQL query with Hibernate - Post entity

The Post entity is mapped as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    private Long id;

    private String title;

    @Column(name = "created_on")
    private Timestamp createdOn;

    //Getters and setters omitted for brevity

Registering the DATE_TRUNC PostgreSQL function

Our business use case requires us to call the DATE_TRUNC PostgreSQL function like this:

    p.title AS col_0_0_,
        (p.created_on AT TIME ZONE ?)
    ) AS col_1_0_
    post p

As explained in this article, before using a SQL function in the SELECT clause of a JPQL or Criteria API query, we first need to register that function.

The best way to register a SQL function with Hibernate is to provide a MetadataBuilderContributor like the following one:

public class SqlFunctionsMetadataBuilderContributor
        implements MetadataBuilderContributor {

    public void contribute(
            MetadataBuilder metadataBuilder) {
            new SQLFunctionTemplate(
                "date_trunc('day', (?1 AT TIME ZONE ?2))"

Now, you need to instruct Hibernate to use the SqlFunctionsMetadataBuilderContributor using the hibernate.metadata_builder_contributor configuration property.

You can either add the hibernate.metadata_builder_contributor property to the JPA persistence.xml file:


Or, if you’re using Spring Boot, you can add the following entry in the configuration file:

Testing time

Now, assuming you have persisted the following Post entity:

Post post = new Post();
    "High-Performance Java Persistence"
        LocalDateTime.of(2018, 11, 23, 11, 22, 33)


You can now use the DATE_TRUNC SQL function in a JPQL query like this:

Tuple tuple = entityManager
    "select " +
    "   p.title as title, " +
    "   date_trunc(p.createdOn, :timezone) as creation_date " +
    "from " +
    "   Post p " +
    "where " +
    " = :postId", Tuple.class)
.setParameter("postId", 1L)
.setParameter("timezone", "UTC")

    "High-Performance Java Persistence", 

        LocalDateTime.of(2018, 11, 23, 0, 0, 0)

And Hibernate is going to execute the SQL statement we wanted from the very beginning:

    p.title AS col_0_0_,
        (p.created_on AT TIME ZONE ?)
    ) AS col_1_0_
FROM post p

Cool, right?

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


Using the hibernate.metadata_builder_contributor configuration property is the best way to register a SQL function with Hibernate, and you can parameterize the function so that you can pass it entity properties and even standard query parameters, as it’s the case of the UTC parameter we used when calling the DATE_TRUNCT function.

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.

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?