Spring Boot 快速上手（三）数据操作

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.Driverspring.datasource.url=jdbc:mysql://127.0.0.1:3306/demo?characterEncoding=UTF-8spring.datasource.username=rootspring.datasource.password=root

这里使用的是本地的demo库，后面将在此库中创建数据库表。

④ 配置JPA

于application.properties文件中添加JPA配置：

## JPA配置spring.jpa.show-sql=truespring.jackson.serialization.indent_output=true

第一条配置开启控制台中打印sql语句，第二条配置美化输出的json字符串。此外，Spring Boot中默认Hibernate是JPA的实现者。

⑤ 其他配置

application.properties文件中维持如下配置：

# 访问路径server.context-path=/demo# 端口号server.port=8088

那么，当前的访问路径是http://localhost:8088/demo/；如果不希望添加该配置，默认访问路径是http://localhost:8080/。

2.使用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;@Entitypublic class Student {@Idprivate 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;}}

其中，@Entity指明该实体类与数据库表相映射，@Id指明该属性映射数据库表的主键。

③ 创建数据访问接口

创建数据访问接口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> {}

查看JpaRepository<T, ID extends Serializable>的源码，源码结构如下：

@NoRepositoryBeanpublic 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指明此接口不是业务使用的接口，从JpaRepository<T, ID extends Serializable>的源码可以看到，该接口继承了PagingAndSortingRepository<T, ID>和QueryByExampleExecutor<T>两个接口，继续观察这两个接口的源码结构：

@NoRepositoryBeanpublic 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 {@Autowiredprivate StudentRepository studentRepository;}

a. 保存信息

StudentController中添加如下方法：

/*** @Description: 保存* @return Student  */@RequestMapping("save0")public Student save0() {Student student = studentRepository.save(new Student(1L, "Xie", 25, "China"));return student;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/save0，页面返回结果显示如下：

{  "id" : 1,  "name" : "Xie",  "age" : 25,  "nat" : "China"}

控制台打印sql如下：

Hibernate: insert into student (age, name, nat, id) values (?, ?, ?, ?)

使用MySQL客户端连接本地数据库，查询sql：SELECT * FROM `demo`.`student`; 结果如下：

    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"} ]

控制台打印sql如下：

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 (?, ?, ?, ?)

可以看到，此处虽然一个接口内新增了5条数据，但却执行了5次单条插入的sql。
数据库客户端查询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);}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/delete?id=6，
控制台打印sql如下：

Hibernate: delete from student where id=?

数据库客户端查询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  

可以看到，id为6的数据已从数据库表中删除。

d.查询数据

StudentController中新增查询方法如下：

/*** @Description: 查询* @return Student  */@RequestMapping("find0")public Student find0(long id) {Student student = studentRepository.findOne(id);return student;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find0?id=2，页面返回结果显示如下：

{  "id" : 2,  "name" : "Mike",  "age" : 25,  "nat" : "USA"}

控制台打印sql如下：

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;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find1，页面返回结果显示如下：

[ {  "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"} ]

控制台打印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_

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;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find2，页面返回结果显示如下：

[ {  "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"} ]

控制台打印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_ order by student0_.age desc, student0_.id desc

g.查询列表（筛选）

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;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find3，页面返回结果显示如下：

[ {  "id" : 3,  "name" : "Ice",  "age" : 26,  "nat" : "UK"}, {  "id" : 4,  "name" : "Geoff",  "age" : 26,  "nat" : "France"}, {  "id" : 5,  "name" : "Ivan",  "age" : 26,  "nat" : "Russia"} ]

控制台打印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_ where student0_.age=26

h.查询列表（分页）

StudentController中新增查询方法如下：

/*** @Description: 查询列表（分页）* @return Page<Student>  */@RequestMapping("find4")public Page<Student> find4() {Page<Student> pageStudents = studentRepository.findAll(new PageRequest(0, 2));return pageStudents;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find4，页面返回结果显示如下：

{  "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;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/find5，页面返回结果显示如下：

{  "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}

控制台打印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_ 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);

该方法用于查询年龄大于某个年龄值的所有学生数据，在StudentController中添加如下方法：

/*** @Description: 数据查询* @param age 年龄* @return List<Student>  */@RequestMapping("query0")public List<Student> query0(int age) {List<Student> students = studentRepository.queryAll(age);return students;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/query0?age=25，页面返回结果显示如下：

[ {  "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='成绩表'

向score表中新增几条数据：

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'); 

新建实体类Score映射数据库表score：

package net.xxpsw.demo.springboot.student.entity;import javax.persistence.Entity;import javax.persistence.Id;@Entitypublic class Score {@Idprivate 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;}}

新建查询结果类StudentScore，包含字段：id(学生ID)，name(姓名)，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;}}

在接口StudentRepository中添加如下方法：

@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);

该方法用于学生表与成绩表的联合查询，检索出年龄大于某个年龄值的所有学生信息，在StudentController中添加如下方法：

/** * @Description: 多表查询 * @param age 年龄 * @return List<StudentScore> */@RequestMapping("query1")public List<StudentScore> query1(int age) {List<StudentScore> studentScores = studentRepository.queryScoreAll(age);return studentScores;}

启动Spring Boot，于浏览器地址栏输入http://localhost:8088/demo/student/query1?age=25，页面返回结果显示如下：

[ {  "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>?