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)

@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)方式实现批处理:

优点:
  可以向数据库发送多条不同的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结构:

类型长度小数点允许空值是否主键
idint110no
usernamevarchar200yes
passwordvarchar200yes

新建一个存储过程

my_procedure1:

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

sql调用以及输出结果

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

idusernamepassword
1user1pwd1
2user2pwd2
3user3pwd3

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;
idusernamebalance
1zhangsan5000
2lisi3000

案例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);
                }
            }
       }
   }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值