How to bind custom Hibernate parameter types to JPA queries
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 we can bind a custom Hibernate parameter type to a JPA query.
I decided to write this article after one of my hibernate-types
framework users created this very interesting issue on GitHub.
Domain Model
Let’s assume our application uses the following Event
entity which has an integer array property called values
.
The Event
entity is mapped as follows:
@Entity(name = "Event") @Table(name = "event") @TypeDef( typeClass = IntArrayType.class, defaultForType = int[].class ) public class Event { @Id private Long id; private String name; @Column( name = "event_values", columnDefinition = "integer[]" ) private int[] values; //Getters and setters omitted for brevity }
The @TypeDef
annotation instructs Hibernate to use the IntArrayType
to handle the int[]
entity properties. As explained in this article, the IntArrayType
is provided by the hibernate-type
open source project.
Next, when persisting an Event
entity:
Event event = new Event(); event.setId(1L); event.setName("Temperature"); event.setValues(new int[]{1, 2, 3}); entityManager.persist(event);
Hibernate executes the following SQL INSERT statement:
Query:[" INSERT INTO event ( name, event_values, id ) VALUES ( ?, ?, ? ) "], Params:[( 'Temperature', {"1","2","3"}, 1 )]
So, the IntArrayType
manages to persist the int[]
Java array to a PostgreSQL integer[]
array column type.
PostgreSQL user-defined function
Now, we are going to define the following fn_array_contains
PostgreSQL function so that we can determine if the array provided as the first parameter contains the array given as the second parameter.
CREATE OR REPLACE FUNCTION fn_array_contains( left_array integer[], right_array integer[] ) RETURNS boolean AS $$ BEGIN return left_array @> right_array; END; $$ LANGUAGE 'plpgsql'
If you want to learn how to call PostgreSQL functions and stored procedures with JPA and Hibernate, check out this article.
Calling the user-defined function in a JPQL query
If we want to execute a JPQL query which calls the fn_array_contains
PostgreSQL function:
Event event = entityManager .createQuery( "select e " + "from Event e " + "where " + " fn_array_contains(e.values, :arrayValues) = true", Event.class) .setParameter( "arrayValues", new int[]{2, 3} ) .getSingleResult();
The PostgreSQL Driver is going to throw the following exception:
org.postgresql.util.PSQLException: ERROR: function fn_array_contains(integer[], bytea) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 138
The problem is that Hibernate does not know how to pass the ARRAY query parameter to the database, hence the failure.
To properly pass a custom Hibernate type to a JPA or Hibernate Query, we need to provide the Hibernate Type explicitly.
There are two ways we can achieve this goal:
- we can pass a
TypedParameterValue
to the JPAQuery
orTypedQuery
- we can cast the JPA
Query
orTypedQuery
to aorg.hibernate.query.Query
and use thesetParameter
method which takes the Hibernate Type as well.
Using TypedParameterValue
The following example illustrates how you can pass the Hibernate Type associated with a given bind parameter value in a JPA Query
or TypedQuery
:
Event event = entityManager .createQuery( "select e " + "from Event e " + "where " + " fn_array_contains(e.values, :arrayValues) = true", Event.class) .setParameter( "arrayValues", new TypedParameterValue( IntArrayType.INSTANCE, new int[]{2, 3} ) ) .getSingleResult();
With the IntArrayType.INSTANCE
Hibernate Type provided when binding the parameter, if we run the JPQL query above, Hibernate executes the proper SQL query:
SELECT e.id AS id1_0_, e.name AS name2_0_, e.event_values AS event_va3_0_ FROM event e WHERE fn_array_contains(e.event_values, {"2","3"}) = true
Using org.hibernate.query.Query
The second approach to pass the Hibernate Type associated with a given JPA bind parameter is to cast the JPA Query
or TypedQuery
to a Hibernate-specific org.hibernate.query.Query
and call the setParameter
overloaded method that takes a Hibernate Type as the third argument.
Event event = (Event) entityManager .createQuery( "select e " + "from Event e " + "where " + " fn_array_contains(e.values, :arrayValues) = true", Event.class) .unwrap(org.hibernate.query.Query.class) .setParameter( "arrayValues", new int[]{2, 3}, IntArrayType.INSTANCE ) .getSingleResult(); assertArrayEquals(new int[]{1, 2, 3}, event.getValues());
Unlike TypedParameterValue
, this time we need to cast the result since, when unwrapping the JPA TypedQuery
to the org.hibernate.query.Query
, the generic type information is lost.
Calling the user-defined function in a Criteria API query
We can call the fn_array_contains
PostgreSQL function using the JPA Criteria API and the binding is done via the same org.hibernate.query.Query
setParameter
method we have previously seen.
Writing JPA Criteria API queries is not very easy. The Codota IDE plugin can guide you on how to write such queries, therefore increasing your productivity.
For more details about how you can use Codota to speed up the process of writing Criteria API queries, check out this article.
CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Event> cq = cb.createQuery(Event.class); Root<Event> root = cq.from(Event.class); cq.select(root); ParameterExpression containValues = cb.parameter( int[].class, "arrayValues" ); cq.where( cb.equal( cb.function( "fn_array_contains", Boolean.class, root.get("values"), containValues ), Boolean.TRUE ) ); Event event = (Event) entityManager .createQuery(cq) .unwrap(org.hibernate.query.Query.class) .setParameter("arrayValues", new int[]{2, 3}, IntArrayType.INSTANCE) .getSingleResult(); assertArrayEquals(new int[]{1, 2, 3}, event.getValues());
Awesome, 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
When binding a custom Hibernate Type as a parameter to a JPA query, it’s important to specify the Type explicitly so that Hibernate knows how to handle it when passing it to the JDBC PreparedStatement
. By unwrapping the JPA Query
to the Hibernate-specific org.hibernate.query.Query
, we can provide the Hibernate Type using an overloaded version of the setParameter
query method.
