SpringJDBC和事物控制

1.Spring整合JDBC环境

 

spring框架除了提供IOC与AOP核心功能外，同样提供了基于JDBC的数据访问功能，使得访问持久层数据更加方便。使用SpringJDBC环境，首先需要一套Spring整合JDBC的环境

1.2添加依赖坐标

 <!--导入相关依赖坐标    -->
    <!--spring环境依赖-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.3.18</version>
    </dependency>

    <!--spring测试环境    -->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.3.18</version>
      <scope>test</scope>
    </dependency>

    <!--spring AOP  -->
    <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.9.6</version>
      <scope>runtime</scope>
    </dependency>

    <!--spring_jdbc  -->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.18</version>
    </dependency>
    <!--    spring 事物-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.3.23</version>
    </dependency>

    <!--  mysql-->
    <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.0.31</version>
    </dependency>

    <!--c3p0连接池-->
    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.5</version>
    </dependency>
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter</artifactId>
      <version>RELEASE</version>
      <scope>compile</scope>
    </dependency>

1.3添加jdbc配置文件

在src/main/resources目录下新建jdbc.properties配置文件，并设置对应配置信息

#驱动名
jdbc.driver =com.mysql.cj.jdbc.Driver
#数据库连接
jdbc.url=jdbc:mysql://127.0.0.1:3306/spring_jdbc?serverTimezone=UTC&useSSL=false&charaterEncodeing=UTF-8
#数据库用户名称
jdbc.user=root（写自己数据库名称，一般为root）
#数据库用户密码
jdbc.password=111111（自己数据库密码）

1.4修改spring配置文件

<?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.xxxx"/>
<!--    加载properties配置文件，可以读取jdbc。properties配置文件中的数据-->
    <context:property-placeholder location="jdbc.properties"/>


</beans>

1.5配置数据源

由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。

C3p0是一个开源的JDBC连接池，它实现了数据源，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernet,Spring等。c3p0有自动回收空闲连接功能。

C3p0数据源配置

<!--    配置c3p0数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!--        通过properties标签配置对应的值，value属性值对应的是properties配置文件中的值-->
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>

    </bean>
  

1.6模板类配置

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

1.7JDBC测试

1.7.1创建指定数据库

1.7.2使用Junit测试

通过junit测试jdbcTemplate bean是否获取到

package com.xxxx.test;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class SpringJdbcTest01 {
    @Test
    public void  testJdbc(){
        //获取Spring的上下文环境
        ApplicationContext ac=new ClassPathXmlApplicationContext("spring.xml");
        //得到模板类 JdbcTemplate 对象
        JdbcTemplate jdbcTemplate= (JdbcTemplate) ac.getBean("jdbcTemplate");
        //crud操作
        //定义sql语句
        String sql="select count(1) from tb_account";
        //执行查询操作
        Integer total=jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println("总记录数："+total);
    }

}

1.7.3环境测试优化

当有多个测试类时，可设置一个父类Basetest，当使用测试类的时候只需要继承父类的方法

package com.xxxx.test;

import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class) //将测试运行在Spring测试环境中
@ContextConfiguration (locations ={"classpath:spring.xml"}) //设置要加载的配置文件
public class BaseTest {

}

2.持久层账户模块操作

当完成Spring jdbc 环境集成后，这里使用spring jdbc完成账户表单crud（增删改查）操作

2.1账户接口方法定义

2.1.1 定义实体类

Account,java

package com.xxxx.po;

import java.util.Date;

/**
 * 用户账户类
 */
public class Account {
    private  Integer account; //账户ID，主键
    private  String accountName; //账户名称
    private  String accountType; //庄户类型
    private  Double money;//账户金额
    private  String remark; //账户备注
    private  Date  createTime; //创建时间
    private  Date updateTime; //修改时间
    private  Integer userId; //用户ID，账户所属用户

    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;
    }

    @Override
    public String toString() {
        return "Account{" +
                "account=" + account +
                ", accountName='" + accountName + '\'' +
                ", accountType='" + accountType + '\'' +
                ", money=" + money +
                ", remark='" + remark + '\'' +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                ", userId=" + userId +
                '}';
    }

    public Integer getAccount() {
        return account;
    }

    public void setAccount(Integer account) {
        this.account = account;
    }

    public String getAccountName() {
        return accountName;
    }

    public void setAccountName(String accountName) {
        this.accountName = accountName;
    }

    public String getAccountType() {
        return accountType;
    }

    public void setAccountType(String accountType) {
        this.accountType = accountType;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }
}

2.1.2定义接口类

IAccountDao.java

package com.xxxx.dao;

import com.xxxx.po.Account;

import java.util.List;

/**
 * 账户模块 定义接口
 *  1.添加账户
 *       添加账户记录，返回受影响的行数
 *       添加账户记录，返回主键
 *       批量添加账户记录，返回受影响的行数
 *  2.修改账户
 *       修改账户记录，返回受影响的行数
 *       批量修改账户记录，返回受影响的行数
 *  3.删除账户
 *       删除账户记录，返回受影响的行数
 *       批量删除账户记录，返回受影响的行数
 *  4.查询账户
 *       查询指定用户的账户总记录数，返回总记录数
 *       查询指定账户的账户详情，返回账户对象
 *       多条件查询指定用户的账户账户列表，返回账户集合
 *
 */
public interface IAccountDao {

    /**
     * 添加账户
     *      添加账户记录，返回受影响的行数
     * @param account
     * @return
     */
    public int addAccount(Account account);

    /**
     * 添加账户
     *      添加账户记录，返回主键
     * @param account
     * @return
     */
    public int addAccountHasKey(Account account);

    /**
     *添加账户
     *      批量添加账户记录，返回受影响的行数
     * @param accounts
     * @return
     */
    public  int addAccountBatch(List<Account>accounts);

    /**
     * 查询账户
     *       查询指定用户的账户总记录数，返回总记录数
     * @param userId
     * @return
     */
    public int queryAccountCount(int userId);

    /**
     * 查询账户
     *      查询指定账户的账户详情，返回账户对象
     * @param accountID
     * @return
     */
    public Account queryAccountByID(int accountID);

    /**
     * 查询账户
     *      多条件查询指定用户的账户账户列表，返回账户集合
     * @param userId 指定用户的ID
     * @param accountName 账户名称（模糊查询）
     * @param accountType 账户类型
     * @param createTime 创建时间（大于当前时间）
     * @return
     */
    public List<Account> queryAccountByParams(Integer userId,String accountName,String accountType,String createTime);

    /**
     * 修改账户
     *      修改账户记录，返回受影响的行数
     * @param account
     * @return
     */
    public int updateAccount(Account account);

    /**
     * 修改账户
     *      批量修改账户记录，返回受影响的行数
     * @param accounts
     * @return
     */
    public  int updateAccountBath(List<Account> accounts);

    /**
     * 删除账户
     *      删除账户记录，返回受影响的行数
     * @param accountID
     * @return
     */
    public int deleteAccount(int accountID);

    /**
     * 删除账户
     *     批量删除账户记录，返回受影响的行数
     * @param ids
     * @return
     */
    public int deleteAccountBath(Integer[]ids);


}

2.2账户记录添加实现

这里对于账户记录添加方式分为三种方式：添加单条记录返回受影响行数、添加单条记录返回主键、批量添加多条记录

2.2.1添加账户记录

 /**
     * 添加账户记录，返回受影响的行数
     * @param account
     * @return
     */
    @Override
    public int addAccount(Account account) {
        //定义sql语句
        String sql ="insert into tb_account(account_name,account_type,money,remark, create_time,update_time,user_id) " +
                "values(?,?,?,?,now(),now(),?)";
        //设置参数
        Object[] objs={account.getAccountName(),account.getAccountType(),account.getMoney(),
                account.getRemark(),account.getUserId()};

        int row =jdbcTemplate.update(sql,objs);
        return row;
    }

测试方法

 @Test
    public  void testAddAccount(){
        //准备要添加的数据
        Account account=new Account("账户3","工商银行",200.0,"奖金",1);
        //调用对象中的添加方法，返回受影响的行数
        int row =accountDao.addAccount(account);

        System.out.println("添加账户，受影响的行数："+row);
    }

2.2.2添加记录返回主键

 /**
     * 添加账户记录，返回主键
     * @param account
     * @return
     */
    @Override
    public int addAccountHasKey(Account account) {
        //定义sql语句
        String sql="insert into tb_account(account_name,account_type,money,remark, create_time,update_time,user_id) " +
                "values(?,?,?,?,now(),now(),?)";
        //定义KeyHolder对象 获取记录的主键值
        KeyHolder keyHolder=new GeneratedKeyHolder();
        jdbcTemplate.update(connection->{
            //预编译sql，并设置返回主键
            PreparedStatement ps=connection.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);
        //得到返回的主键
        int key=keyHolder.getKey().intValue();
        return key;
    }

测试方法

     /**
     * 添加账户记录，返回主键
     */
    @Test
    public void testAddAccountHasKey(){
        //准备要添加的数据
        Account account=new Account("账户4","中国银行",300.0,"绩效奖",2);
        //调用对象中的添加方法，返回主键
        int key =accountDao.addAccountHasKey(account);
        System.out.println("添加账户，返回主键："+key);
    }

2.2.3批量添加账户记录

/**
     * 批量添加账户记录，返回受影响的行数
     * @param accounts
     * @return
     */
    @Override
    public int addAccountBatch(List<Account> accounts) {
        //定义sql语句
        String sql="insert into tb_account(account_name,account_type,money,remark, create_time,update_time,user_id) " +
                "values(?,?,?,?,now(),now(),?)";

         int rows=jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Account account=accounts.get(i);
                //设置参数
                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());

            }

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

测试方法

/**
     * 批量添加账户记录，返回受影响行数
     */
    @Test
    public void testBatchAddAccount(){
        //准备要添加的数据
        Account account=new Account("账户5","农商银行",300.0,"奖金",3);
        Account account2=new Account("账户6","建设银行",500.0,"绩效奖",3);
        Account account3=new Account("账户7","中国银行",650.0,"全勤奖",3);

        List<Account> accounts=new ArrayList<>();
        accounts.add(account);
        accounts.add(account2);
        accounts.add(account3);
        int rows=accountDao.addAccountBatch(accounts);
        System.out.println("批量添加账户，受影响的行数："+rows);
    }

2.3账户记录查询实现

账户记录查询这里提供了三种查询方式，查询指定用户所有账户记录数、查询单条账户记录详情

多条件查询指定用户记录。

2.3.1查询用户的账户总记录数

 /**
     * 查询指定用户的账户总记录数，返回总数量
     * @param userId
     * @return
     */
    @Override
    public int queryAccountCount(int userId) {
        //定义sql语句
        String sql="select count(1) from tb_account where user_id =?";
        //查询方法
        int count=jdbcTemplate.queryForObject(sql,Integer.class,userId);

        return count;
    }

测试方法

     /**
     * 查询指定用户的账户总记录数，返回总数量
     */
    @Test
    public  void testQueryAccountTest(){
        int  total=accountDao.queryAccountCount(1);
        System.out.println("查询指定用户的账户总记录数，返回总数量"+total);
    }

2.3.2查询指定账户记录的详情

/**
     * 查询指定账户记录的详情，返回账户对象
     * @param accountId
     * @return
     */
    @Override
    public Account queryAccountByID(int accountId) {
        //定义sql语句
        String sql="select * from tb_account where account_id=?";
        //查询对象
        Account account= jdbcTemplate.queryForObject(sql, (ResultSet rs, int i)-> {
            Account acc=new Account();
            acc.setAccount(accountId);
            acc.setAccountName(rs.getString("account_name"));
            acc.setAccountType(rs.getString("account_type"));
            acc.setMoney(rs.getDouble("money"));
            acc.setRemark(rs.getString("remark"));
            acc.setUserId(rs.getInt("user_id"));
            acc.setCreateTime(rs.getDate("create_time"));
            acc.setUpdateTime(rs.getDate("update_time"));
            return acc;
        },accountId);
        return account;
    }

测试方法

     /**
     * 查询指定用户的账户总记录数，返回账户详情
     */
    @Test
    public  void testQueryAccountById(){
        Account account=accountDao.queryAccountByID(1);
        System.out.println("账户详情:"+account.toString());
    }

2.3.3多条件查询指定用户的账户记录列表

    /**
     * 多条件查询指定用户的账户记录列表，返回账户集合
     * @param userId 指定用户的ID
     * @param accountName 账户名称（模糊查询）
     * @param accountType 账户类型
     * @param createTime 创建时间（大于当前时间）
     * @return
     */
    @Override
    public List<Account> queryAccountByParams(Integer userId, String accountName, String accountType, String createTime) {
       //定义sql语句
        String sql="select * from tb_account where user_id = ?";
        //定义参数列表
        List<Object> params=new ArrayList<>();
        params.add(userId);
        //判断参数是否为空，如果不为空，拼接sql语句语句及设置对应的参数
        //账户名称
        if(StringUtils.isNoneBlank(accountName)){
            //拼接sql语句
            sql+=" and account_name like  concat('%', ? ,'%')";
            //设置参数
            params.add(accountName);
        }
        //账户类型
        if(StringUtils.isNoneBlank(accountType)){
            //拼接sql语句
            sql+=" and account_type = ?";
            //设置参数
            params.add(accountType);
        }
        //创建时间
        if(StringUtils.isNoneBlank(createTime)){
            //拼接sql语句
            sql+=" and account_time < ?";
            //设置参数
            params.add(createTime);
        }
        //将集合转换为数组
        Object[] objs=params.toArray();
        //查询集合
        List<Account>accountList =jdbcTemplate.query(sql,objs,(ResultSet rs, int i)-> {
            Account acc=new Account();
            acc.setAccount(rs.getInt("account_id"));
            acc.setAccountName(rs.getString("account_name"));
            acc.setAccountType(rs.getString("account_type"));
            acc.setMoney(rs.getDouble("money"));
            acc.setRemark(rs.getString("remark"));
            acc.setUserId(rs.getInt("user_id"));
            acc.setCreateTime(rs.getTimestamp("create_time"));
            acc.setUpdateTime(rs.getTimestamp("update_time"));
            return acc;
        });
        return accountList;
    }

测试方法

@Test
    /**
     * 多条件查询指定用户的账户记录列表，返回账户集合
     */
    public void testQueryAccountList(){
        List<Account>accountList=accountDao.queryAccountByParams(3,null,null,null);
       // System.out.println(accountList.toString());
        List<Account>accountList2=accountDao.queryAccountByParams(3,"6","建设银行",null);
        System.out.println(accountList2.toString());
    }

2.4.账户记录更新实现

2.4.1更新账户记录

/**
     * 更新账户，返回受影响的行数
     * @param account
     * @return
     */
    @Override
    public int updateAccount(Account account) {
        //定义sql
        String sql="update tb_account set account_name = ? ,account_type = ? ,money = ? ,remark =? ," +
                "update_time =now() ,user_id = ? where account_id = ?";
        //设置参数
        Object[] objs ={
                account.getAccountName(),account.getAccountType(),account.getMoney(),
                account.getRemark(),account.getUserId(),account.getAccount()};
        int row =jdbcTemplate.update(sql,objs);
        return row;
    }

测试方法

 /**
     * 修改账户记录，返回受影响的行数
     */
    @Test
    public void  testUpdateAccount(){
        //准备要添加的数据
        Account account=new Account("账户11","农业银行",2000.0,"奖金奖",1);
        account.setAccount(1);
        int row =accountDao.updateAccount(account);
        System.out.println("修改账户记录，返回受影响的行数:"+row);

    }

2.4.2批量修改账户记录

/**
     * 批量修改账户记录，返回受影响行数
      * @param accounts
     * @return
     */
    @Override
    public int updateAccountBath(List<Account> accounts) {
        //定义sql
        String sql="update tb_account set account_name = ? ,account_type = ? ,money = ? ,remark =? ," +
                "update_time =now() ,user_id = ? where account_id = ?";
        int rows= jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Account account=accounts.get(i);
                //设置参数
                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());
                ps.setInt(6,account.getAccount());
            }

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

测试方法

/**
     * 批量修改账户记录，返回受影响行数
     */
    @Test
    public void testBathUpdateAccount(){
        //准备要添加的数据
        Account account=new Account("账户55","农商银行",3300.0,"奖金",3);
        account.setAccount(5);
        Account account2=new Account("账户66","建设银行",5600.0,"绩效奖",3);
        account2.setAccount(6);
        Account account3=new Account("账户77","中国银行",6050.0,"全勤奖",3);
        account3.setAccount(7);

        List<Account> accounts=new ArrayList<>();
        accounts.add(account);
        accounts.add(account2);
        accounts.add(account3);
        int rows=accountDao.addAccountBatch(accounts);
        System.out.println("批量修改账户记录，返回受影响行数："+rows);
    }

2.5账户记录删除实现

2.5.1删除账户记录

/**
     * 删除账户记录，返回受影响的行数
     * @param accountID
     * @return
     */
    @Override
    public int deleteAccount(int accountID) {
        //定义sql语句
        String sql="delete from  tb_account  where account_id = ?";
        int row=jdbcTemplate.update(sql,accountID);
        return row;
    }

测试方法

 /**
     * 删除账户记录，返回受影响的行数
     */
    @Test
    public void testDeleteAccount(){
        int row=accountDao.deleteAccount(1);
        System.out.println("删除账户记录，返回受影响的行数:"+row);
    }

2.5.2批量删除账户记录

/**
     * 批量删除账户记录，返回受影响的行数
     * @param ids
     * @return
     */
    @Override
    public int deleteAccountBath(Integer[] ids) {
        //定义sql语句
        String sql="delete from  tb_account  where account_id = ?";
        int rows =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;
            }
        }).length;


        return rows;
    }

测试方法

/**
     * 批量删除账户记录，返回受影响的行数
     */
    @Test
    public void testBatchAccount(){
       Integer[] ids={2,3,4};
       int rows=accountDao.deleteAccountBath(ids);
        System.out.println("批量删除账户记录，返回受影响的行数:"+rows);

    }