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[])

Answer 1

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