JDBC事务转账业务

1,准备工作

       1) 我们需要使用的jar包如下

                durid.jar包 mysql驱动jar包,dbutils.jar包,C3P0.jar包,junit.jar包

        2) 准备的数据库 account 字段 id(int),name(varchar),money(double)

        3) C3P0配置文件 c3p0-config.xml

        

<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名</property>
        <property name="user">数据库用户名</property>
        <property name="password">数据库密码</property>
    </default-config>
</c3p0-config>

        4) 工具类(C3P0utils)

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class C3P0Utils {
    private static DataSource ds = new ComboPooledDataSource();

    /*
     *  通过static 代码块 让我们这个代码在整个程序运行过程中只加载一次
     * */
    private static String driverclass;
    private static String url;
    private static String username;
    private static String password;

    // 获取我们的数据源
    public static DataSource getDs() {
        return ds;
    }

    // 添加一个方法 , 获取我们的连接对象 Connection
    public static Connection getConn() {

        Connection conn;
        try {
            return conn = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //关流操作
    public static void close(Connection conn, PreparedStatement stat, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 当我们再去做更新操作的时候,我们不需要 ResultSet
    public static void close(Connection conn, PreparedStatement stat) {
        close(conn, stat, null);
    }
}

2.准备实体类Bean

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

3. 实现Dao层代码

        1) AccountDao

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

        2) AccountDaoImpl

public class AccountDaoImpl implements AccountDao {

    //
    QueryRunner qr = new QueryRunner();

    // 声明一个连接对象
    private Connection conn;

    // 通过构造赋值
    public AccountDaoImpl(Connection conn) {
        this.conn = conn;
    }

    /**
     * 通过name查指定用户
     * @param name 用户名
     * */
    @Override
    public Account findByName(String name) {
        String sql = "select * from account where name = ?";
        try {
            return qr.query(conn,sql, new BeanHandler<Account>(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(conn,sql, account.getMoney(), account.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

4. 业务Service层代码

        1) AccountService

public interface AccountService {
    /**
     * 根据账户名查询账户信息
     *
     * @param outName 账户名
     * @param inName  账户名
     * @param money   转账金额
     */
    public void transfer(String outName, String inName, double money);
}

        2) AccountServiceImpl

public class AccountServiceImpl implements AccountService {
    // 从连接池获取Connection对象
    Connection conn = C3P0Utils.getConn();

    private AccountDao accountDao = new AccountDaoImpl(conn);

    @Override
    public void transfer(String outName, String inName, double money) {
        try {
            conn.setAutoCommit(false);//手动开启事物
            // 查询用户
            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);
            conn.commit(); // 到这代码执行完毕，提交事物
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();// 失败回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

5. 测试

public class MyTest01 {
    @Test
    public void test01() {
        AccountService accountService = new AccountServiceImpl();
        accountService.transfer("aaa", "bbb", 100D);

    }
}