idam oracle_当向Oracle数据库中插入数据时，如何获取行ID

In my request, I need to get the rowid after insert the data to the oracle database. Oracle has an insert with returning clause, the gramer is:

INSERT

INTO

<

table_name

>

(column_list)

VALUES

(values_list)

RETURNING

<

value_name

>

INTO

<

variable_name

>

;

How to get the rowid when insert the data to the database?

In JDBC, can use the CallbackStatement to run the Procedure, so we can generate the CallbackStatement from Connection object, and execute the insert sql, then get the return code from the statement Object. The key point is how to write the insert statement? and how to call the statement and how to get the return code. The following is my test code.

Create the test database

create an table FI_T_USER, which contain two columns, the first column is the primary key USER_ID, and the second column is USER_NAME. The create statement is as following:

create

table

FI_T_USER(

USER_ID

varchar2

(

20

)

primary

key

,

USER_NAME

varchar2

(

100

)

);

Write the test code

The following is my test code:

/**/

/* * File name: TestInsertReturnRowId.java

 * 

 * Version: v1.0

 * 

 * Created on Aug 20, 2008 8:56:07 AM

 * 

 * Designed by Stephen

 * 

 * (c)Copyright 2008

*/

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;

/** */

/** *@authorStephen

 * 

 * 测试调用JDBC，往Oracle中插入数据，返回对应的ROWID

*/

public

class

TestInsertReturnRowId

{

privatestaticfinalLog log=LogFactory

            .getLog(TestInsertReturnRowId.class);

publicstaticvoidmain(String[] args){

        TestInsertReturnRowId tester=newTestInsertReturnRowId();

        String rowId=tester.insertUser("Stephen","liwp");

        System.out.println("The rowId is:"+rowId);

    }

publicString insertUser(String userId, String userName){

if(StringUtils.isEmpty(userId)||StringUtils.isEmpty(userName)){

            log.error("Please specify the userId and userName");

returnnull;

        }//check whether the user has already in the databaseString querySQL="select count(1) as cnt from FI_T_USER where USER_ID = '"+userId+"'";

//insert statementString insertSQL="begin insert into FI_T_USER(USER_ID, USER_NAME) values(?,?) return rowid into ?;end;";

        Connection con=DBConnectionPool.getConnection("test");

if(con==null){

            log.error("Error on get the connection!");

returnnull;

        }

try{

introwCount=SqlQueryUtils.getIntValue(querySQL, con);

if(rowCount!=0){

                log.error("User with userId ="+userId+"already exists!");

returnnull;

            }//insert the data to the databaseCallableStatement cs=con.prepareCall(insertSQL);

            cs.setString(1, userId);

            cs.setString(2, userName);

            cs.registerOutParameter(3, OracleTypes.VARCHAR);

            cs.execute();

            String rowId=cs.getString(3);

returnrowId;

        }catch(SQLException e){

            e.printStackTrace();

        }catch(SDBException e){

            e.printStackTrace();

        }finally{

if(con!=null){

try{

                    con.close();

                }catch(SQLException e){

                    e.printStackTrace();

                }            }        }returnnull;

    }}

The important code is specify the insertSQL, which is:

String insertSQL

=

"

begin insert into FI_T_USER(USER_ID, USER_NAME) values(?,?) return rowid into ?;end;

"

;

Then the key point is register the out parameter, and get the out parameter after execute the statement.

It's very useful, in some web site said that the this statement can only run start on Oracle 10, but in my tester database is Oracle 9.2, it can run the statement and get the return value.