本系列部落格記錄自己學習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工具：
呼叫完介面，發現資料庫新增資料成功。
7.2驗證更新
呼叫更新介面將剛剛新增資料的購買日期修改為2010-01-01：
呼叫完介面，發現資料庫更新資料成功。
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個問題：
- purchaseDate欄位的值比資料庫中的少8個小時
- 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的資料刪除：
此時訪問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介面常用註解總結
部落格地址：www.zwwhnly.com
原始碼地址：github.com/zwwhnly/spr…
歡迎大家下載，有問題可以多多交流。
9.參考連結
SpringBoot 返回json格式資料時間格式配置相差8個小時的時差問題