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";
}
}
11.6 小结
本章节讲了Springboot-jdbc的实现