事务操作之转账案例

dao层：

package dao;

import org.apache.commons.dbutils.QueryRunner;
import utils.C3P0utilsXML;

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

public class TransferAccountDao {
    //转出钱
    public int fromAccount(String fromName, double money, Connection con) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0utilsXML.getDataSource());
        String sql = "update account set money=money-? where name=?";
        int  row = qr.update(con, sql,money,fromName);
        return row;

    }

    //转入钱
    public int toAccount(String toName,double money,Connection con) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0utilsXML.getDataSource());
        String sql = "update account set money=money+? where name=?";
        int row = qr.update(con, sql, money, toName);
        return row;

    }
}

service层

package service;

import dao.TransferAccountDao;
import utils.C3P0utilsXML;

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

public class TransferAccountService {
    //转账
    public boolean transferAccount(String fromName, String toName, double money) {
        //要添加事务必须使用同一个connection
        Connection con = C3P0utilsXML.getConnection();
    TransferAccountDao dao = new TransferAccountDao();
    boolean flag = true;

    try {
        con.setAutoCommit(false);//设置手动开启mysql数据库的事务
        int row1 = dao.fromAccount(fromName, money, con);
        int row2 = dao.toAccount(toName, money, con);

        if (row1 > 0 && row2 > 0) {
            con.commit();//如果转出和转入均成功，则提交事务
            flag = true;
        } else {
            con.rollback();//如果有一个操作不成功，就进行事务的回滚，一项操作都不做
            flag = false;
        }


    } catch (SQLException e) {
        e.printStackTrace();
    }
    return flag;


    }
}

web层

package web;

import service.TransferAccountService;

import java.util.Scanner;

public class TransferWeb {
public static void main(String[] args) {
    Scanner sc = new Scanner(System.in);

    System.out.println("请输入转出账户：");
    String fromName = sc.nextLine();

    System.out.println("请输入转入账户:");
    String toName = sc.nextLine();

    System.out.println("请输入转出金额：");
    double money = sc.nextDouble();

    TransferAccountService account = new TransferAccountService();
    boolean b = account.transferAccount(fromName, toName, money);

    if (b){
        System.out.println("转账成功");
    }else {
        System.out.println("转账失败");
    }



    }
}

工具类（C3P0连接池）：

package utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0utilsXML {
    //C3P0连接池特有的，创捷一个工具类需要使用XML配置文件
    //把c3p0-config.xml复到当前模块day03的src下边(名字不能改变,xml文件里边的内容格式也不能改变)
    //C3P0会自动读取该文件，使用连接池的配置信息给C3P0的对象赋值

    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
//连接池的配置信息会自动从XML文件中读取

//创捷静态方法得到一个数据量连接对象
public  static Connection getConnection(){
    try {
        return dataSource.getConnection();
    } catch (SQLException e) {
        throw new RuntimeException("获取数据库连接失败"+e);
    }
}

//创捷静态方法，用来返回连接池对象，共QueryRunner使用
public static DataSource getDataSource(){
    return  dataSource;
}

//创捷静态方法，释放资源
public static void close(Connection conn, Statement stat, ResultSet rs){
    if(conn != null){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if (stat != null){
        try {
            stat.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if (rs != null){
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    }
}