JDBC讀取新插入Oracle資料庫Sequence值的5種方法

JDBC讀取新插入Oracle資料庫Sequence值的5種方法

Oracle的sequence實作非常靈活，是以也帶來一些易用性問題，如何取到新插入記錄生成的sequence值與其它資料庫有較大差别，本文詳國介紹了5種實作讀取新插入記錄sequence值的方法。

測試用的資料庫腳本：

SQL> create table T1 2 ( 3 ID NUMBER 4 ); Table created SQL> create sequence SEQ_T1; Sequence created

//公共代碼：得到資料庫連接配接 public Connection getConnection() throws Exception{ Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:dbname", "username", "password"); return conn; } //方法一 //先用select seq_t1.nextval as id from dual 取到新的sequence值。 //然後将最新的值通過變量傳遞給插入的語句：insert into t1(id) values(?) //最後傳回開始取到的sequence值。 //這種方法的優點代碼簡單直覺，使用的人也最多，缺點是需要兩次sql互動，性能不佳。 public int insertDataReturnKeyByGetNextVal() throws Exception { Connection conn = getConnection(); String vsql = "select seq_t1.nextval as id from dual"; PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql); ResultSet rs=pstmt.executeQuery(); rs.next(); int id=rs.getInt(1); rs.close(); pstmt.close(); vsql="insert into t1(id) values(?)"; pstmt =(PreparedStatement)conn.prepareStatement(vsql); pstmt.setInt(1, id); pstmt.executeUpdate(); System.out.print("id:"+id); return id; } //方法二 //先用insert into t1(id) values(seq_t1.nextval)插入資料。 //然後使用select seq_t1.currval as id from dual傳回剛才插入的記錄生成的sequence值。 //注：seq_t1.currval表示取出目前會話的最後生成的sequence值，由于是用會話隔離，隻要保證兩個SQL使用同一個Connection即可，對于采用連接配接池應用需要将兩個SQL放在同一個事務内才可保證并發安全。 //另外如果會話沒有生成過sequence值，使用seq_t1.currval文法會報錯。 //這種方法的優點可以在插入記錄後傳回sequence，适合于資料插入業務邏輯不好改造的業務代碼，缺點是需要兩次sql互動，性能不佳，并且容易産生并發安全問題。 public int insertDataReturnKeyByGetCurrVal() throws Exception { Connection conn = getConnection(); String vsql = "insert into t1(id) values(seq_t1.nextval)"; PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql); pstmt.executeUpdate(); pstmt.close(); vsql="select seq_t1.currval as id from dual"; pstmt =(PreparedStatement)conn.prepareStatement(vsql); ResultSet rs=pstmt.executeQuery(); rs.next(); int id=rs.getInt(1); rs.close(); pstmt.close(); System.out.print("id:"+id); return id; } //方法三 //采用pl/sql的returning into文法，可以用CallableStatement對象設定registerOutParameter取得輸出變量的值。 //這種方法的優點是隻要一次sql互動，性能較好，缺點是需要采用pl/sql文法，代碼不直覺，使用較少。 public int insertDataReturnKeyByPlsql() throws Exception { Connection conn = getConnection(); String vsql = "begin insert into t1(id) values(seq_t1.nextval) returning id into :1;end;"; CallableStatement cstmt =(CallableStatement)conn.prepareCall ( vsql); cstmt.registerOutParameter(1, Types.BIGINT); cstmt.execute(); int id=cstmt.getInt(1); System.out.print("id:"+id); cstmt.close(); return id; } //方法四 //采用PreparedStatement的getGeneratedKeys方法 //conn.prepareStatement的第二個參數可以設定GeneratedKeys的字段名清單，變量類型是一個字元串數組 //注：對Oracle資料庫這裡不能像其它資料庫那樣用prepareStatement(vsql,Statement.RETURN_GENERATED_KEYS)方法，這種文法是用來取自增類型的資料。 //Oracle沒有自增類型，全部采用的是sequence實作，如果傳Statement.RETURN_GENERATED_KEYS則傳回的是新插入記錄的ROWID，并不是我們相要的sequence值。 //這種方法的優點是性能良好，隻要一次sql互動，實際上内部也是将sql轉換成oracle的returning into的文法，缺點是隻有Oracle10g才支援，使用較少。 public int insertDataReturnKeyByGeneratedKeys() throws Exception { Connection conn = getConnection(); String vsql = "insert into t1(id) values(seq_t1.nextval)"; PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql,new String[]{"ID"}); pstmt.executeUpdate(); ResultSet rs=pstmt.getGeneratedKeys(); rs.next(); int id=rs.getInt(1); rs.close(); pstmt.close(); System.out.print("id:"+id); return id; } //方法五 //和方法三類似，采用oracle特有的returning into文法，設定輸出參數，但是不同的地方是采用OraclePreparedStatement對象，因為jdbc規範裡标準的PreparedStatement對象是不能設定輸出類型參數。 //最後用getReturnResultSet取到新插入的sequence值， //這種方法的優點是性能最好，因為隻要一次sql互動，oracle9i也支援，缺點是隻能使用Oracle jdbc特有的OraclePreparedStatement對象。 public int insertDataReturnKeyByReturnInto() throws Exception { Connection conn = getConnection(); String vsql = "insert into t1(id) values(seq_t1.nextval) returning id into :1"; OraclePreparedStatement pstmt =(OraclePreparedStatement)conn.prepareStatement(vsql); pstmt.registerReturnParameter(1, Types.BIGINT); pstmt.executeUpdate(); ResultSet rs=pstmt.getReturnResultSet(); rs.next(); int id=rs.getInt(1); rs.close(); pstmt.close(); System.out.print("id:"+id); return id; }

以上5種方法都可以實作功能，以下是5種方法的優缺點彙總，個人推薦性能要求一般的業務采用第一種方法，性能要求非常高業務采用第五種方法。

方法	簡介	優點	缺點
方法一	先用seq.nextval取出值，然後用轉入變量的方式插入	代碼簡單直覺，使用的人也最多	需要兩次sql互動，性能不佳
方法二	先用seq.nextval直接插入記錄，再用seq.currval取出新插入的值	可以在插入記錄後傳回sequence，适合于資料插入業務邏輯不好改造的業務代碼	需要兩次sql互動，性能不佳，并且容易産生并發安全問題
方法三	用pl/sql塊的returning into文法，用CallableStatement對象設定輸出參數取到新插入的值	隻要一次sql互動，性能較好	需要采用pl/sql文法，代碼不直覺，使用較少
方法四	設定PreparedStatement需要傳回新值的字段名，然後用getGeneratedKeys取得新插入的值	性能良好，隻要一次sql互動	隻有Oracle10g才支援，使用較少
方法五	returning into文法，用OraclePreparedStatement對象設定輸出參數，再用getReturnResultSet取得新增入的值	性能最好，因為隻要一次sql互動，oracle9i也支援	隻能使用Oracle jdbc特有的OraclePreparedStatement對象

作者：葉正盛

日期：2011-10-30

我的新浪微網誌：http://weibo.com/yzsind