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

Last modified:

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

Introduction

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:

The Post entity is mapped as follows:

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

    @Id
    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:

SELECT 
    p.title AS col_0_0_,
    date_trunc(
        'day', 
        (p.created_on AT TIME ZONE ?)
    ) AS col_1_0_
FROM 
    post p
WHERE 
    p.id = ?

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 {

    @Override
    public void contribute(
            MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "date_trunc",
            new SQLFunctionTemplate(
                StandardBasicTypes.TIMESTAMP,
                "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:

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

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

spring.jpa.properties.hibernate.metadata_builder_contributor=com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor

Testing time

Now, assuming you have persisted the following Post entity:

Post post = new Post();
post.setId(1L);
post.setTitle(
    "High-Performance Java Persistence"
);
post.setCreatedOn(
    Timestamp.valueOf(
        LocalDateTime.of(2018, 11, 23, 11, 22, 33)
    )
);

entityManager.persist(post);

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

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

assertEquals(
    "High-Performance Java Persistence", 
    tuple.get("title")
);

assertEquals(
    Timestamp.valueOf(
        LocalDateTime.of(2018, 11, 23, 0, 0, 0)
    ), 
    tuple.get("creation_date")
);

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

Query:["
SELECT 
    p.title AS col_0_0_,
    date_trunc(
        'day', 
        (p.created_on AT TIME ZONE ?)
    ) AS col_1_0_
FROM post p
WHERE p.id=?
"], 
Params:[(
    UTC, 
    1
)]

Cool, right?

Online Workshops

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

Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September

High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino

High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet

Conclusion

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.