JDBC Reads 5 ways to insert new sequence values into Oracle database

Oracle's sequence implementation is very flexible, so it also brings some usability issues, how to get the new insert record generated by the sequence value and other databases have a large difference, this article detailed the country introduced 5 to read the new insert record sequence value method.

Database Scripts for testing:

sql> CREATE TABLE T1  2      (3 ID number 4);    Table createdsql> create sequence seq_t1; Sequence created

//Public code: Getting a database connection PublicConnection getconnection ()throwsexception{Class.forName ("Oracle.jdbc.driver.OracleDriver"). newinstance (); Connection Conn= Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:dbname", "username", "password"); returnConn;}//Method One//The new sequence value is first taken with the Select Seq_t1.nextval as ID from dual. //The most recent value is then passed to the inserted statement through the variable: INSERT into T1 (ID) VALUES (?)//finally returns the sequence value from which to begin fetching. //The advantages of this method of code is simple and intuitive, the use of the most people, the disadvantage is that it requires two times of SQL interaction, poor performance.  Public intInsertdatareturnkeybygetnextval ()throwsException {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 (); intId=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); returnID;}//Method Two//Insert the 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 taken out, because it is session-isolated, as long as two SQL is guaranteed to use the same connection, it is necessary to put two SQL in the same transaction for a connection pooling application to ensure concurrency security. //In addition, if the session has not generated a sequence value, using the Seq_t1.currval syntax will cause an error. //The advantages of this method can be returned sequence after inserting the record, suitable for the data to insert business logic bad Transformation business code, the disadvantage is that it requires two times of SQL interaction, poor performance, and prone to concurrency security issues.  Public intInsertdatareturnkeybygetcurrval ()throwsException {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 (); intId=rs.getint (1);    Rs.close ();    Pstmt.close (); System.out.print ("ID:" +ID); returnID;}//Method Three//using the returning into syntax of PL/SQL, you can set the value of the output variable with the CallableStatement object Registeroutparameter. //The advantage of this method is that as long as the SQL interaction, performance is good, the disadvantage is the need to use the PL-SQL syntax, code is not intuitive, use less.  Public intInsertdatareturnkeybyplsql ()throwsException {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 (); intId=cstmt.getint (1); System.out.print ("ID:" +ID);    Cstmt.close (); returnID;}//method Four//the Getgeneratedkeys method using PreparedStatement//the second parameter of Conn.preparestatement can set the list of field names for Generatedkeys, which is a string array//Note: There is no way to use the Preparestatement (Vsql,statement.return_generated_keys) method for Oracle databases like other databases, which is used to derive data from the augmented type. //Oracle does not have a self-increment type, all using the sequence implementation, if the Statement.return_generated_keys is returned is the newly inserted record ROWID, is not the sequence value we want. //The advantage of this approach is good performance, as long as the SQL interaction, in fact, the internal is also the SQL into Oracle's returning into syntax, the disadvantage is only oracle10g support, less use.  Public intInsertdatareturnkeybygeneratedkeys ()throwsException {Connection conn=getconnection (); String Vsql= "INSERT into T1 (ID) VALUES (seq_t1.nextval)"; PreparedStatement pstmt= (PreparedStatement) conn.preparestatement (Vsql,Newstring[]{"ID"});    Pstmt.executeupdate (); ResultSet RS=Pstmt.getgeneratedkeys ();    Rs.next (); intId=rs.getint (1);    Rs.close ();    Pstmt.close (); System.out.print ("ID:" +ID); returnID;}//Method Five//similar to method three, the output parameters are set with the Oracle-specific returning into syntax, but the Oraclepreparedstatement object is used in different places. Because the standard PreparedStatement object in the JDBC specification is that the output type parameter cannot be set. //Finally, Getreturnresultset is used to fetch the newly inserted sequence value,//The advantage of this approach is that performance is best because, as long as the SQL interaction is supported by oracle9i, the disadvantage is that only Oracle JDBC-specific oraclepreparedstatement objects can be used.  Public intInsertdatareturnkeybyreturninto ()throwsException {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 (); intId=rs.getint (1);    Rs.close ();    Pstmt.close (); System.out.print ("ID:" +ID); returnID;}

The above 5 methods can realize the function, the following is a summary of the advantages and disadvantages of 5 methods, personal recommendation performance requirements of the general business adoption of the first method, performance requirements very high business adoption of the fifth method.

Method	Brief introduction	Advantages	Disadvantages
Method One	First use Seq.nextval to remove the value, and then insert it in the way of the variable	The code is simple and intuitive, and most people use it	Requires two times of SQL interaction, 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 data insertion business logic is not good to transform business code	Requires two times of SQL interaction, poor performance, and easy to create concurrency security issues
Method Three	Using the returning into syntax of the PL/SQL block, set the output parameter with the CallableStatement object to the newly inserted value	Better performance with a single SQL interaction	need to use PL/SQL syntax, code is not intuitive, using less
Method Four	Set PreparedStatement to return the field name of the new value, and then use Getgeneratedkeys to get the newly inserted value	Good performance, as long as the SQL interaction	Only oracle10g support, use less
Method Five	Returning into syntax, set output parameters with Oraclepreparedstatement object, and then use Getreturnresultset to get new values	Performance is best because, with a single SQL interaction, Oracle9i also supports	Only Oracle JDBC-specific oraclepreparedstatement objects can be used

Reprint: http://blog.csdn.net/yzsind/article/details/6918506

JDBC Reads 5 ways to insert new sequence values into Oracle database