JPA Criteria Builder: How to pass ArrayList to Oracle function?

Question

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.

Answer 1

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);