具有空值关系中目标实体属性的类JPQL表达式

Question

我有一个名为item的实体，它与另一个实体category有关系，该实体可以为null。这两个实体如下：

项目

@Entity
public class Item {
     @Id
     private String id;
     private String name;
     private String code;
     @ManyToOne
     private Category category;
}

类别

@Entity
public class Category {
     @Id
     private String id;
     private String name;
}

现在，我必须选择具有类似于搜索词的名称、代码或类别名称的项，对此我尝试了以下查询：

"SELECT item FROM Item item "
                + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
                + "OR item.category.name LIKE :searchTerm"

query.setParameter("searchTerm", "%" + searchTerm + "%");

"SELECT item FROM Item item "
                + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
                + "OR (item.category IS NOT NULL AND item.category.name LIKE :searchTerm)"

Answer 1

尝试使用左联接：

"SELECT item FROM Item item LEFT JOIN item.category cat"
            + "WHERE item.code LIKE :searchTerm OR item.name LIKE :searchTerm "
            + "OR (cat.categoryID IS NOT NULL AND cat.name LIKE :searchTerm)"

JPQL文档