Practical Technology of Oracle11g-how to obtain rowId when inserting data into an Oracle database

Practical Technology of Oracle11g -- how to obtain rowId when inserting data into an Oracle database
Oracle11g has many new features, I believe you have learned from many channels (Note: still not clear please visit http://wmdata.com.cn/oracle/11g/index.asp? Froms = blog), here, I will focus on how to get RowId when inserting data in Oracle11g, and publish it to discover the secret.

In some application scenarios, we need to return rowId when inserting data into the database. Oracle has a return statement. The syntax is as follows:

Insert into <table_name>
(Column_list)
VALUES
(Values_list)
RETURNING <value_name>
INTO <variable_name>;

But how can we get rowId after inserting data?
In JDBC, you can use the Callback statement to execute Procedure. Therefore, we can generate the Callback statement from the connection object and execute the SQL script that inserts the command to get the return value from the object. The key is how to write this insert statement? And how to call the statement and obtain the return value.
 
The following is my test code.

Create Test Database

Create a table named FI_T_USER, which contains 2 fields. The first is the primary key USER_ID and the other is USER_NAME. The creation statement is as follows:

Create table FI_T_USER (
USER_ID varchar2 (20) primary key,
USER_NAME varchar2 (100)
);

Write test code

The following is my test code:

 

/*
* File name: TestInsertReturnRowId. java
*
* Version: v1.0
*
*
*/
Package test.com. sinosoft. database;

Import java. SQL .*;

Import oracle. jdbc. OracleTypes;

Import org. apache. commons. lang. StringUtils;
Import org. apache. commons. logging. Log;
Import org. apache. commons. logging. LogFactory;

Import com. sinosoft. database. DBConnectionPool;
Import com. sinosoft. database. SqlQueryUtils;
Import com. sinosoft. exception. SDBException;

/**
*
*
* Test JDBC call, insert data to Oracle, and return the corresponding ROWID
*/
Public class TestInsertReturnRowId {
Private static final Log log = LogFactory
. GetLog (TestInsertReturnRowId. class );

Public static void main (String [] args ){
TestInsertReturnRowId tester = new TestInsertReturnRowId ();
String rowId = tester. insertUser ("Stephen", "liwp ");
System. out. println ("The rowId is:" + rowId );
}

Public String insertUser (String userId, String userName ){
If (StringUtils. isEmpty (userId) | StringUtils. isEmpty (userName )){
Log. error ("Please specify the userId and userName ");
Return null;
}
// Check whether the user has already in the database
String querySQL = "select count (1) as cnt from FI_T_USER where USER_ID = '"
+ UserId + "'";
// Insert statement
String insertSQL = "begin insert into FI_T_USER (USER_ID, USER_NAME) values (?,?) Return rowid ?; End ;";
Connection con = DBConnectionPool. getConnection ("test ");
If (con = null ){
Log. error ("Error on get the connection! ");
Return null;
}
Try {
Int rowCount = SqlQueryUtils. getIntValue (querySQL, con );
If (rowCount! = 0 ){
Log. error ("User with userId =" + userId + "already exists! ");
Return null;
}
// Insert the data to the database
CallableStatement cs = con. prepareCall (insertSQL );
Cs. setString (1, userId );
Cs. setString (2, userName );
Cs. registerOutParameter (3, OracleTypes. VARCHAR );
Cs.exe cute ();
String rowId = cs. getString (3 );
Return rowId;
} Catch (SQLException e ){
E. printStackTrace ();
} Catch (SDBException e ){
E. printStackTrace ();
} Finally {
If (con! = Null ){
Try {
Con. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
Return null;
}
}

The important code here is to insert the SQL script:

String insertSQL = "begin insert into FI_T_USER (USER_ID, USER_NAME) values (?,?) Return rowid ?; End ;";

The next key is to register the output parameter and obtain this parameter after the statement is executed.

These codes are very useful. They can be used not only on Oracle11g, but also on Oracle10 and Oracle 9.2.

Okay, as described at the beginning of this article, the secret I found for Oracle11g is:
Oracle11g can compress data when exporting and backing up data, and the efficiency is amazing. According to the Oracle11g White Paper, the compression ratio can reach 74.67%. This article mainly introduces the Practical Technique in Oracle11g-Get RowId when inserting data. As for compression, I will have the opportunity to write it again next time.