Java SQL Query - value pair parameter

Question

I wish to select data where two columns are in a set of pairs.

I have a MySQL query that works:

SELECT t.whatever 
FROM t 
  JOIN 
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

But when I try to use it in Java with parameters I can't get it working. This is what I've tried:

@Query(value = "SELECT t.whatever FROM t JOIN (VALUES (?1) AS x (col1, col2) ON  x.col1 = t.col1 AND x.col2 = t.col2 ;", nativeQuery = true)
    List<Whatever> findByPair(List<List<String>> PairString);

I get the following error: 'x' has more columns than were specified in the column list

Where PairString is a list of lists of strings such as:

[[val1a, val2a], [val1b, val2b], [val1c, val2c], [val1d, val2d]]

Answer 1

I think using Pair/Tuple in Java will be a solution : @see https://www.baeldung.com/java-pairs

If you do not want to add a dependency, a POJO class will handle the ResultSet you have. And I think it is the best path to follow :

public class CustomPair {
    private String key;
    private String value;
     
    // standard getters and setters
}

Previous answer : Maybe the SQL request found on SO here will handle your need correctly. Of course, you must adapt the SQL relevant parts to match your model and your need :

SELECT p.* 
FROM products p
LEFT JOIN (SELECT product_ID, count(Distinct filter_ID) cnt
           FROM products_Filter
           WHERE (Filter_ID = 1 and filter_value = 1)
              or (Filter_ID = 3 and filter_value = 0)
           GROUP BY Product_ID) pf
   on P.Product_ID = PF.Product_ID
 WHERE pf.cnt = 2