2

I want to use 2 columns in where in clause in PostgreSQL 13.2. I tested it by executing the following query

select * from collision_danger_pair_contexts 
where 
(first_target_id, second_target_id) 
in
( ('63efc3d9-8fc7-4b39-a9ce-b926dae4e104', '63efc3d9-8fc7-4b39-a9ce-b926dae4e105') );

it worked.

Now the question is how to achieve the same using Spring Data JPA. I've tried the following query

@Query(
    nativeQuery = true,
    value = """
            SELECT * FROM collision_danger_pair_contexts ctx
            WHERE (ctx.first_target_id, ctx.second_target_id) IN (:targetIds)
            """
)
List<CollisionDangerPairContext> findByTarget1IdAndTarget2Id(@Param("targetIds") List<UUID[]> targetIds);

where targetIds contains a list of arrays of UUIDs. The length of each array is 2.

But I've got the error:

 Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: record = bytea
      Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Before I ran the query my intuition told me that I was to optimistic hoping that it would work, but I don't understand how and where I should do the "casting".

I've also tried to change the parameter to

List<List<UUID>> targetIds

but it didn't work either. What is the correct syntax/casting ?

The best way for me would be using

List<org.apache.commons.lang3.tuple.ImmutablePair<UUID, UUID>> targetIds
ka3ak
  • 2,435
  • 2
  • 30
  • 57

1 Answers1

0

I would serialize the list of UUID arrays as a 2-dim JSON array, i.e.

[
  ["63efc3d9-8fc7-4b39-a9ce-b926dae4e104", "63efc3d9-8fc7-4b39-a9ce-b926dae4e105"],
  ["63efc3d9-8fc7-4b39-a9ce-b926dae4e106", "63efc3d9-8fc7-4b39-a9ce-b926dae4e107"],
  ["63efc3d9-8fc7-4b39-a9ce-b926dae4e108", "63efc3d9-8fc7-4b39-a9ce-b926dae4e109"]
]

and pass it as a parameter to this native query:

select * from collision_danger_pair_contexts cdpc
where exists
(
 select from json_array_elements((:targetIds)::json) j
 where j ->> 0 = cdpc.first_target_id::text
   and j ->> 1 = cdpc.second_target_id::text
);
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • Getting the error: `org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate named parameter [targetIds], expecting one of [targetIds::json]; nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [targetIds], expecting one of [targetIds::json]` – ka3ak Jun 29 '23 at 12:58
  • Well, pass the JSON as a text parameter called `targetIds`. I am not very confident in Java but the double colons `::` might need to be escaped like this `\\:\\:`. – Stefanov.sm Jun 29 '23 at 13:01
  • You may see [this](https://stackoverflow.com/questions/4169582/escaping-the-colon-character-in-jpa-queries) SO thread on JPA native queries and possibly use a positional rather than named parameter. – Stefanov.sm Jun 29 '23 at 13:07