SQL CROSS JOIN 与 JPA 规范

Question

我有一个Spring Boot项目，该项目具有以下三个实体类，

@Entity
@Table("item")
public class Item extends SomeParent {

     @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true, mappedBy = "item")
     @JsonManagedReference("product-s")
     Set<Product> products;
}

@Entity
@Table(name = "product")
public class Product extends SomeParent {

     @ManyToOne
     @JoinColumn(name = "item_id", nullable = false)
     @JsonBackReference("product-s")
     private Item item;

     @ManyToOne
     @JoinColumn(name = "fruit_id", nullable = false)
     private Fruit fruit;

     private String type;
}

@Entity
@Table(name = "fruit")
public class Fruit extends SomeParent {

     private String name;

     private Integer qty;
}

我正在使用JPA规范来构建查询以查找项目 by fruit.name.name和product .type。以下是我的搜索查询，

1. fruit = mango, type = A and fruit = apple, type = B 
2. fruit = mango, type = A or fruit = apple, type = B 

通过第一个查询，它需要搜索所有项目 by fruit.name = mango and product.type = a 和 fruit.name =苹果和product.type = b。 项目必须具有类别的芒果，而苹果则具有类别。

通过第二查询，需要搜索所有项目 by fruit.name = name =芒果和产品。 如果任何项目都有具有类别的芒果或具有B类的Apple，则应返回item

public Specification<Item> search() {
    return (root, query, criteriaBuilder) -> {
       SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);

       if (isAndQuery) { 
          criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"), 
                    criteriaBuilder.equal(productJoin.get("type"), "A")));
          criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"), 
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       } else {
          criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
                    criteriaBuilder.equal(productJoin.get("type"), "A")));
          criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       }

       return criteriaBuilder;
    };
}

我的问题是，并且查询不起作用，因此它没有给我结果。那么有人可以帮助我解决这个问题吗？我在这里做错了什么？

这是JPA生成的和查询，

select * from item item
left outer join product product on item.id=product.item_id 
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
and (fruit.name='apple')
and product.type='B')
order by item.id asc limit ?

这是JPA生成的或查询，

select * from item item 
left outer join product product on item.id=product.item_id 
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
or (fruit.name='apple')
and product.type='B')
and 1=1
order by
item.id asc limit ?

Answer 1

AND 不起作用，因为您正在对与其他检查相同的产品/水果执行过滤器检查。产品的水果不能同时是苹果和芒果。解决方案是手动创建两个单独的显式连接，

public Specification<Item> search() {
   return (root, query, criteriaBuilder) -> {
       Predicate returnPredicate;
       SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);

       if (isAndQuery) { 
          SetJoin<Item, Product> productJoin2 = root.joinSet("products", JoinType.LEFT);
          returnPredicate = criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"), 
                    criteriaBuilder.equal(productJoin.get("type"), "A"))),
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin2.get("fruit").get("name"), "apple"), 
                    criteriaBuilder.equal(productJoin2.get("type"), "B")));
       } else {
          returnPredicate = criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
                    criteriaBuilder.equal(productJoin.get("type"), "A"))),
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       }

       return returnPredicate;
   };
}

这更多地翻译为给我一个项目，该项目既包含带有 Apple 的产品，又包含带有芒果的单独产品。