JDBC 学习笔记（二）—— 大数据+存储过程+批处理+事务管理

浏览数：43 / 时间：2015年06月12日

本文目录:

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客户端窗口界面演示事务的四种隔离级别

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

Demo样例：

 1 public class Demo1 { 
 2   /**读写大文本 
 3    create table testclob
 4    (
 5          id varchar(40) primary key,
 6          resume text
 7    ); 
 8 
 9    */ 
10 
11   @Test
12   public void insert() throws SQLException, FileNotFoundException{ 
13         Connection conn = null;
14         PreparedStatement st = null;
15         ResultSet rs = null;
16         try{               
17               conn = JdbcUtils.getConnection();
18               String sql = "insert into testclob(id,resume) values(?,?)";
19               st = conn.prepareStatement(sql);
20               st.setString(1, "1");
21               File file = new File("src/1.txt");
22               FileReader reader = new FileReader(file);
23               st.setCharacterStream(2, reader, (int) file.length());
24               int num = st.executeUpdate();
25               if(num>0){
26                         System.out.println("插入成功！！");
27               }
28         }finally{
29               JdbcUtils.release(conn, st, rs);
30         }
31   }

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样例：

 1 public class Demo2 {
 2   /*
 3    create table testblob
 4    (
 5      id varchar(40) primary key,
 6      image blob
 7    ); 
 8    */  
 9   @Test
10   public void insert() throws SQLException, FileNotFoundException{
11     Connection conn = null;
12     PreparedStatement st = null;
13     ResultSet rs = null;
14     try{
15       conn = JdbcUtils.getConnection();
16       String sql = "insert into testblob(id,image) values(?,?)";
17       st = conn.prepareStatement(sql);
18       st.setString(1, "1");
19       File file = new File("src/1.jpg");
20       FileInputStream in = new FileInputStream(file);
21       st.setBinaryStream(2, in, (int) file.length());
22       st.executeUpdate();
23     }finally{
24       JdbcUtils.release(conn, st, rs);
25     }
26   }
27   @Test
28   public void read() throws SQLException, IOException{
29     Connection conn = null;
30     PreparedStatement st = null;
31     ResultSet rs = null;
32     try{
33       conn = JdbcUtils.getConnection();
34       String sql = "select id,image from testblob where id=‘1‘";
35       rs = conn.prepareStatement(sql).executeQuery();
36       if(rs.next()){
37         InputStream in = rs.getBinaryStream("image");
38         OutputStream out = new FileOutputStream("c:\\1.jpg");;
39         try {
40           int len = 0;
41           byte buffer[] = new byte[1024];
42           while ((len = in.read(buffer)) > 0) {
43             out.write(buffer, 0, len);
44           }
45         } finally {
46           if (in != null)
47             in.close();
48           if (out != null)
49             out.close();
50         }
51       }
52     }finally{
53       JdbcUtils.release(conn, st, rs);
54     }
55   }
56 }

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)

 1  @Test
 2  public void test1() throws SQLException{
 3             Connection conn = null;
 4             Statement st = null;
 5             ResultSet rs = null;
 6             try {
 7                     conn = JdbcUtil.getConnection();
 8                     String sql1 = "insert into user(name,password,email,birthday)  values(‘kkk‘,‘123‘,‘[email protected]‘,‘1978-08-08‘)";
 9                     String sql2 = "update user set password=‘123456‘ where id=3";
10                     st = conn.createStatement();
11                     st.addBatch(sql1);  //把SQL语句加入到批命令中
12                     st.addBatch(sql2);  //把SQL语句加入到批命令中
13                     st.executeBatch();
14                     st.clearBatch();
15             } finally{
16                  JdbcUtil.free(conn, st, rs);
17             }
18  }

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样例：第二种方式

 1  @Test
 2  public void test2() throws SQLException{
 3         conn = JdbcUtil.getConnection();
 4         String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
 5         st = conn.prepareStatement(sql);
 6         for(int i=0;i<50000;i++){
 7                 st.setString(1, "aaa" + i);
 8                 st.setString(2, "123" + i);
 9                 st.setString(3, "aaa" + i + "@sina.com");
10                 st.setDate(4,new Date(1980, 10, 10));
11                 st.addBatch();
12                 if(i%1000==0){ //为防止(list集合)内存溢出：设定每累加1000条数据就向数据库发送一次
13                         st.executeBatch();
14                         st.clearBatch();
15                 }
16         }
17         st.executeBatch(); //当剩余的条数小于1000条时就不会被发送到数据库，所以此处要在发送一次。
18 ｝

8、采用PreparedStatement.addBatch()实现批处理的优缺点

优点：发送的是预编译后的SQL语句，执行效率高。

缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

9、获得MySQL数据库自动生成的主键

示例：

Connection conn = JdbcUtil.getConnection();

String sql = "insert into user(name,password,email,birthday) values(‘abc‘,‘123‘,‘[email protected]‘,‘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样例：

 1 public class Demo4 { 
 2   /**
 3    获取自动生成的主键
 4    create table test
 5    (
 6          id int primary key auto_increment,
 7          name varchar(40)
 8    ); 
 9    */
10 
11   public static void main(String[] args) throws SQLException { 
12         Connection conn = null;
13         PreparedStatement st = null;
14         ResultSet rs = null; 
15         try{
16               conn = JdbcUtils.getConnection();
17               String sql = "insert into test(name) values(‘aaa‘)";
18               st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
19               st.executeUpdate();
20               rs = st.getGeneratedKeys();
21               if(rs.next()){
22                     System.out.println(rs.getInt(1));
23               } 
24         }finally{
25               JdbcUtils.release(conn, st, rs);
26         }
27   } 
28 }

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样例：

 1 public class Demo5 { 
 2               /**
 3                调用存储过程
 4                * @throws SQLException 
 5                */
 6               public static void main(String[] args) throws SQLException {
 7                     Connection conn = null;
 8                     CallableStatement  st = null;
 9                     ResultSet rs = null;
10                     try{
11                           conn = JdbcUtils.getConnection();
12                           st = conn.prepareCall("{call demoSp(?,?)}");
13                           st.setString(1, "aaaaa");
14                           st.registerOutParameter(2, Types.VARCHAR);
15                           st.execute();
16                           System.out.println(st.getString(2));
17                     }finally{
18                           JdbcUtils.release(conn, st, rs);
19                     }
20               } 
21     }

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样例：

 1 public static void main(String[] args) throws SQLException { 
 2         Connection conn = null;
 3         PreparedStatement st = null;
 4         ResultSet rs = null;
 5         try{
 6               conn = JdbcUtils.getConnection();
 7               conn.setAutoCommit(false);    //start transaction;  开启事务
 8               String sql1 = "update account set money=money-100 where name=‘aaa‘";
 9               String sql2 = "update account set money=money+100 where name=‘bbb‘";
10               st = conn.prepareStatement(sql1);
11               st.executeUpdate();
12               int x = 1/0;    // 人为制造异常，验证事务
13               st = conn.prepareStatement(sql2);
14               st.executeUpdate();
15               conn.commit();    // Commit提交事务
16         }finally{
17               JdbcUtils.release(conn, st, rs);
18         } 
19   }