SpringDataJPA完成CRUD

利用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)方法的返回值类型中获取到总条数、总页数