本教程源码请访问：tutorial_demo

一、JdbcTemplate概述

JdbcTemplate是Spring框架中提供的一个对象，是对原始Jdbc API对象的简单封装。类似于我们前面学习的Apache Commons DbUtils。Spring框架为我们提供了很多的操作模板类。

• 操作关系型数据库的：JdbcTemplate、HibernateTemplate。
• 操作NoSQL数据库的：RedisTemplate。
• 操作消息队列的：JmsTemplate。

二、使用JdbcTemplate

2.1、JdbcTemplate入门

2.1.1、建库建表

DROP DATABASE IF EXISTS springlearn;
CREATE DATABASE springlearn;
USE springlearn;


DROP TABLE IF EXISTS account;
CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(40) DEFAULT NULL,
  money float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

INSERT INTO account VALUES (‘1‘, ‘aaa‘, ‘1000‘);
INSERT INTO account VALUES (‘2‘, ‘bbb‘, ‘1000‘);
INSERT INTO account VALUES (‘3‘, ‘ccc‘, ‘1000‘);
INSERT INTO account VALUES (‘5‘, ‘cc‘, ‘10000‘);
INSERT INTO account VALUES (‘6‘, ‘abc‘, ‘10000‘);
INSERT INTO account VALUES (‘7‘, ‘abc‘, ‘10000‘);

2.1.2、创建Maven工程

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>

    <groupId>org.codeaction</groupId>
    <artifactId>JdbcTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

这里的连接池我们使用c3p0。

2.1.3、使用JdbcTemplate

package org.codeaction.jdbctemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import java.beans.PropertyVetoException;

public class MyTest {
    public static void main(String[] args) throws PropertyVetoException {
        //创建c3p0数据源连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //设置四大参数
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/springlearn");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        //创建JdbcTemplate
        //JdbcTemplate template = new JdbcTemplate();
        //template.setDataSource(dataSource);
        JdbcTemplate template = new JdbcTemplate(dataSource);
        //执行操作
        template.execute("insert into account(name, money) values(‘Tom111‘, 100)");
    }
}

运行main方法进行测试，数据库表中添加了相应的记录。

上面的代码中我们使用了new的方式创建了数据库连接池和JdbcTemplate，这样代码又出现了耦合，我们在之前的教程中学习了SpringIOC，下面我们看一下如何在Spring中使用JdbcTemplate。

2.2、JdbcTemplate在SpringIOC中使用

2.2.1、新建Spring配置文件beans.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">
    <!-- 配置JdbcTemplate -->
    <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 注入数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- 配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 注入数据源属性 -->
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springlearn"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
</beans>

通过上面的配置，JdbcTemplate和数据源就由Spring容器管理了。

2.2.2、创建测试类

package org.codeaction.jdbctemplate;

import org.codeaction.bean.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:beans.xml")
public class MyTest {

    @Autowired
    private JdbcTemplate template;

    @Test
    public void testJdbcTemplate() {
        template.execute("insert into account(name, money) values(‘Bob‘, 1000)");
    }
}

运行测试方法testJdbcTemplate，数据库表中添加了相应的记录。

2.3、Dao中使用JdbcTemplate

2.3.1、添加Dao接口

package org.codeaction.dao;

import org.codeaction.bean.Account;

import java.util.List;

public interface IAccountDao {
    Integer add(Account account);
    Integer delete(Integer id);
    Integer update(Account account);
    List<Account> findAll();
    Account findById(Integer id);
    List<Account> findByName(String name);
    Long count();
}

2.3.2、添加Dao实现类

package org.codeaction.dao.impl;

import org.codeaction.bean.Account;
import org.codeaction.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class AccountDaoImpl implements IAccountDao {
    private JdbcTemplate template;

    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    @Override
    public Integer add(Account account) {
        Object[] args = {account.getName(), account.getMoney()};
        return template.update("insert into account(name, money) values(?, ?)", args);
    }

    @Override
    public Integer delete(Integer id) {
        return template.update("delete from account where id=?", id);
    }

    @Override
    public Integer update(Account account) {
        Object[] args = {account.getName(), account.getMoney(), account.getId()};
        return template.update("update account set name=?, money=? where id=?", args);
    }

    @Override
    public List<Account> findAll() {
        return template.query("select * from account",
                new BeanPropertyRowMapper<Account>(Account.class));
    }

    @Override
    public Account findById(Integer id) {
        List<Account> list = template.query("select * from account where id=?",
                new BeanPropertyRowMapper<Account>(Account.class), id);
        return list.size() != 0 ? list.get(0) : null;
    }

    @Override
    public List<Account> findByName(String name) {
        return template.query("select * from account where name like ?",
                new BeanPropertyRowMapper<Account>(Account.class), name);
    }

    @Override
    public Long count() {
        return template.queryForObject("select count(*) from account", Long.class);
    }
}

上面的代码中有如下内容：

private JdbcTemplate template;

public void setTemplate(JdbcTemplate template) {
	this.template = template;
}

如果程序中有多个Dao，那么这段代码就会在多个Dao中出现，造成代码的冗余，为了解决代码冗余的问题，我们使用JdbcTemplate可以让Dao的实现类继承JdbcDaoSupport类，代码如下：

package org.codeaction.dao.impl;

import org.codeaction.bean.Account;
import org.codeaction.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

//注意这里的继承
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
    @Override
    public Integer add(Account account) {
        Object[] args = {account.getName(), account.getMoney()};
        return getJdbcTemplate().update("insert into account(name, money) values(?, ?)", args);
    }

    @Override
    public Integer delete(Integer id) {
        return getJdbcTemplate().update("delete from account where id=?", id);
    }

    @Override
    public Integer update(Account account) {
        Object[] args = {account.getName(), account.getMoney(), account.getId()};
        return getJdbcTemplate().update("update account set name=?, money=? where id=?", args);
    }

    @Override
    public List<Account> findAll() {
        return getJdbcTemplate().query("select * from account",
                new BeanPropertyRowMapper<Account>(Account.class));
    }

    @Override
    public Account findById(Integer id) {
        List<Account> list = getJdbcTemplate().query("select * from account where id=?",
                new BeanPropertyRowMapper<Account>(Account.class), id);
        return list.size() != 0 ? list.get(0) : null;
    }

    @Override
    public List<Account> findByName(String name) {
        return getJdbcTemplate().query("select * from account where name like ?",
                new BeanPropertyRowMapper<Account>(Account.class), name);
    }

    @Override
    public Long count() {
        return getJdbcTemplate().queryForObject("select count(*) from account", Long.class);
    }
}

不使用继承JdbcDaoSupport的代码，可以使用XML或注解两种方式配置bean，继承了JdbcDaoSupport的代码，只能使用XML方式配置bean。

2.3.3、修改Spring配置文件beans.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">
    <!-- 配置AccountDaoImpl -->
    <bean id="accountDao" class="org.codeaction.dao.impl.AccountDaoImpl">
        <!-- 注入jdbcTemplate -->
        <property name="jdbcTemplate" ref="template"></property>
    </bean>

    <!-- 配置JdbcTemplate -->
    <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 注入数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- 配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 注入数据源属性 -->
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springlearn"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
</beans>

2.3.4、创建测试类

package org.codeaction.jdbctemplate;


import org.codeaction.bean.Account;
import org.codeaction.dao.IAccountDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:beans.xml")
public class TestDao {
    @Autowired
    private IAccountDao accountDao;

    @Test
    public void testAdd() {
        Account account = new Account();
        account.setName("John");
        account.setMoney(100F);
        Integer result = accountDao.add(account);
        System.out.println(result);
    }

    @Test
    public void testDel() {
        Integer result = accountDao.delete(1);
        System.out.println(result);
    }

    @Test
    public void testUpdate() {
        Account account = new Account();
        account.setId(13);
        account.setName("Tom");
        account.setMoney(1000F);
        Integer result = accountDao.update(account);
        System.out.println(result);
    }

    @Test
    public void testFindAll() {
        List<Account> list = accountDao.findAll();
        list.forEach(System.out::println);
    }

    @Test
    public void testFindById() {
        Account account = accountDao.findById(14);
        System.out.println(account);
    }

    @Test
    public void testFindByName() {
        List<Account> list = accountDao.findByName("%Tom%");
        list.forEach(System.out::println);
    }

    @Test
    public void testCount() {
        Long count = accountDao.count();
        System.out.println(count);
    }
}

运行测试方法进行测试。

Spring11_JdbcTemplate