spring - Criteria Api 产生了太多的连接

Question

实体：

@Entity
@Table(name = "shop")
public class Shop implements Serializable{
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    
    private String address;
    
    
    @OneToMany(mappedBy = "shop")
    private List<Product> product = new ArrayList<>();

    public Shop() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    } 
}


@Entity
@Table(name = "product")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "disc_col")
public class Product implements Serializable{
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private BigDecimal price;
    
    @ManyToOne
    private Shop shop;

    public Product() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public Shop getShop() {
        return shop;
    }

    public void setShop(Shop shop) {
        this.shop = shop;
    } 
}


@Entity
@DiscriminatorValue("loose")
public class LooseProduct extends Product {

    
    private BigDecimal weight;

    public LooseProduct() {
    }

    public BigDecimal getWeight() {
        return weight;
    }

    public void setWeight(BigDecimal weight) {
        this.weight = weight;
    }
}

@Entity
@DiscriminatorValue("liquid")
public class LiquidProduct extends Product {
    
    
    private BigDecimal volume;

    public LiquidProduct() {
    }

    public BigDecimal getVolume() {
        return volume;
    }

    public void setVolume(BigDecimal volume) {
        this.volume = volume;
    }  
}

服务：

public class ShopRepositoryImpl implements ShopRepositoryCustom{

    @PersistenceContext
    private EntityManager em;   

    @Override
    public List<Shop> findShops(BigDecimal volume, BigDecimal weight, BigDecimal price) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Shop> cq = cb.createQuery(Shop.class);
        Root<Shop> root = cq.from(Shop.class);
        Join<Shop, Product> product = root.join("product", JoinType.LEFT);
        
       
        Predicate p1 = cb.equal(cb.treat(product, LiquidProduct.class).get("volume"), volume);            
        Predicate p2 = cb.equal(cb.treat(product, LooseProduct.class).get("weight"), weight);        
        Predicate p3 = cb.equal(product.get("price"), price);
        
        
        cq.where(cb.and(p3, cb.or(p1, p2)));
        Query q = em.createQuery(cq);

        return q.getResultList();
    }
}
        

我有一个问题，我的查询findShops生成了太多的连接：

select shop0_.id as id1_1, shop0_.address as address2_1, shop0_.name as name3_1 from shop shop0 
left outer join product product1 on shop0_.id=product1_.shop_id 
left outer join product product2 on shop0_.id=product2_.shop_id 
left outer join product product3_ on shop0_.id=product3_.shop_id where product3_.price=1 and (product2_.volume=1 or product3_.weight=0)

这是一种InheritanceType.SINGLE_TABLE策略，所以它不应该创建三个连接，因为数据库中只有一个表 Product。有没有办法优化这个？

来自 org.hibernate.query.criteria.internal.CriteriaBuilderImpl 类的代码：

@SuppressWarnings("unchecked")
private <X, T, V extends T, K extends JoinImplementor> K treat(
Join<X, T> join,
Class<V> type,
BiFunction<Join<X, T>, Class<V>, K> f) {
final Set<Join<X, ?>> joins = join.getParent().getJoins();
final K treatAs = f.apply( join, type );
joins.add( treatAs );
return treatAs;
}

Treat 方法从现有连接创建新连接。每次都发生独立于继承类型。接下来休眠生成查询并且不检查连接中的重复项。当我们使用处理方法时，您知道如何防止生成额外的连接吗？

我发现错误报告： https ://hibernate.atlassian.net/projects/HHH/issues/HHH-12094?filter=allissues&orderby=created%20DESC&keyword=treat

标签： springhibernatecriteria-api

Answer 1

如果您从 JPA 2.1 开始使用，您可以像这样更改查询：

select shop0_.id as id1_1, shop0_.address as address2_1, shop0_.name as name3_1 
from shop shop0 
    left join product product1 on shop0_.id=product1_.shop_id 
        and (
            (product1.disc_col = 'loose' and product1.weight = weight_var)
            or (product1.disc_col = 'liquid' and product1.volume = volume_var)
        );

对于实现，您需要将 disc_col 列的映射添加到 Product 实体。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Shop> cq = cb.createQuery(Shop.class);

Root<Shop> root = cq.from(Shop.class);
Join<Shop, Product> product = root.join("product", JoinType.LEFT);
    //add other conditions
    product.on(
        cb.and(
            cb.or(
                cb.and(cb.equal(product.get("discCol"),"liquid"),cb.equal(product.get("volume"),volumeVar)),
                cb.and(cb.equal(product.get("discCol"),"loose"),cb.equal(product.get("weight"),weightVar))                    
            )
        )
    );

Query q = em.createQuery(cq);

return q.getResultList();