如何在JPA规范中将复杂的SQL转换为CriteriaBuilder

Question

表sql：在此处输入图像描述

链接创建和测试SQL:https://www.db-fiddle.com/f/9ihygssmwErvhb4ekcUnG5/5

我没有办法将这个sql转换为Spring（方法：toPredicate（Root root， CriteriaQuery

在java中有实体：产品，属性

class Product{
  private Long id;
  private String name;

@JsonIgnore
@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@JoinTable(name = "product_attribute", joinColumns = @JoinColumn(name = "product_id"), 
inverseJoinColumns = @JoinColumn(name = "attribute_id"))
List<Attribute> attributes = new ArrayList<>();
}

class Attribute{
    private Long id;
    private String name;
    private String value;

    @JsonIgnore
@ManyToMany(mappedBy = "attributes")
List<Product> products = new ArrayList<>();
}

SQL：

 SELECT pa1.product_id 
        FROM  product_attribute pa1,  
        product_attribute pa2, 
        product_attribute pa3,
        product_attribute pa4
        where pa1.attribute_id IN ('A','B')
        and pa2.attribute_id IN ('C','D')
        and pa3.attribute_id IN ('E','F')
        and pa4.attribute_id IN ('G','H')
                AND pa2.product_id = pa3.product_id
                AND pa1.product_id = pa3.product_id
                AND pa1.product_id = pa4.product_id
                AND pa2.product_id = pa4.product_id
                AND pa3.product_id = pa4.product_id

我尝试过预测方法，但失败了

public class ProductSpecification implements Specification<Product>  {
   @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        Join<Object, Object> a1Join = root.join( "attributes" );
        a1Join.on( criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.equal( a1Join.get( "name" ), "branch" ),
                        criteriaBuilder.equal( a1Join.get( "value" ), "Acer" )
                ),
                criteriaBuilder.and(
                        criteriaBuilder.equal( a1Join.get( "name" ), "branch" ),
                        criteriaBuilder.equal( a1Join.get( "value" ), "Dell" )
                )
        ) );

        Join<Object, Object> a2Join = root.join( "attributes" );
        a2Join.on( criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.equal( a2Join.get( "name" ), "screen" ),
                        criteriaBuilder.equal( a2Join.get( "value" ), "13 Inch" )
                ),
                criteriaBuilder.and(
                        criteriaBuilder.equal( a2Join.get( "name" ), "screen" ),
                        criteriaBuilder.equal( a2Join.get( "value" ), "15.6 Inch" )
                )
       ) );

        Join<Object, Object> a3Join = root.join( "attributes" );
        a3Join.on( criteriaBuilder.and(
                criteriaBuilder.equal( a3Join.get( "name" ), "need" ),
                criteriaBuilder.equal( a3Join.get( "value" ), "gaming" )
        ) );

        Join<Object, Object> a4Join = root.join( "attributes" );
        a4Join.on( criteriaBuilder.and(
                criteriaBuilder.equal( a4Join.get( "name" ), "memory" ),
                criteriaBuilder.equal( a4Join.get( "value" ), "32 G" )
        ) );

         query.distinct(true);

        List<Predicate> predicates = new ArrayList<>();
        predicates.add(root.in(a1Join,a2Join,a3Join,a4Join));
        return builder.and(predicates.toArray(new Predicate[0]));
    }

}

结果日志:

Hibernate: select distinct product0_.id as id1_7_, product0_.discount as discount2_7_, product0_.long_description as long_des3_7_, product0_.name as name4_7_, product0_.price as price5_7_, product0_.promotion as promotio6_7_, product0_.quantity as quantity7_7_, product0_.short_description as short_de8_7_, product0_.status as status9_7_, product0_.summary as summary10_7_, product0_.view as view11_7_ from product product0_ inner 
join  
(product_attribute attributes1_ inner join attribute attribute2_ on attributes1_.attribute_id=attribute2_.id) on product0_.id=attributes1_.product_id and (attribute2_.name=? and attribute2_.value=?) inner join  (product_attribute attributes3_ inner join attribute attribute4_ on attributes3_.attribute_id=attribute4_.id) on product0_.id=attributes3_.product_id and (attribute4_.name=? and attribute4_.value=?) inner join  (product_attribute attributes5_ inner join attribute attribute6_ on attributes5_.attribute_id=attribute6_.id) on product0_.id=attributes5_.product_id and (attribute6_.name=? and attribute6_.value=?) inner join  (product_attribute attributes7_ inner join attribute attribute8_ on attributes7_.attribute_id=attribute8_.id) on product0_.id=attributes7_.product_id and (attribute8_.name=? and attribute8_.value=?) where product0_.id in (attribute2_.id , attribute4_.id , attribute6_.id , attribute8_.id) limit ?

Answer 1

问题在于，您使用的是@manytomy，这意味着您实际上无法作为一个实体访问桥接表。要编写准确的查询，需要使用桥实体更改映射。参见Hibernate ORM文档中的示例172.双向多对多链接实体。

映射将更改为：

class Product{
  private Long id;
  private String name;

    ...
    @OneToMany(
        mappedBy = "product",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    List<ProductAttribute> attributes = new ArrayList<>();
}

@Entity(name = "ProductAttribute")
@Table(name = "product_attribute")
public static class ProductAttribute implements Serializable {

    @Id
    @ManyToOne
    private Product product;

    @Id
    @ManyToOne
    private Attribute attribute;

}

class Attribute{

    ...

    @OneToMany(
        mappedBy = "attribute",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    List<ProductAttribute> products = new ArrayList<>();
}

现在，您可以编写条件：

CriteriaBuilder criteriaBuilder = ormFactory.getCriteriaBuilder();
CriteriaQuery<Product> criteria = criteriaBuilder.createQuery( Product.class );
Root<Product> root = criteria.from( Product.class );
Join<Object, Object> a1Join = root.join( "attributes" );
Join<Object, Object> a2Join = root.join( "attributes" );
Join<Object, Object> a3Join = root.join( "attributes" );
Join<Object, Object> a4Join = root.join( "attributes" );

Predicate predicate = builder.and(
    a1Join.get( "attribute" ).in('A', 'B'),
    a2Join.get( "attribute" ).in('C', 'D'),
    a3Join.get( "attribute" ).in('E', 'F'),
    a4Join.get( "attribute" ).in('G', 'H')
);

criteria.where(predicate);

也就是说，可以创建一个使用连接返回相同结果的条件。我已将您提供的测试sql查询转换为标准。这不是相同的查询，但应该会给你相同的结果（我已经使用Hibernate ORM测试过了）：

        CriteriaBuilder criteriaBuilder = ormFactory.getCriteriaBuilder();
        CriteriaQuery<Product> criteria = criteriaBuilder.createQuery( Product.class );
        Root<Product> root = criteria.from( Product.class );

        criteria.select( root );
        criteria.distinct( true );

        Join<Object, Object> a1Join = root.join( "attributes" );
        a1Join.on( criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.equal( a1Join.get( "name" ), "branch" ),
                        criteriaBuilder.equal( a1Join.get( "value" ), "Acer" )
                ),
                criteriaBuilder.and(
                        criteriaBuilder.equal( a1Join.get( "name" ), "branch" ),
                        criteriaBuilder.equal( a1Join.get( "value" ), "Dell" )
                )
        ) );

        Join<Object, Object> a2Join = root.join( "attributes" );
        a2Join.on( criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.equal( a2Join.get( "name" ), "screen" ),
                        criteriaBuilder.equal( a2Join.get( "value" ), "13 Inch" )
                ),
                criteriaBuilder.and(
                        criteriaBuilder.equal( a2Join.get( "name" ), "screen" ),
                        criteriaBuilder.equal( a2Join.get( "value" ), "15.6 Inch" )
                )
       ) );

        Join<Object, Object> a3Join = root.join( "attributes" );
        a3Join.on( criteriaBuilder.and(
                criteriaBuilder.equal( a3Join.get( "name" ), "need" ),
                criteriaBuilder.equal( a3Join.get( "value" ), "gaming" )
        ) );

        Join<Object, Object> a4Join = root.join( "attributes" );
        a4Join.on( criteriaBuilder.and(
                criteriaBuilder.equal( a4Join.get( "name" ), "memory" ),
                criteriaBuilder.equal( a4Join.get( "value" ), "32 G" )
        ) );

        try (Session session = ormFactory.openSession()) {
            List<Product> resultList = session.createQuery( criteria ).getResultList();
            System.out.println( resultList );
        }

但是您需要更改方法toPredicate的签名：

public void toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    Join<Object, Object> a1Join = root.join( "attributes" );
    ... // The same as in the example above


    Join<Object, Object> a4Join = root.join( "attributes" );
    a4Join.on( ... );
}

你可以像这样使用这个方法:

CriteriaBuilder criteriaBuilder = ...
CriteriaQuery<Product> query = ...
Root<Product> root = criteria.from( Product.class );
specification.toPredicate(root, query, criteriaBuilder);

// Now you can run the query and it will work