JDBC PreparedStatement always returns 1 as auto generated key

Question

I have this code trying to insert a record in the database:

try {
 Connection conn = getConnection();

 String sql = 
   "INSERT INTO myTable(userId,content,timestamp) VALUES(?,?,NOW())";
 PreparedStatement st = 
    conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

 st.setLong(1, userId);
 st.setString(2, content);
 id = st.executeUpdate(); //this is the problem line            
} catch(Exception e) {}

The problem is, though the record is inserted correctly, I want id to contain the primary key + auto_increment id of the record that was just inserted. However, for some reason, it always returns '1' as the id, possibly because the value of userId is 1 during the inserts.

My table is InnoDB. At first userId was a foreign key to another table, owever I've since deleted the foreign key and even the index on the userId column, but I'm still getting 1 as the return value.

Any ideas what I'm doing wrong?

Answer 1

PreparedStatment.executeUpdate()

Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

You need to use execute() instead and get the ResultSet with getGeneratedKeys(); it's going to contain the data you want.

Edit to add: I read your question as there is an auto-increment field in the table that is not userId

Answer 2

The accepted Answer by Brian Roach is correct. I'm adding some thoughts and an example with full code.

RETURN_GENERATED_KEYS Does Not Mean “return generated keys”

The original poster seems to be confused, understandably, by the phrasing of the flag Statement.RETURN_GENERATED_KEYS. Contrary to intuition, passing this flag does not change the behavior of the PreparedStatement::executeUpdate method. That method always returns an int, the number of rows affected by the SQL executed. The "executeUpdate" method never returns the generated keys.

int countRowsAffected = pstmt.executeUpdate();  // Always return number of rows affected, *not* the generated keys.

Ask, and Ye Shall Receive

If you want the generated keys, you must do two steps:

1. Pass the flag, and
2. Ask for a ResultSet made up of rows containing only the generated key values.

This arrangement allows you to add the behavior of getting back generated keys while keeping the other desirable behavior, getting a count of the number of rows affected.

Example Code

Here is a nearly real-world example taken from a Java 8 app that scrapes data from a data feed. I think in this context a full-blown example may be more useful than a minimal one.

Minor details… This code may not be perfect, syntactically or otherwise, as I copy-pasted-modified real source code. I am using the UUID data type rather than integers as the surrogate primary key of my table. The classes CharHelper and DBHelper are my own, the details of which are not important here. The x and y variables are replacements of my own app's meaningful data. My logging calls are made to the SLF4J framework. The UUID hex strings are a convenient way to link reports in the logs back to the original source code. The database is Postgres, but this kind of code should work on any database supporting the reporting of generating keys.

public UUID dbWrite (  String x , String y , DateTime whenRetrievedArg ) {
    if ( whenRetrievedArg == null ) {
        logger.error( "Passed null for whenRetrievedArg. Message # 2112ed1a-4612-4d5d-8cc5-bf27087a350d." );
        return null;
    }

    Boolean rowInsertComplete = Boolean.FALSE; // Might be used for debugging or logging or some logic in other copy-pasted methods.

    String method = "Method 'dbWrite'";
    String message = "Insert row for some_table_ in " + method + ". Message # edbea872-d3ed-489c-94e8-106a8e3b58f7.";
    this.logger.trace( message );

    String tableName = "some_table_";

    java.sql.Timestamp tsWhenRetrieved = new java.sql.Timestamp( whenRetrievedArg.getMillis() );  // Convert Joda-Time DatTime object to a java.sql.Timestamp object.

    UUID uuidNew = null;

    StringBuilder sql = new StringBuilder( AbstractPersister.INITIAL_CAPACITY_OF_SQL_STRING ); // private final static Integer INITIAL_CAPACITY_OF_SQL_STRING = 1024;
    sql.append( "INSERT INTO " ).append( tableName ).append( CharHelper.CHAR.PAREN_OPEN_SPACED ).append( " x_ , y_ " ).append( CharHelper.CHAR.PAREN_CLOSED ).append( DBHelper.SQL_NEWLINE );
    sql.append( "VALUES ( ? , ? , ?  ) " ).append( DBHelper.SQL_NEWLINE );
    sql.append( ";" );

    try ( Connection conn = DBHelper.instance().dataSource().getConnection() ;

Here we do Step # 1, pass the RETURN_GENERATED_KEYS flag.

            PreparedStatement pstmt = conn.prepareStatement( sql.toString() , Statement.RETURN_GENERATED_KEYS ); ) {

We continue to prepare and execute the statement. Note that int countRows = pstmt.executeUpdate(); returns the count of affected rows, not the generated keys.

        pstmt.setString( 1 , x ); 
        pstmt.setString( 2 , y ); 
        pstmt.setTimestamp( 3 , tsWhenRetrieved );  
        // Execute
        int countRows = pstmt.executeUpdate();  // Always returns an int, a count of affected rows. Does *not* return the generated keys.
        if ( countRows == 0 ) {  // Bad.
            this.logger.error( "Insert into database for new " + tableName + " failed to affect any rows. Message # 67e8de7e-67a5-42a6-a4fc-06929211e6e3." );
        } else if ( countRows == 1 ) {  // Good.
            rowInsertComplete = Boolean.TRUE;
        } else if ( countRows > 1 ) {  // Bad.
            rowInsertComplete = Boolean.TRUE;
            this.logger.error( "Insert into database for new " + tableName + " failed, affecting more than one row. Should not be possible. Message # a366e215-6cf2-4e5c-8443-0b5d537cbd68." );
        } else { // Impossible.
            this.logger.error( "Should never reach this Case-Else with countRows value " + countRows + " Message # 48af80d4-6f50-4c52-8ea8-98856873f3bb." );
        }

Here we do Step # 2, ask for a ResultSet of the generated keys. In the case of this example, we inserted a single row and expect back a single generated key.

        if ( rowInsertComplete ) {
            // Return new row’s primary key value.
            ResultSet genKeys = pstmt.getGeneratedKeys();
            if ( genKeys.next() ) {
                uuidNew = ( UUID ) genKeys.getObject( 1 );  // ResultSet should have exactly one column, the primary key of INSERT table.
            } else {
                logger.error( "Failed to get a generated key returned from database INSERT. Message # 6426843e-30b6-4237-b110-ec93faf7537d." );
            }
        }

The rest is error-handling and clean-up. Do note that we return the UUID, the generated primary key of the inserted record, at the bottom of this code.

    } catch ( SQLException ex ) {
        // We expect to have occasional violations of unique constraint on this table in this data-scraping app.
        String sqlState = ex.getSQLState();
        if ( sqlState.equals( DBHelper.SQL_STATE.POSTGRES.UNIQUE_CONSTRAINT_VIOLATION ) ) {  // SqlState code '23505' = 'unique_violation'.
            this.logger.trace( "Found existing row when inserting a '" + tableName + "' row for y: " + y + ". Expected to happen on most attempts. Message # 0131e8aa-0bf6-4d19-b1b3-2ed9d333df27." );
            return null; // Bail out.
        } else { // Else any other exception, throw it.
            this.logger.error( "SQLException during: " + method + " for table: " + tableName + ", for y: " + y + ". Message # 67908d00-2a5f-4e4e-815c-5e5a480d614b.\n" + ex );
            return null; // Bail out.
        }
    } catch ( Exception ex ) {
        this.logger.error( "Exception during: " + method + " for table: " + tableName + ", for y: " + y + ". Message # eecc25d8-de38-458a-bb46-bd6f33117969.\n" + ex );
        return null;  // Bail out.
    }

    if ( uuidNew == null ) {
        logger.error( "Returning a null uuidNew var. SQL: {} \nMessage # 92e2374b-8095-4557-a4ed-291652c210ae." , sql );
    }
    return uuidNew;
}

Answer 3

String SQLQuery=" ";

String generatedKeys[]= {"column_name"};//'column_name' auto-increment column

prepSt = Connection.prepareStatement(SQLQuery,generatedKeys);

prepSt.setInt(1, 1234); 

.....

.....

....


prepSt.executeUpdate();

ResultSet rs = prepSt.getGeneratedKeys; // used same PreparedStatement object as used   for Insert .


if(rs.next()) {


int id=rs.getLong("column_name");


System.out.println(id);


}
} catch (SQLException e) {
}

Answer 4

If you have set userId has auto-increment in your database, you shouldn't try and add it yourself. You should insert NULL, and it will auto-increment for you! (The clue is in the name!)

Also, you are not updating your table, you are inserting into it. So you don't executeUpdate(). Try...

PreparedStatement pst = conn.prepareStatement("INSERT INTO myTable(userId,content,timestamp) VALUES(NULL,?,NOW())");
pst.setString(1, content);
pst.executeQuery();

Answer 5

What you get is the notification of 'Rows insetrted' (for INSERT statement). We use this method to know whether our DML query is succesful or not. The following is the way to get the Auto generated ID using [prepareStatement(yourSQL, Statement.RETURN_GENERATED_KEYS)]. Pls note that this method only return you a RowID ref. To get the actual val, pls refer to Method 2.

(Method 1)

Try{
String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
myPrepStatement = <Connection>.prepareStatement(yourSQL, Statement.RETURN_GENERATED_KEYS);
myPrepStatement.setInt(1, 123); 
myPrepStatement.setInt(2, 123); 

myPrepStatement.executeUpdate();
ResultSet rs = getGeneratedKeys;
if(rs.next()) {
  java.sql.RowId rid=rs.getRowId(1); 
  //what you get is only a RowId ref, try make use of it anyway U could think of
  System.out.println(rid);
}
} catch (SQLException e) {
}

(Method 2)

Try{
String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
//IMPORTANT: here's where other threads don tell U, you need to list ALL cols 
//mentioned in your query in the array
myPrepStatement = <Connection>.prepareStatement(yourSQL, new String[]{"Id","Col2","Col3"});
myPrepStatement.setInt(1, 123); 
myPrepStatement.setInt(2, 123); 
myPrepStatement.executeUpdate();
ResultSet rs = getGeneratedKeys;
if(rs.next()) {
//In this exp, the autoKey val is in 1st col
  int id=rs.getLong(1);
  //now this's a real value of col Id
  System.out.println(id);
}
} catch (SQLException e) {
}

Basically, try not used Method1 if you just want the value of SEQ.Nextval, b'cse it just return the RowID ref that you may cracked your head finding way to make use of it, which also don fit all data type you tried casting it to! This may works fine (return actual val) in MySQL, DB2 but not in Oracle.

IMPORTANT: Turn off your SQL Developer, Toad or any client which use the same login session to do INSERT when you're debugging. It MAY not affect you every time (debugging call) ... until you find your apps freeze without exception for some time. Yes ... halt without exception!