JPA query for select which multiple values in the "IN" clause

Question

I want to create a JPA parameterised query for following SQL statement

select * from car where (colour, speed) in (('red', 50), ('blue', 70))

this query returns the expected result

entityManager.createQuery("from Car c where (c.colour, c.speed) in (('red', 50), ('blue', 70))", Car.class).getResultList();

How can the currently hardcoded values be passed as parameter?

I came up with below "working" solution. But I fear that there is no guarantee that all parameter pairs are passed in the expected order? I don't want to get "blue" cars with speed "50".
edit: removed as it doesn't work as expected, see also @Gas comment

Lymphoblast

Good question, I'd create jpql dynamically to hve :color :color1 etc., as a last option. – Nashbar

I think, according to JPA spec the IN expression can be used to check whether a single-valued path expression is a member of a collection, so syntax (c.colour, c.speed) in (('red', 50),('blue', 70)) should be illegal. It is not available in many databases, so may not be portable. If your JPA provider allows that, it is not following the specs. And of course "c.colour in (:colour) and c.speed in (:speed)" is a different condition and will match (red, 50), (red, 70), (blue,50), (blue,70). You should rather use set of ands/ors and positional parameters. – Lagging

@Lagging thanks. I tought there would be a better solution, as there are more then two pairs for the select. Maybe the proposed solution from Zeus could be an option. – Lymphoblast

Possible solutions here on SO native query or with Hibernate HQL – Lymphoblast

Answer 1

While searching, I found a very similar question here. Perhaps that's more in line with this problem.

However, I implemented something slightly different.

@Query("SELECT p FROM Product p "
            + "LEFT JOIN p.categories category "
            + "WHERE UPPER(p.name) LIKE UPPER(CONCAT('%', COALESCE(:searchRequest, ''), '%')) "
            + "AND UPPER(p.description) LIKE UPPER(CONCAT('%', COALESCE(:description, ''), '%')) "
            + "AND p.price BETWEEN :priceLow AND :priceHigh "
            + "AND p.averageRating >= :averageRating "
            + "AND p.archived = :archived "
            + "AND ((category.name IN :selectedCategories) "
            + "OR (:amountOfSelectedCategories = 0 AND category IN (SELECT c FROM Category c))) "
            + "GROUP BY p "
            + "HAVING SIZE(p.categories) >= :amountOfSelectedCategories"
    )
    Page<Product> findAllBySearchModel(
            Pageable pageable,
            @Param("searchRequest") String searchRequest,
            @Param("description") String description,
            @Param("priceLow") BigDecimal priceLow,
            @Param("priceHigh") BigDecimal priceHigh,
            @Param("averageRating") double averageRating,
            @Param("archived") boolean archived,
            @Param("selectedCategories") List<String> selectedCategories,
            @Param("amountOfSelectedCategories") int amountOfSelectedCategories
    );

Segal