spring-boot - 如何编写 JPA IN 查询来检查集合是否给出了一组值。？

Question

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

现在我需要检查给定标签集是否存在哪些设备。
以下是我尝试过的查询。

@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);

这以错误告终。

org.springframework.dao.InvalidDataAccessApiUsageException: Parametervalue [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)] atorg.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:373)atorg.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)

谁能建议我一个解决方案？

Answer 1

你不能为所欲为。正如 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.

所以，我看到以下方法:

按以下方式重写您的查询:

@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);

然后在java代码中做进一步过滤。

例如，以这种方式重写您的实体映射:

@Entity
public class Device extends AbstractEntity implements Serializable {

    @OneToMany
    private List<Label> labels;
}

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

   private String text;
}

然后使用以下查询:

@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);

附言另请注意 LazyCollectionOption.FALSE已弃用，因为您应该使用 JPA FetchType @ElementCollection 的属性.见 this .