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;
}

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, and based on the Hibernate version you are using, there are different ways to register an SQL function.

Registering an SQL function on Hibernate 6

If you’re using Hibernate 6, then this is how you can register the date_trunc function:

public class SqlFunctionsMetadataBuilderContributor 
        implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "date_trunc", 
            DateTruncFunction.INSTANCE
        );
    }

    public static class DateTruncFunction extends NamedSqmFunctionDescriptor {
        
        public static final DateTruncFunction INSTANCE = new DateTruncFunction();

        public DateTruncFunction() {
            super(
                "date_trunc",
                false,
                StandardArgumentsValidators.exactly(2),
                null
            );
        }

        public void render(
                SqlAppender sqlAppender, 
                List<? extends SqlAstNode> arguments, 
                SqlAstTranslator<?> walker) {
            Expression timestamp = (Expression) arguments.get(0);
            Expression timezone = (Expression) arguments.get(1);
            sqlAppender.appendSql("date_trunc('day', (");
            walker.render(timestamp, SqlAstNodeRenderingMode.DEFAULT);
            sqlAppender.appendSql(" AT TIME ZONE ");
            walker.render(timezone, SqlAstNodeRenderingMode.DEFAULT);
            sqlAppender.appendSql("))");
        }
    }
}

Registering an SQL function on Hibernate 5

If you’re using Hibernate 5, then this is how you can register the date_trunc function:

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))"
            )
        );
    }
}

Providing the SqlFunctionsMetadataBuilderContributor to Hibernate

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

You can provide the hibernate.metadata_builder_contributor property to the Properties of your Spring

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = 
        new LocalContainerEntityManagerFactoryBean();
    ...

    entityManagerFactoryBean.setJpaProperties(additionalProperties());
    return entityManagerFactoryBean;
}

protected void additionalProperties(Properties properties) {
    properties.put(
            "hibernate.metadata_builder_contributor",
            SqlFunctionsMetadataBuilderContributor.class
    );
}

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?

I'm running an online workshop on the 11th of October about High-Performance SQL.

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

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW