Criteria Api generetes too much joins

Viewed 103

Entities:

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

Service:

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();
    }
}
        

I have a problem that my query findShops generates too much joins:

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)

It is InheritanceType.SINGLE_TABLE strategy so it shouldn't create three joins because there is just one table Product in database. Is there any way to optimize this?

Code from org.hibernate.query.criteria.internal.CriteriaBuilderImpl class:

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

The treat method creates new join from existing one. It is happening every time independent of inheritence type. Next hibernate generates query and do not check duplicates in joins. Do you have any idea how to prevent from generate additional joins when we use treat method?

I found the error report: https://hibernate.atlassian.net/projects/HHH/issues/HHH-12094?filter=allissues&orderby=created%20DESC&keyword=treat

1 Answers

If you use JPA 2.1 onwards you can change the query like this:

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

For the implementation you would need to add the mapping of the disc_col column to the Product entity.

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();
Related