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

最新推荐文章于 2019-03-02 08:21:00 发布

nowseeme

最新推荐文章于 2019-03-02 08:21:00 发布

阅读量996

点赞数

分类专栏： jdbc 文章标签：批处理 JDBC-+

本文链接：https://blog.csdn.net/NowUSeeMe/article/details/54917517

版权

jdbc 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

批处理

需求

当需要向数据库发送一批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)

@Test
public 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()

@Test
public 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	no	是
username	varchar	20	yes	否
password	varchar	20	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);
                }
            }
       }
   }