SpringMVC —— 第十三章 Spring JDBCTemplate 基本使用

---

1. JDBCTemplate 概述

它是 Spring 框架中提供的一个对象，是原始繁琐的 JDBC API 对象的简单封装。Spring 框架为我们提供了很多的操作模版类。例如：操作关系型数据库的 JDBCTemplate 和 HibernateTemplate，操 NoSQL 数据库的 RedisTemplate 操作消息队列的JMSTemplate等等。

2. JDBCTemplate 开发步骤

1. 导入 spring-jdbc 和 spring-tx 坐标
2. 创建数据库表和实体
3. 创建 JDBCTemplate 对象
4. 执行数据库操作

示例代码：

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.gregperlinli</groupId>
    <artifactId>demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>demo</name>
    <packaging>war</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
        <junit.version>5.7.1</junit.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>$&#123;junit.version&#125;</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>$&#123;junit.version&#125;</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.8</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.3.1</version>
            </plugin>
        </plugins>
    </build>
</project>

​ com.yourname.domain.Account

/**
 * @author gregPerlinLi
 * @since 2021-09-09
 */
public class Account {
    private String name;
    private double money;

    public Account() {
    }
    public Account(String name, double money) {
        this.name = name;
        this.money = money;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getMoney() {
        return money;
    }
    public void setMoney(double money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Account{" +
                "name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

com.yourname.test.JdbcTemplateTest

/**
 * @author gregPerlinLi
 * @since 2021-09-09
 */
public class JdbcTemplateTest {
    /**
     * Development steps for testing JDBC Template
     */
    @Test
    public void test1() throws PropertyVetoException {
        // Create data source object
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        dataSource.setUser("root");
        dataSource.setPassword("root");

        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        // Set data source object
        jdbcTemplate.setDataSource(dataSource);
        // Perform operation
        int row = jdbcTemplate.update("insert into account values(?, ?)", "gregPerlinLi", 50000);
        System.out.println(row);
    }
}

数据库：

create table account
(
    name  varchar(50) not null
        primary key,
    money double      null
);

3. Spring 产生 JDBCTemplate 对象

我们可以将 JDBCTemplate 的创建权限交给 Spring，将数据源 DataSource 的创建权也交给 Spring，在 Spring 容器内部将数据源 DataSource 注入到 JDBCTemplate 模版对象中， aplicationContext.xml 配置如下：

<!-- Load external properties file -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="$&#123;jdbc.driver&#125;"/>
    <property name="jdbcUrl" value="$&#123;jdbc.url&#125;"/>
    <property name="user" value="$&#123;jdbc.username&#125;"/>
    <property name="password" value="$&#123;jdbc.password&#125;"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource">
      </property>
</bean>

使用方法如下：

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

com.yourname.test.JdbcTemplateTest

@Test
public void test() {
    ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
    JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
    int row = jdbcTemplate.update("insert into account values(?, ?)", "gregPerlinLi", 50000);
    System.out.println(row);
}

4. JDBCTemplate 的常用操作

修改操作

/**
 * @author gregPerlinLi
 * @since 2021-09-09
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCrudTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testInsert() {
        int row = jdbcTemplate.update("insert into account values(?, ?)", "gregPerlinLi", 50000);
        System.out.println(row);
    }
    @Test
    public void testUpdate() {
        int row = jdbcTemplate.update("update account set money = ? where name = ?", 100000, "gregPerlinLi");
        System.out.println(row);
    }
    @Test
    public void testDelete() {
        int row = jdbcTemplate.update("delete from account where name = ?", "gregPerlinLi");
        System.out.println(row);
    }
}

查询操作

/**
 * @author gregPerlinLi
 * @since 2021-09-09
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCrudTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testQueryOne() {
        Account account = jdbcTemplate.queryForObject("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), "gregPerlinLi");
        System.out.println(account);
    &#125;
    @Test
    public void testQueryAll() &#123;
        List<Account> accountList = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
        System.out.println(accountList);
    &#125;
    @Test
    public void testQueryCount() &#123;
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println(count);
    &#125;
&#125;

5. 知识要点

1. 导入 spring-jdbc 和 spring-tx 坐标

2. 创建数据库和实体

3. 创建 JDBCTemplate 对象

   JdbcTemplate jdbcTemplate = new JdbcTemplate();
   jdbcTemplate.setDataSource(dataSource);
   

4. 执行数据库操作

   更新操作：

   jdbcTemplate.query(sql, params);
   

   查询操作：

   jdbcTemplate.query(sql, Mapper, params);
   jdbcTemplate.queryForObject(sql, Mapper, params);
   

---