SpringBoot JPA使用Specification多条件查询（精确查询、模糊查询、时间段查询）及分页

痛定思痛。 2022-12-28 00:42 1432阅读 0赞

### 1.实体类Mobile ###

package com.frank.jpaSpecification.entity;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import java.time.LocalDate;
    
    /**
     * @author 小石潭记
     * @date 2020/10/4 14:14
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_mobile")
    public class Mobile {
    
        @Id
        @GeneratedValue
        private Integer id;
    
        private String name;
    
        private String branch;
    
        private String address;
    
        private Double price;
    
        private LocalDate createTime;
    
    }

### 2.MobileRepository，注意需要基础JpaSpecificationExecutor ###

package com.frank.jpaSpecification.repository;
    
    import com.frank.jpaSpecification.entity.Mobile;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Component;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:21
     * @Description: ${todo}
     */
    @Component
    public interface MobileRepository extends PagingAndSortingRepository<Mobile, Integer>, JpaSpecificationExecutor<Mobile> {
    }

### 3.PageParamRequest请求参数体 ###

package com.frank.jpaSpecification.entity;
    
    import lombok.Data;
    import org.springframework.format.annotation.DateTimeFormat;
    
    import java.time.LocalDate;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:26
     * @Description: ${todo}
     */
    @Data
    public class PageParamRequest {
        private Integer pageSize = 10;
        private Integer pageNumber = 1;
        private String searchName;
        private String searchBranch;
        private String searchId;
        private List<Double> priceList;
    
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        private LocalDate startTime;
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        private LocalDate endTime;
    
    }

### 4.MobileService ###

package com.frank.jpaSpecification.service;
    
    import com.frank.jpaSpecification.entity.Mobile;
    import com.frank.jpaSpecification.entity.PageParamRequest;
    import com.frank.jpaSpecification.repository.MobileRepository;
    import com.frank.jpaSpecification.specification.MobileSpecification;
    import com.google.common.collect.Lists;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.data.domain.Sort;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:22
     * @Description: ${todo}
     */
    @Service
    public class MobileService {
    
        @Autowired
        private MobileRepository mobileRepository;
    
        @Autowired
        private MobileSpecification mobileSpecification;
    
        public Page<Mobile> getAllMobile(PageParamRequest pageParamRequest) {
            // 根据价格降序
            Sort sort = new Sort(Sort.Direction.DESC, "createTime");
            PageRequest pageRequest = PageRequest.of(pageParamRequest.getPageNumber(), pageParamRequest.getPageSize(), sort);
            return mobileRepository.findAll(mobileSpecification.getMobileSpecification(pageParamRequest), pageRequest);
        }
    
    }

### 5.MobileSpecification ###

package com.frank.jpaSpecification.specification;
    
    import com.frank.jpaSpecification.entity.Mobile;
    import com.frank.jpaSpecification.entity.PageParamRequest;
    import org.apache.commons.lang.StringUtils;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.stereotype.Component;
    import org.springframework.util.CollectionUtils;
    
    import javax.persistence.criteria.Predicate;
    import java.time.LocalDate;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:27
     * @Description: ${todo}
     */
    @Component
    public class MobileSpecification {
    
        /**
         * root 就是mobile实例  root.get("name") name是属性名 不是数据库字段名
         * @param paramRequest
         * @return
         * */
        public Specification<Mobile> getMobileSpecification(PageParamRequest paramRequest) {
            return (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> list = new ArrayList<>();
    
                String searchName = paramRequest.getSearchName();
    
                if (StringUtils.isNotBlank(searchName)) {
                    list.add(criteriaBuilder.like(root.get("name").as(String.class), "%" + searchName + "%"));
                }
    
                String searchId = paramRequest.getSearchId();
                if (StringUtils.isNotBlank(searchId)) {
                    list.add(criteriaBuilder.equal(root.get("id").as(Long.class), searchId));
                }
    
                // 模糊查询
                String searchBranch = paramRequest.getSearchBranch();
                if (StringUtils.isNotBlank(searchBranch)) {
                    list.add(criteriaBuilder.like(root.get("branch").as(String.class), "%" + searchBranch + "%"));
                }
    
                // 前端传递价格列表，查询在列表里面的数据 in
                List<Double> priceList = paramRequest.getPriceList();
                if (!CollectionUtils.isEmpty(priceList)) {
                    list.add(criteriaBuilder.and(root.get("price").as(Double.class).in(priceList)));
                }
    
                // 时间范围
                LocalDate startTime = paramRequest.getStartTime();
                LocalDate endTime = paramRequest.getEndTime();
                //起始日期
                if (startTime != null && !startTime.equals("")) {
                    list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime").as(LocalDate.class), startTime));
                }
                //结束日期
                if (endTime != null && !endTime.equals("")) {
                    list.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime").as(LocalDate.class), endTime));
                }
    
                Predicate[] p = new Predicate[list.size()];
                return criteriaBuilder.and(list.toArray(p));
            };
        }
    
    }

### 6.MobileController ###

package com.frank.jpaSpecification.controller;
    
    import com.frank.jpaSpecification.entity.Mobile;
    import com.frank.jpaSpecification.entity.PageParamRequest;
    import com.frank.jpaSpecification.service.MobileService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.web.bind.annotation.*;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:24
     * @Description: ${todo}
     */
    @RestController
    @RequestMapping("/mobile")
    public class MobileController {
    
        @Autowired
        private MobileService mobileService;
    
        /**
         * 注意这里是从0页开始查询的
         * @param request
         * @return
         */
        @GetMapping("/list")
        public Page<Mobile> getAllMobileList(PageParamRequest request) {
            return mobileService.getAllMobile(request);
        }
    
    }

### 7.测试  ###

**[http://localhost:8080/mobile/list?searchId=&searchBranch=华为&searchName&pageNumber=0&pageSize=5&priceList=6999,2999&startTime=2019-01-01&endTime=2020-01-01][http_localhost_8080_mobile_list_searchId_searchBranch_searchName_pageNumber_0_pageSize_5_priceList_6999_2999_startTime_2019-01-01_endTime_2020-01-01]**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1][]

修改请求参数，测试结果正确。以上就完成了使用jap单表的复杂查询和分页。

sql数据：

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        5.6.40 - MySQL Community Server (GPL)
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  8.2.0.4675
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    -- 导出 test 的数据库结构
    CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
    USE `test`;
    
    
    -- 导出  表 test.t_mobile 结构
    CREATE TABLE IF NOT EXISTS `t_mobile` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `branch` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `price` double DEFAULT NULL,
      `create_time` date DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    -- 正在导出表  test.t_mobile 的数据：~11 rows (大约)
    DELETE FROM `t_mobile`;
    /*!40000 ALTER TABLE `t_mobile` DISABLE KEYS */;
    INSERT INTO `t_mobile` (`id`, `address`, `branch`, `name`, `price`, `create_time`) VALUES
    	(1, '成都', '华为', '华为mete40', 6999, '2010-10-12'),
    	(2, '北京', '小米', '小米10青春版', 2999, '2017-05-04'),
    	(3, '成都', '华为', '华为mete30', 6399, '2018-06-06'),
    	(4, '广东', '苹果', '苹果12max', 8999, '2020-12-12'),
    	(5, '成都', '华为', '华为mete40', 6999, '2020-10-05'),
    	(6, '重庆', '红米', '红米K30', 1999, '2020-04-08'),
    	(7, '成都', '华为', '华为mete40', 6999, '2019-08-08'),
    	(8, '深圳', '魅族', '魅族9 pro', 3299, '2019-07-08'),
    	(9, '成都', '华为', '华为mete40', 6999, '2016-05-05'),
    	(10, '上海', 'OPPO', 'OPPO A92s', 3699, '2014-03-04'),
    	(11, '成都', '华为', '华为P40', 4999, '2015-09-09');
    /*!40000 ALTER TABLE `t_mobile` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

[http_localhost_8080_mobile_list_searchId_searchBranch_searchName_pageNumber_0_pageSize_5_priceList_6999_2999_startTime_2019-01-01_endTime_2020-01-01]: http://localhost:8080/mobile/list?searchId=&searchBranch=%E5%8D%8E%E4%B8%BA&searchName&pageNumber=0&pageSize=5&priceList=6999,2999&startTime=2019-01-01&endTime=2020-01-01
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70]: /images/20221120/139a9f4d07dd43b6b45f7551cafd48ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1]: /images/20221120/21f9f42f4c854a6fb5e703da67da0525.png