JDBC PreparedStatement always returns 1 as auto generated key [duplicate]

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;
}