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

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

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