How to execute SQL functions with multiple parameters in a JPQL query with Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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?
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.
