Spring Data JPA Specification(规范)实现复杂查询 ╰半橙微兮° 2022-02-13 14:54 293阅读 0赞 **目录** 持久化 API 接口概述 JpaSpecificationExecutor 常用 API JPA Specification 编码示例 -------------------- # 持久化 API 接口概述 # 1、JPA 持久化 API 接口主要如下: <table> <tbody> <tr> <td>public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> <br> public interface <span style="color:#f33b45;">JpaRepository</span><T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> <br> public interface JpaRepositoryImplementation<T, ID> extends JpaRepository<T, ID>, <span style="color:#f33b45;">JpaSpecificationExecutor</span><T> <br> public class SimpleJpaRepository<T, ID> implements JpaRepositoryImplementation<T, ID></td> </tr> </tbody> </table> 2、JpaRepository 接口拥有常用的 CURD 方法以及分页方法、字段排序等操作,但是没有与或非、like、以及大于等于、小于等于等操作,这些方法都在 JpaSpecificationExecutor 接口中。 3、如果只需要简单的实现 CRUD、分页、排序,则继承 JpaRepository接口即可,如果还需要复制查询,则可以再继承 JpaSpecificationExecutor 接口。当然也可以直接继承 JpaRepositoryImplementation 接口。 # **JpaSpecificationExecutor 常用 API** # <table> <tbody> <tr> <td colspan="2" style="text-align:center;"><strong>org.springframework.data.jpa.repository.JpaSpecificationExecutor</strong></td> </tr> <tr> <td>List<T> findAll(@Nullable Specification<T> spec)</td> <td>规范查询。没有数据时返回空列表。</td> </tr> <tr> <td>Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable)</td> <td>规范查询。同时进行分页查询。</td> </tr> <tr> <td>List<T> findAll(@Nullable Specification<T> spec, Sort sort)</td> <td>规范查询。同时指定排序字段。</td> </tr> <tr> <td>Optional<T> findOne(@Nullable Specification<T> spec)</td> <td>规范查询单条数据。注意如果结果多余一条,则抛异常。</td> </tr> </tbody> </table> # JPA Specification 编码示例 # 1、本文承接《[《Spring Data JPA 常用 CRUD 操作汇总》][Spring Data JPA _ CRUD],环境为 Spring boot 2.1.4。下面列举几项稍微说明,实际中对于其它需求,同理可以参看 jpa 源码进行编写即可。 **持久化层** import com.wmx.entity.TV; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; /** * Java 接口可以多继承 * JpaRepository 中有常用的 CRUD 、分页、排序等方法 * JpaSpecificationExecutor 可以实现任意的复杂查询 */ public interface TVRepository extends JpaRepository<TV, Integer>, JpaSpecificationExecutor<TV> { } **service 业务层** 在线源码:[https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/service/TvService.java][https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_service_TvService.java] 在线源码:[https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/service/impl/TvServiceImpl.java][https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_service_impl_TvServiceImpl.java] import com.wmx.entity.TV; import org.springframework.data.domain.Page; import java.util.Date; import java.util.List; /** * Created by Administrator on 2019/4/27. */ public interface TVService { //条件查询时间范围在 [start,end] 之间的数据。如果 tvName 不为空,加上名称条件 List<TV> findAll(Date start, Date end, String tvName); //查询生产日期大于等于 start 的数据,且进行分页查询 Page<TV> findAll(Date start, int page, int size); //模糊查询 like List<TV> findAllLike(String tvNameLike); } import com.wmx.entity.TV; import com.wmx.repository.TVRepository; import com.wmx.service.TVService; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.data.jpa.domain.Specification; import org.springframework.stereotype.Service; import javax.annotation.Resource; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import java.util.ArrayList; import java.util.Date; import java.util.List; @Service public class TVServiceImpl implements TVService { @Resource private TVRepository tvRepository; @Override public List<TV> findAll(Date start, Date end, String tvName) { //直接使用匿名内部类实现接口 Specification specification = new Specification<TV>() { @Override public Predicate toPredicate(Root<TV> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> predicateList = new ArrayList<>(); //条件1:查询 tvName 为 海信 的数据,root.get 中的值与 TV 实体中的属性名称对应 if (tvName != null && !"".equals(tvName)) { predicateList.add(cb.equal(root.get("tvName").as(String.class), tvName)); } //条件2:TV 生产日期(dateOfProduction)大于等于 start 的数据,root.get 中的 dateOfProduction 必须对应 TV 中的属性 predicateList.add(cb.greaterThanOrEqualTo(root.get("dateOfProduction").as(Date.class), start)); //条件3:TV 生产日期(dateOfProduction)小于等于 end predicateList.add(cb.lessThanOrEqualTo(root.get("dateOfProduction").as(Date.class), end)); Predicate[] pre = new Predicate[predicateList.size()]; pre = predicateList.toArray(pre); return query.where(pre).getRestriction(); } }; return tvRepository.findAll(specification);//没有数据时,返回空列表 } @Override public Page<TV> findAll(Date start, int page, int size) { page--; page = page < 0 ? 0 : page;//page 为页码,数据库从0页开始 //可以使用重载的 of(int page, int size, Sort sort) 方法指定排序字段 Pageable pageable = PageRequest.of(page, size); //创建查询规范 Specification<TV> tvSpecification = new Specification<TV>() { @Override public Predicate toPredicate(Root<TV> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> predicateList = new ArrayList<>(); //查询生产日期在 start 与当期时间之间的数据,闭区间 predicateList.add(cb.between(root.get("dateOfProduction").as(Date.class), start, new Date())); Predicate[] predicates = new Predicate[predicateList.size()]; return query.where(predicateList.toArray(predicates)).getRestriction(); } }; return tvRepository.findAll(tvSpecification, pageable);//无数据时返回空列表 } @Override public List<TV> findAllLike(String tvNameLike) { Specification<TV> tvSpecification = new Specification<TV>() { @Override public Predicate toPredicate(Root<TV> root, CriteriaQuery<?> query, CriteriaBuilder cb) { Predicate[] predicates = new Predicate[1]; //like(Expression<String> x, String pattern):参数 pattern 表示匹配的格式 predicates[0] = cb.like(root.get("tvName").as(String.class), "%" + tvNameLike + "%"); //同理以 xxx 开头,则为 tvNameLike + "%" return query.where(predicates).getRestriction(); } }; //规范查询的同时,指定以主键 tvId 倒序排序 return tvRepository.findAll(tvSpecification, Sort.by(Sort.Direction.DESC, "tvId")); } } **控制器层** 在线源码: [https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/controller/TvController.java][https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_controller_TvController.java] import com.wmx.entity.TV; import com.wmx.service.TVService; import org.springframework.data.domain.Page; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.ResponseBody; import javax.annotation.Resource; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.logging.Logger; /** * Created by Administrator on 2019/4/27. */ @Controller public class TVController { @Resource private TVService tvService; @GetMapping("findAll1") @ResponseBody public String findAll1(String name) throws ParseException { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date start = dateFormat.parse("2019-04-27 10:00:00"); Date end = dateFormat.parse("2019-04-27 23:00:00"); return tvService.findAll(start, end, name).toString(); } @GetMapping("findAll2") @ResponseBody public String findAll2(Integer page, Integer size) throws ParseException { page = page == null ? 1 : page; size = size == null ? 2 : size; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date start = dateFormat.parse("2019-04-28 08:00:00"); Page<TV> tvPage = tvService.findAll(start, page, size); Logger logger = Logger.getAnonymousLogger(); logger.info("总记录数:" + tvPage.getTotalElements()); logger.info("总页数:" + tvPage.getTotalPages()); List<TV> tvList = tvPage.getContent(); return tvList.toString(); } @GetMapping("findAll3") @ResponseBody public String findAll3(String like) throws ParseException { return tvService.findAllLike(like).toString(); } } github 地址:[https://github.com/wangmaoxiong/h2Smil][https_github.com_wangmaoxiong_h2Smil] [Spring Data JPA _ CRUD]: https://blog.csdn.net/wangmx1993328/article/details/89603744 [https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_service_TvService.java]: https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/service/TvService.java [https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_service_impl_TvServiceImpl.java]: https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/service/impl/TvServiceImpl.java [https_github.com_wangmaoxiong_h2Smil_blob_master_src_main_java_com_wmx_controller_TvController.java]: https://github.com/wangmaoxiong/h2Smil/blob/master/src/main/java/com/wmx/controller/TvController.java [https_github.com_wangmaoxiong_h2Smil]: https://github.com/wangmaoxiong/h2Smil
还没有评论,来说两句吧...