4

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

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
SubOptimal
  • 22,518
  • 3
  • 53
  • 69
  • Good question, I'd create jpql dynamically to hve :color :color1 etc., as a last option. – Zeus Jul 09 '14 at 18:18
  • 1
    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. – Gas Jul 09 '14 at 19:39
  • @Gas 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. – SubOptimal Jul 10 '14 at 07:52
  • 2
    Possible solutions here on SO [native query](https://stackoverflow.com/questions/44267862/sql-multiple-columns-in-in-clause-to-convert-to-jpa#44269064) or with [Hibernate HQL](https://stackoverflow.com/questions/19776826/hibernate-criteria-query-for-multiple-columns-with-in-clause-and-a-subselect#19781340) – SubOptimal Sep 27 '17 at 10:50

1 Answers1

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
    );
Paul
  • 841
  • 13
  • 20