SpringBoot系列之JPA实现按年月日查询
通过例子的方式介绍Springboot集成Spring Data JPA的方法，进行实验，要先创建一个Initializer工程，如图：
选择，需要的jdk版本，maven项目
选择需要的maven配置，这里需要选择spring data jpa，数据库驱动mysql driver
新建项目后，会自动加上如下配置，如果你的mysql服务器是5.7版本的，建议指定mysql-connector-java版本，Druid也需要自己加上，pom配置参考：
<properties>
<java.version>1.8</java.version>
<druid.version>1.1.2</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
新建数据库和数据表
CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `springboot`;
/*Table structure for table `sys_user` */
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`userId` int(10) NOT NULL,
`username` varchar(20) NOT NULL,
`sex` char(10) DEFAULT NULL,
`password` varchar(10) DEFAULT NULL,
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`userId`),
UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `sys_user` */
insert into `sys_user`(`userId`,`username`,`sex`,`password`, `create_time`, `modify_time`) values (1,'admin','man','11', now(), now());
注意，如果mysql数据库版本是5.x版本的，驱动就是com.mysql.jdbc.Driver
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/springboot?characterEncoding=utf8&useSSL=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
initialization-mode: always
type: com.alibaba.druid.pool.DruidDataSource
# 连接池设置
initial-size: 5
min-idle: 5
max-active: 20
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
time-between-eviction-runs-millis: 90000
# 配置一个连接在池中最小生存的时间，单位是毫秒
min-evictable-idle-time-millis: 300000
# Oracle请使用select 1 from dual
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache，并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
use-global-data-source-stat: true
jpa:
hibernate:
# 更新或者创建数据表结构
ddl-auto: update
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# 控制台显示SQL
show-sql: true
新建一个实体类
package com.example.springboot.jpa.entity;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.Data;
import javax.persistence.*;
import java.util.Date;
@Entity
@Table(name = "sys_user")
@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
@Data
public class User{
@Id //主键
@GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键
private Integer userId;
@Column(name = "username",length = 20) //这是和数据表对应的一个列
private String username;
@Column(name = "sex")
private String sex;
@Column(name = "password")
private String password;
@Column(name = "create_time")
private Date createTime;
}
新增mapper接口，继承JpaRepository和JpaSpecificationExecutor ，继承JpaSpecificationExecutor 是为了支持Specification的API
package com.example.springboot.jpa.repository;
import com.example.springboot.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
public interface UserRepository extends JpaRepository<User,Integer>, JpaSpecificationExecutor {
}
业务实现类，这里实现按年月日查询思路是借助Specification来实现，获取传入的时间戳，只获取对应的年月日，startTime再拼上00:00:00，endTime拼上23:59:59，再将createTime转为字符串类型来比较
package com.example.springboot.jpa.service.impl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.example.springboot.jpa.dto.UserSearchDto;
import com.example.springboot.jpa.entity.User;
import com.example.springboot.jpa.repository.UserRepository;
import com.example.springboot.jpa.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.criteria.*;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Optional;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepository;
@Override
public List<User> listUser(UserSearchDto searchDto) {
Specification<User> specification = new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Predicate predicate = criteriaBuilder.conjunction();
Path<Object> createTime = root.get("createTime");
Path<Object> username = root.get("username");
if (ObjectUtil.isNotNull(searchDto.getStartTime())) {
LocalDateTime localDateTime = DateUtil.toLocalDateTime(searchDto.getStartTime());
LocalDateTime createDateStart = LocalDateTime.of(localDateTime.getYear(), localDateTime.getMonthValue(), localDateTime.getDayOfMonth(), 00, 00, 00);
Predicate pSTime = criteriaBuilder.greaterThanOrEqualTo(createTime.as(String.class), DateUtil.format(new DateTime(createDateStart), DatePattern.NORM_DATETIME_PATTERN));
predicate.getExpressions().add(pSTime);
}
if (ObjectUtil.isNotNull(searchDto.getEndTime())) {
LocalDateTime localDateTime = DateUtil.toLocalDateTime(searchDto.getEndTime());
LocalDateTime createDateEnd = LocalDateTime.of(localDateTime.getYear(), localDateTime.getMonthValue(), localDateTime.getDayOfMonth(), 23, 59, 59);
Predicate pETime = criteriaBuilder.lessThanOrEqualTo(createTime.as(String.class), DateUtil.format(new DateTime(createDateEnd), DatePattern.NORM_DATETIME_PATTERN));
predicate.getExpressions().add(pETime);
}
if (StrUtil.isNotBlank(searchDto.getUserName())) {
Predicate pUserName = criteriaBuilder.like(username.as(String.class), searchDto.getUserName());
predicate.getExpressions().add(pUserName);
}
return predicate;
}
};
return Optional.ofNullable(userRepository.findAll(specification)).orElse(Collections.EMPTY_LIST);
}
}
查询，用postman测试一下接口
