2

I need to create a JPA native query relevant to the example below:

select * from inventory where (server, product) in (('server1','product1'), ('server2','product2'), ('server1','product3'));

I have created a native query:

@Query(
            value = "select server, product from inventory where (server, product) in (:myList)",
            nativeQuery = true
    )
    List<Product> getProducts(List<List<String>> myList);

But when I pass List of Lists as an argument:

List<List<String>> myList= Arrays.asList(
    Arrays.asList("server1","product1"),
    Arrays.asList("server2","product2"),
    Arrays.asList("server1","product3")
);
List<Product> products = myRepository.getProducts(myList);

I get an error

java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

I use Oracle database. Please tell me, what am I doing wrong?

This question is not about passing a flat List into a native query to filter db results by 1 column (this works fine) but how to pass a list of nested lists into a query to filter db results by 2 columns.

Ula
  • 85
  • 7
  • What happens when you replace `(:myList)` with `?` – Lino Jun 09 '21 at 09:00
  • If I write: "... in ?" I get java.lang.NullPointerException: null If I write "... in ?1" I get the same error as before: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator – Ula Jun 09 '21 at 09:14
  • This question shouldn't have been closed, it's a different problem than the other question... You want to pass a List of Lists, not a List of types that Jpa easily knows how to unpack :/ – wi2ard Jun 09 '21 at 09:50

1 Answers1

0

Use this:

@Query("select server, product from inventory where (server, product) in (:myList)",
        nativeQuery = true)
List<Product> getProducts(@Param("myList") List<List<String>> myList);
Ali Behzadian Nejad
  • 8,804
  • 8
  • 56
  • 106
  • Unfortunately, I get the same error java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator – Ula Jun 09 '21 at 09:27