Spring Boot入门(五)：使用JDBC访问MySql数据库
本系列博客记录自己学习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.源码地址
正文到此结束
- 本文标签： map App list 删除 mapper GMT web js CTO value http ORM JDBC update https sql 代码 key core bean HashMap java pom 阿里巴巴 IDE XML src Service Mysql数据库 spring 自动化 cat 数据 json lib Property springboot mysql db 数据库 UI Select dataSource ACE Spring Boot tab find 配置 parse 开发 Word id 源码 tar 安装 HTML REST 博客 IO 2019 root
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
