Java JDBC Retrieve ID After Insert

Question

I use triggers to set PK column values of all tables so i do not do any operation about IDs in java but i need the ID after insert.

How can i get the ID?

stat.execute("INSERT INTO TPROJECT_PROCESS_GROUP(NPROJECT_ID,VDESCRIPTION) " +
                "VALUES(" +
                "'" + projectID + "'," +
                "'" + description + "'" +
                "");

Edit: Hi again I read the question, now I get an exception like 'unsupported operation'(i translated from my native language the exact english form might be different). i guess this is about oracle's support for GetGeneratedKeys? Do you know anything about this?

Solution: As mentioned in a book about callablestatements This statement can be used to execute stored procedures and functions. Unlike the PreparedStatement, most databases do not perform any preparation for the call,because it is such a simple command. The CallableStatement instances can be used toreturn the object that the stored procedure—or function, to be more exact—returned.

OracleConnection conn = null;
    //OraclePreparedStatement pstat = null;
    OracleCallableStatement cstat = null;
    String sql = "BEGIN INSERT INTO TPROJECT P (VPROJECT_TITLE,VPROJECT_DESC)    VALUES(?,?) RETURNING P.NPROJECT_ID INTO ?;  END;";
    try {
        conn = ConnectionUtility.GetConnection();
        cstat = (OracleCallableStatement)conn.prepareCall(sql);

        cstat.setString(1, title);
        cstat.setString(2, description);
        cstat.registerOutParameter(3, OracleTypes.NUMBER);
        cstat.execute();

        int returnedID = cstat.getInt(3);
//          System.out.println(returnedID);

        conn.close();

        return returnedID;

Duplicate of [JDBC : How can we get inserted record ID in Java?](http://stackoverflow.com/questions/1915166/jdbc-how-can-we-get-inserted-record-id-in-java) Note that your statement is sensitive to SQL injections. You should prefer `PreparedStatement` over `Statement`. — BalusC, Aug 10 '10 at 18:38
Actually, I'm not sure if this is an *exact* duplicate, because here, somehow, a trigger is used. — erickson, Aug 10 '10 at 18:48
@erickson: The term is indeed ambiguous in the question context, but under the hoods, the MySQL `auto_increment`, DB2/MSSQL `identity` and Oracle/PostgreSQL `serial` are all using triggers. I don't see why one would create a custom one for insert ID. — BalusC, Aug 10 '10 at 18:52
yes i have information about sql injection and prevent attacks in another place, but you are right thats better, this is my first db application experience in java(i am a .net developer LINQ works fine there :P) so i am learning thanks for the advice =) — mehmet6parmak, Aug 10 '10 at 19:04
possible duplicate of [PLSQL JDBC: How to get last row ID?](http://stackoverflow.com/questions/3552260/plsql-jdbc-how-to-get-last-row-id) — BalusC, Oct 03 '11 at 13:39
I would like to point out that the getGeneratedKeys() solution also works with MySQL. — Sanete, Jul 27 '12 at 06:44

Ben · Answer 1 · 2011-11-03T12:54:01.103

6

This example is how you would do it in PostgreSQL. Hopefully you can do something similar in Oracle.

This is how you get the id after INSERT INTO for auto-generated keys like serial . Important here is to provide RETURN_GENERATED_KEYS in the prepareStatement() call.

Resultset result;
PreparedStatement prep;
String query = "INSERT INTO myRel (data) VALUES (?)";

prep = db.prepareStatement(query ,Statement.RETURN_GENERATED_KEYS);

result = prep.getGeneratedKeys();

if(result.next() && result != null){
   System.out.println("Key: " + result.getInt(1));
} else {
   System.out.println("No, Nop nada");
}

Hope that helps someone :)

edited Nov 03 '11 at 12:54

answered Oct 03 '11 at 13:07

Ben

3,012
1
21
25

Doesn't work for Oracle at all. Plus, this is already mentioned in 1st link in the question's comments. – BalusC Oct 03 '11 at 13:39
No, I do not have access to Oracle, I'm using Postgresql. I hoped that the general principle would apply anyway, it's JDBC afterall. – Ben Oct 31 '11 at 04:22
1

this answer sucks. Read http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc – Flavius Jan 04 '12 at 18:48

Java JDBC Retrieve ID After Insert

1 Answers1

Linked