Five methods for reading new Sequence values in Oracle Database

 

Oracle's sequence implementation is very flexible, so it also brings about some usability issues. How to obtain the sequence value generated by the new inserted records is significantly different from other databases, this document introduces five methods for reading the sequence Value of the newly inserted record.

Database scripts used for testing:

 

 

SQL> create table T1

2 (

3 ID NUMBER

4 );

 

Table created

SQL> create sequence SEQ_T1;

 

Sequence created

// Public code: Get the 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 1

// Use select seq_t1.nextval as id from dual to get the new sequence value.

// Then pass the latest value to the inserted statement through the variable: insert into t1 (id) values (?)

// Finally, return the sequence value.

// The advantage of this method is that the code is simple and intuitive, and the majority of users are used. The disadvantage is that two SQL interactions are required and the performance is poor.

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 rs1_pstmt.exe cuteQuery ();

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.exe cuteUpdate ();

System. out. print ("id:" + id );

Return id;

}

 

// Method 2

// Insert into t1 (id) values (seq_t1.nextval) to insert data first.

// Then use select seq_t1.currval as id from dual to return the sequence value generated by the inserted record.

// Note: seq_t1.currval indicates that the sequence value generated at the end of the current session is extracted. Because session isolation is used, you only need to ensure that the two SQL statements use the same Connection, for connection pool applications, two SQL statements must be placed in the same transaction to ensure concurrency security.

// If the session does not generate a sequence value, an error is returned using the seq_t1.currval syntax.

// The advantage of this method can be returned after a record is inserted. This method is suitable for Business Code that cannot be modified due to data insertion. The disadvantage is that it requires two SQL interactions and poor performance, it is prone to concurrent security issues.

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.exe cuteUpdate ();

Pstmt. close ();

Vsql = "select seq_t1.currval as id from dual ";

Pstmt = (PreparedStatement) conn. prepareStatement (vsql );

ResultSet rs1_pstmt.exe cuteQuery ();

Rs. next ();

Int id = rs. getInt (1 );

Rs. close ();

Pstmt. close ();

System. out. print ("id:" + id );

Return id;

}

 

// Method 3

// Use the returning into syntax of pl/SQL. You can use the CallableStatement object to set registerOutParameter to get the value of the output variable.

// The advantage of this method is that as long as one SQL interaction is performed, the performance is good. The disadvantage is that pl/SQL syntax is used, and the code is not intuitive and rarely used.

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.exe cute ();

Int id = cstmt. getInt (1 );

System. out. print ("id:" + id );

Cstmt. close ();

Return id;

}

 

// Method 4

// Use the getGeneratedKeys method of PreparedStatement

// The second parameter of conn. prepareStatement can be used to set the field Name List of GeneratedKeys. The variable type is a string array.

// Note: For Oracle databases, the prepareStatement (vsql, Statement. RETURN_GENERATED_KEYS) method cannot be used as for other databases. This syntax is used to retrieve incremental data.

// Oracle does not have an auto-increment type, but all uses sequence. If Statement. RETURN_GENERATED_KEYS is passed, the ROWID of the newly inserted record is returned, which is not the sequence value we need.

// The advantage of this method is that the performance is good. As long as an SQL interaction is performed, the SQL statement is actually converted to the returning into syntax of oracle internally. The disadvantage is that only Oracle10g is supported and less used.

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.exe cuteUpdate ();

ResultSet rs = pstmt. getGeneratedKeys ();

Rs. next ();

Int id = rs. getInt (1 );

Rs. close ();

Pstmt. close ();

System. out. print ("id:" + id );

Return id;

}

 

// Method 5

// Similar to method 3, the output parameter is set using the returning into syntax unique to oracle, but the difference is that the OraclePreparedStatement object is used, the standard PreparedStatement object in the jdbc specification cannot set output type parameters.

// Use getReturnResultSet to get the newly inserted sequence Value,

// The advantage of this method is the best performance, because oracle9i supports only the OraclePreparedStatement object unique to Oracle jdbc once an SQL interaction is performed.

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.exe cuteUpdate ();

ResultSet rs = pstmt. getReturnResultSet ();

Rs. next ();

Int id = rs. getInt (1 );

Rs. close ();

Pstmt. close ();

System. out. print ("id:" + id );

Return id;

}

 

 

 

The above five methods can implement functions. The following is a summary of the advantages and disadvantages of the five methods. For personal recommendation, the first method is required for general businesses, and the fifth method is used for businesses with high performance requirements.

 

Method	Introduction	Advantages	Disadvantages
Method 1	Use seq. nextval to retrieve the value and insert it as a variable.	The code is simple and intuitive, with the most users	Requires two SQL interactions, with poor performance
Method 2	Use seq. nextval to insert records directly, and then use seq. currval to retrieve the newly inserted values.	The sequence can be returned after a record is inserted, which is suitable for business code that is difficult to transform the business logic of data insertion.	Requires two SQL interactions, poor performance, and prone to concurrency security issues
Method 3	Use the returning into syntax of pl/SQL blocks, and use the CallableStatement object to set the output parameter to get the newly inserted value.	Good performance when only one SQL interaction is performed	Pl/SQL syntax is required, and the code is not intuitive and rarely used.
Method 4	To set PreparedStatement, you need to return the field name of the new value, and then use getGeneratedKeys to get the newly inserted value.	Good performance, as long as one SQL Interaction	It is only supported by Oracle10g and is rarely used.
Method 5	Returning into syntax, use OraclePreparedStatement object to set output parameters, and use getReturnResultSet to get new values	The best performance, because oracle9i supports only one SQL interaction.	Only OraclePreparedStatement objects specific to Oracle jdbc can be used.

   

Author: ye Zhengsheng

Date: 2011-10-30

My Sina Weibo: weibo.com/yzsind