存储过程 务的概念 事务的特性 关于异常的处理 连接池 构JdbcUtil类

1 存储过程
   1)用当地数据库语言，写的一段业务逻辑算法，并该算法存储在客户端
   2）使用存储过程需要用于CallableStatement接口，同时需要使如下SQL命令调用：{call add_pro(?,?,?)}
   3）对于存储过程的输出参数，需要注册：
    cstmt.registerOutParameter(3,Types.INTEGER);
   4)取得返回值时，需要按照输出参数的位置来取

   编写存储过程得到CallableStatement，并调用存储过程：

      CallableStatement cstmt= conn.prepareCall("{call demoSp(?, ?)}");

   设置参数，注册返回值，得到输出

      cstmt.registerOutParameter(2, Types.VARCHAR);

      cstmt.setString(1, "abcdefg");

      cstmt.execute();

      System.out.println(cStmt.getString(2));

package cn.itcast.web.jdbc.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import cn.itcast.web.jdbc.util.JdbcUtil;

//演示JDBC操作MySQL存储过程
public class Demo1 {
    public static void main(String[] args) {
        Connection conn = null;
        //调用存储过程专用的接口
         CallableStatement cstmt = null;
        ResultSet rs = null;
        //存储过程特定的语法
        String sql = "{call add_pro(?,?,?)}";
        try {
            conn = JdbcUtil.getMySqlConnection();
            cstmt = conn.prepareCall(sql);
            //绑三个参数(前二个是输入,后一个是输出)
            cstmt.setInt(1,100);
            cstmt.setInt(2,200);
            //注册一个输出参数，
            //其中Types.INTEGER表示SQL与JDBC之前的是映射类型
            cstmt.registerOutParameter(3,Types.INTEGER);
            //调用存储过程
            boolean flag = cstmt.execute();
            System.out.println("flag="+flag);
            //取得执行结果
            int sum = cstmt.getInt(3);
            System.out.println("sum="+sum);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(cstmt);
            JdbcUtil.close(conn);
        }
    }
}

2 事务的概念
  1)每种数据库都有事务的支持，但支持强度不同
  2)以MySQL为例，
    启动事务
    start transaction;

    提交事务
    commit;

    回滚事务
    rollback;
  3)在事务范围内回滚是允许的，但如果commit后再回滚，无效  
  4)其实每条SQL都有事务存在，只是显示还隐藏而言，默认都是隐藏事务
  5)事务的操作，必须争对同一个Connection。
  6)事务的操作，可以设置一个回滚点，便于回滚到最近的回滚点处。

  当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列语句：

  JDBC控制事务语句

    •Connection.setAutoCommit(false);

    •Connection.rollback();

    •Connection.commit();

  设置事务回滚点

    •Savepoint sp = conn.setSavepoint();

    •Conn.rollback(sp);

    •Conn.commit(); //回滚后必须要提交
3 事务的特性
    1)原子性（A）事务是的各个操作是一个不可分割的子操作。必须将其看成一个整体，即原子操作
    2)一致性（C）事务前后，由一个一致状态转移到另一个一致状态
  *3)隔离性（I）事务中，每个线程操作同张表同记录时，相互分割
    4)持久性（D）事务一旦生效，在没有操作该记录时情况下，永远保持不变

*4 三个缺点(违背隔离性)
   1)脏读：一个线程看到了另一个线程未提交的数据，叫脏读
   2)不可重复读：一个线程多次做查询操作，多次结果都不一致，叫不可重复读
    上述二项，强调的是查询，内容变，但数量不变
   3)幻读/虚读：
    上述一项，强调的是插入，数量变
*5 事务的隔离级别（解药）
   *static int TRANSACTION_READ_COMMITTED
          指示不可以发生脏读的常量；不可重复读和虚读可以发生。
   *static int TRANSACTION_REPEATABLE_READ
          指示不可以发生脏读和不可重复读的常量；虚读可以发生。
    static int TRANSACTION_SERIALIZABLE
          指示不可以发生脏读、不可重复读和虚读的常量。

不可重复读

	read uncommitted	read committed	repeatable read	serializable
脏读		解决	解决	解决
不可重复读			解决	解决
幻读/虚读				解决

   总结：
    项目中，对于select操作不需要事务，对于其它操作(update/delete/insert)操作需要事务。

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Savepoint;
import cn.itcast.web.jdbc.util.JdbcUtil;

//JDBC显示操作事务的API
public class Demo2 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sqlA = "update account set salary=salary-1000 where name='aaa'";
        String sqlB = "update account set salary=salary+1000 where name='bbb'";
        String sqlC = "insert into account(name,salary) values('ccc',3000)";
        Savepoint sp = null;
        try {        
            conn = JdbcUtil.getMySqlConnection();        
            //设置事务显示手工提交
            conn.setAutoCommit(false);        
            //NO1
            pstmt = conn.prepareStatement(sqlA);
            pstmt.executeUpdate();        
            //NO2
            pstmt = conn.prepareStatement(sqlB);
            pstmt.executeUpdate();
            //设置一个回滚点
            sp = conn.setSavepoint();        
            Integer.parseInt("abc");
            //NO3
            pstmt = conn.prepareStatement(sqlC);
            pstmt.executeUpdate();    
            //设置事务手工提交
            conn.commit();        
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //事务回滚，默认情况下，回滚到事务开始之前的状态
                conn.rollback(sp);
                conn.commit();
            } catch (Exception e1) {            
            }
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
}

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.itcast.web.jdbc.util.JdbcUtil;

//JDBC设置事务的隔离级别
public class Demo3 {
    //我(serializable)先执行
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from account";
        try {
            conn = JdbcUtil.getMySqlConnection();
            //设置事务的隔离级别
              conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            //休息
            Thread.sleep(20*1000);
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
                conn.commit();
            } catch (Exception e1) {
            }
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
}

*6 转帐案例
   1)参见图<<转帐各类的交互图示>>
 

   2)项目中，事务可能在dao层，也可能在service层，不论在哪一层，都必须确保使用的都是同一个connection
   3)为了确保在Service和Dao层中用到的Connection一致，你可以使用如下方案解决：
    a)将Service中的Connection传入Dao中
        设计缺点：
            Service和Dao代码过分藕合
            在Service中引用了非业务逻辑操作

    b)将JdbcUtil类中的Connection作成单例/态

    c)使用ThreadLocale<Connection>将每个线程和自已的Connection绑定在一起，每个线程修改自已的Connection，
          不会影响其它线程的Connection

   4)在分层结构中，关闭Connection会推迟到Service层，但一定要关闭Connection对象    

<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <body>
      <form action="/day14/TransferServlet" method="post">
      <table border="1" align="center"> 
          <caption>转帐</caption>
          <tr>
              <th>转出帐号</th>
              <td><input type="text" name="sid"/></td>
          </tr> 
          <tr>
              <th>转入帐号</th>
              <td><input type="text" name="tid"/></td>
          </tr>
          <tr>
              <th>金额</th>
              <td><input type="text" name="money"/></td>
          </tr>
          <tr>
              <td colspan="2" align="center">
                  <input type="submit" value="转帐"/>
              </td>
          </tr>
      </table>
      </form>
  </body>
</html>

package cn.itcast.web.jdbc.domain;

//帐户
public class Account {
    private int id;//帐号
    private String name;//用户名
    private float salary;//薪水
    public Account(){}
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getSalary() {
        return salary;
    }
    public void setSalary(float salary) {
        this.salary = salary;
    }
}

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.itcast.web.jdbc.domain.Account;
import cn.itcast.web.jdbc.util.JdbcUtil;

public class TransferDao {
    //根据ID号查询帐户
    public Account findAccountById(int id) throws SQLException{
        Account account = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from account where id = ?";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                account = new Account();
                account.setId(id);
                account.setName(rs.getString("name"));
                account.setSalary(rs.getFloat("salary"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            //JdbcUtil.close(conn);
        }
        return account;
    }
    //根据ID号更新帐户
    public void updateAccountById(Account newAccount) throws SQLException{
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "update account set salary = ? where id = ?";
        try {
            conn = JdbcUtil.getMySqlConnection();//conn=123
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1,newAccount.getSalary());
            pstmt.setInt(2,newAccount.getId());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            //JdbcUtil.close(conn);
        }
    }
}

package cn.itcast.web.jdbc.service;

import cn.itcast.web.jdbc.dao.TransferDao;
import cn.itcast.web.jdbc.domain.Account;
import cn.itcast.web.jdbc.exception.NoAccountException;
import cn.itcast.web.jdbc.exception.NoMoneyException;
import cn.itcast.web.jdbc.util.JdbcUtil;

public class TransferService {
    //转帐
    public void transfer(int sid,int tid,float money) throws Exception{
        //NO1：判段转入和转出帐号是否存在
         TransferDao transferDao = new TransferDao();
        Account sAccount = transferDao.findAccountById(sid);
        Account tAccount = transferDao.findAccountById(tid);
        if(sAccount!=null && tAccount!=null){
            //NO2：判段转出帐号是否有足够的余额
            if(sAccount.getSalary()-money >= 0){
                //进行转帐操作
                   sAccount.setSalary(sAccount.getSalary() - money);
                tAccount.setSalary(tAccount.getSalary() + money);
                try {
                    //事务开始
                       JdbcUtil.begin();//conn=123
                    transferDao.updateAccountById(sAccount);
                    //int i = 10/0;
                    transferDao.updateAccountById(tAccount);
                    //事务提交
                       JdbcUtil.commit();
                } catch (Exception e) {
                    e.printStackTrace();
                    try {
                        //事务回滚
                            JdbcUtil.rollback();
                        //事务提交
                            JdbcUtil.commit();
                    } catch (Exception e1) {
                    }
                    throw e;
                }finally{
                    //关闭Connection对象
                       JdbcUtil.closeConnection();
                }
            }
        }
    }
    //取款
    public void withdraw(int sid, float money)throws Exception{
        TransferDao transferDao = new TransferDao();
        Account sAccount = transferDao.findAccountById(sid);
        if(sAccount!=null){
            if(sAccount.getSalary()-money >= 0){
                sAccount.setSalary(sAccount.getSalary() - money);
                try {
                    transferDao.updateAccountById(sAccount);
                } catch (Exception e) {
                    e.printStackTrace();    
                }finally{
                    JdbcUtil.closeConnection();
                }
            }else{
                throw new NoMoneyException();
            }
        }else{
            throw new NoAccountException();
        }
    }
}

package cn.itcast.web.jdbc.web;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.itcast.web.jdbc.exception.NoAccountException;
import cn.itcast.web.jdbc.exception.NoMoneyException;
import cn.itcast.web.jdbc.service.TransferService;

public class TransferServlet extends HttpServlet {
    private void withdraw(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        try {
            int sid = Integer.parseInt(request.getParameter("sid"));
            float money = Float.parseFloat(request.getParameter("money"));
            TransferService transferService = new TransferService();
            transferService.withdraw(sid,money);
            request.setAttribute("message","交易成功，请取款");
        }catch(NoMoneyException e){
            e.printStackTrace();
            request.setAttribute("message","帐号余额不足，不能交易");
        }catch(NoAccountException e){
            e.printStackTrace();
            request.setAttribute("message","帐号输入错误，请重试");
        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("message","交易失败，请重试");
        }
        request.getRequestDispatcher("/message.jsp").forward(request,response);
    }    
    public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String method = request.getParameter("method");
        if(method!=null && method.equals("withdraw")){
            this.withdraw(request,response);
            return;
        }
        try {
            int sid = Integer.parseInt(request.getParameter("sid"));
            int tid = Integer.parseInt(request.getParameter("tid"));
            float money = Float.parseFloat(request.getParameter("money"));
            TransferService transferService = new TransferService();
            transferService.transfer(sid,tid,money);
            //转帐成功
            request.setAttribute("message","转帐成功");
        } catch (Exception e) {
            //转帐失败
            request.setAttribute("message","转帐成功");
        }
        request.getRequestDispatcher("/message.jsp").forward(request,response);
    }
}

   7 关于异常的处理
   1)关于分层结构中，处理异常的规则，参见<<关于异常的处理规则.JPG>>
   2)异常在项目中，往往替代boolean值，作为成功与否的标志

*8 连接池
   1)传统方式找DriverManager要连接，数目是有限的。 
   2)传统方式的close()，并没有将Connection重用，只是切断应用程序和数据库的桥梁，即无发送到SQL命令到数据库端执行
   3)项目中，对于Connection不说，不会直接使用DriverManager取得，而使用连接池方式。
   4)DBCP和C3P0，都是Java开源的，都必须直接或间接实现javax.sql.DataSource接口
   5)DBCP连接池需要dbcp.properties文件，同时需加入3个对应的jar包

      •commons-dbcp.jar：连接池的实现

      •commons-pool.jar：连接池实现的依赖类

      •commons-collections.jar ：连接池实现的集合类

  *6)C3P0连接池需要在/WEB-INF/classes/目录下存放c3p0-config.xml文件，该类ComboPooledDataSource在创建时
     会自动在指定的目录下找xml文件，并加载默认设置
   7)重构JdbcUtil类  

package cn.itcast.web.jdbc.datasource;

import java.sql.Connection;
import java.sql.SQLException;
import cn.itcast.web.jdbc.util.JdbcUtil3;


//测试传统方式取得连接的时间和个数
public class Demo1 {
    public static void main(String[] args) throws SQLException {
        long begin = System.currentTimeMillis();
        for(int i=1;i<=5000;i++){
            Connection conn = JdbcUtil3.getMySqlConnection();
            if(conn!=null){
                System.out.println(i+":取得连接");
            }
            JdbcUtil3.close(conn);
        }
        long end = System.currentTimeMillis();
        System.out.println("共用" + (end-begin)/1000+"秒");
    }
}

package cn.itcast.web.jdbc.datasource;

import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

//测试连接池DBCP的用法
public class Demo2 {
    public static void main(String[] args) throws Exception {
        long begin = System.currentTimeMillis();
        //加载属性文件
        InputStream is = Demo2.class.getClassLoader().getResourceAsStream("cn/itcast/web/jdbc/config/dbcp.properties");
        Properties props = new Properties();
        props.load(is);
        //创建DBCP连接池工厂
        BasicDataSourceFactory factory = new BasicDataSourceFactory(); 
        //创建数据源,即连接池
        DataSource ds = factory.createDataSource(props);
        for(int i=1;i<=50000;i++){
            //从连接池中取得一个空闲的连接对象
            Connection conn = ds.getConnection();
            if(conn!=null){
                System.out.println(i+":取得连接");
            }
            //将连接对象还回给连接池
            conn.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("共用" + (end-begin)/1000+"秒");
    }
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mydb2
username=root
password=root

package cn.itcast.web.jdbc.datasource;

import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;

//测试连接池C3P0的用法
public class Demo3 {
    public static void main(String[] args) throws Exception {
        long begin = System.currentTimeMillis();
        //创建C3P0连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        for(int i=1;i<=100000;i++){
            Connection conn = dataSource.getConnection();
            if(conn!=null){
                System.out.println(i+":取得连接");
                conn.close();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("共用" + (end-begin)/1000+"秒");
    }
}