JDBC Reads 5 methods for sequence values of newly inserted Oracle databases

JDBC Reads 5 methods for sequence values of newly inserted Oracle databases

Oracle's sequence implementation is very flexible, so it also brings some usability problems, how to get to the new insert record sequence value generated by the other database is significantly different, this paper describes 5 ways to read the new inserted record sequence value.

Database Scripts for testing:

View plain Copy to clipboard print?       Sql> CREATE TABLE T1 2 (3 ID number 4);       Table created sql> create sequence seq_t1; Sequence created sql> CREATE TABLE T1 2 (3 ID number 4); Table created sql> create sequence seq_t1; Sequence created
View plain Copy to clipboard print? Common code: Get database Connection    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 ;  }     /Method   //First Use select seq_t1.nextval as id  from dual  fetch the new sequence value.   //Then pass the most recent value through the variable to the inserted statement: INSERT INTO T1 (ID)  values (?)     //finally returns the sequence value to begin to fetch.   //The advantages of this method is simple and intuitive, the user is the most, the disadvantage is that two times SQL interaction, poor performance.    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;  }     /Method two   //First Use insert INTO T1 (ID)  values (seq_t1.nextval) inserts the data.   //Then use select seq_t1.currval as id from dual to return the sequence value generated by the record you just inserted.   //Note: seq_ T1.currval indicates that the last generated sequence value of the current session is removed, and as long as the same connection is guaranteed for two SQL, the concurrency security can be guaranteed by placing two SQL in the same transaction for the use of connection pooling applications.   //Additionally, if the session does not generate a sequence value, using the Seq_t1.currval syntax will cause an error. The advantages of the   //method can be returned to sequence after inserting the record, which is suitable for the business code that the data inserts business logic is difficult to transform, the disadvantage is that it requires two SQL interactions, poor performance and easy to generate concurrent security problems.    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 ();     &NBsp;  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;  }     /Method Three   //returning with Pl/sql  into syntax, you can use the CallableStatement object to set the Registeroutparameter to get the value of the output variable.   //The advantage of this method is that as long as the SQL interaction, the performance is good, the disadvantage is the need to adopt PL/SQL syntax, the code is not intuitive, use less.    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;  }     // Method four   //Adopt PreparedStatement Getgeneratedkeys method   // The second parameter of Conn.preparestatement can set the field name list of Generatedkeys, the variable type is a string array   // Note: It is not possible to use the Preparestatement (Vsql,statement.return_generated_keys) method for Oracle databases like other databases, which is used to derive data from an increased type.   //oracle does not have a self added type, all using the sequence implementation, such asThe Statement.return_generated_keys returns the rowid of the newly inserted record, not the sequence value we want.   //This approach has the advantage of good performance, as long as the SQL interaction, in fact, is also the conversion of SQL to Oracle's Returning into syntax, the disadvantage is that only oracle10g support, less use.    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 ();    &Nbsp;  system.out.print ("ID:" +id);       return id;  }      //Method five   //and method three similar, using Oracle-specific Returning into syntax, set output parameters, However, the Oraclepreparedstatement object is used in different places because the standard PreparedStatement object in the JDBC specification cannot set output type parameters.   //finally use Getreturnresultset to get the newly inserted sequence value,   //The advantage of this method is the best performance, because as long as the SQL interaction, Oracle9i also support, The disadvantage is that only ORACLE JDBC-specific oraclepreparedstatement objects can be used.    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;  }  //Common code: Get database Connection 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; //Method I///first fetch the new sequence value with the Select Seq_t1.nextval as ID from dual. The most recent value is then passed through the variable to the inserted statement: INSERT INTO T1 (?)//finally returns the sequence value to begin with. The advantages of this method is simple and intuitive, the most people use, the disadvantage is that two of SQL interaction needs, poor performance. 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; //Method Two//Insert data first with INSERT INTO T1 (ID) VALUES (seq_t1.nextval). Then use the Select Seq_t1.currval as ID from dual to return the sequence value generated by the record you just inserted. Note: seq_ T1.currval indicates that the last generated sequence value of the current session is removed, and as long as the same connection is guaranteed for two SQL, the concurrency security can be guaranteed by placing two SQL in the same transaction for the use of connection pooling applications. In addition, if the session does not generate a sequence value, using the Seq_t1.currval syntax will cause an error. The advantage of this method is that it can be returned to sequence after the record is inserted, which is suitable for business code with bad transformation of data insertion business logic, the disadvantage of which is two times of SQL interaction, poor performance and easy to generate concurrent security problems. 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; }//Method three//using pl/sql returning into syntax, you can use CallableStatement object settings registeroutparameter get output variable value. The advantage of this method is that as long as the SQL interaction, the performance is good, the disadvantage is the need to adopt PL/SQL syntax, the code is not intuitive, use less. 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; }//Method four//using PreparedStatement Getgeneratedkeys method// The second parameter of Conn.preparestatement can set the list of field names for Generatedkeys, and the variable type is a string array//NOTE: The Oracle database cannot be used here like any other database PreparestaTement (Vsql,statement.return_generated_keys) method, which is used to derive data from an increased type. Oracle does not have a sequence type, all using the implementation of the Statement.return_generated_keys, if the transfer is returned by the new inserted record ROWID, not the sequence value we want. The advantage of this approach is good performance, as long as the SQL interaction, in fact, is also converting SQL into Oracle's returning into syntax, the disadvantage is that only oracle10g support, less use. 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; }//Method five//and method three similar, using Oracle-specific returning into syntax, set output parameters, but the different place is to use Oraclepreparedstatement object, The output type parameter cannot be set because the standard PreparedStatement object in the JDBC specification is not. Finally, using Getreturnresultset to fetch the newly inserted sequence value,//The advantage of this method is the best performance, because as long as the SQL interaction, Oracle9i also support, the disadvantage is only the use of Oracle JDBC-specific Oraclepreparedstatement object. 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; }

All of the above 5 methods can be implemented, the following is a summary of the advantages and disadvantages of the 5 methods, the individual recommended performance requirements of the general business use the first method, performance requirements very high business using the fifth method.

Method	Brief Introduction	Advantages	Disadvantage
Method One	First use the Seq.nextval to remove the value and then insert it into the variable	The code is simple and intuitive, and most people use it.	Requires two SQL interactions with poor performance
Method Two	Insert the record directly with Seq.nextval and then use Seq.currval to remove the newly inserted value	can return sequence after inserting a record, suitable for the business code that the data inserts the business logic is not good to transform	Requires two SQL interactions, poor performance, and easy to generate concurrent security issues
Method Three	Using the returning into syntax of the Pl/sql block, set the output parameter with the CallableStatement object to fetch the newly inserted value	Good performance with only one SQL interaction	Need to adopt PL/SQL syntax, code is not intuitive, use less
method Four	Set PreparedStatement the name of the field to return the new value, and then use Getgeneratedkeys to get the newly inserted value	Good performance, just one SQL interaction	Only oracle10g support, less use
Method Five	Returning into syntax, set output parameters with Oraclepreparedstatement object, and then use Getreturnresultset to get added value	Performance is best because, with only one SQL interaction, Oracle9i also supports	Only Oracle JDBC-specific oraclepreparedstatement objects can be used