事务（转账实例）

事务（以转账为例）

事务:
　　就是一件完整的事情,包含多个操作单元,这些操作要么全部成功,要么全部失败.
　　例如:转账，包含转出操作和转入操作.

mysql中的事务:
        mysql中事务默认是自动提交,一条sql语句就是一个事务.
        开启手动事务方式
            方式1:关闭自动事务.(了解)
                set autocommit = off;
            方式2:手动开启一个事务.(理解)
                start transaction;-- 开启一个事务
                commit;-- 事务提交
                rollback;-- 事务回滚
        扩展:
            oracle中事务默认是手动的,必须手动提交才可以.

    java中的事务:
        Connection接口的api:★
            setAutoCommit(false);//手动开启事务
            commit():事务提交
            rollback():事务回滚
        
        扩展:了解 Savepoint还原点
            void rollback(Savepoint savepoint) :还原到那个还原点
            Savepoint setSavepoint() :设置还原点

例如:创建数据库和表
            create database hjh;
            use hjh;
            create table account(
                name varchar(20),
                money int
            );
            
            insert into account values('hejh','1000');
            insert into account values('swy','1000');
            
        完成 hejh给swy转500;
            update account set money = money - 100 where name='';
            update account set money = money + 100 where name='swy';

转账案例：

步骤分析:
    1.数据库和表
    2.新建一个项目 transfer
    3.导入jar包和工具类
        驱动 jdbcUtils
        c3p0及其配置文件和工具类
        dbutils
    4.新建一个account.jsp 表单
    5.accountservlet:
        接受三个参数
        调用accountservice.account方法完成转账操作
        打印信息
    6.account方法中:
        使用jdbc不考虑事务
        调用dao完成转出操作
        调用dao完成转入操作
    7.dao中

代码实现：

　　web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" 
　　xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" 
　　　　　　id="WebApp_ID" version="3.1">
  <servlet>
      <servlet-name>AccountServlet</servlet-name>
      <servlet-class>com.hjh.servlet.AccountServlet</servlet-class>
  </servlet>
  <servlet-mapping>
      <servlet-name>AccountServlet</servlet-name>
      <url-pattern>/account</url-pattern>
  </servlet-mapping>
</web-app>

　　account.jsp　

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>转账页面</title>
</head>
<body>
    <form action="${pageContext.request.contextPath }/account" method="post">
        <table border="1" width="400">
            <tr>
                <td>付款人:</td>
                <td><input type="text" name="fromUser"/></td>
            </tr>
            <tr>
                <td>收款人:</td>
                <td><input type="text" name="toUser"/></td>
            </tr>
            <tr>
                <td>转账金额:</td>
                <td><input type="text" name="money"/></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="转账"/></td>
            </tr>
        </table>
    </form>
</body>
</html>

AccountServlet.java

package com.hjh.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hjh.service.AccountService;

/**
 * 转账案例
 */
public class AccountServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");
        //获取输出流
        PrintWriter w = response.getWriter();
        
        //接收jsp页面传来的三个参数
        String fromUser = request.getParameter("fromUser");
        String toUser = request.getParameter("toUser");
        int money = Integer.parseInt(request.getParameter("money"));
                
        //调用AccountService的transterAccount(fromUser，toUser，money)方法
        try {
            new AccountService().transterAccount(fromUser,toUser,money);
        } catch (Exception e) {
            e.printStackTrace();
            w.println("转账失败");
            return;
        }
        
        //打印提示信息
        w.print("转账成功");
    }

  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

　　AccountService.java

package com.hjh.service;

import java.sql.SQLException;
import com.hjh.dao.AccountDao;

public class AccountService {
    public void transterAccount(String fromUser, String toUser, int money) throws Exception {
        
        AccountDao dao = new AccountDao();
        
        //转出方，出钱
        dao.accountFrom(fromUser,money);
                
        //转入方，进钱
        dao.accountTo(toUser,money);
    }
}

　　AccountDao.java

package com.hjh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.hjh.utils.JDBCUtil;

public class AccountDao {

    //出账
    public void accountFrom(String fromUser, int money) throws SQLException {
        Connection conn = null;
        PreparedStatement st  = null;
        
        try {
            //获取连接
            conn = JDBCUtil.getConnection();
            //编写sql
            String sql="update account set money = money - ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, fromUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转出钱成功"+i);
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally {
             JDBCUtil.closeResourse(conn, st);
        }
        
    }
    
    //入账
    public void accountTo(String toUser, int money) throws SQLException {
        Connection conn = null;
        PreparedStatement st  = null;
        
        try {
            //获取连接
            conn = JDBCUtil.getConnection();
            //编写sql
            String sql="update account set money = money + ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, toUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转入钱成功"+i);
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }    finally {
             JDBCUtil.closeResourse(conn, st);
        }
    }
}

　　JDBCUtil.java

package com.hjh.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {
    final static String driver = "com.mysql.jdbc.Driver";
    final static String url = "jdbc:mysql://localhost/hjh?useUnicode=true&characterEncoding=UTF-8";
    final static String user  = "root";
    final static String password = "root";
    
    Connection conn = null;
    PreparedStatement ps = null;
    Statement st = null;
    ResultSet rs = null;
        
    /**获取连接*/
    public static Connection getConnection() throws SQLException  {
        Connection    conn = null;
        try {
            //注册驱动
            Class.forName(driver);
            //获取连接
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }                    
        return conn;    
    }
    
    /**关闭资源closeResourse（conn,st）*/
    public static void closeResourse(Connection conn,Statement st) {
        try {
            if(st!=null) {
                st.close();
            }else {
                st = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null) {
                conn.close();
            }else {
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**关闭资源closeResourse（conn,ps）*/
    public static void closeResourse(Connection conn,PreparedStatement ps) {
        try {
            if(ps!=null) {
                ps.close();
            }else {
                ps = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null) {
                conn.close();
            }else {
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**关闭资源closeResourse（rs）*/
    public static void closeResourse(ResultSet rs) {
        try {
            if(rs!=null) {
                rs.close();
            }else {
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

　　启动项目，进行转账，不发生异常的时候，转账是成功的，

转账前：

　　转账后：

一旦出现异常,钱飞了.

　　模拟断电：

　　 accountService,java中加一个异常，模拟断电的场景：

package com.hjh.service;

import java.sql.SQLException;
import com.hjh.dao.AccountDao;

public class AccountService {
    public void transterAccount(String fromUser, String toUser, int money) throws Exception {
        
        AccountDao dao = new AccountDao();
        
        //转出方，出钱
        dao.accountFrom(fromUser,money);
        
        int i=3/0;
        
        //转入方，进钱
        dao.accountTo(toUser,money);
    }
}

　　转账前：

　　转账但是中途发生断电后，转账失败，但是在查询数据库之后发现，转出方的钱被扣了，但是转入方的钱不变，这样就会造成很重大的影响：

一旦出现异常,钱飞了.
    要想避免这事情,必须添加事务,在service添加事务.
    为了保证所有的操作在一个事务中,必须保证使用的是同一个连接
    在service层我们获取了连接,开启了事务.如何dao层使用此连接呢????

方法1:
            向下传递参数.注意连接应该在service释放

　　accountDao.java

package com.hjh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.hjh.utils.JDBCUtil;

public class AccountDao {

    //出账
    public void accountFrom(Connection conn, String fromUser, int money) throws SQLException {
        PreparedStatement st  = null;
        
        try {    
            //编写sql
            String sql="update account set money = money - ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, fromUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转出钱成功"+i);    
        } catch (SQLException e) {
            //e.printStackTrace();
            throw e;
        }finally {
             JDBCUtil.closeStatement(st);
        }
    }
    
    //入账
    public void accountTo(Connection conn, String toUser, int money) throws SQLException {
        PreparedStatement st  = null;
        
        try {
            //编写sql
            String sql="update account set money = money + ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, toUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转入钱成功"+i);
            
        } catch (SQLException e) {
            //e.printStackTrace();
            throw e;
        }    finally {
             JDBCUtil.closePreparedStatement(st);
        }
    }
}

　　accountService.java

package com.hjh.service;

import java.sql.Connection;
import com.hjh.dao.AccountDao;
import com.hjh.utils.JDBCUtil;

public class AccountService {
    public void transterAccount(String fromUser, String toUser, int money) throws Exception  {
        Connection conn = null;
        AccountDao dao = new AccountDao();
        
            //开启事务
            try {
                conn = JDBCUtil.getConnection();
                conn.setAutoCommit(false);//设置事务为手动提交
                
                //转出方，出钱
                dao.accountFrom(conn,fromUser,money);
                
                int i=3/0;
                
                //转入方，进钱
                dao.accountTo(conn,toUser,money);
                //事务提交
                conn.commit();
            } catch (Exception e) {
                //e.printStackTrace();
                //事务回滚
                conn.rollback();
                throw e;
            }finally {
                JDBCUtil.closeConnection(conn);
            }
    }
}

　　accountServlet.java

package com.hjh.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hjh.service.AccountService;

/**
 * 转账案例
 *         解决中途断电问题：
 *     方法1:
            向下传递参数.注意连接应该在service释放
 */
public class AccountServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");
        //获取输出流
        PrintWriter w = response.getWriter();
        
        //接收jsp页面传来的三个参数
        String fromUser = request.getParameter("fromUser");
        String toUser = request.getParameter("toUser");
        int money = Integer.parseInt(request.getParameter("money"));
                
        //调用AccountService的transterAccount(fromUser，toUser，money)方法
        try {
            new AccountService().transterAccount(fromUser,toUser,money);
        } catch (Exception e) {
            e.printStackTrace();
            w.println("转账失败");
            return;
        }
        
        //打印提示信息
        w.print("转账成功");
    }

  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

　　JDBCUtil.java

package com.hjh.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {
    final static String driver = "com.mysql.jdbc.Driver";
    final static String url = "jdbc:mysql://localhost/hjh?useUnicode=true&characterEncoding=UTF-8";
    final static String user  = "root";
    final static String password = "root";
    
    Connection conn = null;
    PreparedStatement ps = null;
    Statement st = null;
    ResultSet rs = null;
        
    /**获取连接*/
    public static Connection getConnection() throws SQLException  {
        Connection    conn = null;
        try {
            //注册驱动
            Class.forName(driver);
            //获取连接
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }                    
        return conn;    
    }
    
    public static void closeConnection_Statement_ResultSet(Connection conn,Statement st,ResultSet rs) {    
        closeResultSet(rs);
        closeStatement(st);
        closeConnection(conn);    
    }
    
    public static void closeConnection_Statement(Connection conn,Statement st) {    
        closeStatement(st);
        closeConnection(conn);    
    }
    public static void closeConnection_PreparedStatement_ResultSet(Connection conn,PreparedStatement ps,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ResultSet rs) {    
        closeResultSet(rs);
        closePreparedStatement(ps);
        closeConnection(conn);    
    }
    public static void closeConnection_PreparedStatement(Connection conn,PreparedStatement ps) {    
        closePreparedStatement(ps);
        closeConnection(conn);    
    }
    
    
    /**关闭资源 closeStatement(（st）*/
    public static void closeStatement(Statement st) {
        try {
            if(st!=null) {
                st.close();
            }else {
                st = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }    
    }
    
    /**关闭资源closeResourse（conn,ps）*/
    public static void closeResourse(Connection conn,PreparedStatement ps) {
        closePreparedStatement(ps);
        closeConnection(conn);
    }
    
    public static void closePreparedStatement(PreparedStatement ps) {
        try {
            if(ps!=null) {
                ps.close();
            }else {
                ps = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }    
    }
    
    public static void closeConnection(Connection conn) {
        try {
            if(conn!=null) {
                conn.close();
            }else {
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    
    /**关闭资源closeResourse（rs）*/
    public static void closeResultSet(ResultSet rs) {
        try {
            if(rs!=null) {
                rs.close();
            }else {
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

　　account.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>转账页面</title>
</head>
<body>
    <form action="${pageContext.request.contextPath }/account" method="post">
        <table border="1" width="400">
            <tr>
                <td>付款人:</td>
                <td><input type="text" name="fromUser"/></td>
            </tr>
            <tr>
                <td>收款人:</td>
                <td><input type="text" name="toUser"/></td>
            </tr>
            <tr>
                <td>转账金额:</td>
                <td><input type="text" name="money"/></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="转账"/></td>
            </tr>
        </table>
    </form>
</body>
</html>

　　 accountService.java的这一行代码注释掉，即转入和转出这2个步骤中是连贯的，不存在异常中断的，转账能成功

    int i=3/0;

　　accountService.java的这一行代码不注释，转出操作完成，扣款；发生异常被捕捉，事务回滚到转账操作之前，金额不变，转账失败

 int i=3/0;

方法2:
            可以将connection对象绑定当前线程上
            jdk中有一个ThreadLocal类,
            ThreadLocal 实例通常是类中的 private static 字段，
            它们希望将状态与某一个线程（例如，用户 ID 或事务 ID）相关联。

ThreadLocal的方法:
		构造:
			new ThreadLocal()
		set(Object value):将内容和当前线程绑定
		Object get():获取和当前线程绑定的内容
		remove():将当前线程和内容解绑
	内部维护了map集合
		map.put(当前线程,内容);
		map.get(当前线程)
		map.remove(当前线程)

　　AccountDao.java

package com.hjh.dao;

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

import com.hjh.utils.DataSourseUtils;

public class AccountDao {

    //出账
    public void accountFrom( String fromUser, int money) throws Exception {
        Connection conn = null;
        PreparedStatement st  = null;
        
        try {    
            conn = DataSourseUtils.getConnection();
            //编写sql
            String sql="update account set money = money - ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, fromUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转出钱成功"+i);    
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally {
            DataSourseUtils.closeStatement(st);
        }
    }
    
    //入账
    public void accountTo( String toUser, int money) throws Exception {
        Connection conn = null;
        PreparedStatement st  = null;
        
        try {
            conn = DataSourseUtils.getConnection();
            //编写sql
            String sql="update account set money = money + ?  where name = ?";
            //获取sql语句执行者
            st = conn.prepareStatement(sql);
            //设置sql参数
            st.setInt(1, money);
            st.setString(2, toUser);
            //执行sql
            int i = st.executeUpdate();
            System.out.println("转入钱成功"+i);
        } catch (SQLException e) {
            //e.printStackTrace();
            throw e;
        }    finally {
            DataSourseUtils.closeStatement(st);
        }
    }
}

　　AccountService.java

package com.hjh.service;

import com.hjh.dao.AccountDao;
import com.hjh.utils.DataSourseUtils;

public class AccountService {
    public void transterAccount(String fromUser, String toUser, int money) throws Exception  {
        AccountDao dao = new AccountDao();
        
            try {
                //开启事务
                DataSourseUtils.startTransaction();
                
                //转出方，出钱
                dao.accountFrom(fromUser,money);
                //int i=3/0;
                //转入方，进钱
                dao.accountTo(toUser,money);
                
                //事务提交
                DataSourseUtils.commitAndClose();
            } catch (Exception e) {
                e.printStackTrace();
                //事务回滚
                DataSourseUtils.rollbackAndClose();
                throw e;
            }
    }
}

　　AccountServlet.java

package com.hjh.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hjh.service.AccountService;

/**
 * 转账案例
 *         解决中途断电问题：
 *     方法1:
            向下传递参数.注意连接应该在service释放
 */
public class AccountServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");
        //获取输出流
        PrintWriter w = response.getWriter();
        
        //接收jsp页面传来的三个参数
        String fromUser = request.getParameter("fromUser");
        String toUser = request.getParameter("toUser");
        int money = Integer.parseInt(request.getParameter("money"));
                
        //调用AccountService的transterAccount(fromUser，toUser，money)方法
        try {
            new AccountService().transterAccount(fromUser,toUser,money);
        } catch (Exception e) {
            e.printStackTrace();
            w.println("转账失败");
            return;
        }
        
        //打印提示信息
        w.print("转账成功");
    }

  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

　　DataSourseUtils.java

package com.hjh.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourseUtils {
    //建立连接池ds
    private static ComboPooledDataSource ds =     new ComboPooledDataSource();
    //将connection绑定在当前线程中
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();
    
    //获取数据源
    public static DataSource getDataSourse() {
        return ds;
    }
    
    //获取连接,从当前线程中获取
    public static Connection getConnection() throws Exception {
        Connection conn = tl.get();
        if(conn==null) {
            //第一次获取，创建一个连接和当前线程绑定在一起
            conn =ds.getConnection();
            //绑定
            tl.set(conn);
        }
        return conn;
    }
    
    //获取连接，开启事务
    public static void startTransaction() throws Exception {
        getConnection().setAutoCommit(false);
    }
    
    //事务提交|解除绑定
    public static void commitAndClose() {
        try {
            Connection conn = getConnection();
            //提交事务
            conn.commit();
            //解除绑定
            tl.remove();    
            //释放资源
            closeConnection(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    //事务回滚
        public static void rollbackAndClose() {
            try {
                Connection conn = getConnection();
                //提交事务
                conn.rollback();
                //释放资源
                closeConnection(conn);
                //解除绑定
                tl.remove();    
            } catch (Exception e) {
                e.printStackTrace();
            }
        }    
    
    
    
    //释放资源connection
    public static void closeConnection(Connection conn) {
        try {
            if(conn!=null) {
                conn.close();
                //并和当前线程解绑
                tl.remove();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        conn = null;
    }
    //释放资源Statement
        public static void closeStatement(Statement st) {
            try {
                if(st!=null) {
                    st.close();
                }else {
                    st = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    
        /**释放资源closePreparedStatement*/
        public static void closePreparedStatement(PreparedStatement ps) {
            try {
                if(ps!=null) {
                    ps.close();
                }else {
                    ps = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }        
    //释放资源Connection ,Statement 
    public static void close2Resourse(Connection conn,Statement st) {
        closeStatement(st);
        closeConnection(conn);
    }
    /**释放资源closeResourse（conn,ps）*/
    public static void close2Resourse(Connection conn,PreparedStatement ps) {
        closePreparedStatement(ps);
        closeConnection(conn);
    }
    /**释放资源closeResourse（rs）*/
    public static void closeResourse(ResultSet rs) {
        try {
            if(rs!=null) {
                rs.close();
            }else {
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
}

　　web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation=
　　　　"http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <servlet>
      <servlet-name>AccountServlet</servlet-name>
      <servlet-class>com.hjh.servlet.AccountServlet</servlet-class>
  </servlet>
  <servlet-mapping>
      <servlet-name>AccountServlet</servlet-name>
      <url-pattern>/account</url-pattern>
  </servlet-mapping>
</web-app>

　　account.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>转账页面</title>
</head>
<body>
    <form action="${pageContext.request.contextPath }/account" method="post">
        <table border="1" width="400">
            <tr>
                <td>付款人:</td>
                <td><input type="text" name="fromUser"/></td>
            </tr>
            <tr>
                <td>收款人:</td>
                <td><input type="text" name="toUser"/></td>
            </tr>
            <tr>
                <td>转账金额:</td>
                <td><input type="text" name="money"/></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="转账"/></td>
            </tr>
        </table>
    </form>
</body>
</html>

DButils:
    1.创建queryrunner
    2.编写sql
    3.执行sql
QueryRunner:
    构造:
        new QueryRunner(DataSource ds):自动事务
        new QueryRunner():手动事务
    常用方法:
        update(Connection conn,String sql,Object ... params):执行的cud操作
        query(Connection conn....):执行查询操作
    注意:
        一旦使用手动事务,调用方法的时候都需要手动传入connection,并且需要手动关闭连接

　　accountDao.java

package com.hjh.dao;

import org.apache.commons.dbutils.QueryRunner;
import com.hjh.utils.DataSourseUtils;

public class AccountDao {

    //出账
    public void accountFrom( String fromUser, int money) throws Exception {
        QueryRunner qr = new QueryRunner();
        
            //编写sql
            String sql="update account set money = money - ?  where name = ?";
            //执行sql
            qr.update(DataSourseUtils.getConnection(),sql,money,fromUser);
            System.out.println("转入钱成功");    
    }
    
    //入账
    public void accountTo( String toUser, int money) throws Exception {
        QueryRunner qr = new QueryRunner();
        
        //编写sql
        String sql="update account set money = money + ?  where name = ?";
        //执行sql
        qr.update(DataSourseUtils.getConnection(),sql,money,toUser);
        System.out.println("转出钱成功");    
    }
}

　　accountService.java

package com.hjh.service;

import com.hjh.dao.AccountDao;
import com.hjh.utils.DataSourseUtils;

public class AccountService {
    public void transterAccount(String fromUser, String toUser, int money) throws Exception  {
        AccountDao dao = new AccountDao();
        
            try {
                //开启事务
                DataSourseUtils.startTransaction();
                
                //转出方，出钱
                dao.accountFrom(fromUser,money);
                //int i=3/0;
                //转入方，进钱
                dao.accountTo(toUser,money);
                
                //事务提交
                DataSourseUtils.commitAndClose();
            } catch (Exception e) {
                e.printStackTrace();
                //事务回滚
                DataSourseUtils.rollbackAndClose();
                throw e;
            }
    }
}

　　accountServlet.java

package com.hjh.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hjh.service.AccountService;

/**
 * 转账案例
 *         解决中途断电问题：
 *     方法1:
            dbutils
 */
public class AccountServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=utf-8");
        //获取输出流
        PrintWriter w = response.getWriter();
        
        //接收jsp页面传来的三个参数
        String fromUser = request.getParameter("fromUser");
        String toUser = request.getParameter("toUser");
        int money = Integer.parseInt(request.getParameter("money"));
                
        //调用AccountService的transterAccount(fromUser，toUser，money)方法
        try {
            new AccountService().transterAccount(fromUser,toUser,money);
        } catch (Exception e) {
            e.printStackTrace();
            w.println("转账失败");
            return;
        }
        
        //打印提示信息
        w.print("转账成功");
    }

  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

　　DataSourceUtils.java

package com.hjh.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourseUtils {
    //建立连接池ds
    private static ComboPooledDataSource ds =     new ComboPooledDataSource();
    //将connection绑定在当前线程中
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();
    
    //获取数据源
    public static DataSource getDataSourse() {
        return ds;
    }
    
    //获取连接,从当前线程中获取
    public static Connection getConnection() throws Exception {
        Connection conn = tl.get();
        if(conn==null) {
            //第一次获取，创建一个连接和当前线程绑定在一起
            conn =ds.getConnection();
            //绑定
            tl.set(conn);
        }
        return conn;
    }
    
    //获取连接，开启事务
    public static void startTransaction() throws Exception {
        getConnection().setAutoCommit(false);
    }
    
    //事务提交|解除绑定
    public static void commitAndClose() {
        try {
            Connection conn = getConnection();
            //提交事务
            conn.commit();
            //解除绑定
            tl.remove();    
            //释放资源
            closeConnection(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    //事务回滚
        public static void rollbackAndClose() {
            try {
                Connection conn = getConnection();
                //提交事务
                conn.rollback();
                //释放资源
                closeConnection(conn);
                //解除绑定
                tl.remove();    
            } catch (Exception e) {
                e.printStackTrace();
            }
        }    
    
    
    
    //释放资源connection
    public static void closeConnection(Connection conn) {
        try {
            if(conn!=null) {
                conn.close();
                //并和当前线程解绑
                tl.remove();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        conn = null;
    }
    //释放资源Statement
        public static void closeStatement(Statement st) {
            try {
                if(st!=null) {
                    st.close();
                }else {
                    st = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    
        /**释放资源closePreparedStatement*/
        public static void closePreparedStatement(PreparedStatement ps) {
            try {
                if(ps!=null) {
                    ps.close();
                }else {
                    ps = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }        
    //释放资源Connection ,Statement 
    public static void close2Resourse(Connection conn,Statement st) {
        closeStatement(st);
        closeConnection(conn);
    }
    /**释放资源closeResourse（conn,ps）*/
    public static void close2Resourse(Connection conn,PreparedStatement ps) {
        closePreparedStatement(ps);
        closeConnection(conn);
    }
    /**释放资源closeResourse（rs）*/
    public static void closeResourse(ResultSet rs) {
        try {
            if(rs!=null) {
                rs.close();
            }else {
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
}

　　c3p0-config.xml

<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <!-- 基本配置 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost/hjh?characterEncoding=UTF-8 </property>
        <property name="user">root</property>
        <property name="password">root</property>
    
        <!--扩展配置-->
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config> 
    
    
    <!-- 命名的配置 -->
    <named-config name="XXX">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/xxxx</property>
        <property name="user">root</property>
        <property name="password">1234</property>
        
        
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">20</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">40</property>
        <property name="maxStatements">20</property>
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

　　account.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>转账页面</title>
</head>
<body>
    <form action="${pageContext.request.contextPath }/account" method="post">
        <table border="1" width="400">
            <tr>
                <td>付款人:</td>
                <td><input type="text" name="fromUser"/></td>
            </tr>
            <tr>
                <td>收款人:</td>
                <td><input type="text" name="toUser"/></td>
            </tr>
            <tr>
                <td>转账金额:</td>
                <td><input type="text" name="money"/></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="转账"/></td>
            </tr>
        </table>
    </form>
</body>
</html>

事务总结:

事务的特性:★★★
        ACID
        原子性:事务里面的操作单元不可切割,要么全部成功,要么全部失败
        一致性:事务执行前后,业务状态和其他业务状态保持一致.
        隔离性:一个事务执行的时候最好不要受到其他事务的影响
        持久性:一旦事务提交或者回滚.这个状态都要持久化到数据库中
    不考虑隔离性会出现的读问题★★
        脏读:在一个事务中读取到另一个事务没有提交的数据
        不可重复读:在一个事务中,两次查询的结果不一致(针对的update操作)
        虚读(幻读):在一个事务中,两次查询的结果不一致(针对的insert操作)
    通过设置数据库的隔离级别来避免上面的问题(理解)
        read uncommitted      读未提交    上面的三个问题都会出现
        read committed      读已提交    可以避免脏读的发生
        repeatable read        可重复读    可以避免脏读和不可重复读的发生
        serializable        串行化        可以避免所有的问题

    
    了解
        演示脏读的发生:
            将数据库的隔离级别设置成 读未提交
                set session transaction isolation level read uncommitted;
            查看数据库的隔离级别
                select @@tx_isolation;
        避免脏读的发生,将隔离级别设置成  读已提交
            set session transaction isolation level read committed;
            不可避免不可重复读的发生.
        
        避免不可重复读的发生 经隔离级别设置成 可重复读
            set session transaction isolation level  repeatable read;
            
        演示串行化 可以避免所有的问题
            set session transaction isolation level  serializable;
            锁表的操作.

    四种隔离级别的效率
        read uncommitted>read committed>repeatable read>serializable
    四种隔离级别的安全性
        read uncommitted<read committed<repeatable read<serializable

开发中绝对不允许脏读发生.
        mysql中默认级别:repeatable read
        oracle中默认级别:read committed

    java中控制隔离级别:(了解)
        Connection的api
            void setTransactionIsolation(int level) 
                level是常量

相关阅读:
Individual Project
最后的作业
 Reading Task 2 —— by12061154Joy
Code Review —— by12061154Joy
Pair Project —— Elevator Scheduler
《移山之道》Reading Task——by12061154Joy
Individual Project
qa_model
个人阅读作业2
Personal Reading Assignment 2 -读推荐文章有感以及项目开发目前总结
原文地址：https://www.cnblogs.com/hejh/p/11088127.html