IT序号网

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

developer 2021年06月14日 数据库 269 0

1、Specification


    
     
  1. //查询条件List
  2. List<Predicate> predicateList = new ArrayList<Predicate>();
  3. Specification specification = new Specification() {
  4. @Override
  5. public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
  6. //root即是Join<>内部第一个泛型的类型,意思就是用SkuProduct与Picture通过SkuProduct的pictures左联
  7. Join<SkuProduct,Picture> skuProductPictureJoin = root.join( "pictures",JoinType.INNER);
  8. //添加第1个查询条件:SkuProduct的code等于skuProduct.getCode(),然后将这个criteriaBuilder的条件添加到predicateList
  9. predicateList.add(criteriaBuilder.equal(root.get( "code"),skuProduct.getCode()));
  10. //添加第2个条件
  11. predicateList.add(criteriaBuilder.notEqual(skuProductPictureJoin.get( "pictureType"), 0));
  12. //返回
  13. return criteriaBuilder.and(predicateList.toArray( new Predicate[predicateList.size()]));
  14. }
  15. };
  16. // 重要说明:Specification不支持右连接!
  17. //Specification specification = new Specification() {
  18. // @Override
  19. // public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
  20. // Join<Picture,SkuProduct> skuProductPictureJoin = root.join("pictures",JoinType.RIGHT);
  21. // predicateList.add(criteriaBuilder.notEqual(root.get("pictureType"),0));
  22. // predicateList.add(criteriaBuilder.equal(skuProductPictureJoin.get("code"),skuProduct.getCode()));
  23. // return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
  24. // }
  25. //};
  26. List<SkuProduct> results = skuProductRepository.findAll(specification);
  27. if(results!= null){
  28. results.stream().forEach(result->{
  29. System.out.println(result);
  30. });
  31. }


2、HQL


    
     
  1. @Query(value = "SELECT p FROM Picture p WHERE p.code= :code")
  2. List<Picture> findByPicture(@Param(value = "code") String code);

3、SQL

3.1--:占位符

    
     
  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)
  2. List<Picture> findBySkuProductCodeAndPicture(@Param(value = "code") String code);


3.2--?占位符

    
     
  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=?1 AND p.picture_type=?2",nativeQuery = true)
  2. List<Picture> findSkuProductCodeAndPicture(String code,int pictureType);



版权声明:本文为博主原创文章,未经博主允许不得转载。

原文地址:https://blog.csdn.net/trusause/article/details/78672595
评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!