Spring Data JPA Specification（规范）实现复杂查询

╰半橙微兮° 2022-02-13 14:54 375阅读 0赞

**目录**

持久化 API 接口概述

JpaSpecificationExecutor 常用 API

JPA Specification 编码示例

--------------------

# 持久化 API 接口概述 #

1、JPA 持久化 API 接口主要如下：

<table> 
 <tbody> 
  <tr> 
   <td>public interface PagingAndSortingRepository&lt;T, ID&gt; extends CrudRepository&lt;T, ID&gt;&nbsp;<br> public interface <span style="color:#f33b45;">JpaRepository</span>&lt;T, ID&gt; extends PagingAndSortingRepository&lt;T, ID&gt;, QueryByExampleExecutor&lt;T&gt;&nbsp;<br> public interface JpaRepositoryImplementation&lt;T, ID&gt; extends JpaRepository&lt;T, ID&gt;, <span style="color:#f33b45;">JpaSpecificationExecutor</span>&lt;T&gt;&nbsp;<br> public class SimpleJpaRepository&lt;T, ID&gt; implements JpaRepositoryImplementation&lt;T, ID&gt;</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&lt;T&gt; findAll(@Nullable Specification&lt;T&gt; spec)</td> 
   <td>规范查询。没有数据时返回空列表。</td> 
  </tr> 
  <tr> 
   <td>Page&lt;T&gt; findAll(@Nullable Specification&lt;T&gt; spec, Pageable pageable)</td> 
   <td>规范查询。同时进行分页查询。</td> 
  </tr> 
  <tr> 
   <td>List&lt;T&gt; findAll(@Nullable Specification&lt;T&gt; spec, Sort sort)</td> 
   <td>规范查询。同时指定排序字段。</td> 
  </tr> 
  <tr> 
   <td>Optional&lt;T&gt; findOne(@Nullable Specification&lt;T&gt; 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