JDBC读取新插入Oracle数据库Sequence值的5种方法

Oracle的sequence实现非常灵活，所以也带来一些易用性问题，如何取到新插入记录生成的sequence值与其它数据库有较大差别，本文详国介绍了5种实现读取新插入记录sequence值的方法。

测试用的数据库脚本：

Default

1 2 3 4 5 6 7 8 9

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

Default

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105

//公共代码：得到数据库连接        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对象