JDBC getgeneratedKeys

Besides the getgeneratedKeys method, the returning clause of insert method can also be accessed using JDBC.
For reference I'm providing the following code :
/******************************************/

CREATE TABLE TTTTT
(
id NUMBER,
description VARCHAR2(50)
)

ALTER TABLE TTTTT ADD (
PRIMARY KEY (id));

<Create a trigger for generating PK>

CREATE OR REPLACE TRIGGER tttttt
BEFORE INSERT
ON TTTTT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;

BEGIN
tmpVar := 0;

SELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
:NEW.id:= tmpVar;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END tttttt;

----------------------------------------------------------
<Java Code >

<Using returning clause of insert statement >

String sql="begin insert into t1(description) values (?) returning id into ? ; end; ";
Connection con= <get Ur connection>;
CallableStatement pstmt = con.prepareCall(sql);
pstmt.setString(1,"Some description");
pstmt.registerOutParameter(2,Types.VARCHAR);
succ = pstmt.executeUpdate()>0 ;
// pstmt.execute();
int generatedkey = pstmt.getInt(2) ;

<Using getGeneratedKeys().....>

String sql="insert into t1(description) values (?) ";
Connection con= <get Ur connection>;
PreparedStatement pstmt = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1,"Some description");
succ = pstmt.executeUpdate()>0 ;
// pstmt.execute();
ResultSet rset = pstmt.getGeneratedKeys();
ResultSetMetaData rsmd=rset.getMetaData();
int i1=0;
for (;i1<rsmd.getColumnCount();i1++) {
System.out.print(rsmd.getColumnName(i1+1)+"/t");
}
System.out.println();
while (rset.next()) {
for(int j=0;j<i1;j++) {
// System.out.print( rset.getString((j+1)) + "/t ");

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(" select id,description from TTTTT where rowid='"+rset.getString((1))+"'");

while (rs.next()) {
System.out.println(rs.getInt(1));
}

}

------------------------------------------------------------------------
Hope that will help if somebody has a similar requirement.

P.S. Don't forget to use ojdbc14.jar ver ( 10.2.0.something)
The above code doesn't work for 10.1 version
Also, You can check your version and getGeneratedkeys support thru' the foll. code:
DatabaseMetaData dbmd = con.getMetaData();

System.out.println("===== Database info =====");
System.out.println("DatabaseProductName: " + dbmd.getDatabaseProductName() );
System.out.println("DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
System.out.println("DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() );
System.out.println("DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() );
System.out.println("===== Driver info =====");
System.out.println("DriverName: " + dbmd.getDriverName() );
System.out.println("DriverVersion: " + dbmd.getDriverVersion() );
System.out.println("DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
System.out.println("DriverMinorVersion: " + dbmd.getDriverMinorVersion() );
System.out.println("===== JDBC/DB attributes =====");
System.out.print("Supports getGeneratedKeys(): "+dbmd.supportsGetGeneratedKeys()+"/n");

--------------------------------------------------------------

Message was edited by:
coomar

 

 

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

Weblogic Server Lib path    bea/weblogic81/server/lib