Preparedstatement with Statement.Return_Generated_Keys

PreparedStatement with Statement.RETURN_GENERATED_KEYS

You can either use the prepareStatement method taking an additional int parameter

PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)

For some JDBC drivers (for example, Oracle) you have to explicitly list the column names or indices of the generated keys:

PreparedStatement ps = con.prepareStatement(sql, new String[]{"USER_ID"})

Issue with PreparedStatement while using with Statement.RETURN_GENERATED_KEYS

In oracle DB RETURN_GENERATED_KEYS works in that way.

1) RETURN_GENERATED_KEYS flag

PreparedStatement ps = con.prepareStatement("INSERT INTO XXX(  A, B) VALUES ( xxx_s.nextval, 'aaa')",Statement.RETURN_GENERATED_KEYS);      
    ps.executeUpdate();        
    ResultSet      rs = ps.getGeneratedKeys();
    while(rs.next()) {
        System.out.println(rs.getString(1));
    }

Oracle doesn't know which column was auto-generated and it is returning rowid. returning rowid into :? is appended to the insert

2) List of columns.

PreparedStatement ps = con.prepareStatement("INSERT INTO XXX(  A, B) VALUES ( xxx_s.nextval, 'aaa')",new String[]{"A"});        
        ps.executeUpdate();        
        ResultSet      rs = ps.getGeneratedKeys();
        while(rs.next()) {
            System.out.println(rs.getString(1));
        }
        con.close();

In this case we explicitly specify auto generated column. returning a into :? is appended to the insert

In both case you to access generated column you have to use positional access. Access by column name is not supported.

However you are trying to do insert as select . Statment is tranformated to

insert into xxxy  select * from xxxy returning rowid into :var ; 

And this Statement is not supported by oracle db. And exception ORA-00933: SQL command not properly ended is raised.

PreparedStatement.RETURN_GENERATED_KEYS and Mysql

Since id is declared as AUTO_INCREMENT you don't need to mention it in the query:

String sql = "insert into log ( date, user_name, event, message, audio_path) values (?,?,?,?,?)";

In this way MySQL will automatically generate new id.

Then, you should do this:

PreparedStatement ps = connection.prepareStatement(sql, RETURN_GENERATED_KEYS)
ps.setTimestamp(1, log.getDate());
// ...
ps.setString(5, log.getAudioPath());

int updated = ps.executeUpdate();

ResultSet generatedKeysResultSet = ps.getGeneratedKeys();
// first row of this set will contain generated keys
// in our case it will contain only one row and only one column - generated id
generatedKeysResultSet.next(); // executing next() method to navigate to first row of generated keys (like with any other result set)
long id = generatedKeysResultSet.getLong(1); // since our row contains only one column we won't miss with the column index :)

log.setId(id);

return updated;

Besides that, after you try and get working example I would really recommend to correctly close MySQL Connection and PreparedStatement using JDBC. Please refer to this answer on how to correctly create and close resources.

Hope this helps

PreparedStatement.RETURN_GENERATED_KEYS not working with batch insert

Is there a way to get the auto generated values during the insertion itself.

No. After you ps.executeBatch() you need to call ps.getGeneratedKeys() and loop through the ResultSet it returns.

JDBC PreparedStatement always returns 1 as auto generated key

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

Does Statement.RETURN_GENERATED_KEYS generate any extra round trip to fetch the newly created identifier?

In PostgreSQL with PgJDBC there is no extra round-trip to fetch generated keys.

It sends a Parse/Describe/Bind/Execute message series followed by a Sync, then reads the results including the returned result-set. There's only one client/server round-trip required because the protocol pipelines requests.

However sometimes batches that can otherwise be streamed to the server may be broken up into smaller chunks or run one by on if generated keys are requested. To avoid this, use the String[] array form where you name the columns you want returned and name only columns of fixed-width data types like integer. This only matters for batches, and it's a due to a design problem in PgJDBC.

(I posted a patch to add batch pipelining support in libpq that doesn't have that limitation, it'll do one client/server round trip for arbitrary sized batches with arbitrary-sized results, including returning keys.)

MySQL Error when using Statement.RETURN_GENERATED_KEYS

Recommend changing your code as follows:

• Name the key column

• Get the row count from the executeUpdate call

• Don't call getGeneratedKeys() if no rows were inserted

rows = stmt.executeUpdate(query, new String[] { "flight_id" });
int autoIncKey = -1;
if (rows > 0) {
    result = stmt.getGeneratedKeys();
    if (result.next()) {
        autoIncKey = result.getInt(1);
    }
}
svr.setGeneratedKey(autoIncKey);
obj.setGeneratedKey(autoIncKey);
svr.setRows(rows); //Insert/Update/Delete
svr.setSuccess(rows > 0);

Though, really, a single INSERT statement using VALUES will always insert exactly one row, so checking row count is entirely unnecessary. If the row wasn't inserted, an exception would have been thrown, so your code could be reduced to:

stmt.executeUpdate(query, new String[] { "flight_id" });
try (ResultSet result = stmt.getGeneratedKeys()) {
    result.next();
    int autoIncKey = result.getInt(1);
    svr.setGeneratedKey(autoIncKey);
    obj.setGeneratedKey(autoIncKey);
}
svr.setRows(1);
svr.setSuccess(true);

Java PreparedStatement RETURN_GENERATED_KEYS not working

My JT400.jar file was an older version. I downloaded the latest jar file from sourceforge and the problem was solved.

---

Related Topics