SpringDataJpa简介：Spring Data JPA是Spring Data家族的一员，可以轻松实现基于JPA的存储库。该模块处理对基于JPA的数据访问层的增强支持。这使得构建使用数据访问技术的Spring应用程序变得更加容易。
在相当长的一段时间内，实现应用程序的数据访问层一直很麻烦。必须编写太多样板代码来执行简单查询以及执行分页和审计。Spring Data JPA旨在通过减少实际需要的工作量来显著改善数据访问层的实现。作为开发人员，你编写repository接口，包括自定义查询器方法，Spring将自动提供实现。
下面通过SpringBoot2.x集成SpringDataJpa并进行基本的使用测试，基于Kotlin语言编写，其中SpringBoot使用的2.2.2.RELEASE版本。
一、编写pom文件
引入相关依赖和插件，完整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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.rtxtitanv</groupId>
<artifactId>springboot-data-jpa-kotlin</artifactId>
<version>1.0.0</version>
<packaging>jar</packaging>
<name>springboot-data-jpa-kotlin</name>
<description>SpringBoot2.x 集成 SpringDataJpa 基于Kotlin</description>
<parent>
<!-- SpringBoot 起步依赖 -->
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- 增量编译：为了使构建更快，为Maven启用增量编译 也可以使用 -Dkotlin.compiler.incremental=true 选项运行构建 -->
<kotlin.compiler.incremental>true</kotlin.compiler.incremental>
<java.version>1.8</java.version>
<kotlin.version>1.3.61</kotlin.version>
</properties>
<dependencies>
<!-- Spring Data JPA的起步依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- mysql数据库驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- jackson kotlin 包 -->
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-kotlin</artifactId>
</dependency>
<!-- kotlin反射包 -->
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-reflect</artifactId>
</dependency>
<!-- kotlin jdk8核心库 -->
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-stdlib-jdk8</artifactId>
</dependency>
<!-- 单元测试依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<sourceDirectory>${project.basedir}/src/main/kotlin</sourceDirectory>
<testSourceDirectory>${project.basedir}/src/test/kotlin</testSourceDirectory>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!-- kotlin-maven-plugin 插件 -->
<plugin>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-plugin</artifactId>
<version>${kotlin.version}</version>
<configuration>
<args>
<!-- 负责检查对JSR-305注解的支持 -->
<arg>-Xjsr305=strict</arg>
</args>
<compilerPlugins>
<!-- spring插件 -->
<plugin>spring</plugin>
<!-- jpa插件，与kotlin-spring插件类似，kotlin-jpa是在no-arg之上的一层包装。
该插件自动指定了@Entity、@Embeddable与@MappedSuperclass这几个无参注解 -->
<plugin>jpa</plugin>
</compilerPlugins>
</configuration>
<dependencies>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-allopen</artifactId>
<version>${kotlin.version}</version>
</dependency>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-noarg</artifactId>
<version>${kotlin.version}</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>compile</id>
<goals> <goal>compile</goal> </goals>
</execution>
<execution>
<id>test-compile</id>
<goals> <goal>test-compile</goal> </goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
二、编写配置文件
resources目录下新建application.yml配置文件，配置内容如下：
spring:
# mysql数据库连接信息
datasource:
# 高版本mysql驱动
driver-class-name: com.mysql.cj.jdbc.Driver
# 使用com.mysql.cj.jdbc.Driver驱动需要带上时区serverTimezone
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
username: root
password: root
# jpa相关配置
jpa:
database: mysql
# 控制台是否显示sql语句 true：显示 false：不显示
show-sql: true
# 是否根据实体类（@Entity注解的类）自动建表，true：是 false：否，默认为false
generate-ddl: true
hibernate:
# 自动创建或更新或验证数据库表结构，省略不会自动建表
# create：每次加载Hibernate时都会删除上一次生成的表，然后根据Entity类再重新来生成新表，即使两次没有任何改变也要这样执行，这就是导致数据库表数据丢失的一个重要原因
# create-drop：每次加载Hibernate时根据Entity类生成表，但是sessionFactory一关闭，表就自动删除
# update：最常用的属性，第一次加载Hibernate时根据Entity类会自动建立起表的结构（前提是先建立好数据库），以后加载Hibernate时根据Entity类自动更新表结构，
# 即使表结构改变了，但表中的行仍然存在，不会删除以前的行。要注意的是当部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会
# validate：每次加载Hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值
ddl-auto: update
# 设置数据库存储引擎为InnoDB
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
三、创建实体类并配置映射关系
package com.rtxtitanv.model
import javax.persistence.*
/**
* @name com.rtxtitanv.model.User
* @description 用户实体类
* @author rtxtitanv
* @date 2020/1/17 16:40
* @version 1.0.0
*/
@Entity // 声明实体类
@Table(name = "user") // 建立实体类与表的映射关系
data class User(@Id // 声明该实例域为主键
// 主键生成策略，IDENTITY：由数据库自行管理
@GeneratedValue(strategy = GenerationType.IDENTITY)
// 建立实例域id与表中字段id的映射关系
@Column(name = "id")
// Kotlin变量默认Non-Null，?声明变量为Nullable变量，即允许为null，这里表示类中该属性允许为null
var id: Long? = null,
// 建立实例域uid和表中字段uid的映射关系，length：指定此字段的长度，只对字符串有效，不写默认为255
// unique：是否添加唯一约束，不写时默认为false，nullable：表示此字段是否允许为null
@Column(name = "uid", length = 64, unique = true, nullable = false)
var uid: String? = null,
// 建立实例域userName和表中字段user_name的映射关系
@Column(name = "user_name", length = 16, unique = true, nullable = false)
var userName: String? = null,
// 建立实例域passWord和表中字段pass_word的映射关系
@Column(name = "pass_word", length = 16, nullable = false)
var passWord: String? = null,
// 建立实例域nickName和表中字段nick_name的映射关系
@Column(name = "nick_name", length = 16)
var nickName: String? = null,
// 建立实例域age和表中字段age的映射关系
@Column(name = "age")
var age: Int? = null,
// 建立实例域email和表中字段email的映射关系
@Column(name = "email", unique = true, nullable = false)
var email: String? = null,
// 建立实例域tel和表中字段tel的映射关系
@Column(name = "tel", unique = true, nullable = false)
var tel: String? = null,
// 建立实例域regTime和表中字段reg_time的映射关系
@Column(name = "reg_time", nullable = false)
var regTime: String? = null,
// 该实例域并非一个到数据库表的字段的映射，ORM框架将忽略该域
@Transient
var addr: String? = null)
注意：
1.如果类中的实例域使用枚举类型时，我们想要数据库中存储的是枚举对应的String类型值，而不是枚举的索引值时，需要在实例域上面添加注解@Enumerated(EnumType.STRING) ，例如:
@Enumerated(EnumType.STRING)
@Column(name = "user_type")
var type: UserType? = null
2.如果实体类不在启动类所在包及其子包下，则需要在主启动类上加上以下注解：
@EntityScan(basePackages = ["包含实体类的包路径"])
如果有多个包路径，只需要在主启动类上加上以下注解：
@EntityScan(basePackages = ["包含实体类的包路径1", "包含实体类的包路径2", ..., "包含实体类的包路径n"])
四、创建Repository接口
package com.rtxtitanv.repository
import com.rtxtitanv.model.User
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor
/**
* @name com.rtxtitanv.repository.UserRepository
* @description UserRepository接口用于操作用户表，JpaRepository<实体类类型, 主键类型>：用于完成基本CRUD操作
* JpaSpecificationExecutor<实体类类型>：用于复杂查询
* @author rtxtitanv
* @date 2020/1/17 16:54
* @version 1.0.0
*/
interface UserRepository : JpaRepository<User, Long>, JpaSpecificationExecutor<User>
五、SpringDataJpa的使用测试
首先启动主启动类，会在数据库中建立一张user表，表中暂无数据，建表信息如下：
Hibernate: create table user (id bigint not null auto_increment, age integer, email varchar(255) not null, nick_name varchar(16), pass_word varchar(16) not null, reg_time varchar(255) not null, tel varchar(255) not null, uid varchar(64) not null, user_name varchar(16) not null, primary key (id)) engine=InnoDB
Hibernate: alter table user drop index UK_ob8kqyqqgmefl0aco34akdtpe
Hibernate: alter table user add constraint UK_ob8kqyqqgmefl0aco34akdtpe unique (email)
Hibernate: alter table user drop index UK_nbfia2ok6c7at4i0er6uyskkx
Hibernate: alter table user add constraint UK_nbfia2ok6c7at4i0er6uyskkx unique (tel)
Hibernate: alter table user drop index UK_a7hlm8sj8kmijx6ucp7wfyt31
Hibernate: alter table user add constraint UK_a7hlm8sj8kmijx6ucp7wfyt31 unique (uid)
Hibernate: alter table user drop index UK_lqjrcobrh9jc8wpcar64q1bfh
Hibernate: alter table user add constraint UK_lqjrcobrh9jc8wpcar64q1bfh unique (user_name)
SpringDataJpa单元测试类
package com.rtxtitanv
import com.rtxtitanv.repository.UserRepository
import org.junit.runner.RunWith
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
/**
* @name com.rtxtitanv.JpaTest
* @description SpringDataJpa单元测试类
* @author rtxtitanv
* @date 2020/1/17 17:49
* @version 1.0.0
*/
@RunWith(SpringRunner::class)
@SpringBootTest(classes = [JpaApplication::class])
class JpaTest {
// 单元测试类不能通过构造函数注入，使用@Autowired注入时需声明属性为lateinit
@Autowired
private lateinit var userRepository: UserRepository
private val logger: Logger = LoggerFactory.getLogger(JpaTest::class.java)
}
1.基本的增删改查
使用JpaRepository接口自带的方法进行基本的增删改查。
（1）新增测试
①保存单条记录
/**
* 保存5条测试数据，一次插入一条数据
* 使用方法 <S extends T> S save(S var1)
* 实体中主键不存在时保存记录
*/
@Test
fun saveTest() {
val date = Date()
val dateFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
val formattedDate = dateFormat.format(date)
logger.info("保存5条测试数据开始")
userRepository.save(User(null, "296ebeb46acd49aca54f0d5a5a1257c3", "qwer123", "123456", "aaa", 24,
"qwer123@ss.com", "13915176512", formattedDate, "beijing"))
userRepository.save(User(null, "e6c133e338bb4b7c857be76104986acb", "asd6666", "qw23ss", "asc", 18,
"asd6666@ss.com", "15736226963", formattedDate, "tianjin"))
userRepository.save(User(null, "179a91c205f84416b39347d714516c95", "tgh3er2", "11111", "r123er", 22,
"advx@ss.com", "18956929863", formattedDate, "hangzhou"))
userRepository.save(User(null, "dddfa7b84b194ea5a62393ef8f211710", "erfgyhn", "567809a.", "rw23ed", 27,
"ddfty@ss.com", "16389562477", formattedDate, "shanghai"))
userRepository.save(User(null, "7fc652d011e8448e99aee948f1af9187", "rty7ui81", "a2ef56.a", "asc", 18,
"sdrtyhui@ss.com", "15966358996", formattedDate, "nanjing"))
logger.info("保存5条测试数据结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:19:57.112 INFO 15264 --- [ main] com.rtxtitanv.JpaTest : 保存5条测试数据开始
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
2020-01-21 13:19:57.194 INFO 15264 --- [ main] com.rtxtitanv.JpaTest : 保存5条测试数据结束
user表中成功插入5条记录：
②批量保存
/**
* 批量保存5条测试数据 后台执行时仍是一条一条插入
* 使用方法 <S extends T> List<S> saveAll(Iterable<S> var1)
* 实体中主键不存在时保存记录
*/
@Test
fun saveInBatchTest() {
val date = Date()
val dateFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
val formattedDate = dateFormat.format(date)
val user1 = User(null, "8960a15b37d0552aa84483e24fc57b80", "23erg", "2234rf", "aaa", 15,
"qadfgyty@ss.com", "16895635789", formattedDate, "beijing")
val user2 = User(null, "cc0106b175a6572e8d2967b3dd563193", "63serf", "2ww5t", "w323rrt", 36,
"wer33@ss.com", "15766958245", formattedDate, "suzhou")
val user3 = User(null, "7669890a99c1581483edf72fa48d702c", "2w4r", "3345tt", "aaa", 24,
"qert23@ss.com", "19725689756", formattedDate, "wuhan")
val user4 = User(null, "9a512c6ffe01565abb619e1199002603", "12er", "134rty", "aa23e54", 23,
"qwer5@ss.com", "13858963144", formattedDate, "jinan")
val user5 = User(null, "f7e05429074b5db9a85d623377475ced", "yu2sd", "1w0978", "asc", 31,
"wer123@ss.com", "18741569832", formattedDate, "xian")
val list = listOf(user1, user2, user3, user4, user5)
logger.info("批量保存5条测试数据开始")
userRepository.saveAll(list)
logger.info("批量保存5条测试数据结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:24:05.731 INFO 16116 --- [ main] com.rtxtitanv.JpaTest : 批量保存5条测试数据开始
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user (age, email, nick_name, pass_word, reg_time, tel, uid, user_name) values (?, ?, ?, ?, ?, ?, ?, ?)
2020-01-21 13:24:05.797 INFO 16116 --- [ main] com.rtxtitanv.JpaTest : 批量保存5条测试数据结束
user表中成功插入5条记录：
（2）查询测试
①查询所有记录
/**
* 查询所有记录
* 使用方法 List<T> findAll();
*/
@Test
fun findAllTest() {
logger.info("查询所有开始")
val userList = userRepository.findAll()
if (userList.isEmpty()) {
logger.info("不存在用户数据")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("查询所有结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:26:51.826 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : 查询所有开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=2, uid=e6c133e338bb4b7c857be76104986acb, userName=asd6666, passWord=qw23ss, nickName=asc, age=18, email=asd6666@ss.com, tel=15736226963, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=4, uid=dddfa7b84b194ea5a62393ef8f211710, userName=erfgyhn, passWord=567809a., nickName=rw23ed, age=27, email=ddfty@ss.com, tel=16389562477, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=6, uid=8960a15b37d0552aa84483e24fc57b80, userName=23erg, passWord=2234rf, nickName=aaa, age=15, email=qadfgyty@ss.com, tel=16895635789, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=7, uid=cc0106b175a6572e8d2967b3dd563193, userName=63serf, passWord=2ww5t, nickName=w323rrt, age=36, email=wer33@ss.com, tel=15766958245, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=8, uid=7669890a99c1581483edf72fa48d702c, userName=2w4r, passWord=3345tt, nickName=aaa, age=24, email=qert23@ss.com, tel=19725689756, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=9, uid=9a512c6ffe01565abb619e1199002603, userName=12er, passWord=134rty, nickName=aa23e54, age=23, email=qwer5@ss.com, tel=13858963144, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : User(id=10, uid=f7e05429074b5db9a85d623377475ced, userName=yu2sd, passWord=1w0978, nickName=asc, age=31, email=wer123@ss.com, tel=18741569832, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:26:51.874 INFO 11740 --- [ main] com.rtxtitanv.JpaTest : 查询所有结束
②查询所有记录并排序
/**
* 查询所有记录并排序
* 使用方法 List<T> findAll(Sort var1)
*/
@Test
fun findAllAndSortTest() {
val sort = Sort.by(Sort.Direction.DESC, "age")
logger.info("查询所有并按年龄降序排序开始")
val userList = userRepository.findAll(sort)
if (userList.isEmpty()) {
logger.info("不存在用户数据")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("查询所有并按年龄降序排序结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:29:10.706 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : 查询所有并按年龄降序排序开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ order by user0_.age desc
2020-01-21 13:29:10.751 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=7, uid=cc0106b175a6572e8d2967b3dd563193, userName=63serf, passWord=2ww5t, nickName=w323rrt, age=36, email=wer33@ss.com, tel=15766958245, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:29:10.751 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=10, uid=f7e05429074b5db9a85d623377475ced, userName=yu2sd, passWord=1w0978, nickName=asc, age=31, email=wer123@ss.com, tel=18741569832, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:29:10.751 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=4, uid=dddfa7b84b194ea5a62393ef8f211710, userName=erfgyhn, passWord=567809a., nickName=rw23ed, age=27, email=ddfty@ss.com, tel=16389562477, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:29:10.751 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=8, uid=7669890a99c1581483edf72fa48d702c, userName=2w4r, passWord=3345tt, nickName=aaa, age=24, email=qert23@ss.com, tel=19725689756, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=9, uid=9a512c6ffe01565abb619e1199002603, userName=12er, passWord=134rty, nickName=aa23e54, age=23, email=qwer5@ss.com, tel=13858963144, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=2, uid=e6c133e338bb4b7c857be76104986acb, userName=asd6666, passWord=qw23ss, nickName=asc, age=18, email=asd6666@ss.com, tel=15736226963, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : User(id=6, uid=8960a15b37d0552aa84483e24fc57b80, userName=23erg, passWord=2234rf, nickName=aaa, age=15, email=qadfgyty@ss.com, tel=16895635789, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:29:10.752 INFO 7252 --- [ main] com.rtxtitanv.JpaTest : 查询所有并按年龄降序排序结束
③分页查询所有记录
/**
* 分页查询所有记录
* 使用方法 Page<T> findAll(Pageable var1)
*/
@Test
fun findAllAndPageTest() {
val sort = Sort.by(Sort.Direction.ASC, "age")
val pageable: Pageable = PageRequest.of(2, 3, sort)
logger.info("分页查询所有开始")
val page = userRepository.findAll(pageable)
if (page.isEmpty) {
logger.info("不存在分页数据")
} else {
logger.info("总条数：" + page.totalElements)
logger.info("总页数：" + page.totalPages)
val userList = page.content
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("分页查询所有结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:31:15.469 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : 分页查询所有开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ order by user0_.age asc limit ?, ?
Hibernate: select count(user0_.id) as col_0_0_ from user user0_
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : 总条数：10
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : 总页数：4
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : User(id=8, uid=7669890a99c1581483edf72fa48d702c, userName=2w4r, passWord=3345tt, nickName=aaa, age=24, email=qert23@ss.com, tel=19725689756, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : User(id=4, uid=dddfa7b84b194ea5a62393ef8f211710, userName=erfgyhn, passWord=567809a., nickName=rw23ed, age=27, email=ddfty@ss.com, tel=16389562477, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : User(id=10, uid=f7e05429074b5db9a85d623377475ced, userName=yu2sd, passWord=1w0978, nickName=asc, age=31, email=wer123@ss.com, tel=18741569832, regTime=2020-01-21 13:24:05, addr=null)
2020-01-21 13:31:15.529 INFO 9288 --- [ main] com.rtxtitanv.JpaTest : 分页查询所有结束
④根据id查询
/**
* 根据id查询
* 使用方法 Optional<T> findById(ID var1)
*/
@Test
fun findByIdTest() {
logger.info("根据id查询开始")
val userOptional = userRepository.findById(1L)
if (!userOptional.isPresent) {
logger.info("查询的用户不存在")
} else {
val user = userOptional.get()
val userInfo = user.toString()
logger.info(userInfo)
}
logger.info("根据id查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:33:22.520 INFO 7100 --- [ main] com.rtxtitanv.JpaTest : 根据id查询开始
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
2020-01-21 13:33:22.567 INFO 7100 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:33:22.567 INFO 7100 --- [ main] com.rtxtitanv.JpaTest : 根据id查询结束
⑤根据id批量查询
/**
* 根据id批量查询
* 使用方法 List<T> findAllById(Iterable<ID> var1)
*/
@Test
fun findInBatchByIdTest() {
val ids = listOf(1L, 3L, 5L)
logger.info("根据id批量查询开始")
val userList = userRepository.findAllById(ids)
if (userList.isEmpty()) {
logger.info("查询的用户不存在")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("根据id批量查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 13:35:09.092 INFO 14216 --- [ main] com.rtxtitanv.JpaTest : 根据id批量查询开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.id in (? , ? , ?)
2020-01-21 13:35:09.146 INFO 14216 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:35:09.146 INFO 14216 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:35:09.146 INFO 14216 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 13:35:09.146 INFO 14216 --- [ main] com.rtxtitanv.JpaTest : 根据id批量查询结束
（3）修改测试
①修改单条记录
/**
* 修改单条记录
* 使用方法 <S extends T> S save(S var1)
* 当实体中主键存在时先根据主键查询再根据主键更新
*/
@Test
fun updateOneTest() {
val userOptional = userRepository.findById(3L)
if (!userOptional.isPresent) {
logger.info("该用户不存在")
} else {
val user = userOptional.get()
// $符号,字符串模板写法,将变量插入字符串
logger.info("修改前的记录: $user")
logger.info("修改一条用户记录开始")
user.nickName = "6wer23a"
user.passWord = "123123"
userRepository.save(user)
logger.info("修改一条用户记录结束")
logger.info("修改后的记录: " + userRepository.findById(3L).get().toString())
}
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 14:25:21.986 INFO 9352 --- [ main] com.rtxtitanv.JpaTest : 修改前的记录: User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:25:21.986 INFO 9352 --- [ main] com.rtxtitanv.JpaTest : 修改一条用户记录开始
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, reg_time=?, tel=?, uid=?, user_name=? where id=?
2020-01-21 14:25:22.013 INFO 9352 --- [ main] com.rtxtitanv.JpaTest : 修改一条用户记录结束
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
2020-01-21 14:25:22.016 INFO 9352 --- [ main] com.rtxtitanv.JpaTest : 修改后的记录: User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=123123, nickName=6wer23a, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
②批量修改
/**
* 批量修改
* 使用方法 <S extends T> List<S> saveAll(Iterable<S> var1)
* 当实体中主键存在时先根据主键查询再根据主键更新
*/
@Test
fun updateInBatchTest() {
val ids = listOf(1L, 3L, 5L)
val userList = userRepository.findAllById(ids)
if (userList.isEmpty()) {
logger.info("查询不到记录")
} else {
logger.info("修改前的记录")
userList.forEach { user -> logger.info(user.toString()) }
logger.info("------------分割线-------------")
userList.forEach { user -> user.passWord = "666666" }
logger.info("批量修改开始")
userRepository.saveAll(userList)
logger.info("批量修改结束")
logger.info("修改后的记录")
userRepository.findAllById(ids).forEach { user -> logger.info(user.toString()) }
}
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : 修改前的记录
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=123123, nickName=6wer23a, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : ------------分割线-------------
2020-01-21 14:28:54.628 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : 批量修改开始
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, reg_time=?, tel=?, uid=?, user_name=? where id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, reg_time=?, tel=?, uid=?, user_name=? where id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, reg_time=?, tel=?, uid=?, user_name=? where id=?
2020-01-21 14:28:54.671 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : 批量修改结束
2020-01-21 14:28:54.671 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : 修改后的记录
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.id in (? , ? , ?)
2020-01-21 14:28:54.674 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=666666, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:28:54.674 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=666666, nickName=6wer23a, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 13:19:57, addr=null)
2020-01-21 14:28:54.674 INFO 11172 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=666666, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 13:19:57, addr=null)
（4）删除测试
①根据id删除
/**
* 根据id删除
* 使用方法 void deleteById(ID var1)
*/
@Test
fun deleteByIdTest() {
logger.info("根据id删除开始")
userRepository.deleteById(3L)
logger.info("根据id删除结束")
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表id为3的记录已成功删除。
2020-01-21 14:32:12.407 INFO 16280 --- [ main] com.rtxtitanv.JpaTest : 根据id删除开始
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: delete from user where id=?
2020-01-21 14:32:12.472 INFO 16280 --- [ main] com.rtxtitanv.JpaTest : 根据id删除结束
②删除单条记录
/**
* 删除单条记录
* 使用方法 void delete(T var1)
*/
@Test
fun deleteOneTest() {
val userOptional = userRepository.findById(1L)
if (!userOptional.isPresent) {
logger.info("该用户不存在")
} else {
val user = userOptional.get()
logger.info("删除一条用户记录开始")
userRepository.delete(user)
logger.info("删除一条用户记录结束")
}
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表id为1的记录已成功删除。
2020-01-21 14:34:17.555 INFO 8076 --- [ main] com.rtxtitanv.JpaTest : 删除一条用户记录开始
Hibernate: select user0_.id as id1_5_0_, user0_.age as age2_5_0_, user0_.email as email3_5_0_, user0_.nick_name as nick_nam4_5_0_, user0_.pass_word as pass_wor5_5_0_, user0_.reg_time as reg_time6_5_0_, user0_.tel as tel7_5_0_, user0_.uid as uid8_5_0_, user0_.user_name as user_nam9_5_0_ from user user0_ where user0_.id=?
Hibernate: delete from user where id=?
2020-01-21 14:34:17.584 INFO 8076 --- [ main] com.rtxtitanv.JpaTest : 删除一条用户记录结束
③批量删除
/**
* 批量删除
* 使用方法 void deleteInBatch(Iterable<T> var1)
* 后台执行时生成一条sql语句删除
*/
@Test
fun deleteInBatch() {
val ids = listOf(2L, 4L, 5L, 7L)
val userList = userRepository.findAllById(ids)
if (userList.isEmpty()) {
logger.info("用户数据不存在")
} else {
logger.info("批量删除开始")
userRepository.deleteInBatch(userList)
logger.info("批量删除结束")
}
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表id为2,4,5,7的记录已成功删除。
2020-01-21 14:36:05.403 INFO 13428 --- [ main] com.rtxtitanv.JpaTest : 批量删除开始
Hibernate: delete from user where id=? or id=? or id=? or id=?
2020-01-21 14:36:05.417 INFO 13428 --- [ main] com.rtxtitanv.JpaTest : 批量删除结束
④删除所有记录
/**
* 删除所有记录
* 使用方法 void deleteAllInBatch()
* 后台执行时生成一条sql语句删除
*/
@Test
fun deleteAllTest() {
logger.info("删除所有开始")
userRepository.deleteAllInBatch()
logger.info("删除所有结束")
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表所有记录已成功删除。
2020-01-21 14:39:05.422 INFO 5844 --- [ main] com.rtxtitanv.JpaTest : 删除所有开始
Hibernate: delete from user
2020-01-21 14:39:05.451 INFO 5844 --- [ main] com.rtxtitanv.JpaTest : 删除所有结束
2.方法命名规则的使用
SpringDataJPA支持接口方法命名规则（在Kotlin中为函数）自动生成sql语句来实现数据库操作，语法支持findBy、readBy、queryBy、countBy、getBy、deleteBy、existsBy等后面跟属性名称，例如查询方法以findBy开头，方法名称必须遵循驼峰式命名规则，涉及条件查询时，条件的属性用条件关键字连接，框架在进行方法名解析时，会先把方法名多余的前缀截取掉之后对剩下部分进行解析。
（1）查询测试
①按昵称查询
在UserRepository接口中添加自定义函数findByNickName：
/**
* 方法命名规则之按昵称查询
* @param nickName 呢称 ? = null 这里表示声明参数为Nullable，即该参数允许为null且不显式传参时该参数默认为null
* @return 查询结果集
*/
fun findByNickName(nickName: String? = null): List<User>
方法命名规则之按昵称查询测试函数：
/**
* 方法命名规则之按昵称查询
*/
@Test
fun findByNickNameTest() {
logger.info("方法命名规则查询之按昵称查询开始")
val userList = userRepository.findByNickName("aaa")
if (userList.isEmpty()) {
logger.info("昵称为aaa的用户不存在")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("方法命名规则查询之按昵称查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 14:56:28.385 INFO 9764 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则查询之按昵称查询开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.nick_name=?
2020-01-21 14:56:28.447 INFO 9764 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 14:45:43, addr=null)
2020-01-21 14:56:28.447 INFO 9764 --- [ main] com.rtxtitanv.JpaTest : User(id=6, uid=8960a15b37d0552aa84483e24fc57b80, userName=23erg, passWord=2234rf, nickName=aaa, age=15, email=qadfgyty@ss.com, tel=16895635789, regTime=2020-01-21 14:45:54, addr=null)
2020-01-21 14:56:28.447 INFO 9764 --- [ main] com.rtxtitanv.JpaTest : User(id=8, uid=7669890a99c1581483edf72fa48d702c, userName=2w4r, passWord=3345tt, nickName=aaa, age=24, email=qert23@ss.com, tel=19725689756, regTime=2020-01-21 14:45:54, addr=null)
2020-01-21 14:56:28.447 INFO 9764 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则查询之按昵称查询结束
②按昵称和年龄查询
在UserRepository接口中添加自定义函数findByNickNameAndAge：
/**
* 方法命名规则之按昵称和年龄查询
* @param nickName 昵称
* @param age 年龄
* @return 查询结果集
*/
fun findByNickNameAndAge(nickName: String? = null, age: Int? = null): List<User>
方法命名规则之按昵称和年龄查询测试函数：
/**
* 方法命名规则之按昵称和年龄查询
*/
@Test
fun findByNickNameAndAgeTest() {
logger.info("方法命名规则之按昵称和年龄查询开始")
val userList = userRepository.findByNickNameAndAge("asc", 18)
if (userList.isEmpty()) {
logger.info("昵称为asc并且年龄为18的用户不存在")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("方法命名规则之按昵称和年龄查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 15:04:55.767 INFO 9812 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称和年龄查询开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.nick_name=? and user0_.age=?
2020-01-21 15:04:55.830 INFO 9812 --- [ main] com.rtxtitanv.JpaTest : User(id=2, uid=e6c133e338bb4b7c857be76104986acb, userName=asd6666, passWord=qw23ss, nickName=asc, age=18, email=asd6666@ss.com, tel=15736226963, regTime=2020-01-21 14:45:43, addr=null)
2020-01-21 15:04:55.831 INFO 9812 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 14:45:43, addr=null)
2020-01-21 15:04:55.831 INFO 9812 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称和年龄查询结束
③按昵称模糊查询
在UserRepository接口中添加自定义函数findByNickNameLike：
/**
* 方法命名规则之按昵称模糊查询
* @param nickName 昵称
* @return 查询结果集
*/
fun findByNickNameLike(nickName: String): List<User>
方法命名规则之按昵称模糊查询测试函数：
/**
* 方法命名规则之按昵称模糊查询
*/
@Test
fun findByNickNameLikeTest() {
logger.info("方法命名规则之按昵称模糊查询开始")
val userList = userRepository.findByNickNameLike("%23%")
if (userList.isEmpty()) {
logger.info("昵称包含23的用户不存在")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("方法命名规则之按昵称模糊查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 15:28:29.185 INFO 832 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称模糊查询开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.nick_name like ? escape ?
2020-01-21 15:28:29.246 INFO 832 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 14:45:43, addr=null)
2020-01-21 15:28:29.246 INFO 832 --- [ main] com.rtxtitanv.JpaTest : User(id=4, uid=dddfa7b84b194ea5a62393ef8f211710, userName=erfgyhn, passWord=567809a., nickName=rw23ed, age=27, email=ddfty@ss.com, tel=16389562477, regTime=2020-01-21 14:45:43, addr=null)
2020-01-21 15:28:29.246 INFO 832 --- [ main] com.rtxtitanv.JpaTest : User(id=7, uid=cc0106b175a6572e8d2967b3dd563193, userName=63serf, passWord=2ww5t, nickName=w323rrt, age=36, email=wer33@ss.com, tel=15766958245, regTime=2020-01-21 14:45:54, addr=null)
2020-01-21 15:28:29.246 INFO 832 --- [ main] com.rtxtitanv.JpaTest : User(id=9, uid=9a512c6ffe01565abb619e1199002603, userName=12er, passWord=134rty, nickName=aa23e54, age=23, email=qwer5@ss.com, tel=13858963144, regTime=2020-01-21 14:45:54, addr=null)
2020-01-21 15:28:29.246 INFO 832 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称模糊查询结束
④按年龄条件统计
在UserRepository接口中添加自定义函数countByAgeLessThanEqual：
/**
* 方法命名规则之按年龄条件统计
* @param age 年龄
* @return 小于等于给定年龄的记录总数
*/
fun countByAgeLessThanEqual(age: Int): Long
方法命名规则之按年龄条件统计测试函数：
/**
* 方法命名规则之按年龄条件统计
*/
@Test
fun countByAgeLessThanEqualTest() {
logger.info("方法命名规则之按年龄条件统计开始")
val count = userRepository.countByAgeLessThanEqual(24)
logger.info("年龄不超过24岁的用户总数: $count")
logger.info("方法命名规则之按年龄条件统计结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 15:33:31.698 INFO 5404 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按年龄条件统计开始
Hibernate: select count(user0_.id) as col_0_0_ from user user0_ where user0_.age<=?
2020-01-21 15:33:31.747 INFO 5404 --- [ main] com.rtxtitanv.JpaTest : 年龄不超过24岁的用户总数: 7
2020-01-21 15:33:31.748 INFO 5404 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按年龄条件统计结束
⑤按邮箱模糊查询名称并按年龄升序
使用SpringDataJPA提供的Projections功能，创建一个接口NameOnly，充当一个“视图”的作用，可以用来自定义的封装我们需要查询的字段。
package com.rtxtitanv.projections
/**
* @name com.rtxtitanv.projections.NameOnly
* @description 只查询name的projections
* @author rtxtitanv
* @date 2020/1/17 17:07
* @version 1.0.0
*/
interface NameOnly {
fun getUserName(): String? // ? 这里表示声明返回值为Nullable，即返回值允许为null
fun getNickName(): String?
}
在UserRepository接口中添加自定义函数findByEmailContainingOrderByAgeAsc：
/**
* 方法命名规则之按邮箱模糊查询名称并按年龄排序
* 只查询用户名和昵称
* @param email 邮箱
* @return List<NameOnly> 名称列表（projections接口NameOnly只包含用户名和昵称）
*/
fun findByEmailContainingOrderByAgeAsc(email: String): List<NameOnly>
方法命名规则之按邮箱模糊查询名称并按年龄升序测试函数：
/**
* 方法命名规则之按邮箱模糊查询名称并按年龄升序
*/
@Test
fun findByEmailContainingOrderByAgeTest() {
logger.info("方法命名规则之按邮箱模糊查询名称并按年龄升序开始")
val nameOnly = userRepository.findByEmailContainingOrderByAgeAsc("er")
if (nameOnly.isEmpty()) {
logger.info("不存在满足条件记录")
} else {
nameOnly.forEach { name -> logger.info("userName: " + name.getUserName() + ", nickName: " + name.getNickName()) }
}
logger.info("方法命名规则之按邮箱模糊查询名称并按年龄升序结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 15:39:13.307 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按邮箱模糊查询名称并按年龄升序开始
Hibernate: select user0_.user_name as col_0_0_, user0_.nick_name as col_1_0_ from user user0_ where user0_.email like ? escape ? order by user0_.age asc
2020-01-21 15:39:13.366 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : userName: 12er, nickName: aa23e54
2020-01-21 15:39:13.366 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : userName: qwer123, nickName: aaa
2020-01-21 15:39:13.366 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : userName: 2w4r, nickName: aaa
2020-01-21 15:39:13.367 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : userName: yu2sd, nickName: asc
2020-01-21 15:39:13.367 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : userName: 63serf, nickName: w323rrt
2020-01-21 15:39:13.367 INFO 13364 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按邮箱模糊查询名称并按年龄升序结束
⑥限制查询
有时候我们只需要查询前N个元素，或者只取前一个实体。在UserRepository接口中添加如下自定义方法：
/**
* 方法命名规则之限制查询
* @return 查询结果
*/
fun findFirstByOrderByAgeAsc(): User?
fun findTopByOrderByAgeAsc(): User?
fun queryFirst10ByNickName(nickName: String, pageable: Pageable): Page<User>
fun findFirst10ByNickName(nickName: String, sort: Sort): List<User>
fun findTop10ByNickName(nickName: String, pageable: Pageable): Page<User>
方法命名规则之限制查询测试函数,这里只测试第一个按年龄升序：
/**
* 方法命名规则之限制查询
*/
@Test
fun findFirstByOrderByAgeAscTest() {
logger.info("方法命名规则之限制查询开始")
val user = userRepository.findFirstByOrderByAgeAsc()
if (user == null) {
logger.info("用户数据不存在")
} else {
val userInfo = user.toString()
logger.info(userInfo)
}
logger.info("方法命名规则之限制查询结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 15:57:14.749 INFO 15612 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之限制查询开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ order by user0_.age asc limit ?
2020-01-21 15:57:14.802 INFO 15612 --- [ main] com.rtxtitanv.JpaTest : User(id=6, uid=8960a15b37d0552aa84483e24fc57b80, userName=23erg, passWord=2234rf, nickName=aaa, age=15, email=qadfgyty@ss.com, tel=16895635789, regTime=2020-01-21 14:45:54, addr=null)
2020-01-21 15:57:14.802 INFO 15612 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之限制查询结束
（2）删除测试
①按昵称（忽略大小写）删除
在UserRepository接口中添加自定义函数deleteByNickNameIgnoreCase：
/**
* 方法命名规则之按昵称（忽略大小写）删除
* @param nickName 昵称
* @return 删除的记录数
*/
//@Transactional：开启事务支持
@Transactional(rollbackFor = [Exception::class])
fun deleteByNickNameIgnoreCase(nickName: String? = null): Int
方法命名规则之按昵称（忽略大小写）删除测试函数：
/**
* 方法命名规则之按昵称（忽略大小写）删除
*/
@Test
fun deleteByNickNameIgnoreCaseTest() {
logger.info("方法命名规则之按昵称删除开始")
val result = userRepository.deleteByNickNameIgnoreCase("AAa")
logger.info("总共删除了$result" + "条记录")
logger.info("方法命名规则之按昵称删除结束")
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表昵称为AAa（忽略大小写）的所有记录已成功删除。
2020-01-21 16:10:59.640 INFO 8652 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称删除开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where upper(user0_.nick_name)=upper(?)
Hibernate: delete from user where id=?
Hibernate: delete from user where id=?
Hibernate: delete from user where id=?
2020-01-21 16:10:59.720 INFO 8652 --- [ main] com.rtxtitanv.JpaTest : 总共删除了3条记录
2020-01-21 16:10:59.720 INFO 8652 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按昵称删除结束
②按年龄批量删除
在UserRepository接口中添加自定义函数deleteByAgeIn：
/**
* 方法命名规则之按年龄批量删除
* @param ages 年龄列表
* @return 删除的记录数
*/
@Transactional(rollbackFor = [Exception::class])
fun deleteByAgeIn(ages: List<Int>): Int
方法命名规则之按年龄批量删除测试函数：
/**
* 方法命名规则之按年龄批量删除
*/
@Test
fun deleteByAgeInTest() {
val ages = listOf(18, 23, 30)
logger.info("方法命名规则之按年龄批量删除开始")
val result = userRepository.deleteByAgeIn(ages)
logger.info("总共删除了$result 条记录")
logger.info("方法命名规则之按年龄批量删除结束")
}
控制台打印的日志及自动生成的sql语句如下，navicat刷新user表年龄为18,23,30的所有记录已成功删除。
2020-01-21 16:14:46.204 INFO 14856 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按年龄批量删除开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.age in (? , ? , ?)
Hibernate: delete from user where id=?
Hibernate: delete from user where id=?
Hibernate: delete from user where id=?
2020-01-21 16:14:46.287 INFO 14856 --- [ main] com.rtxtitanv.JpaTest : 总共删除了3 条记录
2020-01-21 16:14:46.287 INFO 14856 --- [ main] com.rtxtitanv.JpaTest : 方法命名规则之按年龄批量删除结束
（3）方法命名规则支持的关键字
| Keyword | Sample | JPQL snippet |
|---|---|---|
| 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.firstame) = UPPER(?1 |
SpringDataJPA 2.2.3.RELEASE 官方文档
3.JPQL和SQL方式实现增删改查
SpringDataJPA支持使用JPQL和SQL的方式完成对数据库的操作。
（1）新增测试
在UserRepository接口中添加自定义函数insertUser：
/**
* 使用sql语句插入一条记录
* @param uid uid
* @param userName 用户名
* @param passWord 密码
* @param nickName 昵称
* @param age 年龄
* @param email 邮箱
* @param tel 手机
* @param regTime 注册时间
* @return 返回1表示插入成功
*/
// nativeQuery：是否使用本地sql，true表示使用本地sql，缺省默认值为false，不使用本地sql
// sql语句中的?占位符后的数字对应方法中的参数索引，从1开始
@Query(value = "insert into user(uid,user_name,pass_word,nick_name,age,email,tel,reg_time)" +
" values(?1,?2,?3,?4,?5,?6,?7,?8)", nativeQuery = true)
// @Modifying：该注解标识该操作为一个插入更新删除操作，框架最终不会生成一个查询操作
@Modifying
@Transactional(rollbackFor = [Exception::class])
fun insertUser(uid: String? = null, userName: String? = null, passWord: String? = null, nickName: String? = null, age: Int? = null, email: String? = null, tel: String? = null, regTime: String? = null): Int
sql语句插入测试函数：
/**
* 使用sql语句插入一条记录
*/
@Test
fun insertUserTest() {
logger.info("sql插入一条记录开始")
val result = userRepository.insertUser("f0ff89db-aa72-55dc-aaba-e1ec11fa2fec", "dfgyytvb2", "123456", "rty235", 18, "miopl@ss.com", "1256698463", "2020-01-02 11:51:16")
logger.info("sql插入一条记录结束")
if (result == 1) {
logger.info("插入成功")
} else {
logger.info("插入失败")
}
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 16:23:34.765 INFO 2736 --- [ main] com.rtxtitanv.JpaTest : sql插入一条记录开始
Hibernate: insert into user(uid,user_name,pass_word,nick_name,age,email,tel,reg_time) values(?,?,?,?,?,?,?,?)
2020-01-21 16:23:34.818 INFO 2736 --- [ main] com.rtxtitanv.JpaTest : sql插入一条记录结束
2020-01-21 16:23:34.818 INFO 2736 --- [ main] com.rtxtitanv.JpaTest : 插入成功
（2）查询测试
①JPQL语句按年龄批量查询名称并排序
在UserRepository接口中添加自定义函数findNameByAgeIn：
/**
* 使用JPQL语句按年龄批量查询名称并排序
* 只查询用户名和昵称
* @param ageList 年龄列表
* @param sort 排序参数
* @return List<NameOnly> 名称列表（projections接口NameOnly只包含用户名和昵称）
*/
// :为占位符，#{#Object} SpEL表达式将对象传递进sql语句
// 此处有坑，注意一定要加别名，并且与实体类的实例域名一致，否则NameOnly实现中封装不进数据，查出来的值为null
@Query("select u.userName as userName, u.nickName as nickName from User u where u.age in :#{#ageList}")
fun findNameByAgeIn(ageList: List<Int>? = null, sort: Sort? = null): List<NameOnly>
JPQL按年龄批量查询名称并排序测试函数：
/**
* 使用JPQL语句按年龄批量查询名称并排序
*/
@Test
fun findNameByAgeInTest() {
val sort = Sort.by("userName").descending()
val ages = listOf(18, 24, 27)
logger.info("JPQL按年龄批量查询名称并按用户名降序开始")
val names = userRepository.findNameByAgeIn(ages, sort)
if (names.isEmpty()) {
logger.info("满足条件记录不存在")
} else {
names.forEach { name -> logger.info("userName: " + name.getUserName() + ", nickName: " + name.getNickName()) }
}
logger.info("JPQL按年龄批量查询名称并按用户名降序结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 16:38:54.067 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : JPQL按年龄批量查询名称并按用户名降序开始
Hibernate: select user0_.user_name as col_0_0_, user0_.nick_name as col_1_0_ from user user0_ where user0_.age in (? , ? , ?) order by col_0_0_ desc
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : userName: rty7ui81, nickName: asc
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : userName: qwer123, nickName: aaa
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : userName: erfgyhn, nickName: rw23ed
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : userName: asd6666, nickName: asc
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : userName: 2w4r, nickName: aaa
2020-01-21 16:38:54.132 INFO 3348 --- [ main] com.rtxtitanv.JpaTest : JPQL按年龄批量查询名称并按用户名降序结束
②sql语句按用户名和昵称模糊分页查询名称
在UserRepository接口添加自定义函数findNameByNickNameAndUserNameLike：
/**
* 使用sql语句按用户名和昵称模糊分页查询名称
* 只查询用户名和昵称
* @param nickName 昵称
* @param userName 用户名
* @param pageable 分页参数
* @return List<NameOnly> 名称列表（projections接口NameOnly只包含用户名和昵称）
*/
// @Param注解的值与:占位符后的字符串一致，用于参数传递
@Query(value = "select u.nick_name as nickName, u.user_name as userName from user u where u.nick_name like %:nickname% and u.user_name like %:username%",
countQuery = "select count(u.nick_name) from user u where u.nick_name like %:nickname% and u.user_name like %:username%", nativeQuery = true)
fun findNameByNickNameAndUserNameLike(@Param("nickname") nickName: String? = null, @Param("username") userName: String? = null, pageable: Pageable? = null): Page<NameOnly>
sql语句按用户名和昵称模糊分页查询名称测试函数：
/**
* 使用sql语句按用户名和昵称模糊分页查询名称
*/
@Test
fun findNameByNickNameAndUserNameLikeTest() {
val pageable: Pageable = PageRequest.of(1, 2)
logger.info("sql语句按用户名和昵称模糊分页查询名称开始")
val names = userRepository.findNameByNickNameAndUserNameLike("a", "r", pageable)
if (names.isEmpty) {
logger.info("满足条件的查询记录不存在")
} else {
logger.info("总条数: ${names.totalElements}")
logger.info("总页数: ${names.totalPages}")
names.content.forEach { name -> logger.info("userName: " + name.getUserName() + ", nickName: " + name.getNickName()) }
}
logger.info("sql语句按用户名和昵称模糊分页查询名称结束")
}
控制台打印的日志及自动生成的sql语句如下：
2020-01-21 16:52:44.015 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : sql语句按用户名和昵称模糊分页查询名称开始
Hibernate: select u.nick_name as nickName, u.user_name as userName from user u where u.nick_name like ? and u.user_name like ? limit ?, ?
Hibernate: select count(u.nick_name) from user u where u.nick_name like ? and u.user_name like ?
2020-01-21 16:52:44.099 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : 总条数: 5
2020-01-21 16:52:44.099 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : 总页数: 3
2020-01-21 16:52:44.100 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : userName: 23erg, nickName: aaa
2020-01-21 16:52:44.100 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : userName: 2w4r, nickName: aaa
2020-01-21 16:52:44.100 INFO 14680 --- [ main] com.rtxtitanv.JpaTest : sql语句按用户名和昵称模糊分页查询名称结束
（3）修改测试
①JPQL语句根据年龄更新昵称
在UserRepository接口添加自定义函数updateUserNameByAge：
/**
* 使用JPQL语句根据年龄更新昵称
* @param nickName 昵称
* @param age 年龄
* @return 更新的记录数
*/
@Query("update User u set u.nickName = ?1 where u.age = ?2")
@Modifying
@Transactional(rollbackFor = [Exception::class])
fun updateUserNameByAge(nickName: String? = null, age: Int? = null): Int
JPQL语句根据年龄更新昵称测试函数：
/**
* 使用JPQL语句根据年龄更新昵称
*/
@Test
fun updateUserNameByAgeTest() {
logger.info("JPQL根据年龄更新昵称开始")
val result = userRepository.updateUserNameByAge("nickname-01", 18)
logger.info("更新了$result" + "条记录")
logger.info("JPQL根据年龄更新昵称结束")
}
控制台打印的日志及自动生成的sql语句如下，刷新user表发现更新成功。
2020-01-21 17:00:53.012 INFO 13564 --- [ main] com.rtxtitanv.JpaTest : JPQL根据年龄更新昵称开始
Hibernate: update user set nick_name=? where age=?
2020-01-21 17:00:53.050 INFO 13564 --- [ main] com.rtxtitanv.JpaTest : 更新了2条记录
2020-01-21 17:00:53.051 INFO 13564 --- [ main] com.rtxtitanv.JpaTest : JPQL根据年龄更新昵称结束
②sql语句更新单条记录
在UserRepository接口添加自定义函数updateUser：
/**
* 使用sql语句更新单条记录
* @param user 用户对象
* @return
*/
// 使用SpEL表达式传递对象属性至sql语句
@Query(value = "update user u set u.uid = :#{#user.uid}, u.user_name = :#{#user.userName}," +
" u.pass_word = :#{#user.passWord}, u.nick_name = :#{#user.nickName}," +
" u.email = :#{#user.email}, u.age = :#{#user.age}," +
" u.tel = :#{#user.tel}, u.reg_time = :#{#user.regTime} where u.id = :#{#user.id}", nativeQuery = true)
@Modifying
@Transactional(rollbackFor = [Exception::class])
fun updateUser(@Param("user") user: User): Int
sql语句更新单条记录测试函数：
/**
* 使用sql语句更新单条记录
*/
@Test
fun updateUserTest() {
val userOptional = userRepository.findById(1L)
if (!userOptional.isPresent) {
logger.info("查询用户不存在")
} else {
val user = userOptional.get()
user.passWord = "6543215622"
user.nickName = "ava33"
logger.info("sql语句更新单条记录开始")
userRepository.updateUser(user)
logger.info("sql语句更新单条记录结束")
}
}
控制台打印的日志及自动生成的sql语句如下，刷新user表发现更新成功。
2020-01-21 17:09:44.563 INFO 10764 --- [ main] com.rtxtitanv.JpaTest : sql语句更新单条记录开始
Hibernate: update user u set u.uid = ?, u.user_name = ?, u.pass_word = ?, u.nick_name = ?, u.email = ?, u.age = ?, u.tel = ?, u.reg_time = ? where u.id = ?
2020-01-21 17:09:44.620 INFO 10764 --- [ main] com.rtxtitanv.JpaTest : sql语句更新单条记录结束
（4）删除测试
在UserRepository接口添加自定义函数deleteInBacthById：
/**
* 使用JPQL语句根据id批量删除
* @param ids
* @return 删除记录数
*/
// #{#entityName} 默认为实体类的名称，如果使用了@Entity(name = "xxx")来注解实体类
// #{#entityName}的值为xxx
@Query("delete from #{#entityName} u where u.id in ?1")
@Modifying
@Transactional(rollbackFor = [Exception::class])
fun deleteInBacthById(ids: List<Long>? = null): Int
JPQL语句根据id批量删除测试函数：
/**
* 使用JPQL语句根据id批量删除
*/
@Test
fun deleteInBacthByIdTest() {
val ids = listOf(1L, 3L, 6L, 9L)
logger.info("使用JPQL语句根据id批量删除开始")
val result = userRepository.deleteInBacthById(ids)
logger.info("总共删除了$result" + "条记录")
logger.info("使用JPQL语句根据id批量删除结束")
}
控制台打印的日志及自动生成的sql语句如下，刷新user表发现成功删除4条记录。
2020-01-21 17:14:17.711 INFO 1456 --- [ main] com.rtxtitanv.JpaTest : 使用JPQL语句根据id批量删除开始
Hibernate: delete from user where id in (? , ? , ? , ?)
2020-01-21 17:14:17.770 INFO 1456 --- [ main] com.rtxtitanv.JpaTest : 总共删除了4条记录
2020-01-21 17:14:17.770 INFO 1456 --- [ main] com.rtxtitanv.JpaTest : 使用JPQL语句根据id批量删除结束
4.Specifications动态查询
SpringDataJpa提供了JpaSpecificationExecutor接口，该接口主要用于动态的条件查询，支持分页和排序。
以下是JpaSpecificationExecutor接口中的方法，Specification接口主要用于封装查询条件。
public interface JpaSpecificationExecutor<T> {
// 根据条件查询返回单个对象
Optional<T> findOne(@Nullable Specification<T> var1);
// 根据条件查询返回List集合
List<T> findAll(@Nullable Specification<T> var1);
// 根据条件分页查询
Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
// 根据条件排序查询
List<T> findAll(@Nullable Specification<T> var1, Sort var2);
// 根据条件统计查询
long count(@Nullable Specification<T> var1);
}
（1）条件查询
①单条件查询
/**
* JpaSpecificationExecutor单条件查询测试
*/
@Test
fun findByOneConditionTest() {
logger.info("单条件查询测试开始")
/**
* root：查询的根对象，可以通过get方法获取实体属性
* criteriaQuery：代表一个顶层查询对象，可以构建自定义查询，包含select、where、orderBy、groupBy等
* criteriaBuilder：查询条件构造器
*/
val userList = userRepository.findAll { root, criteriaQuery, criteriaBuilder ->
// user_name = "qwer123"
val predicate1 = criteriaBuilder.equal(root.get<String>("userName"), "qwer123")
// email like %er%
val predicate2 = criteriaBuilder.like(root.get<String>("email"), "%er%")
// age between 15 and 25
val predicate3 = criteriaBuilder.between(root.get<Int>("age"), 15, 25)
// age >= 18
val predicate4 = criteriaBuilder.ge(root.get<Int>("age"), 18)
// age <= 25
val predicate5 = criteriaBuilder.le(root.get<Int>("age"), 25)
// 从lambda表达式中返回一个值 等价于 return@findAll predicate1
predicate5
}
if (userList.isEmpty()) {
logger.info("没有满足条件的数据")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("单条件查询测试结束")
}
控制台打印的日志及自动生成的sql语句（以age <= 25为例）如下：
2020-01-21 17:17:58.191 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : 单条件查询测试开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.age<=25
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=1, uid=296ebeb46acd49aca54f0d5a5a1257c3, userName=qwer123, passWord=123456, nickName=aaa, age=24, email=qwer123@ss.com, tel=13915176512, regTime=2020-01-21 17:17:25, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=2, uid=e6c133e338bb4b7c857be76104986acb, userName=asd6666, passWord=qw23ss, nickName=asc, age=18, email=asd6666@ss.com, tel=15736226963, regTime=2020-01-21 17:17:25, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=3, uid=179a91c205f84416b39347d714516c95, userName=tgh3er2, passWord=11111, nickName=r123er, age=22, email=advx@ss.com, tel=18956929863, regTime=2020-01-21 17:17:25, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=5, uid=7fc652d011e8448e99aee948f1af9187, userName=rty7ui81, passWord=a2ef56.a, nickName=asc, age=18, email=sdrtyhui@ss.com, tel=15966358996, regTime=2020-01-21 17:17:25, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=6, uid=8960a15b37d0552aa84483e24fc57b80, userName=23erg, passWord=2234rf, nickName=aaa, age=15, email=qadfgyty@ss.com, tel=16895635789, regTime=2020-01-21 17:17:33, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=8, uid=7669890a99c1581483edf72fa48d702c, userName=2w4r, passWord=3345tt, nickName=aaa, age=24, email=qert23@ss.com, tel=19725689756, regTime=2020-01-21 17:17:33, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : User(id=9, uid=9a512c6ffe01565abb619e1199002603, userName=12er, passWord=134rty, nickName=aa23e54, age=23, email=qwer5@ss.com, tel=13858963144, regTime=2020-01-21 17:17:33, addr=null)
2020-01-21 17:17:58.243 INFO 8296 --- [ main] com.rtxtitanv.JpaTest : 单条件查询测试结束
②多条件查询
用于封装查询条件的类：
package com.rtxtitanv.model.query
/**
* @name com.rtxtitanv.model.query.UserQuery
* @description 封装用户查询条件类
* @author rtxtitanv
* @date 2020/1/17 23:55
* @version 1.0.0
*/
data class UserQuery(var idQuery: Long? = null,
var userNameQuery: String? = null,
var nickNameQuery: String? = null,
var passWordQuery: String? = null,
var emailQuery: String? = null,
var telQuery: String? = null,
var minAgeQuery: Int? = null,
var maxAgeQuery: Int? = null,
var idsQuery: List<Long>? = null,
var agesQuery: List<Int>? = null)
动态拼接多条件查询测试函数：
/**
* JpaSpecificationExecutor动态拼接多条件查询测试
* 拼接方式1：每个条件均为and连接
* 实际开发中可以根据实际的动态条件灵活处理
*/
@Test
fun findByConditionsTest() {
// 手动模拟查询条件
// val userQuery : UserQuery? = null
val userQuery = UserQuery()
val ids = listOf(1L, 2L, 5L)
val ages = listOf(18, 24)
userQuery.userNameQuery = "r"
userQuery.nickNameQuery = "a"
userQuery.minAgeQuery = null
userQuery.maxAgeQuery = 25
userQuery.idQuery = null
userQuery.telQuery = "135"
userQuery.emailQuery = "rt"
userQuery.passWordQuery = "123"
userQuery.idsQuery = ids
userQuery.agesQuery = ages
logger.info("动态拼接多条件查询测试开始")
val userList = userRepository.findAll { root, criteriaQuery, criteriaBuilder ->
// 如果userQuery为空或者userQuery所有属性均为空会自动生成where 1 = 1
val predicate = criteriaBuilder.conjunction()
// 如果userQuery为空或者userQuery所有属性均为空会查询所有记录
if (userQuery != null) {
if (userQuery.idQuery != null) {
// notEqual查询 !!：非空断言运算符，将任何值转换为非空类型，若该值为空则抛出异常
predicate.expressions.add(criteriaBuilder.notEqual(root.get<Long>("id"), userQuery.idQuery!!))
}
if (!StringUtils.isNullOrEmpty(userQuery.userNameQuery)) {
// like查询
predicate.expressions.add(criteriaBuilder.like(root.get<String>("userName"), "%${userQuery.userNameQuery}%"))
}
if (!StringUtils.isNullOrEmpty(userQuery.nickNameQuery)) {
// notLike查询
predicate.expressions.add(criteriaBuilder.notLike(root.get<String>("nickName"), "%${userQuery.nickNameQuery}%"))
}
if (!StringUtils.isNullOrEmpty(userQuery.passWordQuery)) {
// equal查询
predicate.expressions.add(criteriaBuilder.equal(root.get<String>("passWord"), userQuery.passWordQuery))
}
if (!StringUtils.isNullOrEmpty(userQuery.emailQuery)) {
// notEqual查询
predicate.expressions.add(criteriaBuilder.notEqual(root.get<String>("email"), userQuery.emailQuery))
}
if (!StringUtils.isNullOrEmpty(userQuery.telQuery)) {
// like查询
predicate.expressions.add(criteriaBuilder.like(root.get<String>("tel"), "%${userQuery.telQuery}"))
}
if (userQuery.minAgeQuery != null && userQuery.maxAgeQuery != null) {
// between查询
predicate.expressions.add(criteriaBuilder.between(root.get<Int>("age"), userQuery.minAgeQuery!!, userQuery.maxAgeQuery!!))
} else if (userQuery.minAgeQuery != null) {
// >=查询 gt为>
predicate.expressions.add(criteriaBuilder.ge(root.get<Int>("age"), userQuery.minAgeQuery!!))
} else if (userQuery.maxAgeQuery != null) {
// <=查询 lt为<
predicate.expressions.add(criteriaBuilder.le(root.get<Int>("age"), userQuery.maxAgeQuery!!))
}
if (userQuery.idsQuery != null && !userQuery.idsQuery!!.isEmpty()) {
// in 批量查询 `in`：in在Kotlin为关键字用反引号转义
predicate.expressions.add(criteriaBuilder.and(root.get<Long>("id").`in`(userQuery.idsQuery!!)))
}
if (userQuery.agesQuery != null && !userQuery.agesQuery!!.isEmpty()) {
predicate.expressions.add(criteriaBuilder.and(root.get<Int>("age").`in`(userQuery.agesQuery!!)))
}
}
predicate
}
if (userList.isEmpty()) {
logger.info("查询不到满足条件的数据")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("动态拼接多条件查询测试结束")
}
所有查询条件都不为空时自动生成的sql语句如下：
2020-01-21 17:24:35.784 INFO 6896 --- [ main] com.rtxtitanv.JpaTest : 动态拼接多条件查询测试开始
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where user0_.id<>3 and (user0_.user_name like ?) and (user0_.nick_name not like ?) and user0_.pass_word=? and user0_.email<>? and (user0_.tel like ?) and (user0_.age between 18 and 27) and (user0_.id in (1 , 2 , 5)) and (user0_.age in (18 , 24))
2020-01-21 17:24:35.834 INFO 6896 --- [ main] com.rtxtitanv.JpaTest : 查询不到满足条件的数据
2020-01-21 17:24:35.834 INFO 6896 --- [ main] com.rtxtitanv.JpaTest : 动态拼接多条件查询测试结束
查询条件为空时生成where 1=1如下：
Hibernate: select user0_.id as id1_5_, user0_.age as age2_5_, user0_.email as email3_5_, user0_.nick_name as nick_nam4_5_, user0_.pass_word as pass_wor5_5_, user0_.reg_time as reg_time6_5_, user0_.tel as tel7_5_, user0_.uid as uid8_5_, user0_.user_name as user_nam9_5_ from user user0_ where 1=1
动态拼接多条件查询测试函数2：
/**
* JpaSpecificationExecutor动态拼接多条件查询测试
* 拼接方式2：与方式1效果相同
*/
@Test
fun findByConditionsTest2() {
// 手动模拟查询条件
// val userQuery : UserQuery? = null
val userQuery = UserQuery()
val ids = listOf(1L, 2L, 5L)
val ages = listOf(18, 24)
userQuery.userNameQuery = "r"
userQuery.nickNameQuery = "a"
userQuery.minAgeQuery = null
userQuery.maxAgeQuery = 25
userQuery.idQuery = 6L
userQuery.telQuery = "135"
userQuery.emailQuery = "rt"
userQuery.passWordQuery = "123"
userQuery.idsQuery = ids
userQuery.agesQuery = ages
logger.info("动态拼接多条件查询测试开始")
val userList = userRepository.findAll { root, criteriaQuery, criteriaBuilder ->
// sql语句会自动生成where 1 = 1
val predicates = mutableListOf(criteriaBuilder.conjunction())
// 如果userQuery为空或者userQuery所有属性均为空会查询所有记录
if (userQuery != null) {
if (userQuery.idQuery != null) {
predicates.add(criteriaBuilder.notEqual(root.get<Long>("id"), userQuery.idQuery!!))
}
if (!StringUtils.isNullOrEmpty(userQuery.userNameQuery)) {
predicates.add(criteriaBuilder.like(root.get<String>("userName"), "%${userQuery.userNameQuery}%"))
}
if (!StringUtils.isNullOrEmpty(userQuery.nickNameQuery)) {
predicates.add(criteriaBuilder.notLike(root.get<String>("nickName"), "%${userQuery.nickNameQuery}%"))
}
if (!StringUtils.isNullOrEmpty(userQuery.passWordQuery)) {
predicates.add(criteriaBuilder.equal(root.get<String>("passWord"), userQuery.passWordQuery))
}
if (!StringUtils.isNullOrEmpty(userQuery.emailQuery)) {
predicates.add(criteriaBuilder.notEqual(root.get<String>("email"), userQuery.emailQuery))
}
if (!StringUtils.isNullOrEmpty(userQuery.telQuery)) {
predicates.add(criteriaBuilder.like(root.get<String>("tel"), "%${userQuery.telQuery}"))
}
if (userQuery.minAgeQuery != null && userQuery.maxAgeQuery != null) {
predicates.add(criteriaBuilder.between(root.get<Int>("age"), userQuery.minAgeQuery!!, userQuery.maxAgeQuery!!))
} else if (userQuery.minAgeQuery != null) {
predicates.add(criteriaBuilder.ge(root.get<Int>("age"), userQuery.minAgeQuery!!))
} else if (userQuery.maxAgeQuery != null) {
predicates.add(criteriaBuilder.le(root.get<Int>("age"), userQuery.maxAgeQuery!!))
}
if (userQuery.idsQuery != null && !userQuery.idsQuery!!.isEmpty()) {
predicates.add(criteriaBuilder.and(root.get<Long>("id").`in`(userQuery.idsQuery!!)))
}
if (userQuery.agesQuery != null && !userQuery.agesQuery!!.isEmpty()) {
predicates.add(criteriaBuilder.and(root.get<Int>("age").`in`(userQuery.agesQuery!!)))
}
}
/**
* Predicate and(Predicate... var1)：and连接查询条件，可传入Predicate[] and连接数组内所有条件
* Predicate or(Predicate... var1)：or连接查询条件，可传入Predicate[] or连接数组内所有条件
* 此处所有条件均为and连接，如果有更复杂的条件连接，比如：
* where ((a=? and b=? and c=?) or (e=? and f=?) or g=?) and x=? and y=?
* 先and连接abc、ef，再or连接abc、ef和g作为一个条件z，再and连接xyz
*/
criteriaBuilder.and(*predicates.toTypedArray())
}
if (userList.isEmpty()) {
logger.info("查询不到满足条件的数据")
} else {
userList.forEach { user -> logger.info(user.toString()) }
}
logger.info("动态拼接多条件查询测试结束")
}
（2）分页查询
/**
* 多条件排序分页查询
*/
@Test
fun findByConditionsPageAndSortTest() {
// 模拟查询条件此处省略
// 定义排序规则 先按age降序，再按tel升序，再按id降序
val orderAge = Sort.Order(Sort.Direction.DESC, "age")
val orderTel = Sort.Order(Sort.Direction.ASC, "tel")
val orderId = Sort.Order(Sort.Direction.DESC, "id")
val sort = Sort.by(orderAge, orderTel, orderId)
// 定义分页参数，由于是测试第几页和每页记录数写死
val pageNum = 3
val pageSize = 3
// jpa分页从0页开始，页码需要-1
val pageable: Pageable = PageRequest.of(pageNum - 1, pageSize, sort)
logger.info("多条件排序分页查询测试开始")
val page = userRepository.findAll({ root, criteriaQuery, criteriaBuilder ->
// 此处封装查询条件省略
}, pageable)
if (page.isEmpty) {
logger.info("查询不到满足条件的数据")
} else {
logger.info("总条数：${page.totalElements}")
logger.info("总页数：${page.totalPages}")
page.content.forEach { user -> logger.info(user.toString()) }
}
logger.info("多条件排序分页查询测试结束")
}
自动生成的sql语句的排序及分页部分如下：
order by user0_.age desc, user0_.tel asc, user0_.id desc limit ?, ?
5.多表关联操作
（1）一对多关联
①创建实体类并配置映射
package com.rtxtitanv.model
import javax.persistence.*
/**
* @name com.rtxtitanv.model.Account
* @description 账户实体类 OneToMany 一对多的一方
* @author rtxtitanv
* @date 2020/1/20 21:08
* @version 1.0.0
*/
@Entity
@Table(name = "account")
class Account(@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "account_id")
var accountId: Long? = null,
@Column(name = "account_name", unique = true, nullable = false)
var accountName: String? = null,
@Column(name = "account_password", nullable = false)
var accountPassword: String? = null,
@Column(name = "account_alias")
var accountAlias: String? = null,
@Column(name = "account_addr")
var accountAddr: String? = null,
@Column(name = "account_tel", unique = true, nullable = false)
var accountTel: String? = null,
@Column(name = "account_rank", nullable = false)
var accountRank: Long? = null,
@Column(name = "ccount_location", nullable = false)
var accountLocation: String? = null,
@OneToMany(mappedBy = "account", // 引用在多方实体类中一方实体类对象名称，一方放弃维护外键关系
// CascadeType为级联设置：操作一个对象同时操作它的关联对象
// PERSIST：保存，REFRESH：刷新，MERGE：合并，REMOVE：删除
// FetchType：设置加载方式，LAZY：延迟加载，EAGER：立即加载
// orphanRemoval：是否使用孤儿删除，即在一方关联多方的集合中移除的多方记录将
// 成为孤儿，没有与一方任何一条记录关联，此时会自动删除这些多方记录，true：使用，false：不使用
cascade = [CascadeType.PERSIST, CascadeType.REFRESH, CascadeType.MERGE, CascadeType.REMOVE],
fetch = FetchType.LAZY, orphanRemoval = true)
// 这里需要声明为可变集合MutableSet，MutableSet是一个带有来自MutableCollection的写操作接口的Set
var orders: MutableSet<Order> = HashSet()) {
override fun toString(): String {
return "Account{" +
"accountId=$accountId" +
", accountName='$accountName'" +
", accountPassword='$accountPassword'" +
", accountAlias='$accountAlias'" +
", accountAddr='$accountAddr'" +
", accountTel='$accountTel'" +
", accountRank=$accountRank" +
", accountLocation='$accountLocation'" +
"}"
}
}
package com.rtxtitanv.model
import javax.persistence.*
/**
* @name com.rtxtitanv.model.Order
* @description 订单实体类 ManyToOne 一对多的多方
* @author rtxtitanv
* @date 2020/1/20 21:08
* @version 1.0.0
*/
@Entity
@Table(name = "orders") // 注意表名不能为order，自动建表时会报错
class Order(@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "orders_id")
var orderId: Long? = null,
@Column(name = "orders_name", nullable = false)
var orderName: String? = null,
@Column(name = "orders_description")
var orderDescription: String? = null,
@Column(name = "orders_status", nullable = false)
var orderStatus: String? = null,
@Column(name = "orders_total_price", nullable = false)
var orderTotalPrice: String? = null,
@Column(name = "orders_item_count", nullable = false)
var orderItemCount: Int? = null,
@Column(name = "orders_addr", nullable = false)
var orderAddr: String? = null,
// FetchType.EAGER 立即加载
@ManyToOne(cascade = [CascadeType.PERSIST], fetch = FetchType.EAGER)
// 定义主键字段和外键字段对应关系，name：外键字段名称，nullable：是否允许为空
@JoinColumn(name = "orders_account_id", nullable = false)
var account: Account? = null) {
override fun toString(): String {
return "Order{" +
"orderId=$orderId" +
", orderName='$orderName'" +
", orderDescription='$orderDescription'" +
", orderStatus='$orderStatus'" +
", orderTotalPrice='$orderTotalPrice'" +
", orderItemCount=$orderItemCount" +
", orderAddr='$orderAddr'" +
"}"
}
}
②新增测试
新建AccountRepository和OrderRepository接口：
package com.rtxtitanv.repository
import com.rtxtitanv.model.Account
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor
/**
* @name com.rtxtitanv.repository.AccountRepository
* @description AccountRepository接口用于操作account表
* @author rtxtitanv
* @date 2020/1/20 22:30
* @version 1.0.0
*/
interface AccountRepository : JpaRepository<Account, Long>, JpaSpecificationExecutor<Account>
---------------------------------------------------------------------------------------------------------------------------------
package com.rtxtitanv.repository
import com.rtxtitanv.model.Order
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor
/**
* @name com.rtxtitanv.repository.OrderRepository
* @description OrderRepository接口用于操作orders表
* @author rtxtitanv
* @date 2020/1/20 22:32
* @version 1.0.0
*/
interface OrderRepository : JpaRepository<Order, Long>, JpaSpecificationExecutor<Order>
测试类中注入AccountRepository和OrderRepository：
@Autowired
private lateinit var accountRepository: AccountRepository
@Autowired
private lateinit var orderRepository: OrderRepository
如果实体类使用了Kotlin的数据类，编译器会从主构造函数中声明的所有属性自动生成equals、hashCode和toString等函数，在生成时会出现循环比较两类中的hashcode导致栈内存溢出，所以在多表关联时创建实体类不使用数据类，自己手写toString函数，如果需要用到hashCode函数时需要自己手写。
java.lang.StackOverflowError
at com.rtxtitanv.model.Account.hashCode(Account.kt)
at com.rtxtitanv.model.Order.hashCode(Order.kt)
at java.util.AbstractSet.hashCode(AbstractSet.java:126)
at com.rtxtitanv.model.Account.hashCode(Account.kt)
at com.rtxtitanv.model.Order.hashCode(Order.kt)
at java.util.AbstractSet.hashCode(AbstractSet.java:126)
保存测试：
/**
* 多表关联一对多保存
*/
@Transactional // 开启事务支持
@Rollback(false) // 设置不回滚
@Test
fun oneToManySaveTest() {
// 封装保存数据
val account = Account()
account.accountName = "rtx_titan_v"
account.accountPassword = "123456"
account.accountAlias = "cdf_dv"
account.accountRank = 7L
account.accountTel = "13313313311"
account.accountLocation = "china"
account.accountAddr = "北京西城区"
val order1 = Order()
val order2 = Order()
val order3 = Order()
order1.orderName = "索尼ps5次世代游戏主机"
order1.orderDescription = "索尼ps5,无索不玩"
order1.orderStatus = "等待卖家发货"
order1.orderTotalPrice = "5000"
order1.orderItemCount = 1
order1.orderAddr = "北京西城区"
order2.orderName = "XBox Edit 2代"
order2.orderDescription = "微软精英2代,无线蓝牙手柄国"
order2.orderStatus = "卖家已发货"
order2.orderTotalPrice = "1390"
order2.orderItemCount = 1
order2.orderAddr = "北京西城区"
order3.orderName = "XBox Edit 3代"
order3.orderDescription = "微软精英3代,无线蓝牙手柄国行"
order3.orderStatus = "卖家已发货"
order3.orderTotalPrice = "1390"
order3.orderItemCount = 1
order3.orderAddr = "北京西城区"
// 关联操作
account.orders.add(order1)
account.orders.add(order2)
order1.account = account
order2.account = account
// 保存操作
logger.info("保存开始")
// // 由于account和order实体设置了级联保存
// // 此处任意保存其中一个order，后台会自动保存order1、order2和关联的account
// orderRepository.save(order1)
// orderRepository.save(order2)
// 保存account，会自动保存关联的order1和order2
accountRepository.save(account)
// // 此处为account关联order1和order2保存之后再关联一个新的order3保存
// account.orders.add(order3)
// order3.account = account
// orderRepository.save(order3)
logger.info("保存结束")
}
控制台打印的日志和自动生成的sql如下，navicat查看保存成功。
2020-01-21 21:19:12.256 INFO 9724 --- [ main] com.rtxtitanv.JpaTest : 保存开始
Hibernate: insert into account (account_addr, account_alias, ccount_location, account_name, account_password, account_rank, account_tel) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into orders (orders_account_id, orders_addr, orders_description, orders_item_count, orders_name, orders_status, orders_total_price) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into orders (orders_account_id, orders_addr, orders_description, orders_item_count, orders_name, orders_status, orders_total_price) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into orders (orders_account_id, orders_addr, orders_description, orders_item_count, orders_name, orders_status, orders_total_price) values (?, ?, ?, ?, ?, ?, ?)
2020-01-21 21:19:12.313 INFO 9724 --- [ main] com.rtxtitanv.JpaTest : 保存结束
③查询测试
/**
* 多表关联一对多查询
*/
//解决延迟加载时Session已关闭出现的LazyInitializationException
@Transactional
@Rollback(false)
@Test
fun oneToManyFindTest() {
// 查询一个账户，并获取账户的所有订单
val accountOptional = accountRepository.findById(1L)
if (!accountOptional.isPresent) {
logger.info("账户不存在")
} else {
val account = accountOptional.get()
logger.info("----------------账户信息----------------")
val accountInf = account.toString()
logger.info(accountInf)
logger.info("----------------订单信息----------------")
account.orders.forEach { order -> logger.info(order.toString()) }
}
// 查询一个订单，并获取订单所对应的账户
val orderOptional = orderRepository.findById(1L)
if (!orderOptional.isPresent) {
logger.info("订单不存在")
} else {
val order = orderOptional.get()
logger.info("----------------订单信息----------------")
val orderInfo = order.toString()
logger.info(orderInfo)
logger.info("----------------账户信息----------------")
val accountInfo = order.account.toString()
logger.info(accountInfo)
}
}
控制台打印的日志及自动生成的sql语句如下：
Hibernate: select account0_.account_id as account_1_0_0_, account0_.account_addr as account_2_0_0_, account0_.account_alias as account_3_0_0_, account0_.ccount_location as ccount_l4_0_0_, account0_.account_name as account_5_0_0_, account0_.account_password as account_6_0_0_, account0_.account_rank as account_7_0_0_, account0_.account_tel as account_8_0_0_ from account account0_ where account0_.account_id=?
2020-01-21 21:21:30.675 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : ----------------账户信息----------------
2020-01-21 21:21:30.675 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Account{accountId=1, accountName='rtx_titan_v', accountPassword='123456', accountAlias='cdf_dv', accountAddr='北京西城区', accountTel='13313313311', accountRank=7, accountLocation='china'}
2020-01-21 21:21:30.675 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : ----------------订单信息----------------
Hibernate: select orders0_.orders_account_id as orders_a8_2_0_, orders0_.orders_id as orders_i1_2_0_, orders0_.orders_id as orders_i1_2_1_, orders0_.orders_account_id as orders_a8_2_1_, orders0_.orders_addr as orders_a2_2_1_, orders0_.orders_description as orders_d3_2_1_, orders0_.orders_item_count as orders_i4_2_1_, orders0_.orders_name as orders_n5_2_1_, orders0_.orders_status as orders_s6_2_1_, orders0_.orders_total_price as orders_t7_2_1_ from orders orders0_ where orders0_.orders_account_id=?
2020-01-21 21:21:30.682 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Order{orderId=1, orderName='XBox Edit 2代', orderDescription='微软精英2代,无线蓝牙手柄国', orderStatus='卖家已发货', orderTotalPrice='1390', orderItemCount=1, orderAddr='北京西城区'}
2020-01-21 21:21:30.682 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Order{orderId=3, orderName='XBox Edit 3代', orderDescription='微软精英3代,无线蓝牙手柄国行', orderStatus='卖家已发货', orderTotalPrice='1390', orderItemCount=1, orderAddr='北京西城区'}
2020-01-21 21:21:30.682 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Order{orderId=2, orderName='索尼ps5次世代游戏主机', orderDescription='索尼ps5,无索不玩', orderStatus='等待卖家发货', orderTotalPrice='5000', orderItemCount=1, orderAddr='北京西城区'}
2020-01-21 21:21:30.683 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : ----------------订单信息----------------
2020-01-21 21:21:30.683 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Order{orderId=1, orderName='XBox Edit 2代', orderDescription='微软精英2代,无线蓝牙手柄国', orderStatus='卖家已发货', orderTotalPrice='1390', orderItemCount=1, orderAddr='北京西城区'}
2020-01-21 21:21:30.683 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : ----------------账户信息----------------
2020-01-21 21:21:30.683 INFO 11180 --- [ main] com.rtxtitanv.JpaTest : Account{accountId=1, accountName='rtx_titan_v', accountPassword='123456', accountAlias='cdf_dv', accountAddr='北京西城区', accountTel='13313313311', accountRank=7, accountLocation='china'}
④修改测试
/**
* 多表关联一对多更新
*/
@Transactional
@Rollback(false)
@Test
fun oneToManyUpdateTest() {
// 通过一方更新多方的记录
val accountOpt = accountRepository.findById(1L)
if (!accountOpt.isPresent) {
logger.info("账号不存在")
} else {
accountOpt.get().orders.forEach { order -> kotlin.run {
if (order.orderId == 1L) {
order.orderTotalPrice = "1590"
order.orderAddr = "重庆渝北区"
}
} }
}
// 通过多方更新一方记录
val orderOpt = orderRepository.findById(1L)
if (!orderOpt.isPresent) {
logger.info("订单不存在")
} else {
orderOpt.get().account!!.accountAddr = "重庆江北区"
orderOpt.get().account!!.accountRank = 8L
}
}
后台执行时自动生成的sql语句如下，navicat刷新表查看更新成功。
Hibernate: select account0_.account_id as account_1_0_0_, account0_.account_addr as account_2_0_0_, account0_.account_alias as account_3_0_0_, account0_.ccount_location as ccount_l4_0_0_, account0_.account_name as account_5_0_0_, account0_.account_password as account_6_0_0_, account0_.account_rank as account_7_0_0_, account0_.account_tel as account_8_0_0_ from account account0_ where account0_.account_id=?
Hibernate: select orders0_.orders_account_id as orders_a8_2_0_, orders0_.orders_id as orders_i1_2_0_, orders0_.orders_id as orders_i1_2_1_, orders0_.orders_account_id as orders_a8_2_1_, orders0_.orders_addr as orders_a2_2_1_, orders0_.orders_description as orders_d3_2_1_, orders0_.orders_item_count as orders_i4_2_1_, orders0_.orders_name as orders_n5_2_1_, orders0_.orders_status as orders_s6_2_1_, orders0_.orders_total_price as orders_t7_2_1_ from orders orders0_ where orders0_.orders_account_id=?
Hibernate: update account set account_addr=?, account_alias=?, ccount_location=?, account_name=?, account_password=?, account_rank=?, account_tel=? where account_id=?
Hibernate: update orders set orders_account_id=?, orders_addr=?, orders_description=?, orders_item_count=?, orders_name=?, orders_status=?, orders_total_price=? where orders_id=?
⑤删除测试
/**
* 多表关联一对多删除
*/
@Transactional
@Rollback(false)
@Test
fun oneToManyDeleteTest() {
// // account设置了级联删除，有多方关联时删除account将会同时删除关联的所有order
// // 如果account没有设置级联删除，有多方关联删除一方时，默认置外键为null，如果外键不允许
// // 为空,会报错，如果配置了放弃维护关联关系则不能删除
// accountRepository.deleteById(1L)
// // 只删除多方记录，直接删除会有问题，删除后再关联查询多方记录时没有生成删除语句
// orderRepository.deleteById(3L)
val accountOptional = accountRepository.findById(1L)
if (!accountOptional.isPresent) {
logger.info("账号不存在")
} else {
val account = accountOptional.get()
val orders = account.orders
logger.info("删除开始")
// 可变迭代器（扩展自Iterator的MutableIterator，在迭代时可以删除，插入和替换元素）遍历可变集合
val mutableIterator = orders.iterator()
while (mutableIterator.hasNext()) {
// 由于orphanRemoval = true，在一方关联多方的集合中移除多方，将会在多方删除这些记录
if (mutableIterator.next().orderId == 1L) {
mutableIterator.remove()
}
}
accountRepository.save(account)
logger.info("删除结束")
}
}
删除account表记录时同时删除orders表关联的记录的sql语句如下，navicat查看删除成功。
Hibernate: select account0_.account_id as account_1_0_0_, account0_.account_addr as account_2_0_0_, account0_.account_alias as account_3_0_0_, account0_.ccount_location as ccount_l4_0_0_, account0_.account_name as account_5_0_0_, account0_.account_password as account_6_0_0_, account0_.account_rank as account_7_0_0_, account0_.account_tel as account_8_0_0_ from account account0_ where account0_.account_id=?
Hibernate: select orders0_.orders_account_id as orders_a8_2_0_, orders0_.orders_id as orders_i1_2_0_, orders0_.orders_id as orders_i1_2_1_, orders0_.orders_account_id as orders_a8_2_1_, orders0_.orders_addr as orders_a2_2_1_, orders0_.orders_description as orders_d3_2_1_, orders0_.orders_item_count as orders_i4_2_1_, orders0_.orders_name as orders_n5_2_1_, orders0_.orders_status as orders_s6_2_1_, orders0_.orders_total_price as orders_t7_2_1_ from orders orders0_ where orders0_.orders_account_id=?
Hibernate: delete from orders where orders_id=?
Hibernate: delete from orders where orders_id=?
Hibernate: delete from orders where orders_id=?
Hibernate: delete from account where account_id=?
在一方关联多方的集合中移除多方自动生成的删除多方记录的语句如下，navicat查看删除成功。
2020-01-21 21:34:15.940 INFO 5956 --- [ main] com.rtxtitanv.JpaTest : 删除开始
Hibernate: select orders0_.orders_account_id as orders_a8_2_0_, orders0_.orders_id as orders_i1_2_0_, orders0_.orders_id as orders_i1_2_1_, orders0_.orders_account_id as orders_a8_2_1_, orders0_.orders_addr as orders_a2_2_1_, orders0_.orders_description as orders_d3_2_1_, orders0_.orders_item_count as orders_i4_2_1_, orders0_.orders_name as orders_n5_2_1_, orders0_.orders_status as orders_s6_2_1_, orders0_.orders_total_price as orders_t7_2_1_ from orders orders0_ where orders0_.orders_account_id=?
2020-01-21 21:34:15.954 INFO 5956 --- [ main] com.rtxtitanv.JpaTest : 删除结束
Hibernate: delete from orders where orders_id=?
（2）多对多关联
①创建实体类并配置映射
package com.rtxtitanv.model
import javax.persistence.*
/**
* @name com.rtxtitanv.model.Role
* @description 角色实体类
* @author rtxtitanv
* @date 2020/1/20 21:09
* @version 1.0.0
*/
@Entity
@Table(name = "role")
class Role(@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "role_id")
var roleId: Long? = null,
@Column(name = "role_name")
var roleName: String? = null,
@Column(name = "role_type")
var roleType: String? = null,
@ManyToMany(mappedBy = "roles", cascade = [CascadeType.PERSIST], fetch = FetchType.LAZY)
var menus: MutableSet<Menu> = HashSet()) {
override fun toString(): String {
return "Role{" +
"roleId=$roleId" +
", roleName='$roleName'" +
", roleType='$roleType'" +
"}"
}
}
package com.rtxtitanv.model
import javax.persistence.*
/**
* @name com.rtxtitanv.model.Menu
* @description 菜单实体类
* @author rtxtitanv
* @date 2020/1/20 21:47
* @version 1.0.0
*/
@Entity
@Table(name = "menu")
class Menu(@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "menu_id")
var menuId: Long? = null,
@Column(name = "menu_name")
var menuName: String? = null,
@Column(name = "menu_path")
var menuPath: String? = null,
@Column(name = "menu_hidden")
var menuHidden: Boolean? = null,
@Column(name = "menu_parent_id")
var menuParentId: Long? = null,
@Column(name = "menu_icon")
var menuIcon: String? = null,
@ManyToMany(cascade = [CascadeType.PERSIST], fetch = FetchType.LAZY)
// 配置中间表，joinColumns：中间表的外键字段关联当前实体类所对应表的主键字段
// inverseJoinColumns：中间表的外键字段关联对方表的主键字段
@JoinTable(name = "role_menu",
joinColumns = [JoinColumn(name = "menu_id")],
inverseJoinColumns = [JoinColumn(name = "role_id")])
var roles: MutableSet<Role> = HashSet()) {
override fun toString(): String {
return "Menu{" +
"menuId=$menuId" +
", menuName='$menuName'" +
", menuPath='$menuPath'" +
", menuHidden=$menuHidden" +
", menuParentId=$menuParentId" +
", menuIcon='$menuIcon" +
"}"
}
}
②新增测试
新建RoleRepository和MenuRepository并在测试类注入。
/**
* 多表关联多对多保存
*/
@Transactional
@Rollback(false)
@Test
fun manyToManySaveTest() {
val role1 = Role()
val role2 = Role()
val role3 = Role()
role1.roleName = "admin"
role1.roleType = "A"
role2.roleName = "user"
role2.roleType = "U"
role3.roleName = "system"
role3.roleType = "S"
val menu1 = Menu()
val menu2 = Menu()
val menu3 = Menu()
menu1.menuName = "导航栏"
menu1.menuHidden = false
menu1.menuPath = "http://ascd/sddf/kk.img"
menu2.menuName = "下拉框"
menu2.menuParentId = 1L
menu2.menuIcon = "下拉图标"
menu3.menuName = "确认按钮"
menu3.menuIcon = "方块"
menu3.menuPath = "http://123.123.44.56/xx"
// 关联操作
role1.menus.add(menu1)
role1.menus.add(menu3)
role2.menus.add(menu2)
role2.menus.add(menu3)
role3.menus.add(menu1)
role3.menus.add(menu2)
menu1.roles.add(role1)
menu1.roles.add(role3)
menu2.roles.add(role2)
menu2.roles.add(role3)
menu3.roles.add(role1)
menu3.roles.add(role2)
// 保存，role和menu均设置了级联保存
menuRepository.save(menu1)
// roleRepository.save(role1) 和save menu一样
}
自动生成的sql语句如下，navicat查看保存成功。
Hibernate: insert into menu (menu_hidden, menu_icon, menu_name, menu_parent_id, menu_path) values (?, ?, ?, ?, ?)
Hibernate: insert into role (role_name, role_type) values (?, ?)
Hibernate: insert into menu (menu_hidden, menu_icon, menu_name, menu_parent_id, menu_path) values (?, ?, ?, ?, ?)
Hibernate: insert into role (role_name, role_type) values (?, ?)
Hibernate: insert into menu (menu_hidden, menu_icon, menu_name, menu_parent_id, menu_path) values (?, ?, ?, ?, ?)
Hibernate: insert into role (role_name, role_type) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
③查询测试
/**
* 多表关联多对多查询
*/
@Transactional
@Rollback(false)
@Test
fun manyToManyFindTest() {
// 查询一个角色，并获取角色的所有菜单
val roleOptional = roleRepository.findById(1L)
if (!roleOptional.isPresent) {
logger.info("角色不存在")
} else {
val role = roleOptional.get()
logger.info("----------------角色信息----------------")
val roleInf = role.toString()
logger.info(roleInf)
logger.info("----------------菜单信息----------------")
role.menus.forEach { menu -> logger.info(menu.toString()) }
}
// 查询一个菜单，并获取菜单的角色
val menuOptional = menuRepository.findById(1L)
if (!menuOptional.isPresent) {
logger.info("菜单不存在")
} else {
val menu = menuOptional.get()
logger.info("----------------菜单信息----------------")
val menuInfo = menu.toString()
logger.info(menuInfo)
logger.info("----------------角色信息----------------")
menu.roles.forEach { role -> logger.info(role.toString()) }
}
}
控制台打印的日志及自动生成的sql语句如下：
Hibernate: select role0_.role_id as role_id1_3_0_, role0_.role_name as role_nam2_3_0_, role0_.role_type as role_typ3_3_0_ from role role0_ where role0_.role_id=?
2020-01-21 21:41:34.680 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : ----------------角色信息----------------
2020-01-21 21:41:34.680 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Role{roleId=1, roleName='admin', roleType='A'}
2020-01-21 21:41:34.680 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : ----------------菜单信息----------------
Hibernate: select menus0_.role_id as role_id2_4_0_, menus0_.menu_id as menu_id1_4_0_, menu1_.menu_id as menu_id1_1_1_, menu1_.menu_hidden as menu_hid2_1_1_, menu1_.menu_icon as menu_ico3_1_1_, menu1_.menu_name as menu_nam4_1_1_, menu1_.menu_parent_id as menu_par5_1_1_, menu1_.menu_path as menu_pat6_1_1_ from role_menu menus0_ inner join menu menu1_ on menus0_.menu_id=menu1_.menu_id where menus0_.role_id=?
2020-01-21 21:41:34.688 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Menu{menuId=1, menuName='导航栏', menuPath='http://ascd/sddf/kk.img', menuHidden=false, menuParentId=null, menuIcon='null}
2020-01-21 21:41:34.688 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Menu{menuId=2, menuName='确认按钮', menuPath='http://123.123.44.56/xx', menuHidden=null, menuParentId=null, menuIcon='方块}
2020-01-21 21:41:34.689 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : ----------------菜单信息----------------
2020-01-21 21:41:34.689 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Menu{menuId=1, menuName='导航栏', menuPath='http://ascd/sddf/kk.img', menuHidden=false, menuParentId=null, menuIcon='null}
2020-01-21 21:41:34.689 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : ----------------角色信息----------------
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
2020-01-21 21:41:34.691 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Role{roleId=3, roleName='system', roleType='S'}
2020-01-21 21:41:34.691 INFO 1652 --- [ main] com.rtxtitanv.JpaTest : Role{roleId=1, roleName='admin', roleType='A'}
④修改测试
/**
* 多表关联多对多更新
*/
@Transactional
@Rollback(false)
@Test
fun manyToManyUpdateTest() {
val roleOptional = roleRepository.findById(1L)
if (!roleOptional.isPresent) {
logger.info("角色不存在")
} else {
val roles = roleOptional.get()
// 通过id为1的role修改role关联的id为1的menu
// 根据id为1的menu修改menu关联的id为3的role
roles.menus.forEach { menu -> kotlin.run{
if (menu.menuId == 1L) {
menu.menuHidden = true
menu.menuName = "左侧导航栏"
menu.roles.forEach { role -> kotlin.run {
if (role.roleId == 3L) {
role.roleName = "vip"
role.roleType = "V"
}
} }
}
} }
}
}
自动生成的sql语句如下，navicat刷新表查看更新成功。
Hibernate: select role0_.role_id as role_id1_3_0_, role0_.role_name as role_nam2_3_0_, role0_.role_type as role_typ3_3_0_ from role role0_ where role0_.role_id=?
Hibernate: select menus0_.role_id as role_id2_4_0_, menus0_.menu_id as menu_id1_4_0_, menu1_.menu_id as menu_id1_1_1_, menu1_.menu_hidden as menu_hid2_1_1_, menu1_.menu_icon as menu_ico3_1_1_, menu1_.menu_name as menu_nam4_1_1_, menu1_.menu_parent_id as menu_par5_1_1_, menu1_.menu_path as menu_pat6_1_1_ from role_menu menus0_ inner join menu menu1_ on menus0_.menu_id=menu1_.menu_id where menus0_.role_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: update menu set menu_hidden=?, menu_icon=?, menu_name=?, menu_parent_id=?, menu_path=? where menu_id=?
Hibernate: update role set role_name=?, role_type=? where role_id=?
⑤删除测试
/**
* 多表关联多对多删除
*/
@Transactional
@Rollback(false)
@Test
fun manyToManyDeleteTest() {
// 准备删除的role
val roleOptional = roleRepository.findById(1L)
if (!roleOptional.isPresent) {
logger.info("角色不存在")
} else {
val role1 = roleOptional.get()
// 关联关系解除（id为1的role-->id为1的menu，id为2的menu）
// 删除中间表的关联记录
role1.menus.forEach { menu -> kotlin.run {
menu.roles.remove(role1)
} }
// 不删除role的情况，重新关联（id为1的role-->id为3的menu）
/*val menuOptional = menuRepository.findById(3L)
if (!menuOptional.isPresent) {
logger.info("菜单不存在")
} else {
val menu3 = menuOptional.get()
role1.menus.add(menu3)
menu3.roles.add(role1)
// 更新关联，可以省略，省略也会更新中间表关联关系
menuRepository.save(menu3)
}*/
// 删除role
roleRepository.delete(role1)
}
}
解除并删除中间表关联后重新关联，执行时自动生成的sql语句如下，navicat查看中间表维护的关系更新成功。
Hibernate: select role0_.role_id as role_id1_3_0_, role0_.role_name as role_nam2_3_0_, role0_.role_type as role_typ3_3_0_ from role role0_ where role0_.role_id=?
Hibernate: select menus0_.role_id as role_id2_4_0_, menus0_.menu_id as menu_id1_4_0_, menu1_.menu_id as menu_id1_1_1_, menu1_.menu_hidden as menu_hid2_1_1_, menu1_.menu_icon as menu_ico3_1_1_, menu1_.menu_name as menu_nam4_1_1_, menu1_.menu_parent_id as menu_par5_1_1_, menu1_.menu_path as menu_pat6_1_1_ from role_menu menus0_ inner join menu menu1_ on menus0_.menu_id=menu1_.menu_id where menus0_.role_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: select menu0_.menu_id as menu_id1_1_0_, menu0_.menu_hidden as menu_hid2_1_0_, menu0_.menu_icon as menu_ico3_1_0_, menu0_.menu_name as menu_nam4_1_0_, menu0_.menu_parent_id as menu_par5_1_0_, menu0_.menu_path as menu_pat6_1_0_ from menu menu0_ where menu0_.menu_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: delete from role_menu where menu_id=? and role_id=?
Hibernate: delete from role_menu where menu_id=? and role_id=?
Hibernate: insert into role_menu (menu_id, role_id) values (?, ?)
解除并删除中间表关联后删除，执行时自动生成的sql语句如下，navicat查看删除成功。
Hibernate: select role0_.role_id as role_id1_3_0_, role0_.role_name as role_nam2_3_0_, role0_.role_type as role_typ3_3_0_ from role role0_ where role0_.role_id=?
Hibernate: select menus0_.role_id as role_id2_4_0_, menus0_.menu_id as menu_id1_4_0_, menu1_.menu_id as menu_id1_1_1_, menu1_.menu_hidden as menu_hid2_1_1_, menu1_.menu_icon as menu_ico3_1_1_, menu1_.menu_name as menu_nam4_1_1_, menu1_.menu_parent_id as menu_par5_1_1_, menu1_.menu_path as menu_pat6_1_1_ from role_menu menus0_ inner join menu menu1_ on menus0_.menu_id=menu1_.menu_id where menus0_.role_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: select roles0_.menu_id as menu_id1_4_0_, roles0_.role_id as role_id2_4_0_, role1_.role_id as role_id1_3_1_, role1_.role_name as role_nam2_3_1_, role1_.role_type as role_typ3_3_1_ from role_menu roles0_ inner join role role1_ on roles0_.role_id=role1_.role_id where roles0_.menu_id=?
Hibernate: delete from role_menu where menu_id=? and role_id=?
Hibernate: delete from role_menu where menu_id=? and role_id=?
Hibernate: delete from role where role_id=?
代码示例
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
