[Java知识库]JdbcTemplate

1 什么是JdbcTemplate？

原始的JDBC在操作数据库方面是比较麻烦的，所以Spring为了提高开发的效率，把JDBC进行封装、改造了一番，而JdbcTemplate就是Spring对原始JDBC封装之后提供的一个操作数据库的工具类。

2 JdbcTemplate的CRUD方法

JdbcTemplate提供以下三种类型的方法来完成所有数据库的增删改查等操作。

• executeXXX()：执行任何SQL语句，对数据库、表进行新建、修改、删除操作。
• update()：执行新增、修改、删除等操作。
• queryXXX()：执行查询相关的操作。

3 JdbcTemplate的简单使用

基于JdbcTemplate的User表的增删改查操作。

3.1 依赖管理

<?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>HelloJdbcTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <maven.compiler.source>9</maven.compiler.source>
        <maven.compiler.target>9</maven.compiler.target>
    </properties>

    <dependencies>
        <!--Spring核心依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>

        <!--Spring JdbcTemplate依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>

        <!--MySQL数据库的依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>

        <!--JUnit单元测试依赖-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

3.2 测试

package com.hc;

import com.hc.domain.User;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.Date;
import java.util.List;

public class TestJdbcTemplate {
    JdbcTemplate jdbcTemplate = null;
    DriverManagerDataSource dataSource = null;

    @Before
    public void init() {
        // 创建数据源
        dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/SSM?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");

        // 创建JdbcTemplate对象，并设置数据源（Spring自带数据源）
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Test
    public void testSave() {
        jdbcTemplate.update("insert into user(username,birthday,sex,address) values (?,?,?,?)", "HCC", new Date(), "男", "湖北宜昌");
    }

    @Test
    public void testDelete() {
        jdbcTemplate.update("delete from user where id = ?", 51);
    }

    @Test
    public void testUpdate() {
        jdbcTemplate.update("update user set username = ? where id = ?", "HCCC", 52);
    }

    @Test
    public void testFindAll() {
        List<User> users = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<User>(User.class));
        for (User u : users) {
            System.out.println(u);
        }
    }

    @Test
    public void testFindOneBuId() {
        List<User> users = jdbcTemplate.query("select * from user where id = ?", new BeanPropertyRowMapper<User>(User.class), 50);
        System.out.println(users.size() == 0 ? "该用户不存在" : users.get(0));
    }
}

4 Spring和Spring JdbcTemplate

很明显，不管是数据源还是JdbcTemplate都可以进一步提交给Spring容器进行管理。

4.1 实体类和三层架构

• User

package com.hc.domain;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

• UserDaoImpl

package com.hc.dao.impl;

import com.hc.dao.UserDao;
import com.hc.domain.User;
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.Date;
import java.util.List;

@Repository(value = "userDao")
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(User user) {
        jdbcTemplate.update("insert into user(username, birthday, sex, address) values (?, ?, ?, ?)", user.getUsername(), user.getBirthday(), user.getSex(), user.getAddress());
    }

    @Override
    public void delete(Integer id) {
        jdbcTemplate.update("delete from user where id = ?", id);
    }

    @Override
    public void update(User user) {
        jdbcTemplate.update("update user set username=?,birthday=?,sex=?,address=? where id = ?", user.getUsername(), user.getBirthday(), user.getSex(), user.getAddress(), user.getId());
    }

    @Override
    public User findOneById(Integer id) {
        List<User> users = jdbcTemplate.query("select * from user where id = ?", new BeanPropertyRowMapper<User>(User.class), id);
        return users.size() == 0 ? null : users.get(0);
    }

    @Override
    public List<User> findAll() {
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<User>(User.class));
    }
}

• UserServiceImpl

package com.hc.service.impl;

import com.hc.dao.UserDao;
import com.hc.domain.User;
import com.hc.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service(value = "userService")
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public void save(User user) {
        userDao.save(user);
    }

    @Override
    public void delete(Integer id) {
        userDao.delete(id);
    }

    @Override
    public void update(User user) {
        userDao.update(user);
    }

    @Override
    public User findOneById(Integer id) {
        return userDao.findOneById(id);
    }

    @Override
    public List<User> findAll() {
        return userDao.findAll();
    }
}

4.2 bean.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.hc"></context:component-scan>

    <!--引入外部properties文件，文件中写着数据库连接信息-->
    <context:property-placeholder location="jdbcConfig.properties"></context:property-placeholder>

    <!--创建jdbcTemplate提交给Spring管理，并注入数据源-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--创建数据源，采取Spring-jdbc自身提供的-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

</beans>

4.3 测试（Spring整合JUnit）

package com.hc;

import com.hc.domain.User;
import com.hc.service.UserService;
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.Date;
import java.util.List;


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

    @Autowired
    private UserService userService;

    @Test
    public void testSave() {
        User user = new User();
        user.setUsername("HFH");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("广东佛山");

        userService.save(user);
    }

    @Test
    public void testDelete() {
        userService.delete(52);
    }

    @Test
    public void testUpdate() {
        User user = new User();
        user.setId(53);
        user.setUsername("HFH");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("HK");

        userService.update(user);
    }

    @Test
    public void testFindOneById() {
        User user = userService.findOneById(53);
        System.out.println(user);
    }

    @Test
    public void testFindAll() {
        List<User> users = userService.findAll();
        for (User u : users) {
            System.out.println(u);
        }
    }
}

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖