Spring Data JPA使用规则和审计的学习

一、引入依赖

完整的pom文件如下所示:

<?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.7.13</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>FunlyDemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>FunlyDemo</name>
    <description>FunlyDemo</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>
        <!--jpa-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

二、application.properties配置文件

#mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/funly?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123456

#jpa
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

三、创建用户实体类

package com.example.funlydemo.bean;

import lombok.Data;
import lombok.ToString;

import javax.persistence.*;

/**
 * @author qx
 * @date 2023/07/19
 * @desc 用户实体类
 */
@Entity
@Table(name = "t_user")
@Data
@ToString
public class User {

    /**
     * ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    /**
     * 姓名
     */
    private String name;
    /**
     * 年龄
     */
    private Integer age;
}

四、JPA规则认识

启动重新创建好数据表后，我们添加一些测试数据到user表。

关键字	举例	JPQL片段
And	findAllByNameAndAge	where user0_.name=? and user0_.age=?
Or	findAllByNameOrAge	where user0_.name=? or user0_.age=?
is,Equals	findAllByNameIs,findAllByNameEquals, findAllByName	where user0_.name=?
Between	findAllByAgeBetween	where user0_.age between ? and ?
LessThan	findAllByAgeLessThan	where user0_.age<?
LessThanEqual	findAllByAgeLessThanEqual	where user0_.age<=?
GreaterThan	findAllByAgeGreaterThan	where user0_.age>?
GreaterThanEqual	findAllByAgeGreaterThanEqual	where user0_.age>=?
IsNull	findAllByAgeIsNull	where user0_.age is null
IsNotNull	findAllByAgeIsNotNull	where user0_.age is not null
Like	findAllByNameLike	where user0_.name like ? escape ?
NotLike	findAllByNameNotLike	where user0_.name not like ? escape ?
StartingWith	findAllByNameStartingWith	where user0_.name like ?(后置%) escape ?
EndingWith	findAllByNameEndingWith	where user0_.name like ?(前置%) escape ?
Containing	findAllByNameContaining	where user0_.name like ?(双%) escape ?
OrderBy	findAllByNameOrderByAgeDesc	where user0_.name=? order by user0_.age desc
Not	findAllByNameNot	where user0_.name<>?
In	findAllByAgeIn	where user0_.age in (? , ?)
NotIn	findAllByAgeNotIn	where user0_.age not in (? , ?)

五、持久层方法编写

package com.example.funlydemo.repository;

import com.example.funlydemo.bean.User;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {

    List<User> findAllByNameAndAge(String name, Integer age);

    List<User> findAllByNameOrAge(String name, Integer age);

    List<User> findAllByNameIs(String name);

    List<User> findAllByNameEquals(String name);

    List<User> findAllByName(String name);


    List<User> findAllByAgeBetween(Integer age1, Integer age2);

    List<User> findAllByAgeLessThan(Integer age);

    List<User> findAllByAgeLessThanEqual(Integer age);

    List<User> findAllByAgeGreaterThan(Integer age);

    List<User> findAllByAgeGreaterThanEqual(Integer age);

    List<User> findAllByAgeIsNull();

    List<User> findAllByAgeIsNotNull();

    List<User> findAllByNameLike(String name);

    List<User> findAllByNameNotLike(String name);

    List<User> findAllByNameStartingWith(String name);

    List<User> findAllByNameEndingWith(String name);

    List<User> findAllByNameContaining(String name);

    List<User> findAllByNameOrderByAgeDesc(String name);

    List<User> findAllByNameNot(String name);

    List<User> findAllByAgeIn(List<Integer> ageList);

    List<User> findAllByAgeNotIn(List<Integer> ageList);
}

六、单元测试类

package com.example.funlydemo;

import com.example.funlydemo.bean.User;
import com.example.funlydemo.repository.UserRepository;
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class FunlyDemoApplicationTests {

    @Autowired
    private UserRepository userRepository;

    @Test
    void testAnd() {
        List<User> userList = userRepository.findAllByNameAndAge("aa", 11);
        System.out.println(userList);
    }

    @Test
    void testOr() {
        List<User> userList = userRepository.findAllByNameOrAge("aa", 11);
        System.out.println(userList);
    }

    @Test
    void testEquals() {
        List<User> userList = userRepository.findAllByNameIs("aa");
        System.out.println(userList);

        List<User> userList1 = userRepository.findAllByNameEquals("aa");
        System.out.println(userList1);

        List<User> userList2 = userRepository.findAllByName("aa");
        System.out.println(userList2);
    }

    @Test
    void testBetween() {
        List<User> userList = userRepository.findAllByAgeBetween(12, 13);
        System.out.println(userList);
    }

    @Test
    void testLessThan() {
        List<User> userList = userRepository.findAllByAgeLessThan(12);
        System.out.println(userList);
    }

    @Test
    void testLessThanEqual() {
        List<User> userList = userRepository.findAllByAgeLessThanEqual(12);
        System.out.println(userList);
    }

    @Test
    void testGreaterThan() {
        List<User> userList = userRepository.findAllByAgeGreaterThan(12);
        System.out.println(userList);
    }

    @Test
    void testGreaterThanEqual() {
        List<User> userList = userRepository.findAllByAgeGreaterThanEqual(12);
        System.out.println(userList);
    }

    @Test
    void testIsNull() {
        List<User> userList = userRepository.findAllByAgeIsNull();
        System.out.println(userList);
    }

    @Test
    void testIsNotNull() {
        List<User> userList = userRepository.findAllByAgeIsNotNull();
        System.out.println(userList);
    }

    @Test
    void testLike() {
        List<User> userList = userRepository.findAllByNameLike("b%");
        System.out.println(userList);
    }

    @Test
    void testNotLike() {
        List<User> userList = userRepository.findAllByNameNotLike("b%");
        System.out.println(userList);
    }

    @Test
    void testStartingWith() {
        List<User> userList = userRepository.findAllByNameStartingWith("b");
        System.out.println(userList);
    }

    @Test
    void testEndingWith() {
        List<User> userList = userRepository.findAllByNameEndingWith("x");
        System.out.println(userList);
    }

    @Test
    void testContaining() {
        List<User> userList = userRepository.findAllByNameContaining("a");
        System.out.println(userList);
    }

    @Test
    void testOrderBy() {
        List<User> userList = userRepository.findAllByNameOrderByAgeDesc("aa");
        System.out.println(userList);
    }

    @Test
    void testNot() {
        List<User> userList = userRepository.findAllByNameNot("aa");
        System.out.println(userList);
    }

    @Test
    void testIn() {
        List<User> userList = userRepository.findAllByAgeIn(Lists.newArrayList(11, 12));
        System.out.println(userList);
    }

    @Test
    void testNotIn() {
        List<User> userList = userRepository.findAllByAgeNotIn(Lists.newArrayList(11, 12));
        System.out.println(userList);
    }
}

七、自定义规则

我们可以使用JPQL和书写原生SQL的方式实现自定义的规则。JPQL最突出的特点就是以Java Bean为操作对象，遵循JPA规范屏蔽了数据库之间的差异，使同一套代码可以用在任意数据库上；而SQL方式是以表为操作对象的，因此可以使用某种数据库特有的功能，比如某个MySQL独有的功能，但是切换到Oracle时就不能使用了。

JPQL使用示例代码如下:

   @Query("select u from User u where u.name=?1")
    List<User> getDataByName(String name);

原生SQL使用示例代码如下:

 @Query(value = "select * from t_user where name=?1",nativeQuery = true)
    List<User> getUserFromName(String name);
}

设置nativeQuery属性为true,说明使用原生SQL。

八、审计

业务数据的插入时间、最后更新时间、创建人及最后更新人的记录对每个系统都很重要，但如果每次操作时都需要手动记录这些信息，就会非常枯燥且烦琐。作为一个成熟的持久层框架，Spring Data JPA应该学会自己审计了。

1.启动类开启审计

package com.example.funlydemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
// 开启审计
@EnableJpaAuditing
public class FunlyDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(FunlyDemoApplication.class, args);
    }

}

2.把审计需要的属性封装到一个公共类

package com.example.funlydemo.bean;

import lombok.Getter;
import lombok.Setter;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.Column;
import javax.persistence.EntityListeners;
import javax.persistence.MappedSuperclass;
import java.time.LocalDateTime;

/**
 * @author qx
 * @date 2023/07/19
 * @desc
 */
@Getter
@Setter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public class BaseEntity {

    @CreatedBy
    @Column(updatable = false)
    private String creater;

    @LastModifiedBy
    private String modifier;

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createTime;

    @LastModifiedDate
    private LocalDateTime updateTime;


}

@Column(updatable=false)将字段设置不可修改，只允许一次赋值。创建者和创建时间只需要插入一次，不需要更新。

3.用户类继承公共基类

package com.example.funlydemo.bean;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

import javax.persistence.*;

/**
 * @author qx
 * @date 2023/07/19
 * @desc 用户实体类
 */
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "t_user")
@Data
@ToString
public class User extends BaseEntity {

    /**
     * ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    /**
     * 姓名
     */
    private String name;
    /**
     * 年龄
     */
    private Integer age;
}

4.创建一个获取当前操作人的接口实现类

package com.example.funlydemo.uti;

import org.springframework.data.domain.AuditorAware;
import org.springframework.stereotype.Component;

import java.util.Optional;

/**
 * @author qx
 * @date 2023/07/19
 * @desc 获取当前操作人的接口
 */
@Component
public class AuditorImpl implements AuditorAware<String> {
    @Override
    public Optional<String> getCurrentAuditor() {
        return Optional.of("admin->" + (int) (Math.random() * 10));
    }
}

5.测试审计

我们在单元测试中，测试添加用户

  @Test
    void testAddUser() {
        User user = new User();
        user.setName("qq");
        user.setAge(20);
        userRepository.save(user);
    }

执行方法后，我们查看数据表，发现自动添加上了创建者和创建时间。

接下来我们测试修改用户的方法

  @Test
    void testUpdateUser(){
        Optional<User> optional = userRepository.findById(2L);
        User user = optional.get();
        user.setAge(22);
        userRepository.save(user);
    }

执行方法后，我们查看数据表，发现自动修改了更新时间和修改操作人。