JPA Criteria Builder: How to pass ArrayList to Oracle function?
I have an Oracle function that takes in a table of POINTS%ROWTYPE. I want to call this function from JPA using the CriteriaBuilder class, which has a function for database functions. When I try to build the query, it dies complaining that ArrayLists are not valid query parameters to the function.
How do I pass in an ArrayList from JPA into an Oracle function?
Oracle Function signature:
CREATE OR REPLACE FUNCTION LOCATION_CONTAINS
(
LATITUDE_IN IN DOUBLE PRECISION,
LONGITUDE_IN IN DOUBLE PRECISION,
points IN types_pkg.point_array,
numPoints IN INTEGER
)
Oracle Type:
create or replace package types_pkg
as
type point_array is table of FILTERPOINT%ROWTYPE;
end types_pkg;
JPA Criteria Builder call
List<FilterPoint> points = getPoints(location_name);
int numPoints = points.size();
Expression ex =
cb.function( "LOCATION_CONTAINS",
Integer.class,
entity.get( "latitude" ),
entity.get( "longitude" ),
cb.literal( points ),
cb.literal( numPoints ) );
Exception:
org.apache.openjpa.persistence.ArgumentException:
The specified parameter of type "class middle.ware.FilterPoint" is not a valid query parameter.
Essentially, I want to grab an array of points outside the function call so I only have to fetch it once (right now I do the select inside the function call, so it's ran every single time the function is called, which is potentially 100,000's of times.) Then I want to pass that array of points back into the function for processing.
I need to use criteria builder for this function is only part of the query.
Thanks for any help.
This could help. As it described here, appropriate Java type for user-defined TABLE type is java.sql.Array . I suppose you need convert your list into this type. One of the possible ways to do it, just invoke method createArrayOf on your connection (see also this answer):
Session session = (Session)em.getDelegate();
Connection conn = session.connection();
String[] data = points.toArray(new FilterPoint[points.size()]);
java.sql.Array sqlArray = conn.createArrayOf(typeName, data);