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!
You can earn a significant passive income stream from promoting my book, courses, tools, training, or coaching subscriptions.
If you're interested in supplementing your income, then join my affiliate program.
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?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
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.
