批处理

假设这样一个场景，张三给李四转账：

//我们先在Navicat中写出这样一段代码，来实现转账操作
create table account(
  id char(36) primary key,
  card_id varchar(20) unique,
  name varchar(8) not null,
  money float(10,2) default 0
);
insert into account (id,card_id,name,money) values('01','1234567890','张三',10000);
insert into account (id,card_id,name,money) values('02','0987654321','李四',0);
update account set money = money-100 where id='01';
update account set money = money+100 where id='02';
select * from account;
//结果正常，转账成功！
但是如果语句 update account set money = money+100 where id='02';出现了问题，那么张三的100元就会不翼而飞；

那么怎么解决这个问题呢？
我们通过 autocommit,rollback来解决：
set autocommit = 0; //在这里令autocommic为0即false来取消自动提交；
insert into account (id,card_id,name,money) values('01','1234567890','张三',10000);
insert into account (id,card_id,name,money) values('02','0987654321','李四',0);
update account set money = money-100 where id='01';
update account set money = money+100 where d='02';
commit;//如果无误则在这里手动提交；
rollback;//如果出现错误则回滚；
select * from account;

那么我们如何在java中实现这样的操作呢？

package com.zzu.client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

	public static void main(String[] args) {
			Connection connection = null;
			Statement statement = null;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				String url = "jdbc:mysql://localhost/test";
				connection = DriverManager.getConnection(url,"root","root");
				connection.setAutoCommit(false);
				statement = connection.createStatement();
				statement.addBatch("update account set money = money-100 where id='01'");
				statement.addBatch("update account set money = money+100 where id='02'");
				statement.executeBatch();
				connection.commit();
				System.out.println("OK");
			} catch (Exception e) {
				try {
					if(connection!=null) {
						connection.rollback();
					}
					
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			}finally {
				
					  try {
						if(statement!=null) {
						       statement.close();
						   }
						  if(connection!=null) {
						       connection.close();
						 }
					} catch (SQLException e) {
						e.printStackTrace();
					}
					     
				
			}
			
		}
		
}

为了以后方便直接调用，我们可以将它写成一个方法放在DBLink工具类中：

public boolean batch(String ...sqls) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection=getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String sql : sqls) {
				statement.addBatch(sql);
			}
			statement.executeBatch();
			connection.commit();
			return true;
		} catch (Exception e) {
			try {
				if(connection!=null) {
					connection.rollback();
				}
				
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			{
				close(statement,connection);
			}
		}
		return false;
	}
----------------------------------------------------------------------------------------
package com.zzu.client;

import com.zzu.tool.db.DBLink;

public class Test {

	public static void main(String[] args) {
		DBLink db = new DBLink();
		System.out.println(db.batch("update account set money=money-100 where id='01'","update account set money=money+100 where id='02'"));
	}
}