利用SpringDataJPA实现简单的CRUD
1、添加依赖:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>lessonthree</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>lessonthree</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.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>
<scope>runtime</scope>
</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>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2、配置数据源以及JPA参数:
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf8
username: root
password: 19970902
driver-class-name: com.mysql.cj.jdbc.Driver
jpa:
database: mysql
show-sql: true
3、根据表结构创建对应的实体映射,根据数据库中的字段创建一个UserEntity来作为对应操作:
package com.example.lessonthree.entity;
import javax.persistence.*;
import java.io.Serializable;
@Entity
@Table(name="t_user")
public class UserEntity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)//主键由数据库自动生成(主要是自动增长型)
@Column(name = "t_id")
private Long id;
@Column(name = "t_name")
private String name;
@Column(name = "t_age")
private int age;
@Column(name = "t_address")
private String address;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
4、创建JPA:创建数据访问的接口UserJPA接口,并且继承SpringDataJPA内的接口作为父类
package com.example.lessonthree.jpa;
import com.example.lessonthree.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import java.io.Serializable;
public interface UserJPA extends JpaRepository<UserEntity,Long>,
JpaSpecificationExecutor<UserEntity>,
Serializable {
}
(1)JpaRepository接口是简单数据操作接口)需要泛型接口参数,第一个参数是实体,第二个参数是主键的类型;
(2)JpaSpecificationExecutor是复杂查询接口
(3)Serializable是序列化接口
5、添加Controller层:
SpringDataJPA内部使用了类代理的方式让继承了它接口的子接口,都以spring管理的Bean的形式存在,也就是说我们可以直接使用@Autowired注解在spring管理bean使用:
package com.example.lessonthree.controller;
import com.example.lessonthree.entity.UserEntity;
import com.example.lessonthree.jpa.UserJPA;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Autowired
private UserJPA userJPA;
@RequestMapping(value = "/list",method = RequestMethod.GET)
public List<UserEntity> list(){
return userJPA.findAll();
}
@RequestMapping(value = "/save",method = RequestMethod.GET)
public UserEntity save(UserEntity entity){
return userJPA.save(entity);
}
@RequestMapping(value = "/delete",method = RequestMethod.GET)
public List<UserEntity> delete(Long id){
userJPA.deleteById(id);
return userJPA.findAll();
}
}
PS:
通过@Query注解自定义SQL语句,参数nativeQuery = true才是表明了使用原生的sql,如果不配置,默认是false,则使用HQL查询方式。@Query配合@Modifying可以完成数据的删除、添加、更新操作,SpringDataJPA自定义SQL时需要在对应的接口或者调用接口的地方添加事务注解@Transactional,来开启事务自动化管理
@Transactional
public interface UserJPA extends JpaRepository<UserEntity, Long>{
@Query(value = "select * from t_user where t_age > ?1",nativeQuery = true)
public List<UserEntity> nativeQuery(int age);//查询年龄大于age的信息
@Modifying
@Query(value = "delete from t_user where t_name = ?1 and t_address = ?2",nativeQuery = true)
public void deleteQuery(String name,String address);//完成数据的删除
}
Controller层添加:
@RequestMapping("/age")
public List<UserEntity> age(){
return userJPA.nativeQuery(20);//查询年龄大于20的记录
}
@RequestMapping(value = "/deleteWhere")
public String deleteWhere(){
userJPA.deleteQuery("ada","ad");//删除
return "delete success";
}
分页查询
创建BaseEntity,添加几个字段:排序列,排序方式,当前页码,每页条数等:
package com.druid.single.base;
import java.io.Serializable;
import java.util.List;
public class BaseEntity implements Serializable{
protected int page = 1;//分页页码,默认页码为1
protected int size = 20;// 分页每页数量,默认20条
protected String sidx = "id";//排序列名称,默认为id
protected String sord = "asc";//排序正序
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getSize() {
return size;
}
public void setSize(int size) {
this.size = size;
}
public String getSidx() {
return sidx;
}
public void setSidx(String sidx) {
this.sidx = sidx;
}
public String getSord() {
return sord;
}
public void setSord(String sord) {
this.sord = sord;
}
}
修改UserEntity继承BaseEntity
UserController添加cutPage方法,用于作为分页查询入口:
public List<UserEntity> cutpage(int page){
UserEntity user = new UserEntity();
user.setSize(4);
user.setSord("desc");
user.setPage(page);
//获取排序对象
Sort.Direction sort_direction = Sort.Direction.ASC.toString()
.equalsIgnoreCase(user.getSord()) ? Sort.Direction.ASC : Sort.Direction.DESC;
//设置排序对象参数
//创建分页对象
PageRequest pageRequest = PageRequest.of(user.getPage() - 1,
user.getSize(),Sort.by(sort_direction,user.getSidx()));
//执行分页查询
return userJPA.findAll(pageRequest).getContent();
}
通过Page对象也就是PagingAndSortingRepository接口内的findAll(PageRequest request)方法的返回值类型中获取到总条数、总页数