第一章：概念

• 为了在特定领域帮助我们简化代码，Spring 封装了很多 『Template』形式的模板类。例如：JDBCTemplate 、RedisTemplate 、RestTemplate 等等。

第二章：准备工作

2.1 环境搭建

• IDEA 2021+。
• JDK 11+。
• MySQL 5.7。

2.2 导入依赖

• pom.xml

<!-- Spring -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.3.12</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aspects</artifactId>
    <version>5.3.12</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.3.12</version>
</dependency>
<!-- Spring 持久化层支持jar包 -->
<!-- Spring 在执行持久化层操作、与持久化层技术进行整合过程中，需要使用orm、jdbc、tx三个jar包 -->
<!-- 导入 orm 包就可以通过 Maven 的依赖传递性把其他两个也导入 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.3.12</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>
<!-- junit单元测试 -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>
<!-- 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

2.3 sql 语句

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` double NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.4 数据库配置信息

• db.properties

jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=123456

2.5 Spring 的配置文件

• applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns="http://www.springframework.org/schema/beans"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
    <!-- 配置自动扫描的包 -->
    <context:component-scan base-package="com.github.fairy.era"></context:component-scan>
    <!-- 导入数据库连接信息 -->
    <context:property-placeholder location="db.properties"></context:property-placeholder>
    <!-- 配置数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driverClass}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    <!-- 配置jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 开启AOP的注解支持 -->
    <aop:aspectj-autoproxy/>
</beans>

2.6 测试装配的 JdbcTemplate

• SpringTest.java

package com.github.fairy.era.bean;
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.SpringRunner;
import javax.sql.DataSource;
/**
 * @author 许大仙
 * @version 1.0
 * @since 2021-11-05 11:02
 */
@RunWith(SpringRunner.class)
@ContextConfiguration(value = {"classpath:applicationContext.xml"})
public class SpringTest {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void testDataSource() {
        System.out.println("dataSource = " + dataSource);
    }
    @Test
    public void testJdbcTemplate() {
        System.out.println("jdbcTemplate = " + jdbcTemplate);
    }
}

第三章：JdbcTemplate 的基本用法

3.1 Dao 层接口

• BookDao.java

package com.github.fairy.era.dao;
import com.github.fairy.era.bean.Book;
/**
 * @author 许大仙
 * @version 1.0
 * @since 2021-11-09 10:42
 */
public interface BookDao {
    /**
     * 新增
     *
     * @param book
     */
    void addBook(Book book);
    /**
     * 修改
     *
     * @param book
     */
    void updateBook(Book book);
    /**
     * 删除
     *
     * @param id
     */
    void deleteBook(Long id);
    /**
     * 查询个数
     */
    Long count();
    /**
     * 查看单个简单类型
     *
     * @param id 主键
     * @return 名称
     */
    String findNameById(Long id);
    /**
     * 查询实体类型
     *
     * @param id
     * @return
     */
    Book findById(Long id);
}

3.2 Dao 层实现类

• BookDaoImpl.java

package com.github.fairy.era.dao.impl;
import com.github.fairy.era.bean.Book;
import com.github.fairy.era.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
 * @author 许大仙
 * @version 1.0
 * @since 2021-11-09 10:43
 */
@Repository
public class BookDaoImpl implements BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addBook(Book book) {
        jdbcTemplate.update(" INSERT INTO book (name,price) VALUES (?,?) ", book.getName(), book.getPrice());
    }
    @Override
    public void updateBook(Book book) {
        jdbcTemplate.update(" UPDATE book SET name =?,price =? WHERE id = ? ", book.getName(), book.getPrice(), book.getId());
    }
    @Override
    public void deleteBook(Long id) {
        jdbcTemplate.update(" DELETE FROM book WHERE id = ? ", id);
    }
    @Override
    public Long count() {
        return jdbcTemplate.queryForObject(" SELECT count(*) FROM book ", Long.class);
    }
    @Override
    public String findNameById(Long id) {
        return jdbcTemplate.queryForObject(" SELECT name FROM book WHERE id = ? ", String.class, id);
    }
    @Override
    public Book findById(Long id) {
        return jdbcTemplate.queryForObject(" SELECT * FROM book where id = ? ", new BeanPropertyRowMapper<>(Book.class), id);
    }
}

3.3 测试

package com.github.fairy.era.bean;
import com.github.fairy.era.dao.BookDao;
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.SpringRunner;
/**
 * @author 许大仙
 * @version 1.0
 * @since 2021-11-05 11:02
 */
@RunWith(SpringRunner.class)
@ContextConfiguration(value = {"classpath:applicationContext.xml"})
public class SpringTest {
    @Autowired
    private BookDao bookDao;
    @Test
    public void testAddBook() {
        Book book = new Book(null, "《小人鱼》", 1.12);
        Book book2 = new Book(null, "《丑小鸭》", 2.12);
        Book book3 = new Book(null, "《卖火柴的小女孩》", 3.12);
        Book book4 = new Book(null, "《拇指姑娘》", 4.12);
        bookDao.addBook(book);
        bookDao.addBook(book2);
        bookDao.addBook(book3);
        bookDao.addBook(book4);
    }
    @Test
    public void testUpdateBook() {
        Book book = new Book(1L, "《小人鱼》", 1.13);
        bookDao.updateBook(book);
    }
    @Test
    public void testDeleteBook() {
        bookDao.deleteBook(1L);
    }
    @Test
    public void testCount() {
        Long count = bookDao.count();
        System.out.println("count = " + count);
    }
    @Test
    public void testFindNameById() {
        String name = bookDao.findNameById(1L);
        System.out.println("name = " + name);
    }
    @Test
    public void testFindById() {
        Book book = bookDao.findById(1L);
        System.out.println("book = " + book);
    }
}