Spring boot中使用 JdbcTemplate

 

JDBC

首先，我们来看看传统的JDBC是如何连接数据库并操作数据库的吧：

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Mysql {

    /**
     * 入口函数
     * @param arg
     */
    public static void main(String arg[]) {
        try {
            Connection con = null; //定义一个MYSQL链接对象
            Class.forName("com.mysql.jdbc.Driver").newInstance(); //MYSQL驱动
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root"); //链接本地MYSQL

            Statement stmt; //创建声明
            stmt = con.createStatement();

            //新增一条数据
            stmt.executeUpdate("insert into post(title, content, created) values('title', 'content', '2015-08-20 00:00:00')");
            ResultSet res = stmt.executeQuery("select LAST_INSERT_ID()");
            int ret_id;
            if (res.next()) {
                ret_id = res.getInt(1);
                System.out.print(ret_id);
            }

        } catch (Exception e) {
            System.out.print("MYSQL ERROR:" + e.getMessage());
        }

    }
}

这是一段非常经典的使用JDBC访问MySQL数据库的代码，它的作用是向数据库post表中增加一条记录('title', 'content', '2015-08-20 00:00:00')并获取自增id。其中的步骤可以抽象为：

• 定义连接参数（包括动态加载驱动类com.mysql.jdbc.Driver）
• 建立数据库连接
• 指定Sql语句并参数化
• 执行Sql语句
• 获取查询结果并处理
• 处理异常
• 事务管理
• 释放各类资源——Statement，ResultSet，Connection

对于上述步骤，实际上很多部分都是通用的——即对于每一次数据库访问都没有变化，例如定义参数、打开连接、处理异常、事务处理、资源释放。完全没有必要再每一次访问里都编写这些代码，一种办法是将这些代码封装起来。Spring JDBC正是提供了这样一种封装，它将与JDBC API交互的诸多细节隐藏起来，通过Spring JDBC开发者能够更加专注于业务代码（建立并执行Sql语句，处理查询结果等）的开发。

JDBC访问数据库的步骤，在Spring JDBC中被抽象为JdbcTemplate，这是Spring JDBC中最核心的类。以下是一些JdbcTemplate的常用方法：

获取Table中记录数量

int rowCount = this.jdbcTemplate.queryForObject("select count(*) from post", Integer.class);

SQL语句参数化

int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
        "select count(*) from post where id = ?", Integer.class, 1L);

其中Sql语句中需要被参数化的对象用?作为占位符替代，可以指定多个占位符，这个方法最后的参数也是变长的。

获取String对象

String title = this.jdbcTemplate.queryForObject(
        "select title from post where id = ?",
        new Object[]{1L}, String.class);

获取业务对象

通常查询结果不是一个简单的基本类型（Integer, String），而是我们自定义的业务对象，例如我们的Post对象：

这时不能直接在queryForObject()方法中直接指定参数Post.class，因为JdbcTempate无法知道应该如何将ResultSet转化为Post对象，所以这时需要实现转换的方法：

List<Post> posts = this.jdbcTemplate.query(
                "select * from post",
                (rs, rowNum) -> new Post(rs.getLong("id"), rs.getString("title"), rs.getString("content"), rs.getDate("created")));

jdbcTemplate.query()方法的第二个参数是接口RowMapper<T>的实现，它的作用就是把ResultSet转化为Post对象，上述写法是在Java 8中使用Lambda表达式的简化结果。

JdbcTemplate的增加/删除/更新方法

上面提到的都是查询方法，而对于数据库的写操作相对于查询来说，要简单很多，在写操作中，使用?参数化也是常用的手段：

this.jdbcTemplate.update(
        "insert into post(title, content, created) values(?, ?, ?)",
        "title", "content", "2015-08-20 00:00:00");

this.jdbcTemplate.update(
        "update post set title = ?, content = ? where id = ?", "update", "update", 1L);

this.jdbcTemplate.update(
        "delete from post where id = ?", 1L);

JdbcTemplate其他操作

JdbcTemplate.execute(..)方法可以执行任何Sql语句，例如创建Table:

this.jdbcTemplate.execute("create table post (id integer, name varchar(100))");

调用存储过程：

this.jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
        Long.valueOf(unionId));

 

在应用中使用JdbcTemplate

为了简化我们的开发，我们暂时使用内存数据库进行开发，这样可以省去我们对数据库的一些管理工作。最后，我们再做一点点简单的配置，将我们的所有数据移动到Mysql数据库中。

添加依赖

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
</dependency>

初始化数据库

首先我们创建一张post表存储POST相关的数据。

Spring JDBC将JDBC通用的方法抽象成了一个JdbcTemplate类。我们可以通过我们通过JdbcTemplate来操作我们的数据可。同时，我们使用@PostConstruct标注使得我们的创建脚本在应用启动时执行：

@PostConstruct
public void initDatabase(){
    jdbcTemplate.execute("CREATE TABLE `post` ("
          + "`id` int(11) unsigned NOT NULL AUTO_INCREMENT,"
          + "`title` varchar(255) NOT NULL DEFAULT '',"
          + "`content` text,"
          + "`created` datetime NOT NULL,"
          + "PRIMARY KEY (`id`)"
          + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
}

但是这种方法在java代码里拼接SQL语句，并不容易维护。好在Spring Boot为我们提供了另外一种办法，在src/main/resources/目录下添加schema.sql：

CREATE TABLE IF NOT EXISTS `post`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` text,
  `created` datetime NOT NULL);

INSERT INTO `post`(`title`, `content`, `created`) values('欢迎来到天码营', '这是系统自动为你生成的博客', '2015-01-01');

获取博客列表

在上一节课中我们知道通过select语句我们可以查询到相应的数据，我们现在通过jdbcTemplate的query方法来获取所有的博客列表：

@Controller
public class IndexController {

    @Autowired 
    private JdbcTemplate jdbcTemplate;

    @RequestMapping(value = "", method = RequestMethod.GET)
    public String index(Model model) {
        model.addAttribute("posts", this.jdbcTemplate.query(
                "select * from post",
                (rs, rowNum) -> new Post(rs.getLong("id"), rs.getString("title"), rs.getString("content"), rs.getDate("created"))));
        return "index";
    }
}

创建博客

创建博客时比我们之前所介绍的稍微复杂一点。因为除了创建博客外，我们还需要获取该博客的id，再将页面重定向到该博客对应的页面。我们可以通过数据库系统函数last_insert_id()类得到创建完成后的博客id：

@RequestMapping(value = "/", method = RequestMethod.POST)
public String create(@Valid Post post, BindingResult result) {
    if (result.hasErrors()) {
        return "create";
    }
    jdbcTemplate.update("insert into post(title, content, created) values(?, ?, ?)",
            post.getTitle(), post.getContent(), new Date());

    Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
    return "redirect:/posts/" + id;
}

获取单个博客

jdbcTemplate的queryForObject方法可以获得单个的对象，注意当select的结果不只一个或者为0时，Spring JDBC会抛出错误：

@RequestMapping(value = "/{id}", method = RequestMethod.GET)
public String get(@PathVariable long id, Model model) {
    model.addAttribute("post", this.jdbcTemplate.queryForObject(
            "select * from post where id = ?", new Object[]{id},
            (rs, rowNum) -> new Post(rs.getLong("id"), rs.getString("title"), rs.getString("content"), rs.getDate("created"))));

    return "post"; 

} 

 

添加评论

新建数据库表，在schema.sql中添加下列建表语句：

CREATE TABLE IF NOT EXISTS `comment`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text,
  `post` int(11) NOT NULL,
  `created` datetime NOT NULL);

添加CommentController来处理创建Comment的POST请求，这里，我们暂时不处理出错的情况。

@Controller
@RequestMapping("/comments")
public class CommentController {

    @Autowired 
    private JdbcTemplate jdbcTemplate;

    @RequestMapping(value = "", method = RequestMethod.POST)
    public String create(@Valid Comment comment, BindingResult result) {
        if (result.hasErrors()) {
            //TODO: 如何处理评论数据出错的情况？
            return "redirect:/posts/" + comment.getPost();
        }
        this.jdbcTemplate.update(
                "insert into comment(content, post, created) values(?, ?, ?);",
                comment.getContent(), comment.getPost(), new Date());
        return "redirect:/posts/" + comment.getPost();
    }

}

然后，我们在post.html中添加创建评论的表单：

<form th:action="@{/comments}" method="post">
    <input type="hidden" name="post" th:value="${post.id}"></input>
    <div class="form-group">
      <textarea name="content" id="new-comment" class="form-control"></textarea>
      <div class="alert alert-danger" style="display: none;"></div>
    </div>

    <div class="form-group">
      <input type="submit" class="btn btn-primary" value="发布"></input>
    </div>
</form>

最后，我们在博客的GET请求中获取所有的Comment列表，并将其显示在页面中：

model.addAttribute("comments", this.jdbcTemplate.query(
        "select * from comment where post = ?", new Object[]{id},
        (rs, rowNum) -> new Comment(rs.getLong("id"), rs.getString("content"), rs.getDate("created"), rs.getLong("post"))));

<div class="col-sm-10">
    <div class="blog-post" th:each="comment : ${comments}">
      <p class="blog-post-meta" th:text="${#dates.format(comment.created, 'yyyy-MM-dd')}">2015年2月3日</p>
      <p class="blog-post-content" th:text="${comment.content}">Amet risus. Dolor ultrices justo, praesent eos nisl lacus, consectetuer vitae lorem cras magna dolor, mauris libero turpis aliquam sed, at sapien tellus penatibus accumsan nec. Parturient amet felis morbi. Quis ac penatibus elementum lacus, vestibulum sem tellus arcu.</p>
    </div>
</div>

使用MySQL

数据库的一个最重要的功能是持久化，所以在真正的应用环境里，我们必须要使用MySQL这一类数据库进行数据存储。但是，在内存数据库的例子中，没有任何代码定义了数据库地址，Spring Boot就直接使用了H2Database。如果需要使用MySQL呢？

Spring Boot在初始化DataSource对象时，会根据外部配置来进行。如果我们不进行任何配置，那么它会使用H2Database的JDBC驱动以及连接地址。如果我们需要使用其它类型的数据库，编辑src/main/resources/application.properties：

spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

这是一个样例文件，需要根据自己的实际情况将几个参数重写。当然也不要忘了在Maven依赖中加入mysql-jdbc驱动：

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

这样Spring Boot应用启动后，会根据spring.datasource.*属性创建一个我们需要的DataSource对象，这样就能够对MySQL数据库进行操作了。本质上它和我们在XML和Java Config文件中自己顶一个DataSource的Bean是完全一样的，但是这样更加简洁、高效。

https://course.tianmaying.com/web-development+spring-jdbc#5