Spring Date Jpa介绍
什么是JPA?
JPA是Java Persistence API的简称,中文名Java持久层API,是JDK5.0注解或XML描述对象-关系表的映射关系,并将运行期的实体对象持久化到数据库中。
Sun引入新的JPAORM规范出于两个原因:其一,简化现有JavaEE和JavaSE应用开发工作;其二,Sun希望整合ORM技术,结束现在Hibernate,TopLink,JDO等ORM框架各自为营的局面,实现天下归一。值得注意的是,JPA是在充分吸收了现有Hibernate,TopLink,JDO等ORM框架的基础上发展而来的,具有易于使用,伸缩性强等优点。
JPA是一套规范,不是一套产品。也就说JPA规范中提供的只是一些接口,显然接口不能直接拿来使用。虽然应用程序可以面向接口编程,但JPA底层一定需要某种JPA实现,否则JPA依然无法使用。
Spring Date Jpa
JPA诞生的缘由是为了整合第三方ORM框架,Spring为了能够更好的完善持久化这一块,于是就有了Spring-data-**这一系列包。包括:Spring-data-jpa,Spring-data-template,Spring-data-mongodb,Spring-data-redis。所以,Spring Data JPA 是 Spring 基于 ORM 框架、JPA 规范的基础上封装的一套JPA应用框架,可使开发者用极简的代码即可实现对数据的访问和操作。它提供了包括增删改查等在内的常用功能,且易于扩展!学习并使用 Spring Data JPA 可以极大提高开发效率!
官方文档:https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
基本配置和操作
1、在第一节搭建好的基本环境pom.xml添加相关的依赖,这里用到的数据库是mysql,所以也要添加mysql的依赖:
<!--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>
2、创建相关实体类(需要相关的sql语句,请到文章最下方的github):
用户实体:User
package com.lyh.demo.entity;
import com.lyh.demo.entity.enums.Sex;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
@Entity
public class User implements Serializable{
private static final long serialVersionUID = 1L;
@Id
private long id;
private String userName;
private String password;
private int age;
private Sex sex;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Sex getSex() {
return sex;
}
public void setSex(Sex sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", age=" + age +
", sex=" + sex +
'}';
}
}
性别枚举:Sex
package com.lyh.demo.entity.enums;
public enum Sex {
男,女
}
3、常用的CRUB(crud是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写)操作:
新建包com.lyh.demo.repository后创建UserRepository接口,继承JpaRepository
public interface UserRepository extends JpaRepository<User,Long> {}
使用JpaRepository自带的方法,新建包com.lyh.demo.controller后新建类JpaController:
public class JpaController {
@Autowired
UserRepository userRepository;
@Autowired
GameScoreRepository gameScoreRepository;
@RequestMapping("/findAll")
public void findAll(){
//使用默认方法findAll()获取所用数据
List<User> userList = userRepository.findAll();
if(userList !=null){
for (User user : userList){
System.out.println(user.toString());
}
}
/*
User user =userRepository.findOne(1L);
user =userRepository.findOne(1L);
这些默认方法可根据方法名可以看出方法的作用,这里就不一一说明了
userRepository.save(user);
userRepository.delete(user);
userRepository.deleteAll();
userRepository.count();
userRepository.exists(1L);*/
}
}
浏览器访问:后可以看到终端输出sql语句和内容:
Hibernate: select user0_.id as id1_1_, user0_.age as age2_1_, user0_.password as password3_1_, user0_.sex as sex4_1_, user0_.user_name as user_nam5_1_ from user user0_
User{id=0, userName='null', password='null', age=0, sex=女}
User{id=1, userName='小明', password='123456', age=20, sex=男}
User{id=2, userName='小明', password='1234567', age=21, sex=男}
User{id=3, userName='小红', password='12345678', age=22, sex=女}
User{id=4, userName='小李', password='12345678', age=25, sex=男}
User{id=5, userName='小陈', password='12345678', age=20, sex=男}
User{id=6, userName='小钱', password='12345678', age=20, sex=女}
User{id=7, userName='小林', password='12345678', age=20, sex=男}
User{id=8, userName='小赵', password='12345678', age=20, sex=女}
4、自定义方法查询
可以通过方法名自定义简单的查询,如List<User> findByUserName(String userName),根据用户姓名查找。自定义查询方法可以参考下面的UserRepository接口的方法和关键字对应的Sql语句表。
UserRepository接口
public interface UserRepository extends JpaRepository<User,Long> {
List<User> findByUserName(String userName);
User findByUserNameAndPassword(String userName, String password);
User findTopByOrderByAgeDesc();
}
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, | … 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 | findByAgeIsNull | … 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<Age> ages) | … where x.age in ?1 |
NotIn | findByAgeNotIn(Collection<Age> age) | … 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) |
5、分页查询
使用Spring封装的分页实现类。在查询的方法中,需要传入参数Pageable。有多个参数的时候,Pageable最好最后一个参数传入。
UserRepository接口添加相应的方法:
Page<User> findALL(Pageable pageable);
Page<User> findByUserName(String userName,Pageable pageable);
@RequestMapping("/page")
public void page() {
//分页的页码,从0开始
int page=0;
//每页有多少行数据,2就表示两条
int size=2;
String userName="小明";
//排序规则
Sort sort=new Sort(Sort.Direction.DESC,"id");
//添加页面、行数和排序规则
Pageable pageable=new PageRequest(page,size,sort);
//根据分页规则查找所有数据
Page<User> userPage1=userRepository.findAll(pageable);
if(userPage1!=null){
System.out.println("findAll:");
for (User user :userPage1){
System.out.println(user.toString());
}
}
//根据分页规则查找指定用户的数据
Page<User> userPage2=userRepository.findByUserName(userName,pageable);
if(userPage2!=null){
System.out.println("findByUserName:");
for (User user :userPage2){
System.out.println(user.toString());
}
}
}
Hibernate: select user0_.id as id1_1_, user0_.age as age2_1_, user0_.password as password3_1_, user0_.sex as sex4_1_, user0_.user_name as user_nam5_1_ from user user0_ order by user0_.id desc limit ?
Hibernate: select count(user0_.id) as col_0_0_ from user user0_
findAll:
User{id=8, userName='小赵', password='12345678', age=20, sex=女}
User{id=7, userName='小林', password='12345678', age=20, sex=男}
Hibernate: select user0_.id as id1_1_, user0_.age as age2_1_, user0_.password as password3_1_, user0_.sex as sex4_1_, user0_.user_name as user_nam5_1_ from user user0_ where user0_.user_name=? order by user0_.id desc limit ?
Hibernate: select count(user0_.id) as col_0_0_ from user user0_ where user0_.user_name=?
findByUserName:
User{id=2, userName='小明', password='1234567', age=21, sex=男}
User{id=1, userName='小明', password='123456', age=20, sex=男}
6、限制查询
查询方法的结果可以通过关键字first或者top来限制,它们可以交替使用。在top/firest后添加数字来表示返回最大的结果数。如果没有数字,则默认假定1作为结果大小。
在UserRepository接口添加方法:
List<User> findFirst2ByAge(int age, Sort sort);
List<User> findTop3ByAge(int age, Pageable pageable);
Page<User> queryFirst3ByAge(int age, Pageable pageable);
在Controller层使用这些方法:
@RequestMapping("/limit")
public void limit(){
int page=0;
int size=3;
int age=20;
Sort sort=new Sort(Sort.Direction.DESC,"id");
Pageable pageable=new PageRequest(page,size);
//根据排序规则和年龄获取前2行
List<User> userList1 =userRepository.findFirst2ByAge(age,sort);
if(userList1 !=null){
System.out.println("findFirst2ByAge:");
for (User user : userList1){
System.out.println(user.toString());
}
}
//分页年龄获取前三行
List<User> userList2 =userRepository.findTop3ByAge(age,pageable);
if(userList2 !=null){
System.out.println("findTop3ByAge:");
for (User user : userList2){
System.out.println(user.toString());
}
}
//这里的query官方文档没有介绍,测试发现和find功能一样
Page<User> userPage1=userRepository.queryFirst3ByAge(age,pageable);
if(userPage1!=null){
System.out.println("queryFirst3ByAge:");
for (User user :userPage1){
System.out.println(user.toString());
}
}
可以参考下官方文档介绍的一些方法:
User findFirstByOrderByLastnameAsc();
User findTopByOrderByAgeDesc();
Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);
Slice<User> findTop3ByLastname(String lastname, Pageable pageable);
List<User> findFirst10ByLastname(String lastname, Sort sort);
List<User> findTop10ByLastname(String lastname, Pageable pageable);
自定义SQL语句查询,有时候根据业务的需求,可能需要自定义SQL语句。这时候Spring Data Jpa 提供了一系列注释方法:
@Query注解写的是SQL语句。
@Modifying如涉及到删除和修改在需要加上。
@Transactional 对事物的支持,查询超时的设置等
在UserRepository接口添加方法:
@Query("update User set userName=?2 where id=?1")
@Transactional(timeout = 100)
@Modifying()
int updateUserNameById(long id,String userName);
7、多表查询
多表查询在spring data jpa中有两种实现方式
第一种:利用hibernate的级联查询来实现;
第二种:创建一个结果集的接口来接收连表查询后的结果;
这里介绍下第二种方式。
业务需求:显示用户游戏分数的记录。
首先先创建用来保存游戏分数的实体类GameScore:
@Entity
public class GameScore {
@Id
private long id;
private String name;
private int score;
private long userId;
private Timestamp createTime;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
}
多表查询结果集的接口类,将查询的结果映射到相应的getXxxx()方法中:
public interface GameRank {
String getUserName();
String getName();
int getScore();
}
然后创建查询方法,新建GameScoreRepository接口后添加自定义Sql语句的查询方法,代码如下:
public interface GameScoreRepository extends JpaRepository<GameScore,Long>{
@Query("select u.userName as userName,gs.name as name,gs.score as score from GameScore gs,User u where gs.userId=u.id and gs.name=?1")
List<GameRank> findRankByName(String name);
}
@RequestMapping("/findRankByName")
public void findRankByName(){
List<GameRank> gameRankList=gameScoreRepository.findRankByName("2048小游戏");
for(GameRank gameRank:gameRankList){
System.out.println("用户名:"+gameRank.getName()+" 游戏名:"+gameRank.getUserName()+" 得分"+gameRank.getScore());
}
}
Hibernate: select user1_.user_name as col_0_0_, gamescore0_.name as col_1_0_, gamescore0_.score as col_2_0_ from game_score gamescore0_ cross join user user1_ where gamescore0_.user_id=user1_.id and gamescore0_.name=?
用户名:2048小游戏 游戏名:小明 得分512
用户名:2048小游戏 游戏名:小明 得分1024
用户名:2048小游戏 游戏名:小明 得分2048