Spring整合JDBC实现转账业务

    
        
            org.springframework
            spring-context
            5.2.3.RELEASE
        
        
            org.springframework
            spring-test
            5.2.3.RELEASE
        
        
            junit
            junit
            4.12
            test
        
        
            mysql
            mysql-connector-java
            5.1.47
        
        
            com.alibaba
            druid
            1.1.10
        
        
            org.springframework
            spring-jdbc
            5.2.3.RELEASE
        
        
            commons-dbutils
            commons-dbutils
            1.7
        
        
            com.mchange
            c3p0
            0.9.5.5
        
    

        实体类Account与我们的数据库字段对应即可,提供setter和getter,后期如果想查看对象,我们可以重写toString(方法)

package com.offcn.bean;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private String name;
    private double money;

    public Integer getId() {
        return id;
    }

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

    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{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", money=" + money +
                '}';
    }
}

        ConnectionUtils,这个工具类我们是主要获取我们的Connection,管理我们的Connection,

在这里面我们遇到了一个新的对象ThreadLocal,这个对象我需要说一下,这个是线程的对象的局部变量,在其内部,维护了一个Map集合,Map map = new HashMap<>();   这个ThreadLocal对象,赋值的通过set(传的是Object),取值通过get(),移除通过remove(),操作的时候， key不需要指定，当前线程对象做key。具体方式,如下代码演示:

    // set 
     public void set(Object obj){
         map.put(CurrentThread,obj);//key 不需要指定，默认是当前线程对象。
     }
     //get
     public Object get(){
         return map.get(CurrentThread);
     }
     // remove 移除
     public Object remove(){
         return map.remove(currentThread);
     }

package com.offcn.utils;

import javax.sql.DataSource;
import java.sql.Connection;

public class ConnectionUtils {
    //创建线程局部变量
    static ThreadLocal threadLocal = new ThreadLocal();
    // Connection 是从我们数据源中获取
    private static DataSource dataSource;


    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    // 创建方式 获取我们的Connection,包装获取的是同一个conn对象
    public Connection getConnection() throws Exception {
        Connection connection = threadLocal.get();
        if (connection == null) {
            // 从C3P0文件里面进行获取
            connection = dataSource.getConnection();
            // 绑定在当前线程的局部变量
            threadLocal.set(connection);
        }
        return connection;
    }

    // 从线程中进行解绑
    public void remove() {
        threadLocal.remove();
    }
}

         TxManager:是管理事务的工具类，主要定义了和事务相关的方法，例如：事务的开启，事务的提交，事务的回滚操作等。该工具类目前需要手动创建和管理，使用Spring进行事务管理后，该工具类由Spring提供，不需要手动编写和管理。所以该工具类重在理解，为Spring进行事务管理做好铺垫

package com.offcn.utils;

import java.sql.Connection;
import java.sql.SQLException;

public class TxManager {
    // 创建ConnectionUtils
    private ConnectionUtils connectionUtils;

    public void setConnectionUtils(ConnectionUtils connectionUtils) {
        this.connectionUtils = connectionUtils;
    }

    public ConnectionUtils getConnectionUtils() {
        return connectionUtils;
    }

    // 创建开启事物的方法
    public void beginTransaction() {
        try {
            // 获取我们的连接对象 //开启事务
         connectionUtils.getConnection().setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 创建提交事物的方法
    public void commit() {
        try {
            // 提交事务
            connectionUtils.getConnection().commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 创建事务回滚
    public void rollback() {
        try {
           connectionUtils.getConnection().rollback();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 释放资源
    public void close() throws Exception {
        connectionUtils.getConnection().close();
    }
}

dbConfig.properties

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

applicationContext.xml

AccountDao 数据库访问层,我们定义根据名字查询用户,通过用户修改数据即可

public interface AccountDao {
    public Account findByName(String name);
    public void update(Account account);
}

AccountDaoImpl 

package com.offcn.dao.impl;

import com.offcn.bean.Account;
import com.offcn.dao.AccountDao;
import com.offcn.utils.ConnectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

public class AccountDaoImpl implements AccountDao {

    //我们通过ioc来获取QueryRunner对象 和 ConnectionUtils对象
    private ConnectionUtils connectionUtils;
    QueryRunner qr;

    public ConnectionUtils getConnectionUtils() {
        return connectionUtils;
    }

    public void setConnectionUtils(ConnectionUtils connectionUtils) {
        this.connectionUtils = connectionUtils;
    }

    public QueryRunner getQr() {
        return qr;
    }

    public void setQr(QueryRunner qr) {
        this.qr = qr;
    }

    @Override
    public Account findByName(String name) {
        String sql = "select * from account where name = ?";
        try {
            return qr.query(connectionUtils.getConnection(), sql, new BeanHandler(Account.class), name);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    
    @Override
    public void update(Account account) {
        String sql = "update account set money = ? where name = ?";
        try {
            qr.update(connectionUtils.getConnection(), sql, account.getMoney(), account.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 AccountService: 我们的事务处理需要在业务处理层处理

public interface AccountService {
    
    public void transfer(String outName, String inName, double money);
}

AccountServiceImpl

package com.offcn.service.impl;

import com.offcn.bean.Account;
import com.offcn.dao.AccountDao;
import com.offcn.service.AccountService;
import com.offcn.utils.TxManager;

public class AccountServiceImpl implements AccountService {

    // 引入事务相关的代码
    private TxManager txManager;
    private AccountDao accountDao;

    public TxManager getTxManager() {
        return txManager;
    }

    public void setTxManager(TxManager txManager) {
        this.txManager = txManager;
    }

    public AccountDao getAccountDao() {
        return accountDao;
    }

    public void setAccountDao(AccountDao accountDao) {
        this.accountDao = accountDao;
    }

    @Override
    public void transfer(String outName, String inName, double money) {

        try {
            // 开启事务
            txManager.beginTransaction();
            // 查询用户
            Account byName = accountDao.findByName(outName); // 来源
            Account byName2 = accountDao.findByName(inName); // 目标

            // 更改用户余额
            byName.setMoney(byName.getMoney() - money); // 来源
            byName2.setMoney(byName2.getMoney() + money);//目标
            //更新数据库
            accountDao.update(byName);
            //int i = 1 / 0;
            accountDao.update(byName2);
            // 事务提交
            txManager.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //回滚
            txManager.rollback();
        } finally {
            try {
                // 关闭连接
                txManager.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

package com.offcn.mytest;

import com.offcn.service.AccountService;
import com.offcn.service.impl.AccountServiceImpl;
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;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class MyTest01 {
    @Autowired
    private AccountService accountService;

    @Test
    public void test01() {
        accountService.transfer("aaa", "bbb", 100D);

    }
}