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));
    }
}