New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Pass array types in PostgreSQL functions as parameters in JPA query #61

Closed
StanislavGubanov opened this Issue Dec 7, 2018 · 2 comments

Comments

Projects
None yet
2 participants
@StanislavGubanov
Copy link

StanislavGubanov commented Dec 7, 2018

Hello, Vlad.

How use array types in PostgreSQL database functions with array params in JPA

https://www.postgresql.org/docs/current/functions-array.html

function for example:

CREATE OR REPLACE FUNCTION fn_helper_array_contains(a integer[],b integer[])
RETURNS boolean AS
$$
BEGIN
  return a @> b;
END;
$$ LANGUAGE 'plpgsql';

-- select fn_helper_array_contains (array[1,2,3]::integer[],array[1,2]::integer[]);

@StanislavGubanov StanislavGubanov changed the title How use array types in PostgreSQL functions as paramaters in JPA How use array types in PostgreSQL functions as parameters in JPA Dec 7, 2018

@vladmihalcea

This comment has been minimized.

Copy link
Owner

vladmihalcea commented Dec 7, 2018

The ARRAY support in hibernate-types is for entity properties only. This is not supported with stored procedures as they don't take a Hibernate Type.

The only way to use ARRAY with stored procedures is via JDBC or using jOOQ.

@vladmihalcea vladmihalcea reopened this Dec 7, 2018

@vladmihalcea vladmihalcea changed the title How use array types in PostgreSQL functions as parameters in JPA Pass array types in PostgreSQL functions as parameters in JPA query Dec 7, 2018

@vladmihalcea

This comment has been minimized.

Copy link
Owner

vladmihalcea commented Dec 7, 2018

This commit shows how you can do it:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Event.class);
Root root = cq.from(Event.class);
cq.select(root);

ParameterExpression containValues = cb.parameter(int[].class, "arrayValues");
cq.where(cb.equal(cb.function("fn_helper_array_contains", Boolean.class, root.get("values"), containValues), Boolean.TRUE));
TypedQuery<Event> query = entityManager.createQuery(cq);
int[] arrayValues = {12, 16};
query.unwrap(Query.class).setParameter("arrayValues", arrayValues, IntArrayType.INSTANCE);
List<Event> events = query.getResultList();
assertArrayEquals(new int[]{12, 14, 16}, events.get(0).getValues());

Notice the unwrap to org.hibernate.query.Query.class and passing the IntArrayType.INSTANCE to bind the parameter:

query.unwrap(Query.class).setParameter("arrayValues", arrayValues, IntArrayType.INSTANCE);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment