在线网站做情侣头像,游戏公司有哪些,外贸获客软件排名前十名,昆明做网站建设多少钱SpringBoot系列之JPA实现按年月日查询
通过例子的方式介绍Springboot集成Spring Data JPA的方法#xff0c;进行实验#xff0c;要先创建一个Initializer工程#xff0c;如图#xff1a; 选择#xff0c;需要的jdk版本#xff0c;maven项目 选择需要的maven配置#x…SpringBoot系列之JPA实现按年月日查询
通过例子的方式介绍Springboot集成Spring Data JPA的方法进行实验要先创建一个Initializer工程如图 选择需要的jdk版本maven项目 选择需要的maven配置这里需要选择spring data jpa数据库驱动mysql driver
新建项目后会自动加上如下配置如果你的mysql服务器是5.7版本的建议指定mysql-connector-java版本Druid也需要自己加上pom配置参考
propertiesjava.version1.8/java.versiondruid.version1.1.2/druid.version/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.21/versionscoperuntime/scope/dependency!-- https://mvnrepository.com/artifact/com.alibaba/druid --dependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion${druid.version}/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scopeexclusionsexclusiongroupIdorg.junit.vintage/groupIdartifactIdjunit-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)
) ENGINEInnoDB DEFAULT CHARSETutf8;/*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?characterEncodingutf8useSSLtrueusername: rootpassword: rootdriver-class-name: com.mysql.cj.jdbc.Driverinitialization-mode: alwaystype: com.alibaba.druid.pool.DruidDataSource# 连接池设置initial-size: 5min-idle: 5max-active: 20# 配置获取连接等待超时的时间max-wait: 60000# 配置间隔多久才进行一次检测检测需要关闭的空闲连接单位是毫秒time-between-eviction-runs-millis: 90000# 配置一个连接在池中最小生存的时间单位是毫秒min-evictable-idle-time-millis: 300000# Oracle请使用select 1 from dualvalidation-query: SELECT 1test-while-idle: truetest-on-borrow: falsetest-on-return: false# 打开PSCache并且指定每个连接上PSCache的大小pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20# 配置监控统计拦截的filters去掉后监控界面sql无法统计wall用于防火墙filters: stat,wall,slf4j# 通过connectProperties属性来打开mergeSql功能慢SQL记录connection-properties: druid.stat.mergeSqltrue;druid.stat.slowSqlMillis5000# 合并多个DruidDataSource的监控数据use-global-data-source-stat: truejpa:hibernate:# 更新或者创建数据表结构ddl-auto: updatenaming:physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl# 控制台显示SQLshow-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 JpaRepositoryUser,Integer, JpaSpecificationExecutor {}
业务实现类这里实现按年月日查询思路是借助Specification来实现获取传入的时间戳只获取对应的年月日startTime再拼上00:00:00endTime拼上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 {Autowiredprivate UserRepository userRepository;Overridepublic ListUser listUser(UserSearchDto searchDto) {SpecificationUser specification new SpecificationUser() {Overridepublic Predicate toPredicate(RootUser root, CriteriaQuery? criteriaQuery, CriteriaBuilder criteriaBuilder) {Predicate predicate criteriaBuilder.conjunction();PathObject createTime root.get(createTime);PathObject 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测试一下接口
