JPA查询之Specification以及HQL、SQL查询

1、Specification

1. 

2. 

//查询条件List


3. 

List<Predicate> predicateList = new ArrayList<Predicate>();


4. 




5. 

Specification specification = new Specification() {


6. 

@Override



7. 

public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {


8. 

//root即是Join<>内部第一个泛型的类型，意思就是用SkuProduct与Picture通过SkuProduct的pictures左联



9. 

Join<SkuProduct,Picture> skuProductPictureJoin = root.join("pictures",JoinType.INNER);


10. 

//添加第1个查询条件：SkuProduct的code等于skuProduct.getCode()，然后将这个criteriaBuilder的条件添加到predicateList



11. 

predicateList.add(criteriaBuilder.equal(root.get("code"),skuProduct.getCode()));


12. 

//添加第2个条件



13. 

predicateList.add(criteriaBuilder.notEqual(skuProductPictureJoin.get("pictureType"),0));


14. 

//返回



15. 

return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));


16. 

    }


17. 

};


18. 




19. 


//重要说明：Specification不支持右连接！



20. 

//Specification specification = new Specification() {


21. 

//    @Override


22. 

//    public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {


23. 

//        Join<Picture,SkuProduct> skuProductPictureJoin = root.join("pictures",JoinType.RIGHT);


24. 

//        predicateList.add(criteriaBuilder.notEqual(root.get("pictureType"),0));


25. 

//        predicateList.add(criteriaBuilder.equal(skuProductPictureJoin.get("code"),skuProduct.getCode()));


26. 

//        return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));


27. 

//    }


28. 

//};


29. 




30. 

List<SkuProduct> results = skuProductRepository.findAll(specification);


31. 


if(results!=null){


32. 

results.stream().forEach(result->{


33. 

System.out.println(result);


34. 

    });


35. 

}


36. 

2、HQL

1. 

2. 


@Query(value = "SELECT p FROM Picture p WHERE p.code= :code")


3. 

List<Picture> findByPicture(@Param(value = "code") String code);


4. 

3、SQL

3.1--:占位符

1. 

2. 


@Query(value = "SELECT p.* FROM picture p LEFT JOIN sku_product_pictures sp_p ON p.id=sp_p.pictures_id LEFT JOIN sku_product sp ON sp_p.sku_product_id=sp.id WHERE sp.code= :code AND p.picture_type=0",nativeQuery = true)


3. 

List<Picture> findBySkuProductCodeAndPicture(@Param(value = "code") String code);


4. 

3.2--?占位符

1. 

2. 


@Query(value = "SELECT p.* FROM picture p LEFT JOIN sku_product_pictures sp_p ON p.id=sp_p.pictures_id LEFT JOIN sku_product sp ON sp_p.sku_product_id=sp.id WHERE sp.code=?1 AND p.picture_type=?2",nativeQuery = true)


3. 

List<Picture> findSkuProductCodeAndPicture(String code,int pictureType);


4.