JdbcTemplate使用
一、JdbcTemplate概述
JdbcTemplate它是spring框架中提供的一个对象,是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如:操作关系型数据的JdbcTemplate和HibernateTemplate,操作nosql数据库的RedisTemplate,操作消息队列的JmsTemplate等等。
二、JdbcTemplate开发步骤
- 导入依赖
- 创建数据库表和实体
- 创建JdbcTemplate对象
- 执行数据库操作
三、案例演示
1.项目结构如下
2.导入依赖
<?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> <groupId>org.example</groupId> <artifactId>spring5_JdbcTemplate01</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <!--引入德鲁伊连接池--> <!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency> <!--引入MySQL连接依赖--> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-context --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.3.22</version> </dependency> <!--spring切面 的包--> <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>5.3.18</version> </dependency> <!--springJDBC包--> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.3.18</version> </dependency> <!--spring事务控制包--> <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.3.18</version> </dependency> <!--spring-orm映射依赖包--> <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>5.3.18</version> </dependency> <!--织入包 spring-aspects 已经导入该包,这里可以不导入--> <!--<dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.9.7</version> </dependency>--> <!--aop联盟包--> <dependency> <groupId>aopalliance</groupId> <artifactId>aopalliance</artifactId> <version>1.0</version> </dependency> <!--Apache Commons日志包--> <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging --> <dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <version>1.2</version> </dependency> <!--导入lombok--> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.22</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/junit/junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.2</version> <scope>test</scope> </dependency> </dependencies> <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </includes> </resource> </resources> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>6</source> <target>6</target> </configuration> </plugin> </plugins> </build> </project>
3.准备service层接口和实现类
package com.augus.service; import com.augus.pojo.Emp; import java.util.List; public interface EmpService { //统计员工人数 int findEmpCount(); //根据编号查询员工信息 Emp findByEmpno(int empno); //根据部门编号查询属于该部门的员工 List<Emp> findByDeptno(int deptno); //新增员工信息 int addEmp(Emp emp); //修改员工信息 int updateEmp(Emp emp); //删除员工信息 int deleteEmp(int empno); }
package com.augus.service.impl; import com.augus.dao.EmpDao; import com.augus.pojo.Emp; import com.augus.service.EmpService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class EmpServiceImpl implements EmpService { @Autowired private EmpDao empDao; @Override public int findEmpCount() { return empDao.findEmpCount(); } @Override public Emp findByEmpno(int empno) { return empDao.findByEmpno(empno); } @Override public List<Emp> findByDeptno(int deptno) { return empDao.findByDeptno(deptno); } @Override public int addEmp(Emp emp) { return empDao.addEmp(emp); } @Override public int updateEmp(Emp emp) { return empDao.updateEmp(emp); } @Override public int deleteEmp(int empno) { return empDao.deleteEmp(empno); } }
4.准备实体类
package com.augus.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; import java.util.Date; @AllArgsConstructor @NoArgsConstructor @Data public class Emp implements Serializable { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Double sal; private Integer comm; private Integer deptno; }
5.准备dao层接口和实现类
package com.augus.dao; import com.augus.pojo.Emp; import java.util.List; public interface EmpDao { //统计员工人数 int findEmpCount(); //根据编号查询员工信息 Emp findByEmpno(int empno); //根据部门编号查询属于该部门的员工 List<Emp> findByDeptno(int deptno); //新增员工信息 int addEmp(Emp emp); //修改员工信息 int updateEmp(Emp emp); //删除员工信息 int deleteEmp(int empno); }
package com.augus.dao.impl; import com.augus.dao.EmpDao; import com.augus.pojo.Emp; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.util.List; @Repository public class EmpDaoImpl implements EmpDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public int findEmpCount() { //统计员工个数 /* * queryForObject有两个参数: * 1.传入SQL * 2.返回值类型 * */ Integer empCount = jdbcTemplate.queryForObject("select count(empno) from emp", Integer.class); return empCount; } public Emp findByEmpno(int empno) { //根据编号查询员工信息 /* * queryForObject有两个参数: * 1.传入SQL * 2.返回值类型,这里要是emp实现类的对象, BeanPropertyRowMapper 将数据库查询结果转换为java对象 * 3.参数 * * BeanPropertyRowMapper将数据库查询结果转换为Java类对象。 常应用于使用Spring的JdbcTemplate查询数据库,获取List结果列表,数据库表字段和实体类自动对应。 * BeanPropertyRowMapper在query过程中使用,可以按照属性名与字段名进行自动的数据类型转换 * */ BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<Emp>(Emp.class); Emp emp = jdbcTemplate.queryForObject("select * from emp where empno = ?", rowMapper, empno); return emp; } @Override public List<Emp> findByDeptno(int deptno) { /* * 根据部门编号查询员工信息 *query:用于查询返回的多个对象,有三个参数 * 1.SQL * 2.返回的对象类型 * 3.传入的参数 * */ BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<Emp>(Emp.class); List<Emp> emps = jdbcTemplate.query("select * from emp where deptno = ?", rowMapper, deptno); return emps; } @Override public int addEmp(Emp emp) { //新增员工 String addEmpSql = "insert into emp values(DEFAULT ,?,?,?,?,?,?,?)"; //创建数组 保存数据,数据类型写成Object 从传入的emp对象中获取属性的值 Object[] args = {emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()}; int i = jdbcTemplate.update(addEmpSql, args); return i; } @Override public int updateEmp(Emp emp) { //根据员工编号修改员工信息 String sql ="update emp set ename=?, job=?, mgr=? , hiredate=?, sal=?, comm=?, deptno=? where empno=?"; //从emp获取属性值保存到数组中 Object[] args = {emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()}; int i = jdbcTemplate.update(sql, args); return i; } @Override public int deleteEmp(int empno) { //根据编号,删除员工信息 int i = jdbcTemplate.update("delete from emp where empno =?", empno); return i; } }
6.准备jdbc.properties内容如下:
jdbc_username=test jdbc_password=123456 jdbc_driver=com.mysql.cj.jdbc.Driver jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
7.准备配置文件,内容如下
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!--开启包扫描--> <context:component-scan base-package="com.augus"></context:component-scan> <!--读取配置文件--> <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder> <!--配置德鲁伊连接池--> <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="username" value="${jdbc_username}"></property> <property name="password" value="${jdbc_password}"></property> <property name="url" value="${jdbc_url}"></property> <property name="driverClassName" value="${jdbc_driver}"></property> </bean> <!--配置JDBCTemplate,并向里面注入druidDataSource--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--通过set方法注入连接池--> <property name="dataSource" ref="druidDataSource"></property> </bean> </beans>
8.测试代码
import com.augus.pojo.Emp; import com.augus.service.EmpService; import lombok.Builder; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import javax.sound.midi.Soundbank; import java.sql.SQLOutput; import java.util.Date; import java.util.List; public class Test1 { @Test public void testEmpService(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); EmpService empService = context.getBean(EmpService.class); //查询员工人数 /*int empCount = empService.findEmpCount(); System.out.println(empCount);*/ //根据员工编号查询员工信息 /*Emp emp = empService.findByEmpno(7902); System.out.println(emp);*/ //根据部门编号查询属于该部门的员工 /* List<Emp> emps = empService.findByDeptno(20); System.out.println(emps);*/ //新增员工信息 /*Emp emp = new Emp(null, "俞莲舟", "测试开大", 7782, new Date(), 4000.0, 500, 10); int i = empService.addEmp(emp); System.out.println(i);*/ //修改员工信息 /*Emp emp = new Emp(7946, "宋远桥", "测试主管", 7782, new Date(), 8000.0, 200, 20); int i = empService.updateEmp(emp); System.out.println(i);*/ //根据编号删除员工 int i = empService.deleteEmp(7946); System.out.println(i); } }
四、JdbcTemplate 批操作
spring JdbcTemplate 进行批量插入或更新操作时实现一次连接,操作表格里的多条数据,就是批量操作
下面案例演示项目结构和上述案例项目结构一致,在上面项目结构上完成即可
1.准备实体类
package com.augus.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; @AllArgsConstructor @NoArgsConstructor @Data public class Dept implements Serializable { private Integer deptno; private String dname; private String loc; }
2.准备service层接口和实现类
package com.augus.service; import com.augus.pojo.Dept; import java.util.List; public interface DeptService { //批量添加 int[] deptBatchAdd(List<Dept> depts); //批量修改 int[] deptBatchUpdate(List<Dept> depts); //批量删除 int[] deptBatchDelete(List<Integer> deptnos); }
package com.augus.service.impl; import com.augus.dao.DeptDao; import com.augus.pojo.Dept; import com.augus.service.DeptService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class DeptServiceImpl implements DeptService { @Autowired private DeptDao deptDao; @Override public int[] deptBatchAdd(List<Dept> depts) { return deptDao.deptBatchAdd(depts); } @Override public int[] deptBatchUpdate(List<Dept> depts) { return deptDao.deptBatchUpdate(depts); } @Override public int[] deptBatchDelete(List<Integer> deptnos) { return deptDao.deptBatchDelete(deptnos); } }
3.准备dao层接口和实现类
package com.augus.dao; import com.augus.pojo.Dept; import java.util.List; public interface DeptDao { //批量添加 int[] deptBatchAdd(List<Dept> depts); //批量修改 int[] deptBatchUpdate(List<Dept> depts); //批量删除 int[] deptBatchDelete(List<Integer> deptnos); }
package com.augus.dao.impl; import com.augus.dao.DeptDao; import com.augus.pojo.Dept; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.util.LinkedList; import java.util.List; @Repository public class DeptDaoImpl implements DeptDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public int[] deptBatchAdd(List<Dept> depts) { //批量新增 String sql ="insert into dept values(DEFAULT,?,?)"; //创建集合,用来保存数组 LinkedList args = new LinkedList(); for (Dept dept : depts) { //创建数组保存取出的值,从每次取出dept对象获取属性的值 Object[] arg = {dept.getDname(),dept.getLoc()}; //将数组添加到集合中 args.add(arg); } //batchUpdate 执行批操作 int[] ints = jdbcTemplate.batchUpdate(sql, args); return ints; } @Override public int[] deptBatchUpdate(List<Dept> depts) { //批量修改 String sql ="update dept set dname =? ,loc =? where deptno=?"; //创建集合,用来保存数组 LinkedList args = new LinkedList(); for (Dept dept : depts) { //创建数组保存取出的值,从每次取出dept对象获取属性的值 Object[] arg = {dept.getDname(),dept.getLoc(),dept.getDeptno()}; //将数组添加到集合中 args.add(arg); } //batchUpdate 执行批操作 int[] ints = jdbcTemplate.batchUpdate(sql, args); return ints; } @Override public int[] deptBatchDelete(List<Integer> deptnos) { //批量删除 String sql ="delete from dept where deptno=?"; //创建集合,用来保存数组 LinkedList args = new LinkedList(); for (Integer deptno : deptnos) { //创建数组保存取出的值,从每次取出dept对象获取属性的值 Object[] arg = {deptno}; //将数组添加到集合中 args.add(arg); } //batchUpdate 执行批操作 int[] ints = jdbcTemplate.batchUpdate(sql, args); return ints; } }
4.准备jdbc.properties(同上一个案例相同)
jdbc_username=test jdbc_password=123456 jdbc_driver=com.mysql.cj.jdbc.Driver jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
5.准备配置文件applicationContext.xml(同上一个案例相同)
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!--开启包扫描--> <context:component-scan base-package="com.augus"></context:component-scan> <!--读取配置文件--> <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder> <!--配置德鲁伊连接池--> <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="username" value="${jdbc_username}"></property> <property name="password" value="${jdbc_password}"></property> <property name="url" value="${jdbc_url}"></property> <property name="driverClassName" value="${jdbc_driver}"></property> </bean> <!--配置JDBCTemplate,并向里面注入druidDataSource--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--通过set方法注入连接池--> <property name="dataSource" ref="druidDataSource"></property> </bean> </beans>
6.测试代码
import com.augus.pojo.Dept; import com.augus.service.DeptService; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.Arrays; import java.util.LinkedList; public class Test2 { @Test public void testDeptBatchAdd() { //测试批量新增 ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); DeptService deptService = context.getBean(DeptService.class); //创建数组,保存产生的dept对象 LinkedList<Dept> deps = new LinkedList<Dept>(); for (int i = 0; i < 10; i++) { //循环产生dept对象存放到数组中去 deps.add(new Dept(null, "销售部" + i, "北京" + i)); } //执行方法 int[] ints = deptService.deptBatchAdd(deps); //获取回来的是int类型的数组,所以使用Arrays.toString输出 System.out.println(Arrays.toString(ints)); } @Test public void testDeptBatchUpdate(){ //测试批量修改 ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); DeptService deptService = context.getBean(DeptService.class); //创建数组,保存产生的dept对象 LinkedList<Dept> deps = new LinkedList<Dept>(); for (int i=101; i<110; i++){ //循环产生dept对象存放到数组中去 deps.add(new Dept(i, "售后部"+i, "上海"+i)); } //执行方法 int[] ints = deptService.deptBatchUpdate(deps); //获取回来的是int类型的数组,所以使用Arrays.toString输出 System.out.println(Arrays.toString(ints)); } @Test public void testDdeptBatchDelete(){ //测试批量删除 ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); DeptService deptService = context.getBean(DeptService.class); //创建数组,保存产生的dept对象 LinkedList<Integer> deptnos = new LinkedList<Integer>(); for (int i=101; i<110; i++){ //循环产生dept对象存放到数组中去 deptnos.add(i); } //执行方法 int[] ints = deptService.deptBatchDelete(deptnos); //获取回来的是int类型的数组,所以使用Arrays.toString输出 System.out.println(Arrays.toString(ints)); } }