JPA nativeQuery taking a list/set/array as parameter

Question

Using a postgres database, I have the following entity : table cards.combo and column colors VARCHAR[], example of values {'red', 'blue'}

I want to find the Combos that have no colors in common, using the overlap operator (&&)

I can't find out how to formulate the correct nativeQuery, something similar to this :

@Query(nativeQuery = true, value = "SELECT * FROM cards.combo cc WHERE cc.colors && CAST(ARRAY[(:providedColors)] AS VARCHAR[])")
List<Combo> findOverlaps(@Param("providedColors") List<String> providedColors);

In a console, this test works fine :

SELECT * FROM cards.combo cc WHERE cc.colors && CAST(ARRAY['red'] AS VARCHAR[])

The syntax (:param) is supposedly correct with other primitive parameters (int, string). I struggle to get the param providedColors converted to an array[] in the query.

Thanks !

edit : found a workaround :provide colors as a csv string, and use && CAST(STRING_TO_ARRAY((:providedColors),',') AS VARCHAR[])

Have you try with the curly braces syntax (SELECT * FROM cards.combo cc WHERE cc.colors && '{"blue", "red"}' ) ? For that you will need to parse providedColors to String instead — g.momo
– g.momo, Commented Feb 5, 2021 at 19:45
Did you look here? stackoverflow.com/questions/40020972/…. — Farrukh Ahmed
– Farrukh Ahmed, Commented Feb 15, 2021 at 15:18
Just curious - how does the :providecolors as a csv string look like? — Sagar Kalburgi
– Sagar Kalburgi, Commented Jan 4, 2023 at 23:10

Роман Зыков · Accepted Answer · 2022-11-20 16:00:15Z

-4

public interface EmployeeDAO extends JpaRepository<Employee,Integer> {
    List<Employee> findByEmployeeNameIn(List<String> names);                
    // 1. Spring JPA In cause using method name
    @Query("SELECT e FROM Employee e WHERE e.employeeName IN (:names)")     
    // 2. Spring JPA In cause using @Query
    List<Employee> findByEmployeeNames(@Param("names")List<String> names);
    @Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")   // 3. Spring JPA In cause using native query
    List<Employee> findByEmployeeName(@Param("names") List<String> names);
}

edited Nov 20, 2022 at 16:00

answered Apr 1, 2022 at 12:02

Роман Зыков

6036 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

JPA nativeQuery taking a list/set/array as parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related