Java List<Integer> cast to int[] throws PSQLException: ERROR: syntax error

Question

I need help with understanding and fixing the following issue.
I have created a postgresql function for retrieving all events in a specific regions based on regions like this:

CREATE OR REPLACE FUNCTION get_all_events_in_regions(regionIds int[])
RETURNS SETOF events AS $$
BEGIN
    RETURN QUERY
    SELECT  DISTINCT ON (e.id)
            e.id,
            e.event_type_id,
            e.vehicle_id,
            e.event_status_id,
            e.event_title,
            ...
    FROM events as e
    JOIN vehicle_region vr ON e.vehicle_id = vr.vehicle_id
    WHERE vr.region_id = ANY(regionIds);
END;
$$ LANGUAGE plpgsql;

And it works great when i call it form command line:

SELECT * FROM get_all_events_in_regions(ARRAY[1, 3,  6]);

Then i have a Spring Boot API that uses JPA for ORM and data persistence. Inside a EnventRepository i created a native query method like this:

@Query(value = "SELECT e.* FROM get_all_events_in_regions(CAST(:regionIds as int[]) e", nativeQuery = true)
List<Event> getAllEventsInRegions(@Param("regionIds") List<Integer> regionIds, Pageable pageable);

But i get PSQLException: ERROR: syntax error at or near ","
regionIds is a normal ArrayList when i log it.

I also tried without CASTING into int[] but the error was:
PSQLException: ERROR: function get_all_events_in_regions(integer, integer, integer, integer, integer, integer, integer, integer, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

And i don't know what is going on or how to fix it. Thanks for your help in advance

Maybe you could take a look at these links? stackoverflow.com/a/74667627/16034206 stackoverflow.com/q/63619548/16034206 — pebble unit
– pebble unit, Commented Sep 7, 2023 at 14:45

lance-java · Accepted Answer · 2023-09-07 14:40:01Z

1

I'd guess you'd need to somehow register a converter from java.util.List -> java.sql.Array. The converter would do something like

Array array = connection.createArrayOf("int", regionIds.toArray());

Under the hood this java.sql.Array could then be set on the PreparedStatement

answered Sep 7, 2023 at 14:40

lance-java

29.4k6 gold badges73 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deadron · Accepted Answer · 2023-09-07 15:32:04Z

0

Your error strongly implies that its binding in the List by expanding the List into its individual values.

try

get_all_events_in_regions(ARRAY[:regionIds]))

answered Sep 7, 2023 at 15:32

Deadron

5,2891 gold badge18 silver badges28 bronze badges

Comments

Jure Beton · Accepted Answer · 2023-09-07 16:07:42Z

0

Thanks, I solved it.

I had to convert it into a string that looks like this {6,12,9,4,10,8,11,5,7}.

It appears that either Spring, JPA, or JDBC somehow, somewhere under the hood casts this string into int[] - ARRAY[6,12,9,4,10,8,11,5,7], which PostgreSQL understands.

So I changed the parameter type from List to String and converted it like this

private String convertListToPGString(List<Integer> integerList) {
    return "{" + integerList.stream()
            .map(Object::toString)
            .collect(Collectors.joining(",")) + "}";
}

answered Sep 7, 2023 at 16:07

Jure Beton

1771 gold badge1 silver badge10 bronze badges

1 Comment

lance-java Over a year ago

If this were strings, I believe this would create a security risk as you'd be open to sql injection attacks. xkcd.com/327

Collectives™ on Stack Overflow

Java List<Integer> cast to int[] throws PSQLException: ERROR: syntax error

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related