SpringDataJPA框架使用笔记

布满荆棘的人生 2024-03-23 17:26 92阅读 0赞

## SpringDataJPA框架使用笔记 ##

### 什么是JPA ###

#### jpa概念 ####

JPA是Java中用于实现对象关系映射ORM的API标准，
    JPA提供了一种将JAVA对象映射到关系数据库的方式，
    使开发人员可以使用面向对象的方式操作数据库，而不需要编写繁琐的SQL语句。
    jpa中的一些概念有：
    实体类Entity
    实体管理器EntityManager
    实体管理器工厂EntityManagerFactory
    持久化上下文PersistenceContext
    主键PrimaryKey
    关系映射Relationship Mapping
    查询语言JPQL

#### 一些jpa框架 ####

目前Java开发市场中的JPA框架有：
    1.Hibernate 
    最流行的JPA实现之一，提供了全面的ORM功能，常和Structs2 +Spring一起使用，即SSH技术体系
    2.EclipseLink
    是Eclipse基金会提供的JPA实现，高性能，可扩展，支持多数据源
    3.SpringDataJPA
    是Spring提供的JPA实现，可以使用少量代码实现常见的CRUD操作，而无需写复杂的实现代码。
    4.OpenJPA
    是Apache基金会的一个项目，实现了JPA规范，支持动态代理和动态类增强。

#### 什么是springDataJPA ####

SpringDataJPA是spring框架下的一个模块，用于简化JPA的开发，
    它结合了spring框架和JPA的特性，提供了一种简便的方法来访问和操作数据库。
    它使用少量的注解和约定，自动实现常见的CRUD操作，减轻了开发负担
    
    SpringDataJPA的一些主要特性：
    1.仓库接口Repository Interface
    SpringDataJPA通过仓库接口来提供数据访问功能，
    自定义的xxxRepository需要继承 JpaRepository /CrudRepository/JpaSpecificationExecutor
    来获取JPA相关操作功能
    
    2.自定义查询方法
    通过springDataJPA方法的命名约定实现自动生成查询，它会根据方法名和参数，自动解析出相应的查询条件，并生成相应的SQL语句执行查询
    
    3.查询创建器
    可以在方法名中使用findBy\And\Or等关键字，快速定义复杂的查询条件
    
    4.分页和排序
    在查询方法中传递Pageable参数，可以指定需要返回的页数、每页记录数和排序方式
    
    5.对其他数据库和NoSql的支持
    除了常见的关系数据库，还支持如MongoDB/Neo4j/ES/Redis等NoSQL数据库

### springboot整合JPA操作mysql数据库 ###

#### xml依赖配置 ####

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
    <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>

#### 开启JPA审计功能 ####

@Slf4j
    
    // 启注解事务管理，等同于xml配置方式的 <tx:annotation-driven />
    @EnableTransactionManagement 
    
    //启用JPA审计功能-自动写新增时间/修改时间 （插入的时间可以是Date也可以是Long型）
    @EnableJpaAuditing
    
    @SpringBootApplication
    public class DemoApplication {
        
        public static void main(String[] args) {
        
            SpringApplication.run(DemoApplication.class,args);
        }
    }

#### yml配置 ####

spring:
      datasource:
    #    driver-class-name: com.mysql.jdbc.Driver #mysql5的驱动
        driver-class-name: com.mysql.cj.jdbc.Driver #mysql8的驱动
        name: defaultDataSource
        url: jdbc:mysql://127.0.0.1:3307/aep?serverTimezone=UTC&autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8&useSSL=false
        username: aep
        password: aep123456
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          maximum-pool-size: 20
          minimum-idle: 5
      jpa:
        database: mysql
        database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
        show-sql: true
        hibernate:
    #      ddl-auto: create-drop #开机时重建数据库表结构(清库后重建)
          ddl-auto: update  #开机时更新数据库表结构(更新库表字段)
        open-in-view: false

#### 使用示例代码 ####

##### 实体类 #####

package cn.test.orm.menu;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_menu")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_menu set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    @org.hibernate.annotations.Table(appliesTo = "t_menu",comment="前端菜单表")
    public class MenuDO {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private String url;
    
        private String path;
    
        private String component;
    
        private String name;
    
        private String iconCls;
    
        private Boolean keepAlive;
    
        private Boolean requireAuth;
    
        private Integer parentId;
    
        private Boolean enabled;
    
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '备注'")
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "create_time",columnDefinition = "datetime DEFAULT NULL COMMENT '添加时间'")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "update_time",columnDefinition = "datetime DEFAULT NULL COMMENT '修改时间'")
        @LastModifiedDate
        private Date  updateTime;
    
    
        @Column(name = "is_del", columnDefinition = "int(1)  DEFAULT 0 COMMENT '逻辑删除标志'")
        private Integer isDel=0;
    }
    
    
    ///
    package cn.test.orm.mr;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_menu_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_menu_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    @org.hibernate.annotations.Table(appliesTo = "t_menu_role",comment="菜单-角色-关系表")
    public class MenuRole {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private Long mid;
    
        private Long rid;
    
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '备注'")
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "create_time",columnDefinition = "datetime DEFAULT NULL COMMENT '添加时间'")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "update_time",columnDefinition = "datetime DEFAULT NULL COMMENT '修改时间'")
        @LastModifiedDate
        private Date  updateTime;
    
    
        @Column(name = "is_del", columnDefinition = "int(1)  DEFAULT 0 COMMENT '逻辑删除标志'")
        private Integer isDel=0;
    
    
    }
    
    //
    package cn.test.orm.role;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    @org.hibernate.annotations.Table(appliesTo = "t_role",comment="角色表")
    public class RoleDO {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        @Column(unique = true,columnDefinition = "varchar(255) DEFAULT NULL COMMENT '角色标志'")
        private String roleFlag;
    
    
    
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '备注'")
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "create_time",columnDefinition = "datetime DEFAULT NULL COMMENT '添加时间'")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "update_time",columnDefinition = "datetime DEFAULT NULL COMMENT '修改时间'")
        @LastModifiedDate
        private Date  updateTime;
    
    
        @Column(name = "is_del", columnDefinition = "int(1)  DEFAULT 0 COMMENT '逻辑删除标志'")
        private Integer isDel=0;
    }
    
    
    
    package cn.test.orm.ur;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_user_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_user_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    @org.hibernate.annotations.Table(appliesTo = "t_user_role",comment="用户-角色-关系表")
    public class UserRole {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private Long uid;
    
        private Long rid;
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '备注'")
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "create_time",columnDefinition = "datetime DEFAULT NULL COMMENT '添加时间'")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "update_time",columnDefinition = "datetime DEFAULT NULL COMMENT '修改时间'")
        @LastModifiedDate
        private Date  updateTime;
    
        @Column(name = "is_del", columnDefinition = "int(1)  DEFAULT 0 COMMENT '逻辑删除标志'")
        private Integer isDel=0;
    }
    
    //
    
    
    package cn.test.orm.user;
    
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_user")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_user set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    @org.hibernate.annotations.Table(appliesTo = "t_user",comment="用户表")
    public class UserDO {
        
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        @Column(unique = true, columnDefinition = "varchar(255) DEFAULT NULL COMMENT '账号'")
        private String account;
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '密码'")
        private String pwd;
    
    
        @Column(columnDefinition = "varchar(255) DEFAULT NULL COMMENT '备注'")
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "create_time",columnDefinition = "datetime DEFAULT NULL COMMENT '添加时间'")
        @CreatedDate
        private Date  createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @Column(name = "update_time",columnDefinition = "datetime DEFAULT NULL COMMENT '修改时间'")
        @LastModifiedDate
        private Date  updateTime;
    
        /**
         * 版本号，用于乐观锁
         */
        @Column
        @Version
        private Long version;
    
    
        @Column(name = "is_del", columnDefinition = "int(1)  DEFAULT 0 COMMENT '逻辑删除标志'")
        private Integer isDel=0;
    
    }

##### repo操作接口类 #####

package cn.test.orm.menu;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface MenuRepo extends JpaRepository<MenuDO, Long>, JpaSpecificationExecutor<MenuDO> {
        
    }
    //-------------------------------------------------------------
    package cn.test.orm.mr;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    @Repository
    public interface MenuRoleRepo extends JpaRepository<MenuRole, Long>, JpaSpecificationExecutor<MenuRole> {
        
        List<MenuRole> findAllByMid(Long mid);
    }
    //-----------------------------------------------------------------
    package cn.test.orm.role;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface RoleRepo extends JpaRepository<RoleDO, Long>, JpaSpecificationExecutor<RoleDO> {
        
    }
    //-------------------------------------------------------------------
    package cn.test.orm.ur;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.stereotype.Repository;
    import java.util.List;
    @Repository
    public interface UserRoleRepo extends JpaRepository<UserRole, Long>, JpaSpecificationExecutor<UserRole> {
        
        List<UserRole> findAllByUid(Long uid);
    }
    //--------------------------------------------------------------------
    package cn.test.orm.user;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.CrudRepository;
    import org.springframework.data.repository.query.Param;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface UserRepo extends JpaRepository<UserDO, Long>, CrudRepository<UserDO, Long>, JpaSpecificationExecutor<UserDO> {
        
    
    
        @Modifying
        @Query("update UserDO m set m.remark=?2 where m.id=?1")
        int updateRemark(Long uid, String desc);
    
    
        UserDO findByAccount(String account);
    
        @Modifying
        @Query("update UserDO m set " +
                "m.account= :#{#userDO.account} " + "," +
                "m.pwd=:#{#userDO.pwd} " +
                "where m.id=:#{#userDO.id}")
        void update(@Param("userDO") UserDO userDO);
    }

##### service层 #####

package cn.test.biz;
    import cn.test.orm.user.UserDO;
    import cn.test.orm.user.UserRepo;
    import cn.test.sys.APIException;
    import cn.test.sys.ResultCodeEnum;
    import org.springframework.beans.BeanUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.cache.annotation.CacheEvict;
    import org.springframework.cache.annotation.CachePut;
    import org.springframework.cache.annotation.Cacheable;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    @Service
    public class UserService {
        
        @Autowired
        private UserRepo userRepo;
    
        @Cacheable(cacheNames = "user", key = "#id",condition = "#id != null ")
        public UserDO getById(Long id) {
        
            UserDO userDO = userRepo.findById(id).orElse(null);
            return userDO;
        }
    
        @Transactional(rollbackFor = Exception.class)
        @CacheEvict(cacheNames = "user", key = "#id")
        public void delUser(Long id) {
        
            UserDO userDO = userRepo.findById(id).orElse(null);
            if (userDO != null) {
        
                userRepo.delete(userDO);
            }
        }
    
    
        @CachePut(cacheNames = "user",
                key = "#userInfo.id",
                condition = "#userInfo != null")
        public UserDO addUser(UserDO userInfo) {
        
            return userRepo.save(userInfo);
        }
    
     
        @CachePut(cacheNames = "user", key = "#userInfo.id")
        @Transactional(rollbackFor = Exception.class)
        public UserDO updateUserInfo(UserDO userInfo) {
        
            UserDO userDO = userRepo.findById(userInfo.getId()).orElse(null);
            if (userDO != null) {
        
                Long id = userDO.getId();
                BeanUtils.copyProperties(userInfo, userDO);
                userDO.setId(id);
                userRepo.save(userDO);
            }
            return userDO;
        }
    
    
        public Integer judgeMoreThan999(int a, int b) {
        
            int r = a + b;
            if (r < 0) {
        
                throw new APIException();
            }
            if (r < 999 && r > 0) {
        
                //适用于 有某业务异常，不往下执行后续业务，直接抛出
                throw new APIException(ResultCodeEnum.BIZ_ERROR);
            } else {
        
                return r;
            }
        }
    
    
        @Transactional(rollbackFor = Exception.class)
        public void test() {
        
            System.err.println(userRepo.updateRemark(1L, "wer3"));
        }
    
    
        @Transactional(rollbackFor = Exception.class)
        public void testError() {
        
            userRepo.updateRemark(1L, "wer3");
            int a = 3 / 0; //测试除0异常会不会引起事务 , 实测不管 本语句在 updateRemark之前或之后，均会引起事务回退
        }
    }

### springboot整合JPA操作pgsql(或类pg数据库) ###

#### xml依赖配置 ####

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
    
     <dependency>
                <groupId>com.highgo</groupId>
                <artifactId>HgdbJdbc</artifactId>
                <version>6.2.2</version>
            </dependency>

#### yml配置 ####

spring:
      datasource:
       #配置pg数据库驱动或类pg数据库的驱动
        driver-class-name: com.highgo.jdbc.Driver 
        name: defaultDataSource
        url: jdbc:highgo://10.11.5.112:5866/QBXT2023?currentSchema=SYNDCOS
        username: QBXT
        password: kSh@88861158
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          maximum-pool-size: 20
          minimum-idle: 5
      jpa:
        database: postgresql
        database-platform: org.hibernate.dialect.PostgreSQLDialect
        show-sql: true
        hibernate:
    #      ddl-auto: create-drop
          ddl-auto: update
        open-in-view: false

#### 使用示例代码 ####

##### 实体类 #####

package cn.test.orm.menu;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_menu")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_menu set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    public class MenuDO {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private String url;
    
        private String path;
    
        private String component;
    
        private String name;
    
        private String iconCls;
    
        private Boolean keepAlive;
    
        private Boolean requireAuth;
    
        private Integer parentId;
    
        private Boolean enabled;
    
    
    
        @Column(length = 255)
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @LastModifiedDate
        private Date  updateTime;
    
    
        private Integer isDel=0;
    }
    //------------------------------------------------------
    package cn.test.orm.mr;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_menu_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_menu_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    public class MenuRole {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private Long mid;
    
        private Long rid;
    
    
        @Column(length = 255)
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    
        @LastModifiedDate
        private Date  updateTime;
    
    
    
        private Integer isDel=0;
    
    
    }
    
    //----------------------------------------------------------------
    package cn.test.orm.role;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    public class RoleDO {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
    
        @Column(length = 255)
        private String roleFlag;
    
    
        @Column(length = 255)
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @LastModifiedDate
        private Date  updateTime;
    
    
    
        private Integer isDel=0;
    }
    //-------------------------------------------------------------------------
    
    package cn.test.orm.ur;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_user_role")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_user_role set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    public class UserRole {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private Long uid;
    
    
        private Long rid;
    
    
        @Column(length = 255)
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @CreatedDate
        private Date createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @LastModifiedDate
        private Date  updateTime;
    
        private Integer isDel=0;
    }
    
    //---------------------------------------------------------------------
    
    package cn.test.orm.user;
    
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.*;
    import org.hibernate.annotations.DynamicInsert;
    import org.hibernate.annotations.DynamicUpdate;
    import org.hibernate.annotations.SQLDelete;
    import org.hibernate.annotations.Where;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.*;
    import java.util.Date;
    
    
    
    
    @Getter
    @Setter
    @ToString
    @NoArgsConstructor
    @AllArgsConstructor
    @Entity
    @Table(name = "t_user")
    @EntityListeners(AuditingEntityListener.class)
    @SQLDelete(sql = "update t_user set is_del = 1 where id = ?")
    @Where(clause = "is_del = 0")
    @DynamicUpdate
    @DynamicInsert
    public class UserDO {
        
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
    
        @Column(length = 255)
        private String account;
    
    
        @Column(length = 255)
        private String pwd;
    
    
    
        @Column(length = 255)
        private String remark;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @CreatedDate
        private Date  createTime;
    
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
        @LastModifiedDate
        private Date  updateTime;
    
        /**
         * 版本号，用于乐观锁
         */
        @Column
        @Version
        private Long version; //版本号从0开始，每次update时会version+1
    
    
        private Integer isDel=0;
    
    
    }

##### repo操作接口类 #####

repo操作接口类与上节相同，这里不再展示

##### service层 #####

service层与上节相同，这里不再展示

### springboot整合JPA操作SQLite ###

#### 什么是SQLite ####

SQLite是领先的文件型数据库，类似的文件数据库还有Microsoft Access;
    SQLite无序数据库服务器，只需要一个xxx.db文件，即可实现存取数据；
    这里推荐一个SQLite客户端管理工具---DB Browser for SQLite;

![请添加图片描述][d515c1bed7c9404aafd679e965fd1f37.png]

#### xml依赖配置 ####

<dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.28.0</version>
            </dependency>
    
            <dependency>
                <groupId>com.github.gwenn</groupId>
                <artifactId>sqlite-dialect</artifactId>
                <version>0.1.0</version>
            </dependency>
     <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>

#### yml配置 ####

spring:
      datasource:
      # 配置驱动名
        driver-class-name: org.sqlite.JDBC
        name: defaultDataSource
        #配置jdbc-url地址，直接指向xxx.db数据库文件
        url: jdbc:sqlite:C://Users/Administrator/Desktop/302/test.db
        username: QBXT
        password: Sh@88861158
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          maximum-pool-size: 20
          minimum-idle: 5
      jpa:
        #指定数据库方言
        database-platform: org.hibernate.dialect.SQLiteDialect
        show-sql: true
        hibernate:
    #      ddl-auto: create-drop
          ddl-auto: update
        open-in-view: false
        generate-ddl: true
        properties:
          hibernate:
            format_sql: true  #格式化打印sql日志

以上配置完成后，即可开始使用SpringDataJPA

[d515c1bed7c9404aafd679e965fd1f37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/5369012c928b47adabed97d1342d9b45.png