JDBC 学习笔记（二）—— 大数据+存储过程+批处理+事务管理
本文查阅方法：
1、查阅目录 —— 查阅本文目录，确定想要查阅的目录标题
2、快捷“查找” —— 在当前浏览器页面，按键 “Ctrl+F” 按键组合，开启浏览器的查找功能，
在查找搜索框中 输入需要查阅的 目录标题，便可以直接到达 标题内容 的位置。
3、学习小结 —— 文中的学习小结内容，是笔者在学习之后总结出的，开发时可直接参考其进行应用开发的内容， 进一步加快了本文的查阅 速度。（水平有限，仅供参考。）
本文目录
学习小结
1、使用JDBC处理大数据
2、使用JDBC处理大文本
3、使用JDBC处理二进制数据
4、Oracle中大数据处理
5、使用JDBC进行批处理
6、采用Statement.addBatch(sql)方式实现批处理的优缺点
7、实现批处理的第二种方式：PreparedStatement.addBatch()
8、采用PreparedStatement.addBatch()实现批处理的优缺点
9、获得MySQL数据库自动生成的主键
10、JDBC调用存储过程
11、事务的概念
12、JDBC控制事务语句
13、事务的四大特性(ACID)
14、事务的隔离级别
15、事务的隔离性可避免问题—— 脏读：
16、事务的隔离性可避免问题—— 不可重复读
17、事务的隔离性可避免问题—— 虚读(幻读)
18、事务隔离性的设置语句
19、在MySQL客户端窗口界面演示事务的四种隔离级别。
相关学习
JDBC 学习笔记（一）—— 基础知识 + 分页技术
链接地址：http://even2012.iteye.com/blog/1886946
JDBC 学习笔记（二）—— 大数据+存储过程+批处理+事务
链接地址：http://even2012.iteye.com/blog/1886950
JDBC 学习笔记（三）—— 数据源(数据库连接池)：DBCP数据源、C3P0 数据源以及自定义数据源技术
链接地址：http://even2012.iteye.com/blog/1886953
JDBC 学习笔记（四）—— 自定义JDBC框架+Apache—DBUtils框架+事务管理+操作多表
链接地址：http://even2012.iteye.com/blog/1886956
学习小结
1、使用JDBC处理大数据
在实际开发中，程序需要把大文本或二进制数据保存到数据库。
基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：clob和blob
（a）clob用于存储大文本。（mysql 中采用Text）
（b）blob用于存储二进制数据，例如图像、声音、二进制文等。
对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，其体系中的Text和blob分别又分为：
（a）Text ——TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
（b）blob ——TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
2、使用JDBC处理大文本
(1) 保存数据—— 对于MySQL中的Text类型，可调用如下方法设置：
PreparedStatement.setCharacterStream(i, reader, length);
//注意length长度须设置，并且设置为int型
(2) 获取数据—— 对于MySQL中的Text类型，可调用如下方法获取：
(a) reader = resultSet. getCharacterStream(i);
(b) reader = resultSet.getClob(i).getCharacterStream();
(c) string s = resultSet.getString(i);
Demo样例：
public class Demo1 {
/**读写大文本
create table testclob
(
id varchar(40) primary key,
resume text
);
*/
@Test
public void insert() throws SQLException, FileNotFoundException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "insert into testclob(id,resume) values(?,?)";
st = conn.prepareStatement(sql);
st.setString(1, "1");
File file = new File("src/1.txt");
FileReader reader = new FileReader(file);
st.setCharacterStream(2, reader, (int) file.length());
int num = st.executeUpdate();
if(num>0){
System.out.println("插入成功！！");
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
@Test
public void read() throws SQLException, IOException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select id,resume from testclob where id='1'";
st = conn.prepareStatement(sql);
rs = st.executeQuery();
if(rs.next()){
//String resume = rs.getString("resume");
Reader reader = rs.getCharacterStream("resume");
FileWriter writer = new FileWriter("c:\\1.txt");
try {
int len = 0;
char buffer[] = new char[1024];
while ((len = reader.read(buffer)) > 0) {
writer.write(buffer, 0, len);
}
} finally {
if (reader != null) {
reader.close();
}
writer.close();
}
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
}
3、使用JDBC处理二进制数据
(1) 保存数据—— 对于MySQL中的BLOB类型，可调用如下方法设置：
PreparedStatement. setBinaryStream(i , inputStream, length);
(2) 获取数据—— 对MySQL中的BLOB类型，可调用如下方法获取：
(a) InputStream in = resultSet.getBinaryStream(i);
(b) InputStream in = resultSet.getBlob(i).getBinaryStream();
Demo样例：
public class Demo2 {
/*
create table testblob
(
id varchar(40) primary key,
image blob
);
*/
@Test
public void insert() throws SQLException, FileNotFoundException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "insert into testblob(id,image) values(?,?)";
st = conn.prepareStatement(sql);
st.setString(1, "1");
File file = new File("src/1.jpg");
FileInputStream in = new FileInputStream(file);
st.setBinaryStream(2, in, (int) file.length());
st.executeUpdate();
}finally{
JdbcUtils.release(conn, st, rs);
}
}
@Test
public void read() throws SQLException, IOException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select id,image from testblob where id='1'";
rs = conn.prepareStatement(sql).executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("image");
OutputStream out = new FileOutputStream("c:\\1.jpg");;
try {
int len = 0;
byte buffer[] = new byte[1024];
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
} finally {
if (in != null)
in.close();
if (out != null)
out.close();
}
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
}
4、Oracle中大数据处理
Oracle定义了一个BLOB字段用于保存二进制数据，但这个字段并不能存放真正的二进制数据，只能向这个字段存一个指针，然后把数据放到指针所指向的Oracle的LOB段中， LOB段是在数据库内部表的一部分。
因而在操作Oracle的Blob之前，必须获得指针（定位器）才能进行Blob数据的读取和写入。
如何获得表中的Blob指针呢？ 可以先使用insert语句向表中插入一个空的blob（调用oracle的函数empty_blob() ），这将创建一个blob的指针，然后再把这个empty的blob的指针查询出来，这样就可得到BLOB对象，从而读写blob数据了。
Oracle中LOB类型的处理步骤
(1) 插入空blob —— insert into test(id,image) values(?,empty_blob());
(2) 获得blob的cursor —— select image from test where id= ? for update;
Blob b = rs.getBlob(“image”);
注意: 须加for update，锁定该行，直至该行被修改完毕，保证不产生并发冲突。
(3) 利用 io，和获取到的cursor往数据库读写数据
注意：以上操作需开启事务。
备注：本文关于Oracle中LOB类型数据处理的操作仅供参考，详细内容参见 有关Oracle的博文。
5、使用JDBC进行批处理
业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。
实现批处理有两种方式，
第一种方式： Statement.addBatch(sql) （其实是将sql语句 放在了一个 list 集合中。）
第二种方式： PreparedStatement.addBatch() （其实是将sql语句 放在了一个 list 集合中。）
执行批处理SQL语句
executeBatch()方法：执行批处理命令
clearBatch()方法：清除批处理命令（实际上是清除 List集合中的SQL语句，否则会造成内存溢出。）
Demo样例：第一种方式：Statement.addBatch(sql)
@Test
public void test1() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
String sql1 = "insert into user(name,password,email,birthday) values('kkk','123','abc@sina.com','1978-08-08')";
String sql2 = "update user set password='123456' where id=3";
st = conn.createStatement();
st.addBatch(sql1); //把SQL语句加入到批命令中
st.addBatch(sql2); //把SQL语句加入到批命令中
st.executeBatch();
st.clearBatch();
} finally{
JdbcUtil.free(conn, st, rs);
}
}
6、采用Statement.addBatch(sql)方式实现批处理的优缺点
优点：可以向数据库发送多条不同的ＳＱＬ语句。
缺点：SQL语句没有预编译。当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。
例如：
Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);
7、实现批处理的第二种方式：PreparedStatement.addBatch()
Demo样例：第二种 方式
@Test
public void test2() throws SQLException{
conn = JdbcUtil.getConnection();
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
st = conn.prepareStatement(sql);
for(int i=0;i<50000;i++){
st.setString(1, "aaa" + i);
st.setString(2, "123" + i);
st.setString(3, "aaa" + i + "@sina.com");
st.setDate(4,new Date(1980, 10, 10));
st.addBatch();
if(i%1000==0){ //为防止(list集合) 内存溢出：设定每累加1000条数据就向数据库发送一次
st.executeBatch();
st.clearBatch();
}
}
st.executeBatch(); //当剩余的条数小于1000条时就不会被发送到数据库，所以此处要在发送一次。
｝
8、采用PreparedStatement.addBatch()实现批处理的优缺点
优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。
9、获得MySQL数据库自动生成的主键
示例：
Connection conn = JdbcUtil.getConnection();
String sql = "insert into user(name,password,email,birthday) values('abc','123','abc@sina.com','1978-08-08')";
//重载函数：返回生成的自动主键
PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS );
st.executeUpdate();
ResultSet rs = st.getGeneratedKeys(); //得到插入行的主键
if(rs.next())
System.out.println(rs.getObject(1));
注：此参数仅对insert操作有效。
Demo样例：
public class Demo4 {
/**
获取自动生成的主键
create table test
(
id int primary key auto_increment,
name varchar(40)
);
*/
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "insert into test(name) values('aaa')";
st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
rs = st.getGeneratedKeys();
if(rs.next()){
System.out.println(rs.getInt(1));
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
}
10、JDBC调用存储过程
编写存储过程（参看mysql文档）
本文重在JDBC对存储过程的调用，关于其知识内容，将在《Oracle数据库 知识》博文中详细介绍
存储过程Demo样例：
CREATE PROCEDURE demoSp (IN inputParam VARCHAR(255) ,INOUT inOutParam varchar(255) )
BEGIN
SELECT CONCAT ( 'ZYXW---- ', inputParam ) into inOutParam ;
END
在Java中，JDBC对存储过程的调用 (这才是本文重点)：
(1) 得到CallableStatement，并调用存储过程：
CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");
(2) 设置参数，注册返回值，得到输出
cStmt.setString(1, "abcdefg");
cStmt.registerOutParameter(2, Types.VARCHAR); //类型参数值参见JDK中：java.sql.Types
cStmt.execute();
System.out.println(cStmt.getString(2));
小常识：“存储过程”在金融证券 行业中应用的非常广泛，并且将会保密其表结构和字段，完全使用公开的存储过程来实现表数据的调用。
Demo样例：
public class Demo5 {
/**
调用存储过程
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
Connection conn = null;
CallableStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
st = conn.prepareCall("{call demoSp(?,?)}");
st.setString(1, "aaaaa");
st.registerOutParameter(2, Types.VARCHAR);
st.execute();
System.out.println(st.getString(2));
}finally{
JdbcUtils.release(conn, st, rs);
}
}
}
11、事务的概念
事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部都成功，要么全部不成功。
例如：A——B转帐，对应于如下两条sql语句
update from account set money=money+100 where name=‘b’;
update from account set money=money-100 where name=‘a’;
数据库默认事务是自动提交的，也就是发一条sql它就执行一条。如果想多条sql放在一个事务中执行，则需要使用如下语句。
数据库开启事务命令
start transaction 开启事务
Rollback 回滚事务
Commit 提交事务
Demo：
Start transaction
…
…
commit
Demo样例：
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); //start transaction; 开启事务
String sql1 = "update account set money=money-100 where name='aaa'";
String sql2 = "update account set money=money+100 where name='bbb'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
int x = 1/0; // 人为制造异常，验证事务
st = conn.prepareStatement(sql2);
st.executeUpdate();
conn.commit(); // Commit 提交事务
}finally{
JdbcUtils.release(conn, st, rs);
}
}
12、JDBC控制事务语句
当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列语句：
(1) JDBC控制事务语句
Connection.setAutoCommit(false); //相当于 start transaction
Connection.rollback(); //相当于 rollback
Connection.commit(); //相当于 commit
(2) 设置事务回滚点
Savepoint sp = conn.setSavepoint();
Conn.rollback(sp); // 会滚到指定的位置，该位置之前的操作任然有效执行，因为需要被提交
Conn.commit(); //回滚后必须要提交
Demo样例：事务回滚点——演示银行转帐案例
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
Savepoint sp = null;
try{
conn = JdbcUtils.getConnection(); //mysql repeatable read
conn.setAutoCommit(false); //start transaction;
String sql1 = "update account set money=money-100 where name='aaa'";
String sql2 = "update account set money=money+100 where name='bbb'";
String sql3 = "update account set money=money+100 where name='ccc'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
sp = conn.setSavepoint();
st = conn.prepareStatement(sql2);
st.executeUpdate();
int x = 1/0;
st = conn.prepareStatement(sql3);
st.executeUpdate();
conn.commit();
}catch (Exception e) {
e.printStackTrace();
conn.rollback(sp);
conn.commit(); //手动回滚后，一定要记得提交事务
}finally{
JdbcUtils.release(conn, st, rs);
}
}
13、事务的四大特性(ACID)
(1) 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
(2) 一致性（Consistency） 事务前后，数据的完整性必须保持一致。
(3) 隔离性（Isolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
(4) 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
14、事务的隔离级别
多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。
如果不考虑隔离性，可能会引发问题：脏读、不可重复读、虚读(幻读) 。
15、事务的隔离性可避免问题—— 脏读：
脏读： 指一个事务读取了另外一个事务未提交的数据。【针对同一个业务操作】
这是非常危险的，假设Ａ向Ｂ转帐货款100元，对应sql语句如下所示
(1) update account set money=money+100 while name=‘b’;
(2) update account set money=money-100 while name=‘a’;
当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。
16、事务的隔离性可避免问题—— 不可重复读
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。【针对同一行数据】
不可重复读和脏读的区别：脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。
例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。
很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。
17、事务的隔离性可避免问题—— 虚读(幻读)
虚读(幻读) ：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。【针对整张表】
如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。
18、事务隔离性的设置语句
数据库共定义了四种隔离级别：
(1) Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化，近似于单线程操作。）
(2) Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
(3) Read committed：可避免脏读情况发生（读已提交）。
(4) Read uncommitted：最低级别，以上情况均无法保证。(读未提交)
set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别
Demo样例：Java中设定事务的隔离级别。
public static void main(String[] args) throws SQLException, InterruptedException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
Savepoint sp = null;
try{
conn = JdbcUtils.getConnection(); //mysql repeatable read
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false); //start transaction;
String sql = "select * from account";
conn.prepareStatement(sql).executeQuery();
Thread.sleep(1000*20); // 等待另一个程序运行同样的代码以校验事务
conn.commit();
}finally{
JdbcUtils.release(conn, st, rs);
}
}
19、在MySQL客户端窗口界面掩饰事务的四种隔离级别。
(1) 演示脏读发生
a窗口
set transaction isolation level read uncommitted;
start transaction;
select * from account;
-------------发现a帐户是1000元，转到b窗口
select * from account;
-------------发现a帐户是1100元，发生了脏读（这个事务读取到了别的事务未提交的数据）
b窗口
start transaction;
update account set money=money+100 where name='aaa';
-------------事务在不提交的情况下，转到a窗口进行查询
(2) 避免脏读，并演示不可重复读问题的发生
a窗口
set transaction isolation level read committed;
start transaction;
select * from account;
-------------发现a帐户是1000元，转到b窗口
select * from account;
-------------发现a帐户是1000元，这时我们发现read committed这种级别可以避免脏读
-------------转到b窗口
select * from account;
-------------发现a帐户是1100元，这时就发生不可重复读（指这个事务读取到了别的事务提交的数据）
b窗口
start transaction;
update account set money=money+100 where name='aaa';
-------------事务在不提交的情况下，转到a窗口进行查询
commit;
-------------转到a窗口
(3)避免脏读、不可重复读，并演示虚读问题的发生
a窗口
set transaction isolation level repeatable read;
start transaction;
select * from account;
--------------------发现a帐户是1000元，并且表的总纪录数是3条,这时转到b窗口
select * from account
--------------------发现a帐户是1000元，这说明repeatable read这种级别可避免脏读
-------------------转到b窗口
select * from account
---------------------发现a帐户是1000元，这说明repeatable read这种级别还可以避免不可重复读
---------------------转到b窗口
select * from account
---------------------发现表中可能会多出一条ddd的记录，这就发生了虚读，也就是在这个事务内读取了别的事务插入的数据（幻影数据）
b窗口
start transaction;
update account set money=money+100 where name='aaa';
---------------------转到a窗口
commit;
---------------------转到a窗口
start transaction;
insert into account(name,money) values('ddd','1000');
commit;
--------------------转到a窗口
敬请评论
（1）若您觉得本文 有用处 —— 请留言评论，以坚定其他 IT童鞋 阅读本文的信心。
（2）若您觉得本文 没用处 —— 请留言评论，笔者将会改进不足，以便为大家整理更加好用的笔记。