mysql事务处理

c3p0工具类
package com.offcn.util;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/*
 * c3p0 基于c3p0工具类
 */
public class C3P0Util {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    public static Connection getconn(){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
}
------------------------------------------------------------------------------------------------------------------------------
控制层
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        
        String from = request.getParameter("from");
        String to = request.getParameter("to");
        Double money = Double.parseDouble(request.getParameter("money"));

        TransformService ts = new TransformService();
        try {
            ts.trans(from,to,money);
            response.getWriter().println("转账成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
业务层
    public Boolean trans(String from, String to, Double money) throws SQLException {
        //若from表示张三，to表示李四，则张三账户减少1000元，李四账户增加1000元
        Connection conn = C3P0Util.getconn();
        TransformDao td = new TransformDao();
        try {
            conn.setAutoCommit(false);
            int i = td.from(from,money,conn);
            int j = td.to(to,money,conn);
            int p = 10/0;
            //提交事务
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            //回滚事务
            conn.rollback();
        }finally {
            try {
            //关闭连接
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }
持久层
public class MoneyDao {

    public int from(String from, double money) throws Exception {
        
        Connection conn = c3p0Util.getConn();
        PreparedStatement pstmt = conn.prepareStatement("update account set money=money-? where name=?");
        pstmt.setDouble(1, money);
        pstmt.setString(2, from);
        int i = pstmt.executeUpdate();
        pstmt.close();
        conn.close();
        return i;
        
    }

    public int to(String to, double money) throws Exception {

        Connection conn = c3p0Util.getConn();
        PreparedStatement pstmt = conn.prepareStatement("update account set money=money+? where name=?");
        pstmt.setDouble(1, money);
        pstmt.setString(2, to);
        int i = pstmt.executeUpdate();
        pstmt.close();
        conn.close();
        return i;
    }

}

 

转载于:https://www.cnblogs.com/zhangningning/p/9917656.html