Spring Boot+Vue 全栈开发实战 - 第5章

Spring Boot 整合持久层技术

持久层是JavaEE中访问数据库的核心操作，Spring Boot对常见的持久层框架提供了自动化配置，如：JdbcTemplate、JPA等，MyBatis的自动化配置则是MyBatis官方提供的。

1 整合JdbcTemplate

1.1 自动配置原理

Spring Boot提供了自动化配置类JdbcTemplateAutoConfiguration，部分代码如下：

@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcTemplateAutoConfiguration {

	@Configuration
	static class JdbcTemplateConfiguration {

		private final DataSource dataSource;

		private final JdbcProperties properties;

		JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
			this.dataSource = dataSource;
			this.properties = properties;
		}

		@Bean
		@Primary
		@ConditionalOnMissingBean(JdbcOperations.class)
		public JdbcTemplate jdbcTemplate() {
			JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
			JdbcProperties.Template template = this.properties.getTemplate();
			jdbcTemplate.setFetchSize(template.getFetchSize());
			jdbcTemplate.setMaxRows(template.getMaxRows());
			if (template.getQueryTimeout() != null) {
				jdbcTemplate
						.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
			}
			return jdbcTemplate;
		}

1. 当类路径下同时存在DataSource和JdbcTemplate两个类时，并且只有一个DataSource实例时，自动配置才会生效。
2. 当没有提供JdbcOperations类时，会自动向容器注入一个JdbcTemplate。

1.2 测试表

以mysql数据库为例，数据库中存在表book，建表语句如下

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `author` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3 添加依赖

在pom文件中添加如下依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.9</version>
</dependency>

1.4 数据库配置

application.properties中添加配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456

1.5 创建实体类

package com.example.chapter05.entity;

public class Book {
    private Integer id;
    private String name;
    private String author;
    // get、set、toString 方法省略
}

1.6 创建数据库访问层

package com.example.chapter05.dao;

import com.example.chapter05.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class BookDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    public int addBook(Book book) {
        return jdbcTemplate.update("insert into book(name, author) values (?, ?)", book.getName(), book.getAuthor());
    }
    public int updateBook(Book book) {
        return jdbcTemplate.update("update book set name=?, author=? where id=?", book.getName(), book.getAuthor(), book.getId());
    }

    public int deleteBookById(Integer id) {
        return jdbcTemplate.update("delete from book where id=?", id);
    }

    public Book getBookById(Integer id) {
        return jdbcTemplate.queryForObject("select * from book where id=?", new BeanPropertyRowMapper<>(Book.class), id);
    }

    public List<Book> getAllBooks() {
        return jdbcTemplate.query("select * from book", new BeanPropertyRowMapper<>(Book.class));
    }
}

1. JdbcTemplate中的方法：增删改使用update和batchUpdate来完成；查询通过query和queryForObject来完成；execute可以执行任意sql；call用来调用存储过程。
2. 执行查询方法时，需要一个RowMapper将查询结果和实体类对应，如果列名和属性名都是相同的，可以直接使用BeanPropertyRowMapper，否则需要自定义RowMapper。

1.7 创建Service和Controller

Service

package com.example.chapter05.service;

import com.example.chapter05.dao.BookDao;
import com.example.chapter05.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {
    @Autowired
    BookDao bookDao;
    public int addBook(Book book) {
        return bookDao.addBook(book);
    }
    public int updateBook(Book book) {
        return bookDao.updateBook(book);
    }

    public int deleteBookById(Integer id) {
        return bookDao.deleteBookById(id);
    }

    public Book getBookById(Integer id) {
        return bookDao.getBookById(id);
    }

    public List<Book> getAllBooks() {
        return bookDao.getAllBooks();
    }
}

Controller

package com.example.chapter05.controller;

import com.example.chapter05.entity.Book;
import com.example.chapter05.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps() {
        Book book1 = new Book();
        book1.setName("西厢记");
        book1.setAuthor("王实甫");
        bookService.addBook(book1);
        Book book2 = new Book();
        book2.setName("朝花夕拾");
        book2.setAuthor("王蒙");
        bookService.addBook(book2);
        book2.setId(2);
        book2.setAuthor("鲁迅");
        bookService.updateBook(book2);
        Book book3 = new Book();
        book3.setName("西游记");
        book3.setAuthor("吴承恩");
        bookService.addBook(book3);
        bookService.deleteBookById(3);
        List<Book> allBooks = bookService.getAllBooks();
        System.out.println(allBooks);
    }
}

1.8 效果

在浏览器访问http://localhost:8080/bookOps进行测试，数据库中结果如下：

2 整合MyBatis

Mybatis几乎避免了所有的JDBC代码手动设置参数以及获取结果集。

2.1 创建项目

添加依赖

<dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>1.3.2</version>
 </dependency>
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <scope>runtime</scope>
 </dependency>
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.1.9</version>
 </dependency>

2.2 数据库配置

同1.4节

2.3 创建实体类

同1.5节

2.4 创建数据库访问层

创建接口类BookMapper

package com.example.chapter0502.mapper;

import com.example.chapter0502.entity.Book;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface BookMapper {
    int addBook(Book book);
    int deleteBookById(Integer id);
    int updateBookById(Book book);
    Book getBookById(Integer id);
    List<Book> getAllBooks();
}

两种方式声明某个类是一个Mapper:

1. 在类上加 @Mapper 注解
2. 在配置类上添加 @MapperScan(“具体包名”) 注解

2.5 创建BookMapper.xml

在上节类BookMapper所在的路径下创建BookMapper.xml文件。

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.chapter0502.mapper.BookMapper">
    <insert id="addBook" parameterType="com.example.chapter0502.entity.Book">
        INSERT INTO book(name, author) VALUES (#{name}, #{author})
    </insert>
    <delete id="deleteBookById" parameterType="int">
        DELETE FROM book where id=#{id}
    </delete>
    <update id="updateBookById" parameterType="com.example.chapter0502.entity.Book">
        UPDATE book set name=#{name}, author=#{author} where id=#{id}
    </update>
    <select id="getBookById" parameterType="int" resultType="com.example.chapter0502.entity.Book">
        select * from book where id=#{id}
    </select>
    <select id="getAllBooks" resultType="com.example.chapter0502.entity.Book">
        select * from book
    </select>
</mapper>

实体中的属性通过#{属性名}获取

2.6 创建Service和Controller

BookService

package com.example.chapter0502.service;

import com.example.chapter0502.entity.Book;
import com.example.chapter0502.mapper.BookMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {
    @Autowired
    BookMapper bookMapper;
    public int addBook(Book book) {
        return bookMapper.addBook(book);
    }
    public int deleteBookById(Integer id) {
        return bookMapper.deleteBookById(id);
    }
    public int updateBookById(Book book) {
        return bookMapper.updateBookById(book);
    }
    public Book getBookById(Integer id) {
        return bookMapper.getBookById(id);
    }
    public List<Book> getAllBooks() {
        return bookMapper.getAllBooks();
    }
}

BookController

package com.example.chapter0502.controller;

import com.example.chapter0502.entity.Book;
import com.example.chapter0502.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/bookOps")
    public void bookOps() {
        Book book1 = new Book();
        book1.setName("西游记");
        book1.setAuthor("吴承恩");
        bookService.addBook(book1);
        Book book2 = new Book();
        book2.setName("水浒传");
        book2.setAuthor("吴承恩");
        bookService.addBook(book2);
        book2.setId(2);
        book2.setAuthor("施耐庵");
        bookService.updateBookById(book2);
        Book book3 = new Book();
        book3.setName("红楼梦");
        book3.setAuthor("曹雪芹");
        bookService.addBook(book3);
        bookService.deleteBookById(3);
        List<Book> allBooks = bookService.getAllBooks();
        System.out.println(allBooks);
    }
}

2.7 配置pom.xml

xml配置文件建议写在resources目录下，但是上文中的BookMapper.xml是建在了类BookMapper所在的目录下了，需要在pom.xml文件里重新指定资源文件位置，如下：

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
        <plugins>省略...</plugins>
    </build>

否则会出现如下报错

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.chapter0502.mapper.BookMapper.addBook

2.8 效果

在浏览器访问http://localhost:8080/bookOps进行测试，数据库中结果如下：

3 整合Spring Data JPA

JPA和Spring Data是两个范畴的概念。

• JPA（Java Persistence API）
  Hibernate是一个ORM框架，而JPA则是一种ORM规范，两则的关系类似JDBC和JDBC驱动。JPA制定了ORM规范，而Hibernate是这些规范的实现（实际上先有的Hibernate，后有的JPA）。
• Spring Data
  Spring的一个子项目，致力于简化数据库访问。

Spring Data JPA可以有效简化关系型数据库访问代码。

Spring Boot整合Spring Data JPA步骤如下：

3.1 添加项目依赖

pom.xml

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

3.2 数据库配置

application.properties

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
spring.jpa.show-sql=true
spring.jpa.database=mysql
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

3.3 创建实体类

package com.example.chapter0503.entity;

import javax.persistence.*;

@Entity(name = "t_book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "book_name", nullable = false)
    private String name;
    private String author;
    private Float price;
    @Transient
    private String description;

    //省略getter、setter和toString方法
}

1. @Entity标注表名该类是一个实体类，name对应数据库中表的名称，不配置时默认以类名来作为表名。
2. @Transient表示在自动生成数据库表时，忽略该属性

3.4 创建BookDao接口

package com.example.chapter0503.dao;

import com.example.chapter0503.entity.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BookDao extends JpaRepository<Book, Integer> {
    List<Book> getBooksByAuthorStartingWith(String author);
    List<Book> getBooksByPriceGreaterThan(Float price);
    @Query(value = "select * from t_book where id=(select max(id) from t_book)", nativeQuery = true)
    Book getMaxIdBook();
    @Query(value = "select b from t_book b where b.id>:id and b.author=:author")
    List<Book> getBookByIdAndAuthor(@Param("author") String author, @Param("id") Integer id);
    @Query(value = "select b from t_book b where b.id<?2 and b.name like %?1%")
    List<Book> getBooksByIdAndName(String name, Integer id);
}

1. BookDao继承自JpaRepository，JpaRepository提供了一些基本的数据库操作方法：基本的增删改查、分页查询、排序查询等。
2. 在Spring Data JPA中，只要方法的定义符合既定规范，Spring Data 就能分析出开发者的意图，从而避免开发者定义SQL。

KeyWords	方法命名举例	对应的SQL
And	findByNameAndAge	where name=? and age=?
Or	findByNameOrAge	where name=? or age=?
Is	findByAgeIs	where age=?
…	…	…

3. getBookByIdAndAuthor上配置的是JPQL(Java Persistence Query Language), 用nativeQuery = true表示使用原生的SQL查询。
4. 如果方法中涉及到修改操作，需要添加@Modifying注解。

3.5 创建BookService

package com.example.chapter0503.service;

import com.example.chapter0503.dao.BookDao;
import com.example.chapter0503.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {
    @Autowired
    BookDao bookDao;
    public void addBook(Book book) {
        bookDao.save(book);
    }
    public Page<Book> getBookByPage(Pageable pageable) {
        return bookDao.findAll(pageable);
    }
    public List<Book> getBooksByAuthorStartingWith(String author) {
        return bookDao.getBooksByAuthorStartingWith(author);
    }
    public List<Book> getBooksByPriceGreaterThan(Float price) {
        return bookDao.getBooksByPriceGreaterThan(price);
    }
    public Book getMaxIdBook() {
        return bookDao.getMaxIdBook();
    }
    public List<Book> getBookByIdAndAuthor(String author, Integer id) {
        return bookDao.getBookByIdAndAuthor(author, id);
    }
    public List<Book> getBooksByIdAndName(String name, Integer id) {
        return bookDao.getBooksByIdAndName(name, id);
    }
}

1. 代码中使用的save方法是由JpaRepository接口提供的。
2. 分页查询findAll，返回值是Page<Book>，该对象中记录分页信息，包括总记录数、总页数、每页数据量、当前页记录数等。

3.6 创建BookController

package com.example.chapter0503.controller;

import com.example.chapter0503.entity.Book;
import com.example.chapter0503.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/findAll")
    public void findAll() {
        PageRequest pageable = PageRequest.of(2, 3);
        Page<Book> page = bookService.getBookByPage(pageable);
        System.out.println("总页数: " + page.getTotalPages());
        System.out.println("总记录数: " + page.getTotalElements());
        System.out.println("查询结果: " + page.getContent());
        System.out.println("当前页数: " + page.getNumber() + 1);
        System.out.println("当前页记录数: " + page.getNumberOfElements());
        System.out.println("每页记录数: " + page.getSize());
    }
    @GetMapping("/search")
    public void search() {
        List<Book> bs1 = bookService.getBookByIdAndAuthor("鲁迅", 7);
        List<Book> bs2 = bookService.getBooksByAuthorStartingWith("吴");
        List<Book> bs3 = bookService.getBooksByIdAndName("西", 8);
        List<Book> bs4 = bookService.getBooksByPriceGreaterThan(30F);
        Book b = bookService.getMaxIdBook();
        System.out.println("bs1: " + bs1);
        System.out.println("bs2: " + bs2);
        System.out.println("bs3: " + bs3);
        System.out.println("bs4: " + bs4);
        System.out.println("b: " + b);
    }
    @GetMapping("/save")
    public void save() {
        Book book = new Book();
        book.setAuthor("鲁迅");
        book.setName("呐喊");
        book.setPrice(23F);
        bookService.addBook(book);
    }
}

3.7 测试

调用

http://localhost:8080/findAll
http://localhost:8080/save
http://localhost:8080/search

具体情况略。