SpringBoot2+Druid+JdbcTemplate+MySql实现增删改查 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;
}
}

最终项目目录如下：

启动项目，测试查询接口：