JDBC(4)：批处理、存储过程和事务

批处理

需求

当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。

主要API

添加批处理

void  addBatch(String sql)  

执行批处理

int[] executeBatch()    

清空批处理

void  clearBatch()          

案例

程序有100个UserInfo对象（list集合），需要插入数据库，使用JDBC的批处理解决
表user_info结构

CREATE TABLE `user_info` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(20) DEFAULT NULL,  `password` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`))

批处理实现方式1：Statement.addBatch(sql)

@Testpublic void BatchTest1(){    String driver = "com.mysql.jdbc.Driver";    String url = "jdbc:mysql://127.0.0.1:3306/address";    String user = "root";    String pwd = "password";    Connection conn = null;    Statement statement = null;    String sql1 = "insert into user_info(username,password) values('now','123')";       String sql2 = "update user_info set password='2333' where id=3";       try {           //获取连接        Class.forName(driver);           conn = (Connection) DriverManager.getConnection(url,user, pwd);        statement = (Statement) conn.createStatement();        statement.addBatch(sql1);  //把SQL语句加入到批命令中        statement.addBatch(sql2);  //把SQL语句加入到批命令中        statement.executeBatch();       } catch (Exception e) {           e.printStackTrace();       } finally {           if (statement != null) {               try {                statement.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }           if (conn != null) {               try {                   conn.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }   }}

采用Statement.addBatch(sql)方式实现批处理：

优点：
　　可以向数据库发送多条不同的ＳＱＬ语句。
缺点：
　　1.SQL语句没有预编译。
　　2.当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。如：

insert into user_info(username,password) values('user1','123');insert into user_info(username,password) values('user2','123')；insert into user_info(username,password) values('user3','123');insert into user_info(username,password) values('user4','123')；

批处理实现方式2：PreparedStatement.addBatch()

@Testpublic void BatchTest2(){    String driver = "com.mysql.jdbc.Driver";    String url = "jdbc:mysql://127.0.0.1:3306/address";    String user = "root";    String pwd = "password";    Connection conn = null;    PreparedStatement psmt = null;    String sql = "insert into user_info(username,password) values(?,?)";       try {           //获取连接        Class.forName(driver);           conn = (Connection) DriverManager.getConnection(url,user, pwd);           psmt =  (PreparedStatement) conn.prepareStatement(sql);           for (int i = 0; i < 100; i++) {            psmt.setString(1, "user"+i);            psmt.setString(2, "pwd"+i);            psmt.addBatch();        }        psmt.executeBatch();       } catch (Exception e) {           e.printStackTrace();       } finally {           if (psmt != null) {               try {                psmt.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }           if (conn != null) {               try {                   conn.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }   }}

采用PreparedStatement.addBatch()实现批处理

优点：
　　发送的是预编译后的SQL语句，执行效率高。
缺点：
　　只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

存储过程

创建存储过程

表user_info结构：

名 类型 长度 小数点 允许空值 是否主键 id int 11 0 no 是 username varchar 20 0 yes 否 password varchar 20 0 yes 否

新建一个存储过程

my_procedure1:

CREATE PROCEDURE my_procedure1(IN inputId INT)BEGIN    SELECT * FROM user_info WHERE id=inputId;END

sql调用以及输出结果

SQL语句：CALL pro_findById(5);
输出结果：

id username password 1 user1 pwd1 2 user2 pwd2 3 user3 pwd3

java代码调用及输出结果

    @Test    public void testCall() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String pwd = "password";        Connection conn = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        String sql = "CALL my_procedure1(?)";        try {            Class.forName(driver);            conn = (Connection) DriverManager.getConnection(url,user, pwd);            preparedStatement =  (PreparedStatement) conn.prepareStatement(sql);            preparedStatement.setInt(1, 3);            resultSet = preparedStatement.executeQuery();            while( resultSet.next() ){                   String username = resultSet.getString("username");                   String password =resultSet.getString("password");                   System.out.println("username:"+username +" password:"+password);            }        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {            if (preparedStatement != null) {                try {                    preparedStatement.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }        }

输出结果：

username:user3 password:pwd3

创建带有输出参数的存储过程

有输出参数的存储过程，在使用java调用时没有ResultSet返回，是通过输出参数来获取执行结果的

新建一个有输出参数的存储过程

my_procedure2:

CREATE PROCEDURE my_procedure2(IN inputId INT, OUT  outputname VARCHAR(20))BEGIN    SELECT username into outputname FROM user_info WHERE id=inputId;END

输入参数为inputId，输出参数为outputname

sql调用以及输出结果

SQL语句（@name=outputname）：CALL my_procedure2(3,@name);
获取输出参数结果：SELECT @name;
输出结果：

@name user3

java代码调用及输出结果

    @Test    public void testCall2() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://192.168.1.20:3306/address";        String user = "root";        String pwd = "zaqwsx123";        Connection conn = null;        CallableStatement  cstmt = null;          String sql = "CALL my_procedure2(?,?)";        try {            Class.forName(driver);            conn = (Connection) DriverManager.getConnection(url,user, pwd);            cstmt =   (CallableStatement) conn.prepareCall(sql); //这里调用方法不同与testCall那个方法里面的方法，返回的Statement子类也不一样            cstmt.setInt(1, 3);            //设置输出参数(注册输出参数)            //参数一：参数位置            //参数二：存储过程中的输出参数的jdbc类型  VARCHAR(20)            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);            cstmt.executeQuery();//结果不是返回到结果集中，而是返回到输参数中            //得到输出参数的值            // 索引值：预编译sql中的输出参数的位置            String res = cstmt.getString(2);//专门获取存储过程中的输出参数            System.out.println(res);        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {            if (cstmt != null) {                try {                    cstmt.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }        }

输出结果：

user3

事务

定义

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
特性：原子性、一致性、隔离性、持久性（简称ACID）。

事务的三个方法

java.sql.Connection类中存在关于事务的三个方法：
- 关闭自动提交 Connection.setAutoCommit(false);
- 开启事务 Connection.rollback();
- 回滚事务 Connection.commit(); —提交事务

典型案例

前提

假设存在一张账户表（mysql表引擎必须为InnoDB，才支持事务）， 表建立如下SQL语句，有用户名和账号余额字段，存在用户zhangsan和lisi，余额分别为5000和3000。

CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(20) DEFAULT NULL,  `balance` double DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE= InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

id username balance 1 zhangsan 5000 2 lisi 3000

案例1

张三向李四转让了1000，此时张三和李四余额分别为4000和1000，映射到具体代码即为执行两条SQL语句：1，将用户zhangsan的余额改为4000 2.将lisi的余额改为4000。假如执行完1后突然系统异常，2没执行。这是不允许的，我们希望1也能回滚一下，即将用户zhangsan的余额重改为5000。这个时候就需要用到事务。
使用事务代码：

  @Test  public void trans1(){        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String pwd = "password";        Connection conn = null;        PreparedStatement preparedStatement = null;        String sql_zs = "update account set balance=balance-1000 where username='zhangsan'";        String sql_ls = "update account set balance=balance+1000 where username='lisi' 2333"; //人为设置SQL语句错误        try {            //获取连接            Class.forName(driver);            conn = (Connection) DriverManager.getConnection(url,user, pwd);            conn.setAutoCommit(false);  //开启事务，需手动进行提交            //------------第一次  张三-1000-----------------------            preparedStatement =  (PreparedStatement) conn.prepareStatement(sql_zs);            preparedStatement.executeUpdate();            //------------第二次  李四+1000-----------------------            preparedStatement = (PreparedStatement) conn.prepareStatement(sql_ls);            preparedStatement.executeUpdate();        } catch (Exception e) {            try {                conn.rollback(); //发生异常时，进行事务回滚操作            } catch (SQLException e1) {                e1.printStackTrace();            }             e.printStackTrace();            throw new RuntimeException(e);        } finally {              try {                conn.commit();  //最终提交事务            } catch (SQLException e1) {                e1.printStackTrace();            }            if (preparedStatement != null) {                try {                    preparedStatement.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }       }   }

以上代码中，人为设置SQL语句String sql_lisi错误。不用事务提交回滚的话将会出现zhangsan的账户余额改为4000，而lisi的账户余额没变的情况。使用事务回滚zhangsan和lisi账户余额都没变。

案例2

张三第一次向李四转让了1000，此时转让成功，操作也都执行了，张三李四余额分别为都4000。第二次李四发现张三转多了，向张三转回500，这次出错了，错误跟案例1类似。也能回滚一下，但会滚到第一次转账结束后即可，这个时候需要用到SavePoint。

 @Test  public void trans2(){        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String pwd = "password";        Connection conn = null;        PreparedStatement preparedStatement = null;        String sql_zs1 = "update account set balance=balance-1000 where username='zhangsan'";        String sql_ls1 = "update account set balance=balance+1000 where username='lisi'";        Savepoint savepoint = null;        String sql_zs2 = "update account set balance=balance+500 where username='zhangsan'";        String sql_ls2 = "update account set balance=balance-500 where username='lisi' 23333";        try {            //获取连接            Class.forName(driver);            conn = (Connection) DriverManager.getConnection(url,user, pwd);            conn.setAutoCommit(false);  //开启事务，需手动进行提交            //------------第一次  张三-1000   李四+1000  假设执行成功-----------------------            preparedStatement =  (PreparedStatement) conn.prepareStatement(sql_zs1);            preparedStatement.executeUpdate();            preparedStatement = (PreparedStatement) conn.prepareStatement(sql_ls1);            preparedStatement.executeUpdate();            //设置回滚点            savepoint = conn.setSavepoint();            //------------第二次   张三+500   李四-500 sql语句出错-----------------------            preparedStatement =  (PreparedStatement) conn.prepareStatement(sql_zs2);            preparedStatement.executeUpdate();            preparedStatement = (PreparedStatement) conn.prepareStatement(sql_ls2);            preparedStatement.executeUpdate();        } catch (Exception e) {            try {                conn.rollback(savepoint);  //发生异常时，进行事务回滚到设置好的保存点            } catch (SQLException e1) {                e1.printStackTrace();            }             e.printStackTrace();            throw new RuntimeException(e);        } finally {              try {                conn.commit();  //最终提交事务            } catch (SQLException e1) {                e1.printStackTrace();            }            if (preparedStatement != null) {                try {                    preparedStatement.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }       }   }