SpringBoot 访问mysql数据库的几种方式

前言

最近学完ssm框架了，闲来无事想学学spring boot。spring的入门程序"HelloWorld"就不写了，前两天写的时候没有把过程记录下来，我那时候入门是直接写了一个增删改查的页面，至于前端页面我用的是Thymeleaf模板引擎，相关资料我参考的是这里，这是大神自己写的springBoot系列教程demo，可以把项目clone下来慢慢学。
我这里就直接从spring boot访问数据库的几种方式开始做学习笔记了，如果有人看并且可以从中学到东西，那就太好了（虽然并不会有人看到...）

方式一：spring boot+jdbcTemplate

1.导入需要的依赖包

<?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.baidu</groupId>
    <artifactId>ch7-thymeleaf</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>ch7-thymeleaf</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.4.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>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--thymeleaf-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!-- 单元测试依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--web相关依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--JDBC-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- h2 数据源连接驱动 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2.实体类

package com.baidu.ch7thymeleaf.pojo;

import java.io.Serializable;

/**
 * @author huangdongjiang
 * @date 2018/8/27 16:07
 */
public class Book implements Serializable {
    private static final long serialVersionUID = 3537921742065870581L;
    /**
     * 编号
     */
    private Long id;

    /**
     * 书名
     */
    private String name;

    /**
     * 作者
     */
    private String writer;

    /**
     * 简介
     */
    private String introduction;

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    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 String getWriter() {
        return writer;
    }

    public void setWriter(String writer) {
        this.writer = writer;
    }

    public String getIntroduction() {
        return introduction;
    }

    public void setIntroduction(String introduction) {
        this.introduction = introduction;
    }
}

3.根据上面的的实体类创建好对应的数据表，数据库我用的是Mysql
4.配置application.properties文件

## 是否开启缓存
spring.thymeleaf.cache=false
## 渲染之前检查模板是否存在
spring.thymeleaf.check-template=true
##检查模板位置是否存在。
spring.thymeleaf.check-template-location=true
##为Web框架启用Thymeleaf视图解析。
spring.thymeleaf.enabled=true
##模板文件编码。
spring.thymeleaf.encoding=UTF-8
#模板
spring.thymeleaf.mode=HTML5
#前缀
spring.thymeleaf.prefix=classpath:/templates/
#响应数据格式
spring.thymeleaf.servlet.content-type=text/html
#后缀
spring.thymeleaf.suffix=.html
#mysql相关配置
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

5.DAO层接口IBookDao

package com.baidu.ch7thymeleaf.dao;

import com.baidu.ch7thymeleaf.pojo.Book;

import java.util.List;

/**
 * @author huangdongjiang
 * @date 2018/8/27 21:53
 */
public interface IBookDao {
    /**
     * 查所有书
     * @return 所有的书本
     */
    List<Book> findAll();

    /**
     * 添加一本书
     * @return 返回添加的书本
     */
    Book insertByBook(Book book);

    /**
     * 更新书本信息
     * @param book 要更新的书
     * @return 跟新的书
     */
    Book update(Book book);

    /**
     * 根据id删除书本
     * @param id 要删除的书本的id
     * @return 删除的书本
     */
    void delete(Long id);

    /**
     * 根据id查找书本
     * @param id 要查找书本的id
     * @return 返回要查找的书本
     */
    Book findById(Long id);
}

实现类：BookDaoImpl

package com.baidu.ch7thymeleaf.dao.impl;

import com.baidu.ch7thymeleaf.dao.IBookDao;
import com.baidu.ch7thymeleaf.pojo.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author huangdongjiang
 * @date 2018/8/27 22:04
 */
@Repository
public class BookDaoImpl implements IBookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Book> findAll() {
        String sql = "SELECT id,name,writer,introduction FROM t_book";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(Book.class));
    }

    @Override
    public Book insertByBook(Book book) {
        String sql = "INSERT INTO t_book(name,writer,introduction) VALUES(?,?,?)";
        jdbcTemplate.update(sql, book.getName(),book.getWriter(), book.getIntroduction());
        return book;
    }

    @Override
    public Book update(Book book) {
        String sql = "UPDATE t_book SET name=?,writer=?,introduction=? WHERE id=?";
        int i = jdbcTemplate.update(sql, book.getName(), book.getWriter(),
                book.getIntroduction(), book.getId());
        System.out.println("更新书本的id是：" + book.getId());
        return book;
    }

    @Override
    public void delete(Long id) {
        String sql = "DELETE FROM t_book WHERE id=?";
        jdbcTemplate.update(sql, id);
    }

    @Override
    public Book findById(Long id) {
        String sql = "SELECT id,name,writer,introduction FROM t_book WHERE id=?";
        return (Book) jdbcTemplate.queryForObject(sql, new Object[]{id}, new BeanPropertyRowMapper(Book.class));
    }
}

6.业务层接口和实现类
IBookService

package com.baidu.ch7thymeleaf.service;

import com.baidu.ch7thymeleaf.pojo.Book;

import java.util.List;

/**
 * @author huangdongjiang
 * @date 2018/8/27 16:10
 */
public interface IBookService {
    /**
     * 获取所有 Book
     */
    List<Book> findAll();

    /**
     * 新增 Book
     *
     * @param book {@link Book}
     */
    Book insertByBook(Book book);

    /**
     * 更新 Book
     *
     * @param book {@link Book}
     */
    Book update(Book book);

    /**
     * 删除 Book
     *
     * @param id 编号
     */
    void delete(Long id);

    /**
     * 获取 Book
     *
     * @param id 编号
     */
    Book findById(Long id);
}

BookServiceImpl

package com.baidu.ch7thymeleaf.service.impl;

import com.baidu.ch7thymeleaf.dao.IBookDao;
import com.baidu.ch7thymeleaf.pojo.Book;
import com.baidu.ch7thymeleaf.service.IBookService;
import com.sun.org.apache.bcel.internal.generic.RETURN;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author huangdongjiang
 * @date 2018/8/27 16:11
 */
@Service
public class BookServiceImpl implements IBookService {

    @Autowired
    private IBookDao bookDao;

    @Override
    public List<Book> findAll() {
        return bookDao.findAll();
    }

    @Override
    public Book insertByBook(Book book) {
        bookDao.insertByBook(book);
        return book;
    }

    @Override
    public Book update(Book book) {
        bookDao.update(book);
        return book;
    }

    @Override
    public void delete(Long id) {
        bookDao.delete(id);
    }

    @Override
    public Book findById(Long id) {
        return bookDao.findById(id);
    }
}

7.控制层

package com.baidu.ch7thymeleaf.controller;

import com.baidu.ch7thymeleaf.pojo.Book;
import com.baidu.ch7thymeleaf.service.IBookService;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;

/**
 * @author huangdongjiang
 * @date 2018/8/27 16:23
 */
@Controller//@RESTController
@RequestMapping("/book")
public class BookController {
    private static final Log log = LogFactory.getLog(BookController.class);

    private static final String BOOK_FORM_PATH_NAME = "bookForm";
    private static final String BOOK_LIST_PATH_NAME = "bookList";
    private static final String REDIRECT_TO_BOOK_URL = "redirect:/book";

    @Autowired
    private IBookService bookService;

    /**
     * 获取数的列表
     * 处理"/book"的get请求，获取所有书的列表
     * @return 书的列表
     */
    @RequestMapping(method = RequestMethod.GET)
    public String getBookList(ModelMap modelMap) {
        log.info("展示书单方法运行...");
        modelMap.addAttribute("bookList", bookService.findAll());
        return BOOK_LIST_PATH_NAME;
    }

    /**
     * 获取创建book表单
     * @return book表单
     */
    @RequestMapping(value = "/create", method = RequestMethod.GET)
    public String createBookForm(ModelMap modelMap) {
        log.info("跳转到列表...");
        modelMap.addAttribute("book", new Book());
        modelMap.addAttribute("action", "create");
        return BOOK_FORM_PATH_NAME;
    }
    /**
     * 创建book
     * 处理"/book/create"的post请求，用来新建book信息
     * 通过@ModelAttribute绑定表单实体参数，也通过@RequestParam传递参数
     */
    @RequestMapping(value = "/create", method = RequestMethod.POST)
    public String postBook(@ModelAttribute Book book) {
        log.info("添加书本到列表中...");
        bookService.insertByBook(book);
        return REDIRECT_TO_BOOK_URL;
    }
    /**
     * 获取更新Book表单
     * 处理"/book/update/{id}"的get请求，通过URL中的id来获取Book信息
     * URL中的id，通过@PathVariable
     */
    @RequestMapping(value = "/update/{id}", method = RequestMethod.GET)
    public String updateBook(@PathVariable Long id, ModelMap modelmap) {
        log.info("更新方法运行...");
        log.info(bookService.findById(id));
        modelmap.addAttribute("book", bookService.findById(id));
        modelmap.addAttribute("action", "update");
        return BOOK_FORM_PATH_NAME;
    }
    /**
     * 更新book
     * 处理"/update"的PUT请求，用来更新Book的信息
     */
    @RequestMapping(value = "/update", method = RequestMethod.POST)
    public String putBook(@ModelAttribute Book book) {
        log.info("更新书本到列表中...");
        log.info(book);
        bookService.update(book);
        return REDIRECT_TO_BOOK_URL;
    }
    /**
     *删除book
     * 处理"/book/{id}"的get请求，用来删除book信息
     */
    @RequestMapping(value = "/delete/{id}", method = RequestMethod.GET)
    public String delete(@PathVariable Long id) {
        log.info("删除书本...");
        bookService.delete(id);
        return REDIRECT_TO_BOOK_URL;
    }
}

8.三层架构到这里就写完了，这里就应该结合Thymeleaf加入前端页面了，另外要注意spring boot工程的目录结构的最佳实践，也就是大家公认的目录结构应该这样建，之所以有这个东西是因为spring boot有一个启动类，要扫描加载的注解很多，如果目录结构的层级关系建的不好的话有可能有些类不能被扫描到，从而导致项目启动失败。具体怎么建请参考这篇文章
bookList.html

<!DOCTYPE html>
<html lang="zh-CN" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <script type="text/javascript" th:src="@{https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js}"></script>
    <link th:href="@{https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css}" rel="stylesheet"/>
    <link th:href="@{/static/css/default.css}" rel="stylesheet"/>
    <link rel="icon" th:href="@{/static/images/favicon.ico}" type="image/x-icon"/>
    <meta charset="UTF-8"/>
    <title>书籍列表</title>
</head>

<body>

<div class="contentDiv">

    <h5> 《Spring Boot 2.x 核心技术实战 - 上 基础篇》第 4 章《模板引擎》Demo </h5>

    <table class="table table-hover table-condensed">
        <legend>
            <strong>书籍列表</strong>
        </legend>
        <thead>
        <tr>
            <th>书籍编号</th>
            <th>书名</th>
            <th>作者</th>
            <th>简介</th>
            <th>管理</th>
        </tr>
        </thead>
        <tbody>
        <tr th:each="book : ${bookList}">
            <th scope="row" th:text="${book.id}"></th>
            <td><a th:href="@{/book/update/{bookId}(bookId=${book.id})}" th:text="${book.name}"></a></td>
            <td th:text="${book.writer}"></td>
            <td th:text="${book.introduction}"></td>
            <td><a class="btn btn-danger" th:href="@{/book/delete/{bookId}(bookId=${book.id})}">删除</a></td>
        </tr>
        </tbody>
    </table>

    <div><a class="btn btn-primary" href="/book/create" role="button">新增书籍</a></div>
</div>

</body>
</html>

bookForm.html

<!DOCTYPE html>
<html lang="zh-CN" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <script type="text/javascript" th:src="@{https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js}"></script>
    <link th:href="@{https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css}" rel="stylesheet"/>
    <link th:href="@{/css/default.css}" rel="stylesheet"/>
    <link rel="icon" th:href="@{/images/favicon.ico}" type="image/x-icon"/>
    <meta charset="UTF-8"/>
    <title>书籍管理</title>
</head>

<body>
<div class="contentDiv">

    <h5>《Spring Boot 2.x 核心技术实战 - 上 基础篇》第 4 章《模板引擎》Demo </h5>

    <legend>
        <strong>书籍管理</strong>
    </legend>

    <form th:action="@{/book/{action}(action=${action})}" method="post" class="form-horizontal">
        <input type="hidden" name="id" th:value="${book.id}" th:field="*{book.id}"/>

        <div class="form-group">
            <label for="book_name" class="col-sm-2 control-label">书名:</label>
            <div class="col-xs-4">
                <input type="text" class="form-control" id="book_name" name="name" th:value="${book.name}"
                       th:field="*{book.name}"/>
            </div>
        </div>

        <div class="form-group">
            <label for="book_writer" class="col-sm-2 control-label">作者:</label>
            <div class="col-xs-4">
                <input type="text" class="form-control" id="book_writer" name="writer" th:value="${book.writer}"
                       th:field="*{book.writer}"/>
            </div>
        </div>

        <div class="form-group">
            <label for="book_introduction" class="col-sm-2 control-label">简介:</label>
            <div class="col-xs-4">
                <textarea class="form-control" id="book_introduction" rows="3" name="introduction"
                          th:value="${book.introduction}" th:field="*{book.introduction}"></textarea>
            </div>
        </div>

        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <input class="btn btn-primary" type="submit" value="提交"/>&nbsp;&nbsp;
                <input class="btn" type="button" value="返回" onclick="history.back()"/>
            </div>
        </div>
    </form>
</div>
</body>
</html>

方式二：spring Data JPA

用完JDBCTemplate后再用JPA操作数据库简直太方便了，JPA好像跟Hibernate语法比较相近？我之前没学过Hibernate，所以学起来相对慢一丢丢，但是问题不大。这里不再贴详细代码了，制铁关键步骤的代码，需要完整代码请去上面给出的github地址中自取。
1.导入依赖包

 <!-- Spring Data JPA 依赖 :: 数据持久层框架 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- h2 数据源连接驱动 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

2.建一个实体类，这个实体类很重要，因为自动生成的数据表是根据这个实体类来生成的。建好这个类表就会自动生成。感觉这是Hibernate强大的地方，当然，这样也是有弊端的，就是不能很灵活地处理sql语句。
这里要特别关注这些注解。
@Id是声明表的主键
@GeneraterValue 指定主键的生成策略
@Column 声明该属性与数据库字段的映射关系。

/**
     * id
     */
    @Id
    @GeneratedValue
    private Long id;

    /**
     * 书名
     */
    @Column(nullable = false)
    private String name;

    /**
     * 作者
     */
    @Column(nullable = false)
    private String writer;

    /**
     * 简介
     */
    @Column(nullable = false)
    private String introduction;

    /**
     * 空参构造
     */
    public JPABook() {
    }
    /**
     * 有参构造
     */
    public JPABook(String name, String writer, String introduction) {
        this.name = name;
        this.writer = writer;
        this.introduction = introduction;
    }
//省略了setter和getter方法

3.这里才是spring JPA神奇地方，只需要写一个dao接口继承JpaRepository<T, ID>,就能完成增删改查操作，既不能实现接口也不用写sql语句，简直是神器啊...JPA自己本身写好了一些增删改查地方法，参考JPA的APIJPA API，另外JPA还支持根据你的接口方法名结合关键字自动生生sql语句，就是说你不用关心sql语句，只需要关心业务逻辑。如果这些方法不够用的话也可以用@Query注解自己写sql语句。下面是定义类的Repository:

package com.baidu.ch08spring_data_jpa.pojo.repository;

import com.baidu.ch08spring_data_jpa.pojo.JPABook;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author huangdongjiang
 * @date 2018/8/28 16:25
 */
public interface JPABookRepository extends JpaRepository<JPABook, Long> {

}

4.业务层

@Service
public class JPABookServiceImpl implements IJPABookService {

    @Autowired
    private JPABookRepository jpaBookRepository;

    @Override
    public List<JPABook> findAll() {
        return jpaBookRepository.findAll();
    }

    @Override
    public JPABook insertByBook(JPABook jpaBook) {
        return jpaBookRepository.save(jpaBook);
    }

    @Override
    public JPABook update(JPABook jpaBook) {
        return jpaBookRepository.save(jpaBook);
    }

    @Override
    public void delete(Long id) {
        jpaBookRepository.deleteById(id);
    }

    @Override
    public JPABook findById(Long id) {
        return jpaBookRepository.getOne(id);
    }
}

方式三：spring boot + mybatis

1.导入mybatis的相关依赖包

<!--mybatis-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2.配置application.properties文件

## Mybatis 配置
mybatis.typeAliasesPackage=com.baidu.ch8mybatis.pojo
mybatis.mapperLocations=classpath:mapper/*.xml

前者用来设置别名，后者指定mapper.xml文件的位置。
另外除了以上配置还有下面这些怕配置可以选择来用：

#mybatis.type-handlers-package=type handlers处理器包路径
#mybatis.check-config-location=检查mybatis配置是否存在，一般命名为mybatis配置是否存在，一般命名为mybatis-config.xml

3.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.baidu.ch8mybatis.dao.IBookMapper">
    <resultMap id="BaseResultMap" type="com.baidu.ch8mybatis.pojo.Book">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="writer" property="writer"/>
        <result column="introduction" property="introduction"/>
    </resultMap>

    <parameterMap id="book" type="com.baidu.ch8mybatis.pojo.Book"/>

    <sql id="Base_Column_List">
        id, name, writer, introduction
    </sql>

    <!--查询所有书本-->
    <select id="findAll" resultType="Book" parameterType="java.lang.String">
        select
        <include refid="Base_Column_List"/>
        from t_book
    </select>
    <!--添加一本书-->
    <insert id="insertByBook" parameterType="Book">
        insert into
        t_book(name,writer,introduction)
        values (#{name},#{writer},#{introduction})
    </insert>
    <!--更新一本书-->
    <update id="update" parameterType="Book">
        update
        t_book
        set name=#{name},writer=#{writer},introduction=#{introduction}
    </update>
    <!--根据ID删除一本书-->
    <delete id="delete" parameterType="java.lang.Long">
        delete from
        t_book
        where id=#{id}
    </delete>
    <!--根据id查询一本书-->
    <select id="findById" parameterType="java.lang.Long" resultType="Book">
        select
        <include refid="Base_Column_List"/>
        from t_book
        where id=#{id}
    </select>
</mapper>

4.在DAO接口上加上注解,不加的话会报注入不了Bean的错误

@Mapper
public interface IBookMapper {
    ....
}

5.在启动类上加上扫描DAO的注解@MapperScan("DAO的完整包名")

@SpringBootApplication
@MapperScan("com.baidu.ch8mybatis.dao.IBookMapper")
public class Ch8MybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(Ch8MybatisApplication.class, args);
    }
}

参考文献

JPA
【参考一】https://blog.csdn.net/dandandeshangni/article/details/79497804
mybatis
【参考二】http://www.spring4all.com/article/145

作者：CH0918
链接：https://www.jianshu.com/p/7fa6dc5c35fe
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

转载于:https://my.oschina.net/boonya/blog/3091727