SpringBoot2+Druid+JdbcTemplate+MySql实现增删改查
1.配置pom.xml文件
<?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 http://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.1.4.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.czhappy.bootlearn</groupId> <artifactId>04-Spring-Boot-JdbcTemplate</artifactId> <version>0.0.1-SNAPSHOT</version> <name>04-Spring-Boot-JdbcTemplate</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- 数据库 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- JdbcTemplate --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- Druid Pool --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.16</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
2.修改配置文件application.yml：
server:
port: 8080
servlet:
context-path: /web
spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/springbootlearn?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username: root
password: root
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
# 配置连接在池中的最小生存时间
min-evictable-idle-time-millis: 300000
validation-query: select 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache，并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-open-prepared-statements: 20
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
filters: stat,wall
# Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔
aop-patterns: com.springboot.servie.*
# WebStatFilter配置
web-stat-filter:
enabled: true
# 添加过滤规则
url-pattern: /*
# 忽略过滤的格式
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
# StatViewServlet配置
stat-view-servlet:
enabled: true
# 访问路径为/druid时，跳转到StatViewServlet
url-pattern: /druid/*
# 是否能够重置数据
reset-enable: false
# 需要账号密码才能访问控制台
login-username: druid
login-password: druid123
# IP白名单
# allow: 127.0.0.1
# IP黑名单（共同存在时，deny优先于allow）
# deny: 192.168.1.218
# 配置StatFilter
filter:
stat:
log-slow-sql: true
3.实现增删改查功能：
创建测试表tb_student，并插入几条测试数据
CREATE TABLE `tb_student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sno` varchar(10) DEFAULT NULL, `sname` varchar(100) DEFAULT NULL, `ssex` char(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `tb_student` VALUES ('1', '001', '张三', 'M'); INSERT INTO `tb_student` VALUES ('2', '002', '李四', 'M'); INSERT INTO `tb_student` VALUES ('3', '003', '王五', 'F');
创建对应实体：
package com.czhappy.bootlearn.SpringBootJdbcTemplate.entity; import java.io.Serializable; public class Student implements Serializable { private static final long serialVersionUID = -339516038496531943L; private Integer id; private String sno; private String name; private String sex; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public static long getSerialVersionUID() { return serialVersionUID; } public String getSno() { return sno; } public void setSno(String sno) { this.sno = sno; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } }
StudentDaoImp类代码
package com.czhappy.bootlearn.SpringBootJdbcTemplate.dao.impl; import com.czhappy.bootlearn.SpringBootJdbcTemplate.dao.StudentDao; import com.czhappy.bootlearn.SpringBootJdbcTemplate.entity.Student; import com.czhappy.bootlearn.SpringBootJdbcTemplate.mapper.StudentMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.Types; import java.util.List; import java.util.Map; @Repository("studentDao") public class StudentDaoImp implements StudentDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public int add(Student student) { // String sql = "insert into student(sno,sname,ssex) values(?,?,?)"; // Object[] args = { student.getSno(), student.getName(), student.getSex() }; // int[] argTypes = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR }; // return this.jdbcTemplate.update(sql, args, argTypes); String sql = "insert into tb_student(sno,sname,ssex) values(:sno,:name,:sex)"; NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource()); return npjt.update(sql, new BeanPropertySqlParameterSource(student)); } @Override public int update(Student student) { String sql = "update tb_student set sname = ?,ssex = ? where sno = ?"; Object[] args = { student.getName(), student.getSex(), student.getSno() }; int[] argTypes = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR }; return this.jdbcTemplate.update(sql, args, argTypes); } @Override public int deleteBysno(String sno) { String sql = "delete from tb_student where sno = ?"; Object[] args = { sno }; int[] argTypes = { Types.VARCHAR }; return this.jdbcTemplate.update(sql, args, argTypes); } @Override public List<Map<String, Object>> queryStudentsListMap() { String sql = "select * from tb_student"; return this.jdbcTemplate.queryForList(sql); } @Override public Student queryStudentBySno(String sno) { String sql = "select * from tb_student where sno = ?"; Object[] args = { sno }; int[] argTypes = { Types.VARCHAR }; List<Student> studentList = this.jdbcTemplate.query(sql, args, argTypes, new StudentMapper()); if (studentList != null && studentList.size() > 0) { return studentList.get(0); } else { return null; } } }
可以直接使用List<Map<String, Object>>来接收；也可以使用库表对应的实体对象来接收，需要手动创建一个实现了org.springframework.jdbc.core.RowMapper的对象，用于将实体对象属性和库表字段一一对应：
package com.czhappy.bootlearn.SpringBootJdbcTemplate.mapper; import com.czhappy.bootlearn.SpringBootJdbcTemplate.entity.Student; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; public class StudentMapper implements RowMapper<Student> { @Override public Student mapRow(ResultSet rs, int rowNum) throws SQLException { Student student = new Student(); student.setId(rs.getInt("id")); student.setSno(rs.getString("sno")); student.setName(rs.getString("sname")); student.setSex(rs.getString("ssex")); return student; } }
最终项目目录如下：
启动项目，测试查询接口：
