How to write JPA IN query to check a collection has given set of values.?

user_15188317 0
Viewed 404
@javax.persistence.Entity
public class Device extends AbstractEntity implements Serializable {
    @ElementCollection
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<String> labels;
}

Now I need to check whether what are the devices existing for the given label set.

Below are the querirs that I tried.

@Query("SELECT DISTINCT d FROM Device d JOIN d.labels l WHERE d.entity.id IN :entity_ids AND l IN :labels ")
List<Device> find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);

@Query("SELECT d FROM Device d WHERE d.entity.id IN :entity_ids AND  d.labels IN :labels") List<Device> 
find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);

This ended up with en error.

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [D2_label1] did not match expected type [java.util.Collection (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [D2_label1] did not match expected type [java.util.Collection (n/a)] at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:373) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)

Can anyone suggest me a solution?

spring-data-jpa spring-boot hibernate
3 Answers

You can not do what you want. As it's stated in the documentation:

IN predicates performs a check that a particular value is in a list of values. Its syntax is:

in_expression ::= single_valued_expression [NOT] IN single_valued_list

single_valued_list ::= constructor_expression | (subquery) | collection_valued_input_parameter

constructor_expression ::= (expression[, expression]*)

The types of the single_valued_expression and the individual values in the single_valued_list must be consistent.

JPQL limits the valid types here to string, numeric, date, time, timestamp, and enum types, and, in JPQL, single_valued_expression can only refer to:

  • "state fields", which is its term for simple attributes. Specifically, this excludes association and component/embedded attributes.

  • entity type expressions. See Entity type.

In HQL, single_valued_expression can refer to a far more broad set of expression types. Single-valued association are allowed, and so are component/embedded attributes, although that feature depends on the level of support for tuple or "row value constructor syntax" in the underlying database.

So, I see the following approaches:

  1. Rewrite your query in the following way:
@Query("SELECT DISTINCT d FROM Device d join fetch d.labels WHERE d.entity.id IN :entity_ids")
List<Device> find(@Param("entity_ids") Set<Long> entityIds);

and then do further filtering in java code.

  1. Rewrite your entity mapping for example in this way:
@Entity
public class Device extends AbstractEntity implements Serializable {

    @OneToMany
    private List<Label> labels;
}

@Entity
public class Label
{
   @Id
   private Long id;

   private String text;
}

and then use the following query:

@Query("SELECT DISTINCT d FROM Device d join fetch d.labels l WHERE d.entity.id IN :entity_ids and l.text in :labels")
List<Device> find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);

P.S. Please also note that LazyCollectionOption.FALSE is deprecated since you should use the JPA FetchType attribute of the @ElementCollection. See this.

user_62771040

From my point of view you should write the IN clause in brackets:

@Query("SELECT DISTINCT d FROM Device d JOIN d.labels l WHERE d.entity.id IN (:entity_ids) AND l IN (:labels)")
List<Device> find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);

@Query("SELECT d FROM Device d WHERE d.entity.id IN (:entity_ids) AND  d.labels IN (:labels)") List<Device> 
find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);
user_34930360

This is correct:

@Query("SELECT DISTINCT d FROM Device d JOIN d.labels l WHERE d.entity.id IN :entity_ids AND l IN :labels ")
List<Device> find(@Param("entity_ids") Set<Long> entityIds, @Param("labels") Set<String> labels);

Can you share the error you are getting when using this query?

user_4124460
Related