Spring JDBC的使用

概述

​ Spring的JDBC模块负责数据库资源管理和错误处理，大大简化了开发人员对数据库的操作，使得开发人员可以从烦琐的数据库操作中解脱出来，从而将更多的精力投入编写业务逻辑中。

JdbcTemplate类是Spring JDBC的核心类，它继承自抽象类JdbcAccessor，同时实现了JdbcOperations接口。

• JdbcOperations接口定义了在JdbcTemplate类中可以使用的操作集合，包括添加、修改、查询和删除等操作。

• JdbcTemplate类的直接父类是JdbcAccessor，该类为子类提供了一些访问数据库时使用的公共属性，具体如下：

  • DataSource：其主要功能是获取数据库连接，具体实现时还可以引入对数据库连接的缓冲池和分布事务的支持，它可以作为访问数据库资源的标准接口。
  • SQLExceptionTranslator

1. 表结构

create table tb_account
(
    account_id   int primary key auto_increment comment '账户id, 主键, 自增长'
    account_name varchar(20)                           not null comment '账户名称',
    account_type varchar(20)                           not null comment '账户类型（建设银行，招商银行）',
    money        double(11, 0)                         not null comment '账户金额',
    remark       varchar(50)                           null comment '备注',
    create_time  timestamp default (CURRENT_TIMESTAMP) not null,
    upate_time   timestamp default (CURRENT_TIMESTAMP) null,
    user_id      int                                   not null comment '外键'
);

2. 依赖

<!-- spring -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context</artifactId>
	<version>5.3.19</version>
</dependency>
<!-- spring-jdbc -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>5.3.19</version>
</dependency>
<!-- spring-aop -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-aspects</artifactId>
	<version>5.3.14</version>
</dependency>
<!-- mysql 8.0 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.26</version>
</dependency>
<!-- c3p0 连接池-->
<dependency>
	<groupId>com.mchange</groupId>
	<artifactId>c3p0</artifactId>
	<version>0.9.5.2</version>
</dependency>
<!-- spring test -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-test</artifactId>
	<version>5.2.12.RELEASE</version>
</dependency>
<!-- junit -->
<dependency>
	<groupId>junit</groupId>
	<artifactId>junit</artifactId>
	<version>4.12</version>
	<scope>test</scope>
</dependency>

3. jdbc.properties

位于src/main/resources 目录下

jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mytest?useSSL=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=123456
# 指定连接池的初始化连接数。取值应在minPoolSize与maxPoolSize之间，默认3
jdbc.initialPoolSize=20
# 指定连接池中保留的最大连接数 默认15
jdbc.maxPoolSize=100
# 指定连接池最小连接数
jdbc.minPoolSize=10
# 最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认0
jdbc.maxIdleTime=600
# 当连接池中的连接耗尽时c3p0一次同时获取的连接数。默认3
jdbc.acquireIncrement=5
# JDBC的标准，用以控制数据源内加载的PreparedStatements的数量
jdbc.maxStatements=5
# 每60s检查所有连接池中的空闲连接 默认0
jdbc.idleConnectionTestPeriod=60

4. spring.xml

位于src/main/resources 目录下

<?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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://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.xxx"/>

    <!-- 加载properties配置文件，用来读取jdbc.properties中的数据 -->
    <context:property-placeholder location="jdbc.properties"/>

    <!-- 配置c3p0数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <!-- 指定连接池的初始化连接数。取值应在minPoolSize与maxPoolSize之间，默认3 -->
        <property name="initialPoolSize" value="${jdbc.initialPoolSize}"/>
        <!-- 指定连接池中保留的最大连接数 默认15 -->
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"/>
        <!-- 指定连接池最小连接数 -->
        <property name="minPoolSize" value="${jdbc.minPoolSize}"/>
        <!-- 最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认0 -->
        <property name="maxIdleTime" value="${jdbc.maxIdleTime}"/>
        <!-- 当连接池中的连接耗尽时c3p0一次同时获取的连接数。默认3 -->
        <property name="acquireIncrement" value="${jdbc.acquireIncrement}"/>
        <!-- JDBC的标准，用以控制数据源内加载的PreparedStatements的数量 -->
        <property name="maxStatements" value="${jdbc.maxStatements}"/>
        <!-- 每60s检查所有连接池中的空闲连接 默认0 -->
        <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}"/>
    </bean>

    <!-- 配置jdbcTemplate实例，并注入一个dataSource数据源 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

5. 实体类

package com.xxx.entity;

public class Account {
    private Integer accountId;
    private String accountName;
    private String accountType; // 账户类型（邮政，建设..）
    private Double money;
    private String remark;  // 备注
    private Integer userId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;

    public Account() {
    }

    public Account(String accountName, String accountType, Double money, String remark, Integer userId) {
        this.accountName = accountName;
        this.accountType = accountType;
        this.money = money;
        this.remark = remark;
        this.userId = userId;
    }
    
    // setter,getter,toString()方法...
}

6. Dao层

package com.xxx.dao;

public interface IAccountDao {
    // 添加账户记录，返回受影响的行数
    int addAccount(Account account);
    // 添加账户记录，返回主键
    int addAccountHasKey(Account account);
    // 批量添加账户记录，返回受影响的行数
    int[] addAccountBatch(List<Account> accounts);
    // 查询指定用户的账户个数，并返回
    int queryAccountCountByUserId(int userId);
    // 查询指定账户的账户详情，返回账户对象
    Account queryAccountById(int accountId);
    /**
     * 多条件查询指定用户的账户列表，返回账户集合
     *
     * @param userId
     * @param accountName (模糊查询)
     * @param accountType
     * @param createTime  （大于当前时间）
     * @return
     */
    List<Account> queryAccountByParams(Integer userId, String accountName, String accountType, String createTime);
    // 修改用户记录，返回受影响的行数
    int updateAccount(Account account);
    // 批量修改账户记录，返回受影响的行数
    int[] updateAccountBatch(List<Account> accounts);
    // 删除用户记录，返回受影响的行数
    int deleteAccountById(int accountId);
    // 批量删除账户记录，返回受影响的行数
    int[] deleteAccountBatch(Integer[] ids);
}

7. 实现类

package com.xxx.dao.Impl;

@Repository
public class AccountDaoImpl implements IAccountDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    // 添加账户记录，返回受影响的行数
    @Override
    public int addAccount(Account account) {
        String sql = "insert into tb_account(account_name, account_type, money, remark,user_id) value(?,?,?,?,?)";
        Object[] obj = {account.getAccountName(), account.getAccountType(), account.getMoney(), account.getRemark(), account.getUserId()};
        return jdbcTemplate.update(sql, obj);
    }

    // 添加账户记录，返回主键
    @Override
    public int addAccountHasKey(Account account) {
        String sql = "insert into tb_account(account_name, account_type, money, remark,user_id) value(?,?,?,?,?)";
        Object[] obj = {account.getAccountName(), account.getAccountType(), account.getMoney(), account.getRemark(), account.getUserId()};
        // 定义keyHolder对象，获取记录主键
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            ps.setString(1, account.getAccountName());
            ps.setString(2, account.getAccountType());
            ps.setDouble(3, account.getMoney());
            ps.setString(4, account.getRemark());
            ps.setInt(5, account.getUserId());
            return ps;
        }, keyHolder);
        // 返回得到的主键
        return keyHolder.getKey().intValue();

    }

    // 批量添加账户记录，返回受影响的行数
    @Override
    public int[] addAccountBatch(List<Account> accounts) {
        String sql = "insert into tb_account(account_name, account_type, money, remark,user_id) value(?,?,?,?,?)";
        int[] rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // 设置参数
                ps.setString(1, accounts.get(i).getAccountName());
                ps.setString(2, accounts.get(i).getAccountType());
                ps.setDouble(3, accounts.get(i).getMoney());
                ps.setString(4, accounts.get(i).getRemark());
                ps.setInt(5, accounts.get(i).getUserId());
            }

            @Override
            public int getBatchSize() {
                return accounts.size();
            }
        });
        return rows;
    }

    // 查询指定用户的账户个数，并返回
    @Override
    public int queryAccountCountByUserId(int userId) {
        String sql = "select count(1) from tb_account where user_id=?";
        int i = jdbcTemplate.queryForObject(sql, int.class, userId);
        return i;
    }

    // 查询指定账户的账户详情，返回账户对象
    @Override
    public Account queryAccountById(int accountId) {
        String sql = "select * from tb_account where account_id=?";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class);
        Account account = jdbcTemplate.queryForObject(sql, rowMapper, accountId);
        return account;
    }

    /**
     * 多条件查询指定用户的账户列表，返回账户集合
     *
     * @param userId
     * @param accountName (模糊查询)
     * @param accountType
     * @param createTime  （大于当前时间）
     * @return
     */
    @Override
    public List<Account> queryAccountByParams(Integer userId, String accountName, String accountType, String createTime) {
        String sql = "select * from tb_account where user_id=?";
        List<Object> params = new ArrayList<>();    // 查询参数
        params.add(userId);
        // 判断参数是否为空
        if (accountName != null && !accountName.trim().equals("")) {
            params.add(accountName);
            sql += " and account_name like concat('%', ? , '%') ";
        }
        if (accountType != null && !accountType.trim().equals("")) {
            params.add(accountType);
            sql += " and account_type=?";
        }
        if (createTime != null && !createTime.trim().equals("")) {
            params.add(createTime);
            sql += " and create_time> ?";
        }
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class);
        List<Account> accounts = jdbcTemplate.query(sql, rowMapper, params.toArray());
        return accounts;
    }

    // 修改用户记录，返回受影响的行数
    @Override
    public int updateAccount(Account account) {
        String sql = "update tb_account set account_name=?,account_type=?,money=?,remark=?,user_id=? where account_id=?";
        Object[] objs = {account.getAccountName(), account.getAccountType(), account.getMoney(),
                account.getRemark(), account.getUserId(), account.getAccountId()};
        return jdbcTemplate.update(sql, objs);
    }

    // 批量修改账户记录，返回受影响的行数
    @Override
    public int[] updateAccountBatch(List<Account> accounts) {
        String sql = "update tb_account set account_name=?,account_type=?,money=?,remark=?,user_id=? where account_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // 设置参数
                ps.setString(1, accounts.get(i).getAccountName());
                ps.setString(2, accounts.get(i).getAccountType());
                ps.setDouble(3, accounts.get(i).getMoney());
                ps.setString(4, accounts.get(i).getRemark());
                ps.setInt(5, accounts.get(i).getUserId());
                ps.setInt(6, accounts.get(i).getAccountId());
            }

            @Override
            public int getBatchSize() {
                return accounts.size();
            }
        });
        return ints;
    }

    // 删除用户记录，返回受影响的行数
    @Override
    public int deleteAccountById(int accountId) {
        String sql = "delete from tb_account where account_id=?";
        int row = jdbcTemplate.update(sql, accountId);
        return row;
    }

    // 批量删除账户记录，返回受影响的行数
    @Override
    public int[] deleteAccountBatch(Integer[] ids) {
        String sql = "delete from tb_account where account_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, ids[i]);
            }
            @Override
            public int getBatchSize() {
                return ids.length;
            }
        });
        return ints;
    }
}

测试

package com.xxx;

/**
 * spring-test与junit整合 ？
 * 因为junit的运行器不能再测试方法中通过注解的方法读取外部文件。如果这两者整合后，
 * 就可以替换junit的运行器为SpringJUnit4ClassRunner，测试方法就可以通过注解读取外部文件。
 * <p>
 * BaseTest作为父类，子测试类继承父类，不必重复配置@RunWith和@ContextConfiguration
 */
@RunWith(SpringJUnit4ClassRunner.class)     // 将测试运行在Spring测试环境中
@ContextConfiguration(locations = {"classpath:spring.xml",})    // 要加载的配置文件
public class AccountTest extends BaseTest {
    
    @Resource
    private IAccountDao dao;

    @Test
    public void addTest() {
        int i = dao.addAccount(new Account("张三",
                "工商银行", 1000.0, "这是生活费", 2));
        System.out.println("受影响行数：" + i);

        int key = dao.addAccountHasKey(new Account("李四",
                "农商银行", 900.0, "这是学杂费", 3));
        System.out.println("主键：" + key);

        ArrayList<Account> list = new ArrayList<>();
        list.add(new Account("王五", "建设银行", 1900.0, "不知", 1));
        list.add(new Account("赵六", "人民银行", 12000.0, "零花钱", 2));
        int[] keys = dao.addAccountBatch(list);
        System.out.println("受影响行数数组：" + Arrays.toString(keys));
    }

    @Test
    public void deleteTest() {
        int i = dao.deleteAccountById(18);
        System.out.println("受影响行数：" + i);

        int[] ints = dao.deleteAccountBatch(new Integer[]{11, 12, 13, 14, 15, 16});
        System.out.println("受影响行数数组：" + Arrays.toString(ints));
    }

    @Test
    public void updateTest() {
        Account account = new Account("张三", "工商银行", 1000D, "这是***", 2);
        account.setAccountId(3);
        int i = dao.updateAccount(account);
        System.out.println("受影响行数：" + i);

        Account account1 = new Account("张三", "郑州垃圾银行", 1000D, "这是***", 2);
        account1.setAccountId(3);
        Account account2 = new Account("王五", "郑州垃圾银行", 1900.0, "不知", 1);
        account2.setAccountId(5);
        ArrayList<Account> list = new ArrayList<>();
        list.add(account1);
        list.add(account2);
        int[] ints = dao.updateAccountBatch(list);
        System.out.println("受影响行数数组：" + Arrays.toString(ints));
    }

    @Test
    public void queryTest() {
        Account account = dao.queryAccountById(1);
        System.out.println(account);

        int count = dao.queryAccountCountByUserId(1);
        System.out.println("count: " + count);

        List<Account> accounts = dao.queryAccountByParams(1, "户", null, null);
        accounts.forEach(System.out::println);
    }
}