SQL批处理与事务控制
博客专区 > sdksdk0 的博客 > 博客详情
SQL批处理与事务控制
sdksdk0 发表于2年前
SQL批处理与事务控制
  • 发表于 2年前
  • 阅读 2
  • 收藏 0
  • 点赞 0
  • 评论 0

【腾讯云】新注册用户域名抢购1元起>>>   

今天我想要分享的是关于数据库的批处理与事务的控制。批处理对于项目的实际应用有非常大的具体意义。

一、批处理部分

首先我们新建一个表:

 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);
		}
	}


  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 1
博文 105
码字总数 73456
×
sdksdk0
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: