Question 198186 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Jpa @Query Annotation problem to use IN clause with java ArrayList

i have problem to use in clause in jpa Query
when i use c.id IN (1,7) its work fin but when i use c.id IN :categories its give me this error

org.hibernate.type.descriptor.java.CoercionException: Cannot coerce value [1, 7] [java.util.Arrays$ArrayList] as Long


@Query("SELECT new com.example.events.dto.Event.ParticipantGetAllEventsDto(e, COUNT(p)) "
    + "FROM Event e JOIN FETCH e.reservation r JOIN FETCH r.salon s "
    + "LEFT JOIN r.participants p "
    + "JOIN e.categories c "
    + "WHERE (:title is null OR e.title LIKE %:title%) AND "
    + "(:isFree is null OR ((:isFree = true AND e.cost = 0) OR (:isFree = false AND e.cost >0 ))) AND "
    + "(:categories is null OR c.id IN (:categories) ) "
    + "GROUP BY e")
List<ParticipantGetAllEventsDto> getAll(@Param("title") String   title,@Param("categories") List<Long> categories ,@Param("isFree") boolean isFree);

i also check categories value its arrayList and its value is [1,7]

Tags:

3

Answers


  1. 0

    The error you’re encountering when using the IN clause with a named parameter in JPA queries is likely due to how you’re passing the parameter values. When using a named parameter with the IN clause, you need to provide a collection or an array as the parameter value.
    Example,

     List<Integer> categoryIds = Arrays.asList(1, 7); 

 TypedQuery<EntityType> query = entityManager.createQuery(
"SELECT e FROM EntityType e WHERE e.id IN :categoryIds", EntityType.class);
 query.setParameter("categoryIds", categoryIds);

  List<EntityType> results = query.getResultList();

    In the above example, categoryIds is a list containing the values you want to use in the IN clause. You pass this list as the parameter value using the setParameter method with the corresponding named parameter.

    Make sure you’re passing a collection or an array as the parameter value, rather than a single value or a string representation of multiple values. If you’re still experiencing issues, please provide more details about the error message you’re receiving, and I’ll be happy to assist you further.

    Login or Signup to reply.
  2. 0

    The error message you’re encountering suggests that there is an issue with the data type conversion for the categories parameter in your query. The error specifically states that it cannot coerce the value [1, 7] [java.util.Arrays$ArrayList] as a Long.

    Based on the error message and your query, it seems that you are passing an ArrayList containing the values [1, 7] as the categories parameter, which should be a list of Long values. However, the values in your ArrayList are not of the correct type.

    To resolve this error, ensure that the categories parameter contains only Long values. If the values you want to pass are 1 and 7, you can modify your code as follows:

    List<Long> categories = Arrays.asList(1L, 7L);

    By explicitly specifying the L suffix after the numbers, you indicate that they are Long values. Make sure you update the code where you call the getAll method accordingly.

    This modification will ensure that the categories parameter is passed as a List, resolving the type coercion issue in your query.

    Login or Signup to reply.
  3. 0

    I have reproduced your settings and get the same error. It looks like JPQL can’t handle the check :myParameter IS NULL if myParameter is a collection. If you remove the check for null the query works without issue.

    Of course it is not what you wanted – to switch the IN clause on/off depending on a parameter.

    Workaround 1

    Introduce a separate boolean parameter like here:

        @Query("SELECT c FROM Customer c WHERE (:noList = true OR c.id IN (:theList))")
    List<Customer> findFromList(List<Long> theList, boolean noList);

    Workaround 2

    Use Criteria query instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search