目录

1、创建数据库表

2、引入项目依赖

3、application.yml配置文件

4、创建Article实体类

5、articleDao

6、创建ArticleService

7、创建ArticleServiceImpl

8、ArticleController

9、测试

1、查询一篇文章

2、查询所有文章

3、添加一篇文章

4、修改一篇文章

5、删除一篇文章

​6、事务操作

一、Springboot集成SpringJDBC单数据源

1、创建数据库表

DROP TABLE IF EXISTS `article`;
CREATE TABLE `article`  (`id` int(11) NOT NULL AUTO_INCREMENT,`author` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`title` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`content` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`create_time` datetime(0) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、引入项目依赖

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.4</version><relativePath/></parent><groupId>com.lyf</groupId><artifactId>springboot_jdbc</artifactId><version>0.0.1-SNAPSHOT</version><name>springboot_jdbc</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><!--web--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!--test--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!--spring-boot-jdbc--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><!-- mysql--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.19</version></dependency><!--swagger--><dependency><groupId>io.swagger</groupId><artifactId>swagger-models</artifactId><version>1.5.16</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

3、application.yml配置文件

server:port: 8888
spring:jackson:date-format: yyyy-MM-dd HH:mm:sstime-zone: GMT+8datasource:url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTCusername: rootpassword: '密码'driver-class-name: com.mysql.cj.jdbc.Driver

4、创建Article实体类

package com.lyf.springboot_jdbc.model;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;/*** 实体类*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Article {private Long id;private String author;private String title;private String content;@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")private Date createTime;
}

5、articleDao

package com.lyf.springboot_jdbc.dao;
import com.lyf.springboot_jdbc.model.Article;
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;/*** @Author 刘耀福* @create 2021/9/6 20:30*/
@Repository
public class ArticleDao {//jdbcTemplate.update适合于insert 、update和delete操作；@Autowiredprivate JdbcTemplate jdbcTemplate;/*** 保存文章* @param article*/public void save(Article article){jdbcTemplate.update("INSERT INTO article(author, title,content,create_time) values(?, ?, ?, ?)",article.getAuthor(),article.getTitle(),article.getContent(),article.getCreateTime());}/*** 删除文章* @param id*/public void deleteById(Long id) {jdbcTemplate.update("DELETE FROM article WHERE id = ?",id);}//更新文章public void updateById(Article article) {jdbcTemplate.update("UPDATE article SET author = ?, title = ? ,content = ?,create_time = ? WHERE id = ?",article.getAuthor(),article.getTitle(),article.getContent(),article.getCreateTime(),article.getId());}//根据id查询一条记录：BeanPropertyRowMapper 将数据库的字段映射为我们对象的字段public Article findById(Long id) {return jdbcTemplate.queryForObject("SELECT * FROM article WHERE id=?",new Object[]{id},new BeanPropertyRowMapper<>(Article.class));}//查询所有public List<Article> findAll(){return jdbcTemplate.query("SELECT * FROM article ",new BeanPropertyRowMapper<>(Article.class));}
}

6、创建ArticleService

package com.lyf.springboot_jdbc.service;
import com.lyf.springboot_jdbc.model.Article;
import java.util.List;/*** @Author 刘耀福* @create 2021/9/6 20:49*/
public interface ArticleService {/*** 保存一篇文章* @param article*/public void saveArticle(Article article);/*** 删除一篇文章* @param id*/void deleteArticle(Long id);/*** 更新一篇文章* @param article*/void updateArticle(Article article);/*** 查询一篇文章* @param id* @return*/Article getArticle(Long id);/*** 查询所有文章* @return*/List<Article> getAll();
}

7、创建ArticleServiceImpl

package com.lyf.springboot_jdbc.service.impl;
import com.lyf.springboot_jdbc.dao.ArticleDao;
import com.lyf.springboot_jdbc.model.Article;
import com.lyf.springboot_jdbc.service.ArticleService;
import com.sun.xml.internal.bind.v2.TODO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;/*** @Author 刘耀福* @create 2021/9/6 20:56*/
@Service
public class ArticleServiceImpl implements ArticleService {@Autowiredprivate ArticleDao articleDao;@Overridepublic void saveArticle(Article article) {articleDao.save(article);}@Overridepublic void deleteArticle(Long id) {articleDao.deleteById(id);}@Overridepublic void updateArticle(Article article) {if(article.getId()==null){//TODO 执行一些操作}articleDao.updateById(article);}@Overridepublic Article getArticle(Long id) {return articleDao.findById(id);}@Overridepublic List<Article> getAll() {return articleDao.findAll();}
}

8、ArticleController

package com.lyf.springboot_jdbc.controller;
import com.lyf.springboot_jdbc.model.Article;
import com.lyf.springboot_jdbc.service.ArticleService;
import com.lyf.springboot_jdbc.utils.AjaxResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;@Slf4j
@Controller
@RequestMapping("/rest")
public class ArticleController {@Resourceprivate ArticleService articleService;/*** 查询所有，使用GET方法* @return*/@GetMapping("/articles")public @ResponseBody AjaxResponse getArticle(){List<Article> articles = articleService.getAll();log.info("articles:" + articles);return AjaxResponse.success(articles);}/*** 获取一篇Article，使用GET方法,根据id查询一篇文章* @param id* @return*/@GetMapping("/articles/{id}")public @ResponseBody AjaxResponse getArticle(@PathVariable("id") Long id){Article article = articleService.getArticle(id);log.info("article:" + article);return AjaxResponse.success(article);}/*** 增加一篇Article ，使用POST方法* @param article* @return*/@PostMapping("/articles")public @ResponseBody AjaxResponse saveArticle(@RequestBody Article article){articleService.saveArticle(article);log.info("saveArticle:" + article);return AjaxResponse.success();}/*** 更新一篇Article，使用PUT方法，以id为主键进行更新* @param article* @return*/@PutMapping("/articles")public @ResponseBody AjaxResponse updateArticle(@RequestBody Article article){articleService.updateArticle(article);log.info("updateArticle:" + article);return AjaxResponse.success();}/*** 删除一篇Article，使用DELETE方法，参数是id* @param id* @return*/@DeleteMapping("/articles/{id}")public @ResponseBody AjaxResponse deleteArticle(@PathVariable("id") Long id){articleService.deleteArticle(id);log.info("deleteArticle:" + id);return AjaxResponse.success();}}

9、测试

1、查询一篇文章

 2、查询所有文章

springboot动态数据源？

 3、添加一篇文章

 4、修改一篇文章

5、删除一篇文章

10、事务操作

 在修改的方法上添加@Transactional注解，我们在里面制造了异常

/*** @Transactional:事务回滚添加此注解即可解决* @param article*/@Override@Transactionalpublic void updateArticle(Article article) {if(article.getId()==null){//TODO 执行一些操作}int a = 2/0;articleDao.updateById(article);}

postman中请求的数据为：

 打开数据库查看，数据库信息回滚了：