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; } }
最终项目目录如下:
启动项目,测试查询接口: