1、Specification
-
//查询条件List
-
List<Predicate> predicateList =
new ArrayList<Predicate>();
-
-
Specification specification =
new Specification() {
-
@Override
-
public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
-
//root即是Join<>内部第一个泛型的类型,意思就是用SkuProduct与Picture通过SkuProduct的pictures左联
-
Join<SkuProduct,Picture> skuProductPictureJoin = root.join(
"pictures",JoinType.INNER);
-
//添加第1个查询条件:SkuProduct的code等于skuProduct.getCode(),然后将这个criteriaBuilder的条件添加到predicateList
-
predicateList.add(criteriaBuilder.equal(root.get(
"code"),skuProduct.getCode()));
-
//添加第2个条件
-
predicateList.add(criteriaBuilder.notEqual(skuProductPictureJoin.get(
"pictureType"),
0));
-
//返回
-
return criteriaBuilder.and(predicateList.toArray(
new Predicate[predicateList.size()]));
-
}
-
};
-
-
//
重要说明:Specification不支持右连接!
-
//Specification specification = new Specification() {
-
// @Override
-
// public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
-
// Join<Picture,SkuProduct> skuProductPictureJoin = root.join("pictures",JoinType.RIGHT);
-
// predicateList.add(criteriaBuilder.notEqual(root.get("pictureType"),0));
-
// predicateList.add(criteriaBuilder.equal(skuProductPictureJoin.get("code"),skuProduct.getCode()));
-
// return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
-
// }
-
//};
-
-
List<SkuProduct> results = skuProductRepository.findAll(specification);
-
if(results!=
null){
-
results.stream().forEach(result->{
-
System.out.println(result);
-
});
-
}
2、HQL
-
@Query(value =
"SELECT p FROM Picture p WHERE p.code= :code")
-
List<Picture> findByPicture(@Param(value = "code") String code);
3、SQL
3.1--:占位符
-
@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)
-
List<Picture> findBySkuProductCodeAndPicture(@Param(value = "code") String code);
3.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)
-
List<Picture> findSkuProductCodeAndPicture(String code,int pictureType);
版权声明:本文为博主原创文章,未经博主允许不得转载。