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

Source: Internet
Author: User
Tags connection pooling

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.