SpringBoot系列(七) jpa的使用,以增删改查为例
JPA是Java Persistence API的简称,Java持久层API,是JDK 5.0注解或XML描述对象-关系表的映射关系,并将运行期的实体对象持久化到数据库中。
它是SUN公司推出的一套基于ORM的规范。ORM又是什么?全英文名为Object-Relational Mapping:对象关系映射,简单来说为了不用JDBC那一套原始方法来操作数据库,ORM框架横空出世。
简单介绍下,Spring中的Spring-data-jpa技术,Spring Data JPA 是 Spring Data 系列的一部分,可以轻松实现基于 JPA 的存储库。该模块在处理对基于 JPA 的数据访问层的有所增强。使用数据访问技术快速构建应用变得更加容易。
特征:
- 支持基于spring和JPA构建存储数据业务
- 支持基于实体映射的XML
- 提供使用原生sql能力的@Query注解
- 分页支持、动态查询
本文的目录结构:
表示层:controller
业务层:Entity、Service、Vo
数据访问层:Repository
下面,我们通过一个完整地项目来展现JPA地能力:
1、创建数据库和表:
我们使用Navicat for MySQL工具,执行建表语句,创建一个student表
student建表语句
CREATE TABLE `student` (
`student_id` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL DEFAULT 'cavan',
`classroom` varchar(50) NOT NULL DEFAULT '0',
`sex` varchar(10) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL,
`phone` varchar(20) NOT NULL DEFAULT '',
`created_by` varchar(20) NOT NULL DEFAULT 'cavan',
`last_modified_by` varchar(20) NOT NULL DEFAULT 'cavan',
`last_modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`student_id`),
UNIQUE KEY (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查看创建后的表:
2、引入依赖包
pom.xml的配置信息
pom.xml
<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>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.1.14</version>
</dependency>
3、配置application.properties连接数据库信息
application.properties
# 应用名称
spring.application.name=jpa
# 数据库驱动:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.url=jdbc:mysql://121.43.225.17:3306/test?serverTimezone=UTC
# 数据库用户名&密码:
spring.datasource.username=root
spring.datasource.password=
# 应用服务 WEB 访问端口
server.port=8888
4、业务层
4.1 定义entity实体类(StudentEntity类)
StudentEntity类
package com.cavan.jpa.entity;
import org.hibernate.annotations.Generated;
import org.hibernate.annotations.GenerationTime;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.sql.Timestamp;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-03 20:44
*/
@Entity
@Table(name = "student")
@GenericGenerator(name = "uuid", strategy = "uuid")
public class StudentEntity {
private String id;
private String name;
private String classroom;
private String sex;
private String email;
private String phone;
private String createdBy;
private String lastModifiedBy;
private Timestamp lastModifiedDate;
private Timestamp createdDate;
@Id
@GeneratedValue(generator = "uuid")
@Column(name = "student_id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClassroom() {
return classroom;
}
public void setClassroom(String classroom) {
this.classroom = classroom;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public String getLastModifiedBy() {
return lastModifiedBy;
}
public void setLastModifiedBy(String lastModifiedBy) {
this.lastModifiedBy = lastModifiedBy;
}
@Column(name = "last_modified_date",
columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP",
insertable = false,
updatable = false)
@Generated(GenerationTime.ALWAYS)
public Timestamp getLastModifiedDate() {
return lastModifiedDate;
}
public void setLastModifiedDate(Timestamp lastModifiedDate) {
this.lastModifiedDate = lastModifiedDate;
}
@Column(name = "created_date",
columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP",
insertable = false,
updatable = false)
@Generated(GenerationTime.ALWAYS)
public Timestamp getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Timestamp createdDate) {
this.createdDate = createdDate;
}
}
一些常用注解(本文用到不多):
注解 | 作用 | 常用属性 |
---|---|---|
@Entity | 指定当前类是实体类 | |
@Table | 指定实体类和表之间的对应关系 | name:指定数据库表的名称 |
@EntityListeners | 在实体类增删改的时候监听,为创建人/创建时间等基础字段赋值 | value:指定监听类 |
@Id | 指定当前字段是主键 | |
@SequenceGenerator | 指定数据库序列别名 | sequenceName:数据库序列名,name:取的别名 |
@GeneratedValue | 指定主键的生成方式 | strategy :指定主键生成策略 generator:选择主键别名 |
@Column | 指定实体类属性和数据库表之间的对应关系 | name:指定数据库表的列名称, unique:是否唯一, nullable:是否可以为空,nserttable:是否可以插入,updateable:是否可以更新,columnDefinition: 定义建表时创建此列的DDL |
@CreatedBy | 自动插入创建人 | |
@CreatedDate | 自动插入创建时间 | |
@LastModifiedBy | 自动修改更新人 | |
@LastModifiedDate | 自动修改更新时间 | |
@JsonFormat | 插入/修改/读取的时间转换成想要的格式 | pattern:展示格式,timezone:国际时间 |
注意:
有了@EntityListeners(AuditingEntityListener.class)这个注解,@CreatedBy、@CreatedDate 、@LastModifiedBy 、@LastModifiedDate才生效哦,而且创建人和更新人需要另作注入操作。
4.2 Vo请求/响应类
由于我们在进行增删改查操作时所需要的请求入参信息是不一样的,接口响应需要返回的信息也不太一样,所以我们定义了不同的请求类,用以接收请求消息,传递给业务使用。这里我们分别定义了StudentVo类(创建、更新使用)、StudentIdVo类(删除使用)、StudentQueryVo类(查询使用)。
实际上,当你严格校验你的请求参数时,创建和更新请求类应该创建两个类加以区分,这个在请求入参校验实践会详细讲解。
创建更新请求Vo类:
StudentVo类
package com.cavan.jpa.vo;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-03 22:02
*/
public class StudentVo {
private String id;
private String name;
private String classroom;
private String sex;
private String email;
private String phone;
private String createdBy;
private String lastModifiedBy;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClassroom() {
return classroom;
}
public void setClassroom(String classroom) {
this.classroom = classroom;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public String getLastModifiedBy() {
return lastModifiedBy;
}
public void setLastModifiedBy(String lastModifiedBy) {
this.lastModifiedBy = lastModifiedBy;
}
}
删除请求类:
StudentIdVo类
package com.cavan.jpa.vo;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-04 11:55
*/
public class StudentIdVo {
private String id;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
}
查询请求类:
StudentQueryVo类
package com.cavan.jpa.vo;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-05 11:25
*/
public class StudentQueryVo {
private Integer pagesize;
private Integer pageNum;
private String id;
private String name;
private String classroom;
private String sex;
public Integer getPagesize() {
return pagesize;
}
public void setPagesize(Integer pagesize) {
this.pagesize = pagesize;
}
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
this.pageNum = pageNum;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClassroom() {
return classroom;
}
public void setClassroom(String classroom) {
this.classroom = classroom;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
4.3 service服务类(分为 StudentService 服务接口类和 StudentServiceImpl 服务实现类)
业务逻辑层的核心就是service服务及其实现类,一些重要的逻辑都会写到impl中,而不是直接写在controller中。
我们对学生实现最简单的新增、删除、修改、查询功能。
代码如下:
服务接口类:
StudentService类
package com.cavan.jpa.service;
import com.cavan.jpa.entity.StudentEntity;
import com.cavan.jpa.vo.StudentIdVo;
import com.cavan.jpa.vo.StudentQueryVo;
import com.cavan.jpa.vo.StudentVo;
import java.util.List;
public interface StudentService {
/**
* create student
*
* @return student
*/
String create(StudentVo studentVo);
/**
* delete student
*
* @return student
*/
String delete(StudentIdVo studentIdVo);
/**
* update student
*
* @return student
*/
String update(StudentVo studentVo);
/**
* query student
*
* @return student
*/
List<StudentEntity> query(StudentQueryVo studentQueryVo);
}
服务实现类:
StudentServiceImpl类
package com.cavan.jpa.service.impl;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import com.cavan.jpa.entity.StudentEntity;
import com.cavan.jpa.repository.StudentRepository;
import com.cavan.jpa.service.StudentService;
import com.cavan.jpa.vo.StudentIdVo;
import com.cavan.jpa.vo.StudentQueryVo;
import com.cavan.jpa.vo.StudentVo;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
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.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-03 21:37
*/
@Service
@Transactional(rollbackFor = Exception.class)
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentRepository studentRepository;
@Override
public String create(StudentVo studentVo) {
StudentEntity studentEntity = new StudentEntity();
BeanUtils.copyProperties(studentVo, studentEntity);
studentRepository.save(studentEntity);
return "create ok";
}
@Override
public String delete(StudentIdVo studentIdVo) {
studentRepository.deleteById(studentIdVo.getId());
return "delete ok";
}
@Override
public String update(StudentVo studentVo) {
StudentEntity studentEntity = studentRepository.findById(studentVo.getId()).get();
BeanUtil.copyProperties(studentVo, studentEntity, true,
CopyOptions.create().setIgnoreNullValue(true).setIgnoreError(true));
studentRepository.save(studentEntity);
return "update ok";
}
@Override
public List<StudentEntity> query(StudentQueryVo studentQueryVo) {
Integer pageNum = studentQueryVo.getPageNum();
List<Sort.Order> orders = new ArrayList<>();
orders.add(new Sort.Order(Sort.Direction.DESC, "last_modified_date"));
Sort sort = Sort.by(orders);
Pageable pageable = PageRequest.of(pageNum > 0 ? pageNum - 1 : pageNum, studentQueryVo.getPagesize(), sort);
Page<StudentEntity> page = studentRepository.queryStudent(studentQueryVo.getId(),
studentQueryVo.getName(),
studentQueryVo.getSex(),
studentQueryVo.getClassroom(), pageable);
if (page.getTotalPages() < pageNum && page.getTotalPages() > 0) {
page = studentRepository.queryStudent(studentQueryVo.getId(),
studentQueryVo.getName(),
studentQueryVo.getSex(),
studentQueryVo.getClassroom(), pageable);
}
return page.getContent();
}
}
5、表示层
我们这里全部使用的是POST请求,分别定义了以下功能接口:
- 创建接口,用来创建一条学生信息数据
- 删除接口,根据学生的学号删除学生信息
- 更新接口,根据学生的学号更新学生信息
- 查询接口,根据学生姓名、性别、班级等进行查询操作
StudentManageController类
package com.cavan.jpa.controller;
import com.cavan.jpa.entity.StudentEntity;
import com.cavan.jpa.service.StudentService;
import com.cavan.jpa.vo.StudentIdVo;
import com.cavan.jpa.vo.StudentQueryVo;
import com.cavan.jpa.vo.StudentVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @program: jpa
* @description: <description>
* @author: cavan
* @create: 2021-12-03 20:22
*/
@RestController
@RequestMapping("/student")
public class StudentManageController {
@Autowired
private StudentService service;
/**
* 创建学生
*
* @param studentVo 学生信息
* @return create OK
*/
@PostMapping("/create")
public String create(@RequestBody StudentVo studentVo) {
return service.create(studentVo);
}
/**
* 根据学生的学号删除学生信息
*
* @param studentIdVo 学号
* @return delete OK
*/
@PostMapping("/delete")
public String delete(@RequestBody StudentIdVo studentIdVo) {
return service.delete(studentIdVo);
}
/**
* 根据学生的学号更新学生信息
*
* @param studentVo 学号
* @return update OK
*/
@PostMapping("/update")
public String update(@RequestBody StudentVo studentVo) {
return service.update(studentVo);
}
/**
* 根据学生姓名、性别、班级等查询
*
* @return query OK
*/
@PostMapping("/query")
public List<StudentEntity> query(@RequestBody StudentQueryVo studentQueryVo) {
return service.query(studentQueryVo);
}
}
6、数据连接层
这里只定义了原生sql的查询方法,其他基本的增删改查操作使用JPA内部提供的即可,只用继承 JpaRepository,不需要另外声明。
StudentRepository接口
package com.cavan.jpa.repository;
import com.cavan.jpa.entity.StudentEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
@Repository
public interface StudentRepository extends JpaRepository<StudentEntity, String> {
/**
* query
*/
@Query(value = "select * from student where IF(ifnull(?1,'')!='',student_id=?1,1=1) " +
"and IF(ifnull(?2,'')!='',name like CONCAT('%',?2,'%'),1=1) " +
"and IF(ifnull(?3,'')!='',sex like CONCAT('%',?3,'%'),1=1) " +
"and IF(ifnull(?4,'')!='',classroom like CONCAT('%',?4,'%'),1=1)",
countQuery = "select count(1) from student where IF(ifnull(?1,'')!='',student_id=?1,1=1) " +
"and IF(ifnull(?2,'')!='',name like CONCAT('%',?2,'%'),1=1) " +
"and IF(ifnull(?3,'')!='',sex like CONCAT('%',?3,'%'),1=1) " +
"and IF(ifnull(?4,'')!='',classroom like CONCAT('%',?4,'%'),1=1)", nativeQuery = true)
Page<StudentEntity> queryStudent(String id, String name, String sex, String classroom, Pageable pageable);
}
问题记录:
(1)第一个问题
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2021-12-03 22:42:34.389 ERROR 15628 --- [ main] o.s.b.d.LoggingFailureAnalysisReporter :
APPLICATION FAILED TO START
Description:
Field service in com.cavan.jpa.controller.StudentManageController required a bean of type 'com.cavan.jpa.service.StudentService' that could not be found.
The injection point has the following annotations:
- @org.springframework.beans.factory.annotation.Autowired(required=true)
Action:
Consider defining a bean of type 'com.cavan.jpa.service.StudentService' in your configuration.
问题分析:
此问题是由于我在使用注解时错误的将
@Service
@Transactional(rollbackFor = Exception.class)
这两个注解放在了StudentService接口类上,实际应该定义在实现类上。
(2)第二个问题
2021-12-03 23:10:39.864 ERROR 15240 --- [nio-8888-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : Unknown column 'created_by' in 'field list'
2021-12-03 23:10:39.917 ERROR 15240 --- [nio-8888-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause
java.sql.SQLSyntaxErrorException: Unknown column 'created_by' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.22.jar:8.0.22]
问题分析:
在使用建表语句时,我们创建的Entity实体类中的createdBy字段,映射到数据时会自动识别小驼峰,将其对应成created_by字段,所以建表时,我们要使用下划线方式,创建字段。
错误建表字段:
(3)第三个问题
2021-12-03 23:21:02.685 ERROR 14972 --- [nio-8888-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Column 'classroom' cannot be null
2021-12-03 23:21:02.744 ERROR 14972 --- [nio-8888-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause
java.sql.SQLIntegrityConstraintViolationException: Column 'classroom' cannot be null
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.22.jar:8.0.22]
问题分析:
未在请求参数前加 @RequestBody 注解导致请求传过去的参数都为null值
(4)第四个问题
java.sql.SQLSyntaxErrorException: FUNCTION test.count does not exist. Check the 'Function Name Parsi
问题分析:
在使用原生sql的时候,多打了一个空格,导致报错。在使用原生sql这里最容易出错,可以通过在Navicat本地来测试语句的正确性。
本文代码Gitee链接:
https://gitee.com/cavan2021/springboot/tree/master/jpa