JDBC inserts data into Oracle to obtain the primary key

Source: Internet
Author: User

JDBC inserts data into the database to obtain the primary key. Currently, I have summarized two methods:
 
1. Applicable to databases with auto-increment columns (such as SQL-server and mysql)
/**
* Insert data to obtain the primary key
* @ Param annex
* @ Return
*/
Public long save2 (Annex annex ){

Long queue id = 0;

// Obtain the connection
Connection con = ProxoolManager. getConnection ();

String insertSQL = "insert into annex values (?,?, Sysdate ,?,?,?) ";

PreparedStatement pstmt = null;
ResultSet rs = null;

Try {
Pstmt = con. prepareStatement (insertSQL, Statement. RETURN_GENERATED_KEYS );
Pstmt. setInt (1, 1 );
Pstmt. setString (2, annex. getpipeline name ());
Pstmt. setString (3, annex. get1_format ());
Pstmt. setString (4, DefineUtil. getFilePath () + File. separator + annex. getFileName () + "." + annex. get1_format ());
Pstmt. setInt (5, 1 );

// Save the data to the database
Pstmt.exe cuteUpdate ();

// Obtain the primary key
Rs = pstmt. getGeneratedKeys ();
If (rs. next ()){
System. out. println (rs. getString (1 ));
Vertex id = rs. getLong (1 );
}

} Catch (SQLException ex ){
Ex. printStackTrace ();
}

Return response ID;
}
 
If you use the method described above in the ORACLE database, the obtained ROW_ID is not the primary key value we really want.
 
2. methods suitable for Oracle databases
/**
* Insert data to obtain the primary key
* @ Param annex
* @ Return
*/
Public long save3 (Annex annex ){
Long queue id = 0;

// Obtain the connection
Connection con = ProxoolManager. getConnection ();

String insertSQL = "BEGIN insert into annex (pai_name, pai_creattime, pai_format, pai_path, ADVUSER_ID) values (?, Sysdate ,?,?,?) Returning success _id ?; END ;";

Try {
CallableStatement call = con. prepareCall (insertSQL );
Call. setString (1, annex. getpipeline name ());
Call. setString (2, annex. getmediaformat ());
Call. setString (3, DefineUtil. getFilePath () + File. separator + annex. getFileName () + "." + annex. getAnnexFormat ());
Call. setInt (4, 1 );

Call. registerOutParameter (5, OracleTypes. NUMBER );

Call.exe cute ();

Consumer id = call. getInt (5 );
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}

Return response ID;
}
 
CallableStatement is mainly used to execute a stored procedure and obtain the output value of the stored procedure.
Since it is a stored procedure, begin end must not be missing in SQL statements (must not be less)
Call. registerOutParameter (5, OracleTypes. NUMBER); set the output index position and output type

Author: "wuzhengxuan"
 

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.