Spring Boot入门(五)：使用JDBC访问MySql数据库

时间：2022-05-24 11:15:31

本系列博客记录自己学习Spring Boot的历程，如帮助到你，不胜荣幸，如有错误，欢迎指正！

在程序开发的过程中，操作数据库是必不可少的部分，前面几篇博客中，也一直未涉及到数据库的操作，本篇博客

就讲解下在Spring Boot中如何使用JDBC操作数据库。

1.前期准备

假设你的机器已经安装好了MySql，我们先执行如下语句创建数据库和表：

CREATE DATABASE springbootdemo_db

create table book_list
(
  book_id       int auto_increment primary key,
  book_name     varchar(50) not null comment '书名',
  book_author   varchar(20) not null comment '作者',
  purchase_date date        not null comment '购买日期'
)
comment '书单';

2.修改pom文件

pom文件引入jdbc的starter pom和mysql的驱动，因后面要编写控制器，因此也引入下阿里巴巴的fastjson：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.35</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>

3.配置数据源

在resources/application.yml中配置数据源：

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springbootdemo_db
    username: root
    password:

4.定义数据库实体

定义数据库实体Book：

package com.zwwhnly.springbootdemo.jdbc;

import java.util.Date;

public class Book {
    private Integer bookId;
    private String bookName;
    private String bookAuthor;
    private Date purchaseDate;

    public Integer getBookId() {
        return bookId;
    }

    public void setBookId(Integer bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public Date getPurchaseDate() {
        return purchaseDate;
    }

    public void setPurchaseDate(Date purchaseDate) {
        this.purchaseDate = purchaseDate;
    }
}

5.编写Dao层代码

定义接口BookDao：

package com.zwwhnly.springbootdemo.jdbc;

import java.util.List;

public interface BookDao {
    int add(Book book);

    int update(Book book);

    int delete(Integer id);

    Book findBook(Integer id);

    List<Book> findBookList();
}

定义接口实现类BookDaoImpl：

package com.zwwhnly.springbootdemo.jdbc;

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 BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int add(Book book) {
        return jdbcTemplate.update("INSERT INTO book_list values (NULL, ?, ?, ?);",
                book.getBookName(), book.getBookAuthor(), book.getPurchaseDate());
    }

    @Override
    public int update(Book book) {
        return jdbcTemplate.update("UPDATE book_list SET book_name=?,book_author=?,purchase_date=? WHERE book_id = ?;",
                new Object[]{book.getBookName(), book.getBookAuthor(), book.getPurchaseDate(), book.getBookId()});
    }

    @Override
    public int delete(Integer id) {
        return jdbcTemplate.update("DELETE FROM book_list where book_id = ?", id);
    }

    @Override
    public Book findBook(Integer id) {
        List<Book> list = jdbcTemplate.query("SELECT * FROM book_list where book_id = ?", new Object[]{id}, new BeanPropertyRowMapper<Book>(Book.class));
        if (null != list && list.size() > 0) {
            Book book = list.get(0);
            return book;
        } else {
            return null;
        }
    }

    @Override
    public List<Book> findBookList() {
        List<Book> list = jdbcTemplate.query("SELECT * FROM book_list", new Object[]{}, new BeanPropertyRowMapper<Book>(Book.class));
        return list;
    }
}

注意：实现类添加@Repository注解，以便 Spring Boot实现自动装配。

关于自动装配的问题，可以参考之前的博客Spring入门(二)：自动化装配bean。

6.编写Service层代码

定义接口BookService：

package com.zwwhnly.springbootdemo.jdbc;

import java.util.List;

public interface BookService {
    int add(Book book);

    int update(Book book);

    int delete(Integer id);

    Book findBook(Integer id);

    List<Book> findBookList();
}

然后定义实现类BookServiceImpl：

package com.zwwhnly.springbootdemo.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service("bookService")
public class BookServiceImpl implements BookService {
    @Autowired
    private BookDao bookDao;

    @Override
    public int add(Book book) {
        return this.bookDao.add(book);
    }

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

    @Override
    public int delete(Integer id) {
        return this.bookDao.delete(id);
    }

    @Override
    public Book findBook(Integer id) {
        return this.bookDao.findBook(id);
    }

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

注意：实现类添加@Service注解，以便 Spring Boot实现自动装配。

7.编写控制器验证

最后新建控制器BookController,验证下增删改查是否成功：

package com.zwwhnly.springbootdemo.controller;

import com.alibaba.fastjson.JSONObject;
import com.zwwhnly.springbootdemo.jdbc.Book;
import com.zwwhnly.springbootdemo.jdbc.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping(value = "/jdbc/book")
public class BookController {
    @Autowired
    private BookService bookService;

    @RequestMapping(value = "getBookList", method = RequestMethod.GET)
    public Map<String, Object> getBookList() {
        List<Book> bookList = this.bookService.findBookList();
        Map<String, Object> param = new HashMap<>();
        param.put("total", bookList.size());
        param.put("rows", bookList);
        return param;
    }

    @RequestMapping(value = "/getBook/{bookId:\\d+}", method = RequestMethod.GET)
    public Book getBook(@PathVariable Integer bookId) {
        Book book = this.bookService.findBook(bookId);
        if (book == null) {
            throw new RuntimeException("查询错误");
        }
        return book;
    }

    @RequestMapping(value = "add", method = RequestMethod.POST)
    public void add(@RequestBody JSONObject jsonObject) {
        String bookName = jsonObject.getString("bookName");
        String bookAuthor = jsonObject.getString("bookAuthor");
        String purchaseDate = jsonObject.getString("purchaseDate");

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

        try {
            Book book = new Book();
            book.setBookName(bookName);
            book.setBookAuthor(bookAuthor);
            book.setPurchaseDate(simpleDateFormat.parse(purchaseDate));
            this.bookService.add(book);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("新增错误");
        }
    }

    @RequestMapping(value = "/update/{bookId:\\d+}", method = RequestMethod.PUT)
    public void update(@PathVariable Integer bookId, @RequestBody JSONObject jsonObject) {
        Book book = this.bookService.findBook(bookId);
        String bookName = jsonObject.getString("bookName");
        String bookAuthor = jsonObject.getString("bookAuthor");
        String purchaseDate = jsonObject.getString("purchaseDate");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            book.setBookName(bookName);
            book.setBookAuthor(bookAuthor);
            book.setPurchaseDate(simpleDateFormat.parse(purchaseDate));
            this.bookService.update(book);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("更新错误");
        }
    }

    @RequestMapping(value = "/delete/{bookId:\\d+}", method = RequestMethod.DELETE)
    public void delete(@PathVariable Integer bookId) {
        try {
            this.bookService.delete(bookId);
        } catch (Exception e) {
            throw new RuntimeException("删除错误");
        }
    }
}

7.1验证新增

因为新增是Post请求，因此这里我们使用下Postman工具：

Spring Boot入门(五)：使用JDBC访问MySql数据库

调用完接口，发现数据库新增数据成功。

7.2验证更新

调用更新接口将刚刚新增数据的购买日期修改为2010-01-01：

Spring Boot入门(五)：使用JDBC访问MySql数据库

调用完接口，发现数据库更新数据成功。

7.3验证获取列表

在浏览器访问http://localhost:8080/jdbc/book/getBookList，返回数据如下：

{
  "total": 2,
  "rows": [
    {
      "bookId": 1,
      "bookName": "平凡的世界",
      "bookAuthor": "路遥",
      "purchaseDate": "2009-12-31T16:00:00.000+0000"
    },
    {
      "bookId": 2,
      "bookName": "人生",
      "bookAuthor": "路遥",
      "purchaseDate": "2010-12-31T16:00:00.000+0000"
    }
  ]
}

观察返回的数据，我们发现2个问题：

  1. purchaseDate字段的值比数据库中的少8个小时
  2. purchaseDate字段显示不够友好

出现原因：Spring Boot中返回json格式默认使用jackson框架转换，而jackson框架默认的时区是GMT(相比于中国是少了8小时)。

解决方案：

如果是全局统一修改，则修改resources/application.yml，添加如下配置：

spring:
  jackson:
      time-zone: GMT+8,
      date-format: yyyy-MM-dd HH:mm:ss

如果想单个修改，则修改下实体类：

import com.fasterxml.jackson.annotation.JsonFormat;

@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
private Date purchaseDate;

再次访问http://localhost:8080/jdbc/book/getBookList，返回数据如下：

{
  "total": 2,
  "rows": [
    {
      "bookId": 1,
      "bookName": "平凡的世界",
      "bookAuthor": "路遥",
      "purchaseDate": "2010-01-01 00:00:00"
    },
    {
      "bookId": 2,
      "bookName": "人生",
      "bookAuthor": "路遥",
      "purchaseDate": "2011-01-01 00:00:00"
    }
  ]
}

7.4验证获取单个数据

在浏览器访问http://localhost:8080/jdbc/book/getBook/1，返回如下数据：

{
  "bookId": 1,
  "bookName": "平凡的世界",
  "bookAuthor": "路遥",
  "purchaseDate": "2010-01-01 00:00:00"
}

7.5验证删除

调用删除接口，将bookId为2的数据删除：

Spring Boot入门(五)：使用JDBC访问MySql数据库

此时访问http://localhost:8080/jdbc/book/getBookList，返回数据只有1条了：

{
  "total": 1,
  "rows": [
    {
      "bookId": 1,
      "bookName": "平凡的世界",
      "bookAuthor": "路遥",
      "purchaseDate": "2010-01-01 00:00:00"
    }
  ]
}

8.源码地址

原文地址：Spring Boot入门(四)：开发Web Api接口常用注解总结

博客地址：http://www.zwwhnly.com

源码地址：https://github.com/zwwhnly/springbootdemo.git

欢迎大家下载，有问题可以多多交流。

9.参考链接

SpringBoot 返回json格式数据时间格式配置相差8个小时的时差问题

Spring boot Controller类返回Date时间少8小时

Spring Boot 揭秘与实战（二） 数据存储篇 - MySQL