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.测试
修改请求参数,测试结果正确。以上就完成了使用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 */;