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 JPA Query or TypedQuery
• we can cast the JPA Query or TypedQuery to a org.hibernate.query.Query and use the setParametermethod 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.

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.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW