简介

JPA是Java Persistence API的简称，中文名Java持久层API，是JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体[对象持久化](https://baike.baidu.com/item/对象持久化/7316192)到数据库中。 （[来源：百度百科](https://baike.baidu.com/item/JPA/5660672)）。总结一点就是对于简单的CRUD，只需要操作对应的实体类，无需编写SQL即可实现对应的功能，同时也支持自定义SQL，详情可查看[JPA官方操作手册](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#)。

版本介绍

	版本	备注
SpringBoot	2.6.4
MySQL	5.6.51
p6spy	3.9.1	用于格式化sql文件，可以将占位符直接替换成参数值，将执行sql直接打印出来

准备工作

测试表相关SQL

用户表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键id',
  `account` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '账户名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `gender` int(1) NULL DEFAULT NULL COMMENT '性别',
  `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
  `create_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' COMMENT '更新时间',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Compact;

用户机构表

DROP TABLE IF EXISTS `user_org`;
CREATE TABLE `user_org`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键id',
  `user_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
  `org_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构代码',
  `org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构名称',
  `create_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' COMMENT '更新时间',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表机构表' ROW_FORMAT = Compact;

引入依赖

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.4</version>
        </dependency>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/p6spy/p6spy -->
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.1</version>
        </dependency>

编写配置文件

application.properties

spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/demo-jpa
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=root
spring.datasource.password=root

spy.properties

logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=sql -> %(sqlSingleLine)
appender=com.p6spy.engine.spy.appender.StdoutLogger

通用审计日志基础信息配置

由于数据创建人、创建时间以及数据修改人、修改时间为公共字段，实现AuditorAware接口配合@CreateBy、@CreateDate、@LastModifiedBy、@LastModifiedDate即可字段插入对应的字段信息。

@Component
@EnableJpaAuditing
public class JpaAuditConfig implements AuditorAware<String> {
    /**
     * 可 用于 获取 当前操作人信息，根据实际项目做调整
     * @return
     */
    @Override
    public Optional<String> getCurrentAuditor() {
        // 当前测试为固定在，项目中根据实际情况调整即可
        String operatorId = "operateId";
        return Optional.of(operatorId);
    }

自定义主键生成策略

由于多表新增时涉及到关联主键的问题，故表的主键需要先生成备用，新增数据时的主键生成逻辑需要调整：若新增的实体类已存在主键，则不重新生成。

jpa主键生成策略可参考：https://www.cnblogs.com/SummerinShire/p/7544897.html

public class CustomUUIDConfig extends UUIDGenerator {
    /**
     *
     * @param session
     * @param entityObj 当前实体类对象
     * @return
     * @throws HibernateException
     */
    @Override
    public Serializable generate(SharedSessionContractImplementor session, Object entityObj) throws HibernateException {
        String entityIdField = this.getEntityIdField(entityObj);
        if (StrUtil.isBlank(entityIdField)) {
            throw new RuntimeException(" Entity must contains id annotation");
        }
        Object existId = ReflectUtil.getFieldValue(entityObj, entityIdField);
        if (ObjectUtil.isNotNull(existId)) {
            return (Serializable) existId;
        }
        return IdUtil.fastSimpleUUID();
    }

    /**
     * 获得实体类中id对应的字段名称
     * @param entityObj
     * @return
     */
    private String getEntityIdField(Object entityObj) {
        String idFieldName = null;
        Field[] fields = ReflectUtil.getFields(entityObj.getClass());
        for (Field field : fields) {
            if (AnnotationUtil.hasAnnotation(field, Id.class)) {
                idFieldName = field.getName();
                break;
            }
        }
        return idFieldName;
    }
}

jpa 主键生成的4中策略

编写实体类

用户实体类

@Data
@Entity
@ToString
@Table(name = "user")
@Accessors(chain = true)
@EntityListeners(AuditingEntityListener.class)
public class UserPO implements Serializable {

    private static final long serialVersionUID = -228275281168696921L;

    /**
     * 主键id,使用自定义的主键生成策略
     */
    @Id
    @Column
    @GeneratedValue(generator = "my-uuid")
    @GenericGenerator(name = "my-uuid", strategy = "top.zhoujb.demo.jpa.config.CustomUUIDConfig")
    private String id;
    /**
     * 账户名
     */
    @Column
    private String account;
    /**
     * 密码
     */
    @Column
    private String password;
    /**
     * 名称
     */
    @Column
    private String userName;
    /**
     * 性别
     */
    @Column
    private Integer gender;
    /**
     * 年龄
     */
    @Column
    private Integer age;
    /**
     * 创建人
     */
    @Column
    @CreatedBy
    private String createUser;
    /**
     * 创建时间
     */
    @Column
    @CreatedDate
    private LocalDateTime createTime;
    /**
     * 更新人
     */
    @Column
    @LastModifiedBy
    private String updateUser;
    /**
     * 更新时间
     */
    @Column
    @LastModifiedDate
    private LocalDateTime updateTime;
    /**
     * 删除标识
     */
    @Column
    private String deleteFlag;

    /**
     * 机构代码
     */
    @Transient
    private String orgCode;
    /**
     * 机构名称
     */
    @Transient
    private String orgName;
}

用户机构实体类

@Data
@Entity
@ToString
@Accessors(chain = true)
@Table(name = "user_org")
@EntityListeners(AuditingEntityListener.class)
public class UserOrgPO implements Serializable {

    private static final long serialVersionUID = 8618860155842190730L;
    /**
     * 主键id,使用自定义的主键生成策略
     */
    @Id
    @Column
    @GeneratedValue(generator = "my-uuid")
    @GenericGenerator(name = "my-uuid", strategy = "top.zhoujb.demo.jpa.config.CustomUUIDConfig")
    private String id;
    /**
     * 用户id
     */
    private String userId;
    /**
     * 机构代码
     */
    private String orgCode;
    /**
     * 机构名称
     */
    private String orgName;
    /**
     * 创建人
     */
    private String createUser;
    /**
     * 创建时间
     */
    private LocalDateTime createTime;
    /**
     * 更新人
     */
    private String updateUser;
    /**
     * 更新时间
     */
    private LocalDateTime updateTime;
    /**
     * 删除标识
     */
    private String deleteFlag;
}

数据库操作接口

用户数据库访问接口

public interface UserDao extends JpaRepository<UserPO, String> {}

用户机构数据库访问层接口

public interface UserOrgDao extends JpaRepository<UserOrgPO, String> {}

测试案例

新增

单表新增

    @Test
    void insertOne() {
        String password = SecureUtil.md5("user");
        UserPO userPO = new UserPO()
                .setAccount("user")
                .setPassword(password)
                .setUserName("测试用户")
                .setAge(20)
                .setGender(1)
                .setDeleteFlag("0");
        this.userDao.save(userPO);
    }

多表新增

    @Test
    void insertMultipleWithCustomId() {
        String password = SecureUtil.md5("user");
        List<UserPO> userList = new ArrayList<>();
        List<UserOrgPO> userOrgList = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            String userId = IdUtil.fastSimpleUUID();
            UserPO userPO = new UserPO()
                    .setId(userId)
                    .setAccount("user" + i + RandomUtil.randomInt())
                    .setPassword(password)
                    .setUserName("测试用户" + i + RandomUtil.randomInt())
                    .setAge(20 + i)
                    .setGender(i % 2)
                    .setDeleteFlag("0");
            userList.add(userPO);
            UserOrgPO userOrgPO = new UserOrgPO()
                    .setUserId(userId)
                    .setDeleteFlag("0")
                    .setOrgCode("orgCode" + i + RandomUtil.randomInt())
                    .setOrgName("orgName" + i + RandomUtil.randomInt());
            userOrgList.add(userOrgPO);
        }
        this.userDao.saveAllAndFlush(userList);
        this.userOrgDao.saveAllAndFlush(userOrgList);
    }

修改

    @Test
    public void update() {
        String userId = "5d744def266146fc9b99d7f7b87883e4";
        Optional<UserPO> opt = this.userDao.findById(userId);
        UserPO userPO = new UserPO();
        if (opt.isPresent()) {
            BeanUtil.copyProperties(opt.get(), userPO);
        }
        userPO.setUserName("777").setAccount("user666");
        UserPO save = this.userDao.save(userPO);
        System.out.println(save.toString());
    }

删除

    @Test
    public void deleteOne() {
        String userId = "2e78a777172b46e392cc2416ad0043b4";
        this.userDao.deleteById(userId);
    }

    @Test
    public void deleteMany() {
        List<String> idList = ListUtil.toList("f4394921d44243228fe15115fa4ea693", "ce99a184246a4af5ae5f3bbabf0dd807");
        this.userDao.deleteAllByIdInBatch(idList);
    }

查询

简单查询

    @Test
    public void findOne() {
        String userId = "a1f704b9600548e6a71e6d694e04698a";
        Optional<UserPO> byId = this.userDao.findById(userId);
        if (byId.isPresent()) {
            System.out.println(byId.get().toString());
        } else {
            System.out.println("no data");
        }
    }

    @Test
    public void findAll() {
        List<UserPO> all = this.userDao.findAll();
        System.out.println(all.size());
    }

普通单表分页

自定义分页方法，参数为实体类时，在UserDao接口中添加如下代码

    /**
     * 分页查询数据
     * @param params
     * @param pageRequest
     * @return
     */
    @Query(nativeQuery=true, value = "select * from user where account like %:#{#user.account}%")
    Page<UserPO> selectByPage(@Param("user") UserPO params, Pageable pageRequest);

测试代码

    @Test
    public void findByPage() {
        int page = 1;
        int size = 5;
        Sort genderSort = Sort.by(Sort.Direction.ASC, "gender");
        Pageable pageRequest = PageRequest.of(page, size, genderSort);
        UserPO params = new UserPO().setAccount("user");
        Page<UserPO> poPage = this.userDao.selectByPage(params, pageRequest);
        System.out.println(poPage.getContent().size());
    }

多表联合分页

多表分页，参数为实体类时，在UserDao接口中添加如下代码。注意：此时需要自己重写countQuery，否则会导致sql执行报错或者统计结果错误等问题。

    /**
     * 分页查询数据
     * @param params
     * @param pageRequest
     * @return
     */
    @Query(nativeQuery = true,
            value = "select u.*,uo.org_code,uo.org_name from user u join user_org uo on uo.user_id = u.id where u.account like %:#{#user.account}%"
            , countQuery = "select count(*) from user u join user_org uo on uo.user_id = u.id where u.account like %:#{#user.account}%"
    )
    Page<UserPO> selectUseAndOrgByPage(@Param("user") UserPO params, Pageable pageRequest);

测试代码

    @Test
    public void findByPageWithMultipleTable() {
        int page = 1;
        int size = 5;
        Sort genderSort = Sort.by(Sort.Direction.ASC, "gender");
        Pageable pageRequest = PageRequest.of(page, size, genderSort);
        UserPO params = new UserPO().setAccount("user");
        Page<UserPO> poPage = this.userDao.selectUseAndOrgByPage(params, pageRequest);
        List<UserPO> content = poPage.getContent();
        if (CollUtil.isNotEmpty(content)) {
            content.stream().forEach(uo -> System.out.println(uo.toString()));
        }
    }

总结

注意事项

	说明
查询参数为实体类时参数书写方式	格式: :#{#实体类参数名.实体类属性名}。示例：实体类参数名称为：params,其中有name属性，则在sql中应为：:#{#params.name}
多表联合查询注意事项	多表联合分页查询时需要统计sql

jpa 的关键字使用

关键字	案例	执行的SQL片段
Distinct	findDistinctByLastnameAndFirstname	select distinct … where x.lastname = ?1 and x.firstname = ?2
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull, Null	findByAge(Is)Null	… where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	… where x.age not in ?1
TRUE	findByActiveTrue()	… where x.active = true
FALSE	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstname) = UPPER(?1)

来源：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation