今天我想要分享的是关于数据库的批处理与事务的控制。批处理对于项目的实际应用有非常大的具体意义。
一、批处理部分
首先我们新建一个表:
create table t3(
id int primary key auto_increment,
name varchar(100)
);
注意:auto_increment只适用于mysql中,对于oracle需要用的是创建一个序列来实现自动增长:create squences seq_t3_id start with 1 increment by 1;这里以mysql为例,对于oracle方法类似,请自行脑补。
同时请注意:JDBC的批处理不能加入select语句,否则会抛异常:
java.sql.BatchUpdateException: Can not issue SELECT viaexecuteUpdate().
atcom.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1007)
只能作用于增删改。SQL批处理是JDBC性能优化的重要武器,批处理的用法有三种。
1、混合多种语句的批量处理,可同时执行增删改:
@Test public void test1(){ String sql1="insert into t3(name) values('aa1')"; String sql2="insert into t3(name) values('aa2')"; String sql3="insert into t3(name) values('aa3')"; String sql4="insert into t3(name) values('aa4')"; String sql5="insert into t3(name) values('aa5')"; String sql6="delete from t3 where name='aa1'"; String sql7="update t3 set name='bb1' where name='aa2'"; Connection conn=null; Statement stmt=null; try { conn=JdbcUtil.getConnection(); stmt=conn.createStatement(); stmt.addBatch(sql1); stmt.addBatch(sql2); stmt.addBatch(sql3); stmt.addBatch(sql4); stmt.addBatch(sql5); stmt.addBatch(sql6); stmt.addBatch(sql7); int[] ii=stmt.executeBatch(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.release(null, stmt, conn); } }2、批量插入少量数据,例如进行数据迁移的时候
//批量插入100条记录,数据迁移项目 @Test public void test1(){ String sql="insert into t3(name) values(?)"; Connection conn=null; PreparedStatement stmt=null; try { conn=JdbcUtil.getConnection(); stmt=conn.prepareStatement(sql); for(int i=0;i<100;i++){ stmt.setString(1, "aa"+(i+1)); stmt.addBatch(); } int[] ii=stmt.executeBatch(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.release(null, stmt, conn); } }
批量插入大量数据,例如插入几万或者几十万的时候,这个时候因为数据量大,如果我们直接插入的话速度是非常非常慢的,因为这样插入的时候executeBatch();会占用内存,数据量大自然占的内存也就对了,这对我们的机器是非常不好的,所以我们要对这些数据进行一些处理。
//批量插入100000条记录,数据迁移项目 @Test public void test1(){ String sql="insert into t3(name) values(?)"; Connection conn=null; PreparedStatement stmt=null; try { conn=JdbcUtil.getConnection(); stmt=conn.prepareStatement(sql); for(int i=0;i<100000;i++){ stmt.setString(1, "aa"+(i+1)); stmt.addBatch(); //每一百次清理一次 if(i%100==0){ stmt.executeBatch(); stmt.clearBatch(); } } int[] ii=stmt.executeBatch(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.release(null, stmt, conn); } }4、同时执行带参的和无参的
public class BatchDemo3 { public void test() throws Exception { Connection conn = null; PreparedStatement stmt = null; try { conn = JdbcUtil.getConnection(); String sql = "insert into t3(name) values (?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, "ccc"); pstmt.addBatch(); // 添加一次预定义参数//添加一次静态sql pstmt.addBatch("update t3 set name = 'aa5' where name='aa4'");// 批量执行预定义 pstmt.executeBatch(); } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtil.release(null, stmt, conn); } } }
二、事务的处理部分
MySQL引擎:InnoDB(支持事务的)
MySQL默认自动提交事务的。每条语句都处在单独的事务之中。而oracle是也好手动提交事务的。
手工控制事务
开启事务:starttransaction|begin
提交事务:commit
回滚事务:rollback
对于事务,最经典的就数转账的操作ill,要么同时成功,要么同时失败,不存在中间态。首先我们可以新来建一个账号表:
create table account(id int primary key auto_increment,name varchar(40),money float )character set utf8 collate utf8_general_ci;
然后可以插入几条数据insert into account(name,money) values('a',1000);insert into account(name,money) values('b',1000);insert into account(name,money) values('c',1000);
接下来编写代码,因为事务的特性,所以我们主要是加了这两段代码:setAutoCommit(false)和commit(),当发生问题的时候就需要rollback()数据回滚。
@Test public void test1(){ Connection conn=null; PreparedStatement stmt=null; try { conn=JdbcUtil.getConnection(); //设置隔离级别 conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); conn.setAutoCommit(false); //相当于start transaction | begin 开启事务 String sql1="update account set money=money-100 where name='a'"; String sql2="update account set money=money+100 where name='c'"; stmt=conn.prepareStatement(sql1); stmt.executeUpdate(); stmt=conn.prepareStatement(sql2); stmt.executeUpdate(); conn.commit(); } catch (SQLException e) { if(conn!=null){ try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.release(null, stmt, conn); }记得在代码中需要添加两个stmt.executeUpdate()哦!
执行完之后再到数据库中查询就可以清晰的看到结果啦!
对了,我抽取的这个基类的代码是:
public class JdbcUtil { private static String driverClass; private static String url; private static String user; private static String password; static{ ResourceBundle rb = ResourceBundle.getBundle("dbinfo"); driverClass = rb.getString("driverClass"); url = rb.getString("url"); user = rb.getString("user"); password = rb.getString("password"); try { Class.forName(driverClass); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws Exception{ Connection conn = DriverManager.getConnection(url,user,password); return conn; } public static void release(ResultSet rs,Statement stmt,Connection conn){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
最后来回顾一下事务的特性吧!
l A:原子性:是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
l C:一致性:事务必须使数据库从一个一致性状态变换到另外一个一致性状态。举例:转账,a账户1000块,b账户1000块,a给b转账后加起来应该是2000块。
l I:隔离性:多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
l D:持久性:一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
三、事务的隔离
不考虑事务的隔离级别,会出现什么情况:
l 脏读(Dirty Read):一个线程中的事务读到了另外一个线程中事务中未提交的数据。
l 不可重复读(UnRepeatable Read):一个线程中的事务读到了另外一个线程中提交的事务的数据(UPDATE数据)。
l 虚读:一个线程中的事务读到了另外一个线程事务中INSERT的数据。
数据库通过设置事务的隔离级别防止以上情况的发生的:
l 1:READUNCOMMITTED:脏读、不可重复读、虚读都有可能发生。
l 2:READCOMMITTED:避免脏读,不可重复读、虚读都有可能发生。(Oracle默认的)
l 4:REPEATABLEREAD:避免脏读、不可重复读,虚读有可能发生。(MySQL默认)
l 8:SERIALIZABLE:避免脏读、不可重复读、虚读的发生。
级别越高,性能越低,数据越安全。
MySQL:(必须用在事务之中)
设置隔离级别必须在开启事务之前。
查看当前事务的隔离级别:SELECT@@TX_ISOLATION;
更改当前事务的隔离级别:SETTRANSACTION ISOLATION LEVEL 四个级别之一;
最后再提一下这个数据库中存放和读取文件和图片吧!
我们可以新建两个表:主要是文件的存放和图片存放读取操作使用的
create table t1(
id int primary key auto_increment,
content longtext
);
create table t2(
id int primary key auto_increment,
content longblob
);
// 读取文本文件,即把文件存放到数据库中 public void testWrite(){ Connection con=null; PreparedStatement pstmt=null; try{ con=JdbcUtil.getConnection(); String sql="insert into t1(content) values(?)"; pstmt=con.prepareStatement(sql); File file=new File("src/demo1.txt"); Reader reader=new FileReader(file); pstmt.setCharacterStream(1, reader, (int)file.length()); pstmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ JdbcUtil.release(null, pstmt, con); } } <span style="white-space:pre"> </span>//将文件文件从数据库中读取出来, @Test public void testReader(){ Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; try{ con=JdbcUtil.getConnection(); String sql="select *from t1 where id=1" ; pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ Reader r=rs.getCharacterStream("content"); Writer w=new FileWriter("d:/1.txt"); char buf[] =new char[1024]; int len=-1; while((len=r.read(buf))!=-1){ w.write(buf,0,len); } r.close(); w.close(); } }catch(Exception e){ e.printStackTrace(); }finally{ JdbcUtil.release(null, pstmt, con); } } //将图片信息存放到数据库中,以二进制流的方式读取写入等 @Test public void testWrite2(){ Connection con=null; PreparedStatement pstmt=null; try{ con=JdbcUtil.getConnection(); String sql="insert into t2(content) values(?)"; pstmt=con.prepareStatement(sql); InputStream in=new FileInputStream("src/1.jpg"); pstmt.setBinaryStream(1, in, in.available()); pstmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ JdbcUtil.release(null, pstmt, con); } } //将图片从数据库中读出来 @Test public void testReader2(){ Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; try{ con=JdbcUtil.getConnection(); String sql="select *from t2 where id=1" ; pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ InputStream in=rs.getBinaryStream("content"); OutputStream out=new FileOutputStream("d:/1.jpg"); byte buf[] =new byte[1024]; int len=-1; while((len=in.read(buf))!=-1){ out.write(buf,0,len); } in.close(); out.close(); } }catch(Exception e){ e.printStackTrace(); }finally{ JdbcUtil.release(null, pstmt, con); } }