CODEWITHSUNDEEP
javapostgresqljpanativequery
Chintamani Bhat
Chintamani Bhat

Reputation: 21

How to pass List of strings as parameter to JPA native query and substitute the same in Postgresql ARRAY[]

I have a JPA native query which looks something like this

@Query(
value = "SELECT * FROM feature f where jsonb_exists_any(f.feature_json -> 'properties' -> 'ids', array[:ids])", 
nativeQuery = true)

feature_json is a jsonb column and :ids is the input param I need to pass to the query which would be a List of UUID Strings

Query with sample values is as below

SELECT * FROM feature f where jsonb_exists_any(f.feature_json -> 'properties' -> 'ids', array['79eeb7ae-c2d0-3344-a6f5-eb424b820d03','a14bdb38-cedb-3743-bd50-9562ed2856b2'])

When I pass the above values as a List of strings in Java, the query fails to execute as the value in :ids is substituted as (79eeb7ae-c2d0-3344-a6f5-eb424b820d03,a14bdb38-cedb-3743-bd50-9562ed2856b2) instead of the required '79eeb7ae-c2d0-3344-a6f5-eb424b820d03','a14bdb38-cedb-3743-bd50-9562ed2856b2'

I also tried to pass this quoted comma separated values as a string to :ids parameter, the query executes without any errors but does not fetch any results.

How to pass a list of uuid strings from Java as a parameter to the native query as specified above?

Upvotes: 1

Views: 1969

Answers (1)

Chintamani Bhat
Chintamani Bhat

Reputation: 21

UPDATE:

I modified the query as below:

@Query(
value = "SELECT * FROM feature f where jsonb_exists_any(f.feature_json -> 'properties' -> 'ids', :ids)", 
nativeQuery = true)
List<FeatureEntity> getFeaturesByIds(@Param("ids") TypedParameterValue ids);

And from the invoking function, passed in new TypedParameterValue(StringArrayType.INSTANCE, stringArray)

The stringArray is cast into postgresql text array during query execution and the query returns expected response.

Upvotes: 1

Related Questions