1.Spring Boot中配置JPA
Spring Boot中访问关系型数据库，可以使用Spring Data JPA建立数据访问层，那么先来介绍下相关的必要配置。
① 添加JPA依赖
于Spring Boot项目的pom.xml文件中添加如下依赖：
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
② 添加数据库驱动依赖
访问数据库的前提是需要先有一个数据库，本文使用MySQL做演示，故添加MySQL的驱动如下：
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>此处添加数据库驱动并没有指定版本号，因为版本号已在spring-boot-dependencies中指定。Spring Boot的当前最新版本号是1.5.7.RELEASE，MySQL驱动的版本号是5.1.44。
③ 配置数据源
于application.properties文件中添加数据源配置：
## 数据源配置
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/demo?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root④ 配置JPA
于application.properties文件中添加JPA配置：
## JPA配置
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true⑤ 其他配置
application.properties文件中维持如下配置：
# 访问路径
server.context-path=/demo
# 端口号
server.port=80882.使用JPA操作数据库
① 创建数据库表
于本地MySQL数据库的demo库中创建学生表student，该表包含4个字段：id(学生编号)，name(姓名)，age(年龄)，nat(国籍)；建表sql如下：
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`age` int(4) DEFAULT NULL COMMENT '年龄',
`nat` varchar(200) DEFAULT NULL COMMENT '国籍',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'② 创建实体类
创建与student数据表字段一致的实体类Student：
package net.xxpsw.demo.springboot.student.entity;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Student {
@Id
private Long id;
private String name;
private Integer age;
private String nat;
public Student() {
super();
}
public Student(Long id, String name, Integer age, String nat) {
super();
this.id = id;
this.name = name;
this.age = age;
this.nat = nat;
}
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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getNat() {
return nat;
}
public void setNat(String nat) {
this.nat = nat;
}
}③ 创建数据访问接口
创建数据访问接口StudentRepository，该接口继承JpaRepository<T, ID extends Serializable>：
package net.xxpsw.demo.springboot.student.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import net.xxpsw.demo.springboot.student.entity.Student;
public interface StudentRepository extends JpaRepository<Student, Long> {
}@NoRepositoryBean
public interface JpaRepository<T, ID extends Serializable> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
List<T> findAll();
List<T> findAll(Sort sort);
List<T> findAll(Iterable<ID> ids);
<S extends T> List<S> save(Iterable<S> entities);
void flush();
<S extends T> S saveAndFlush(S entity);
void deleteInBatch(Iterable<T> entities);
void deleteAllInBatch();
T getOne(ID id);
@Override
<S extends T> List<S> findAll(Example<S> example);
@Override
<S extends T> List<S> findAll(Example<S> example, Sort sort);
}@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> {
Iterable<T> findAll(Sort sort);
Page<T> findAll(Pageable pageable);
}public interface QueryByExampleExecutor<T> {
<S extends T> S findOne(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example, Sort sort);
<S extends T> Page<S> findAll(Example<S> example, Pageable pageable);
<S extends T> long count(Example<S> example);
<S extends T> boolean exists(Example<S> example);
}从上述源码中，我们可以一窥Spring Data JPA所提供的一系列数据访问接口。
这里需要注意的是QueryByExampleExecutor<T>，该接口在Spring Data JPA的1.10.x及其后续版本才出现，因此使用时需注意版本的限制。④ 访问数据库
接下来，创建StudentController演示数据库的访问：
package net.xxpsw.demo.springboot.student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import net.xxpsw.demo.springboot.student.dao.StudentRepository;
@RestController
@RequestMapping("student")
public class StudentController {
@Autowired
private StudentRepository studentRepository;
}a. 保存信息
StudentController中添加如下方法：
/**
* @Description: 保存
* @return Student
*/
@RequestMapping("save0")
public Student save0() {
Student student = studentRepository.save(new Student(1L, "Xie", 25, "China"));
return student;
}{
"id" : 1,
"name" : "Xie",
"age" : 25,
"nat" : "China"
}控制台打印sql如下：
Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?) id name age nat
------ ------ ------ --------
1 Xie 25 China b. 批量保存
StudentController中新增批量保存方法：
/**
* @Description: 保存（批量）
* @return List<Student>
*/
@RequestMapping("save1")
public List<Student> save1() {
List<Student> params = new ArrayList<Student>();
params.add(new Student(2L, "Mike", 25, "USA"));
params.add(new Student(3L, "Ice", 26, "UK"));
params.add(new Student(4L, "Geoff", 26, "France"));
params.add(new Student(5L, "Ivan", 26, "Russia"));
params.add(new Student(6L, "David", 26, "USA"));
List<Student> students = studentRepository.save(params);
return students;
}启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/save1，页面返回结果显示如下：
[ {
"id" : 2,
"name" : "Mike",
"age" : 25,
"nat" : "USA"
}, {
"id" : 3,
"name" : "Ice",
"age" : 26,
"nat" : "UK"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
}, {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
}, {
"id" : 6,
"name" : "David",
"age" : 26,
"nat" : "USA"
} ]Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)
Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)
Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)
Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)
Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)数据库客户端查询sql：SELECT * FROM `demo`.`student`; 结果如下：
id name age nat
------ ------ ------ --------
1 Xie 25 China
2 Mike 25 USA
3 Ice 26 UK
4 Geoff 26 France
5 Ivan 26 Russia
6 David 26 USA c. 删除数据
StudentController中新增删除方法如下：
/**
* @Description: 删除
*/
@RequestMapping("delete")
public void delete(long id) {
studentRepository.delete(id);
}控制台打印sql如下：
Hibernate: delete from student where id=? id name age nat
------ ------ ------ --------
1 Xie 25 China
2 Mike 25 USA
3 Ice 26 UK
4 Geoff 26 France
5 Ivan 26 Russia d.查询数据
StudentController中新增查询方法如下：
/**
* @Description: 查询
* @return Student
*/
@RequestMapping("find0")
public Student find0(long id) {
Student student = studentRepository.findOne(id);
return student;
}{
"id" : 2,
"name" : "Mike",
"age" : 25,
"nat" : "USA"
}Hibernate: select student0_.id as id1_0_0_, student0_.age as age2_0_0_, student0_.name as name3_0_0_,
student0_.nat as nat4_0_0_ from student student0_ where student0_.id=?e.查询列表
StudentController中新增查询方法如下：
/**
* @Description: 查询列表
* @return List<Student>
*/
@RequestMapping("find1")
public List<Student> find1() {
List<Student> students = studentRepository.findAll();
return students;
}[ {
"id" : 1,
"name" : "Xie",
"age" : 25,
"nat" : "China"
}, {
"id" : 2,
"name" : "Mike",
"age" : 25,
"nat" : "USA"
}, {
"id" : 3,
"name" : "Ice",
"age" : 26,
"nat" : "UK"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
}, {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
} ]Hibernate: select student0_.id as id1_0_, student0_.age as age2_0_, student0_.name as name3_0_, student0_.nat as nat4_0_
from student student0_f.查询列表（排序）
StudentController中新增查询方法如下：
// 排序条件
private Sort sort() {
List<Order> orders = new ArrayList<Order>();
orders.add(new Order(Direction.DESC, "age"));
orders.add(new Order(Direction.DESC, "id"));
return new Sort(orders);
}
/**
* @Description: 查询列表（排序）
* @return List<Student>
*/
@RequestMapping("find2")
public List<Student> find2() {
List<Student> students = studentRepository.findAll(sort());
return students;
}[ {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
}, {
"id" : 3,
"name" : "Ice",
"age" : 26,
"nat" : "UK"
}, {
"id" : 2,
"name" : "Mike",
"age" : 25,
"nat" : "USA"
}, {
"id" : 1,
"name" : "Xie",
"age" : 25,
"nat" : "China"
} ]Hibernate: select student0_.id as id1_0_, student0_.age as age2_0_, student0_.name as name3_0_, student0_.nat as nat4_0_
from student student0_ order by student0_.age desc, student0_.id descg.查询列表（筛选）
StudentController中新增查询方法如下：
// 筛选条件
private Example<Student> example() {
Student student = new Student();
student.setAge(26);
return Example.of(student);
}
/**
* @Description: 查询列表（筛选）
* @return List<Student>
*/
@RequestMapping("find3")
public List<Student> find3() {
List<Student> students = studentRepository.findAll(example());
return students;
}[ {
"id" : 3,
"name" : "Ice",
"age" : 26,
"nat" : "UK"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
}, {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
} ]Hibernate: select student0_.id as id1_0_, student0_.age as age2_0_, student0_.name as name3_0_, student0_.nat as nat4_0_
from student student0_ where student0_.age=26h.查询列表（分页）
StudentController中新增查询方法如下：
/**
* @Description: 查询列表（分页）
* @return Page<Student>
*/
@RequestMapping("find4")
public Page<Student> find4() {
Page<Student> pageStudents = studentRepository.findAll(new PageRequest(0, 2));
return pageStudents;
}{
"content" : [ {
"id" : 1,
"name" : "Xie",
"age" : 25,
"nat" : "China"
}, {
"id" : 2,
"name" : "Mike",
"age" : 25,
"nat" : "USA"
} ],
"last" : false,
"totalElements" : 5,
"totalPages" : 3,
"number" : 0,
"size" : 2,
"sort" : null,
"first" : true,
"numberOfElements" : 2
}控制台打印sql如下：
Hibernate: select student0_.id as id1_0_, student0_.age as age2_0_, student0_.name as name3_0_, student0_.nat as nat4_0_
from student student0_ limit ?i.查询列表（排序 + 筛选 + 分页）
StudentController中新增查询方法如下： /**
* @Description: 查询列表（排序 + 筛选 + 分页）
* @return Page<Student>
*/
@RequestMapping("find5")
public Page<Student> find5() {
Page<Student> pageStudents = studentRepository.findAll(example(), new PageRequest(0, 2, sort()));
return pageStudents;
}{
"content" : [ {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
} ],
"last" : false,
"totalElements" : 3,
"totalPages" : 2,
"number" : 0,
"size" : 2,
"sort" : [ {
"direction" : "DESC",
"property" : "age",
"ignoreCase" : false,
"nullHandling" : "NATIVE",
"ascending" : false,
"descending" : true
}, {
"direction" : "DESC",
"property" : "id",
"ignoreCase" : false,
"nullHandling" : "NATIVE",
"ascending" : false,
"descending" : true
} ],
"first" : true,
"numberOfElements" : 2
}Hibernate: select student0_.id as id1_0_, student0_.age as age2_0_, student0_.name as name3_0_, student0_.nat as nat4_0_
from student student0_ where student0_.age=26 order by student0_.age desc, student0_.id desc limit ?⑤ 使用@Query查询
Spring Data JPA还支持使用@Query注解在接口的方法上实现查询。a. 数据查询
在接口StudentRepository中添加如下方法：
@Query("SELECT S FROM Student S WHERE S.age > :age ")
public List<Student> queryAll(@Param("age") Integer age); /**
* @Description: 数据查询
* @param age 年龄
* @return List<Student>
*/
@RequestMapping("query0")
public List<Student> query0(int age) {
List<Student> students = studentRepository.queryAll(age);
return students;
}[ {
"id" : 3,
"name" : "Ice",
"age" : 26,
"nat" : "UK"
}, {
"id" : 4,
"name" : "Geoff",
"age" : 26,
"nat" : "France"
}, {
"id" : 5,
"name" : "Ivan",
"age" : 26,
"nat" : "Russia"
} ]可以看到，数据库中年龄大于25的三个学生都被查找出来了。
控制台打印sql如下：
Hibernate: select student0_.id as id1_1_, student0_.age as age2_1_, student0_.name as name3_1_, student0_.nat as nat4_1_
from student student0_ where student0_.age>?b.多表查询
现在，我们来新建一张学生的成绩表，然后对两张表做联合查询，找出符合条件的学生信息。于本地数据库的demo库中新建数据库表score，建表sql如下：
CREATE TABLE `score` (
`id` bigint(20) NOT NULL COMMENT 'ID',
`student_id` bigint(20) DEFAULT NULL COMMENT '学生ID',
`score` int(4) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表'INSERT INTO `demo`.`score` (`id`, `student_id`, `score`) VALUES ('1', '1', '90');
INSERT INTO `demo`.`score` (`id`, `student_id`, `score`) VALUES ('2', '3', '85');
INSERT INTO `demo`.`score` (`id`, `student_id`, `score`) VALUES ('3', '5', '95'); package net.xxpsw.demo.springboot.student.entity;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Score {
@Id
private Integer id;
private Integer studentId;
private Integer score;
public Score() {
super();
}
public Score(Integer id, Integer studentId, Integer score) {
super();
this.id = id;
this.studentId = studentId;
this.score = score;
}
public Integer getStudentId() {
return studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
}package net.xxpsw.demo.springboot.student.entity;
public class StudentScore {
private Long id;
private String name;
private Integer score;
public StudentScore() {
super();
}
public StudentScore(Long id, String name, Integer score) {
super();
this.id = id;
this.name = name;
this.score = score;
}
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 Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
} @Query("SELECT new net.xxpsw.demo.springboot.student.entity.StudentScore(S.id,S.name,C.score) "
+ "FROM Student S, Score C "
+ "WHERE S.id = C.studentId AND S.age > :age ")
public List<StudentScore> queryScoreAll(@Param("age") Integer age); /**
* @Description: 多表查询
* @param age 年龄
* @return List<StudentScore>
*/
@RequestMapping("query1")
public List<StudentScore> query1(int age) {
List<StudentScore> studentScores = studentRepository.queryScoreAll(age);
return studentScores;
}[ {
"id" : 3,
"name" : "Ice",
"score" : 85
}, {
"id" : 5,
"name" : "Ivan",
"score" : 95
} ]可以看到，所有符合条件的学生信息都已经被检索出来了，并且学生信息中包含且只包含 id(学生ID)、name(姓名)和score(成绩)。
此时控制台打印sql如下：
Hibernate: select student0_.id as col_0_0_, student0_.name as col_1_0_, score1_.score as col_2_0_
from student student0_ cross join score score1_ where student0_.id=score1_.student_id and student0_.age>?
5711
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
