十一 . Springboot-Jdbc

最新推荐文章于 2022-03-19 14:58:58 发布

原创最新推荐文章于 2022-03-19 14:58:58 发布 · 213 阅读

0 ·

CC 4.0 BY-SA版权

springboot 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了如何在SpringBoot项目中整合JDBC进行数据库操作，包括项目搭建、依赖配置、YAML配置、代码编写等步骤，并提供了具体的示例代码。

SpringBoot的数据访问层都是封装在可以直接使用jdbc

11.1 步骤分析

1 准备一个springboot项目
	略过
2 pom导入对应stater
3 application.yml配置
4 入口类
5 写代码测试

11.2 pom.xml

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

11.3 application.yml

在application.properties文件中配置mysql连接配置文件
########################################################
###datasource
########################################################
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10

11.4 入口类

11.5 代码测试

11.5.1 表设计

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50728
Source Host           : localhost:3306
Source Database       : mytest

Target Server Type    : MYSQL
Target Server Version : 50728
File Encoding         : 65001

Date: 2020-04-04 23:20:48
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('101', 'IT部');
INSERT INTO `department` VALUES ('102', '销售部');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `sex` int(2) DEFAULT NULL,
  `birth` datetime DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('101', '王小八', 'ba@qq.com', '1', '2020-03-29 11:48:51', '101');
INSERT INTO `employee` VALUES ('102', '小花', 'hua@qq.com', '0', '2020-03-29 16:53:03', '101');

11.5.2 代码测试

@RestController
@RequestMapping("/jdbc")
public class JDBCController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/findAll")
    public List<Map<String, Object>> findAll(){
        String sql = "select * from employee";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }
    @GetMapping("/save")
    public String save(){
        String sql = "INSERT INTO employee (name,email,sex,birth,department_id) VALUES(?,?,?,?,?)";
        jdbcTemplate.update(sql,"小花","hua@qq.com",0,new Date(),101);
        return "success";
    }
    @GetMapping("/update")
    public String update(){
        String sql = "update employee set name=?,email=?,sex=?,birth=?,department_id=? where id= ?";
        jdbcTemplate.update(sql,"小花儿","huaxx@qq.com",0,new Date(),101,103);
        return "success";
    }
    @GetMapping("/delete/{id}")
    public String delete(@PathVariable("id")Integer id){
        String sql = "delete from employee where id=?";
        jdbcTemplate.update(sql,id);
        return "success";
    }
}