PreparedStatement with Statement.RETURN_GENERATED_KEYS

Question

The only way that some JDBC drivers to return Statement.RETURN_GENERATED_KEYS is to do something of the following:

long key = -1L;
Statement statement = connection.createStatement();
statement.executeUpdate(YOUR_SQL_HERE, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs != null && rs.next()) {
    key = rs.getLong(1);
}

Is there a way to do the same with PreparedStatement?

Edit

The reason I asked if I can do the same with PreparedStatement consider the following scenario:

private static final String SQL_CREATE = 
            "INSERT INTO
            USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS, DOB) 
            VALUES (?, ?, ?, ?, ?)";

In the USER table there's a PRIMARY KEY (USER_ID) which is a BIGINT AUTOINCREMENT (hence why you don't see it in the SQL_CREATE String.

Now, I populate the ? using PreparedStatement.setXXXX(index, value). I want to return ResultSet rs = PreparedStatement.getGeneratedKeys(). How can I achieve this?

Many people misunderstand and use PreparedStatement#executeUpdate(arg) . Java doc says `This method with argument cannot be called on a PreparedStatement or CallableStatement.` It means we have to use executeUpdate() without argument even though `executeUpdate(arg)` method can be inherited in PreparedStatement class but we don't have to use it otherwise we will get SQLException. — AmitG, Jan 08 '15 at 17:33

score 150 · Accepted Answer · edited Aug 06 '11 at 21:55

150

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"})

edited Aug 06 '11 at 21:55

Buhake Sindi

87,898
29
167
228

answered Nov 19 '10 at 11:59

Jörn Horstmann

33,639
11
75
118

I've accepted your answer as you've showed more ways to achieve the same result. – Buhake Sindi Nov 19 '10 at 12:13

score 72 · Answer 2 · edited May 07 '16 at 12:32

72

You mean something like this?

long key = -1L;

PreparedStatement preparedStatement = connection.prepareStatement(YOUR_SQL_HERE, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setXXX(index, VALUE);
preparedStatement.executeUpdate();

ResultSet rs = preparedStatement.getGeneratedKeys();

if (rs.next()) {
    key = rs.getLong(1);
}

edited May 07 '16 at 12:32

Prasad Khode

6,602
11
44
59

answered Nov 19 '10 at 11:59

nanda

24,458
13
71
90

How can the generated keys result set be null? – AlikElzin-kilaka Apr 10 '16 at 17:02

score 10 · Answer 3 · answered Nov 19 '10 at 11:06

Not having a compiler by me right now, I'll answer by asking a question:

Have you tried this? Does it work?

long key = -1L;
PreparedStatement statement = connection.prepareStatement();
statement.executeUpdate(YOUR_SQL_HERE, PreparedStatement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs != null && rs.next()) {
    key = rs.getLong(1);
}

Disclaimer: Obviously, I haven't compiled this, but you get the idea.

PreparedStatement is a subinterface of Statement, so I don't see a reason why this wouldn't work, unless some JDBC drivers are buggy.

that's not what I'm looking for I know that `PreparedStatement` is a subclass of `Statement`....see my updated post. — Buhake Sindi, Nov 19 '10 at 11:20

score 5 · Answer 4 · answered Aug 19 '17 at 04:58

5

String query = "INSERT INTO ....";
PreparedStatement preparedStatement = connection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

preparedStatement.setXXX(1, VALUE); 
preparedStatement.setXXX(2, VALUE); 
....
preparedStatement.executeUpdate();  

ResultSet rs = preparedStatement.getGeneratedKeys();  
int key = rs.next() ? rs.getInt(1) : 0;

if(key!=0){
    System.out.println("Generated key="+key);
}

answered Aug 19 '17 at 04:58

Dharmendrasinh Chudasama

1,007
12
12

If key generated then key otherwise key=0 if not generated – Dharmendrasinh Chudasama Aug 19 '17 at 05:04

score 0 · Answer 5 · edited Nov 27 '12 at 13:06

private void alarmEventInsert(DriveDetail driveDetail, String vehicleRegNo, int organizationId) {

    final String ALARM_EVENT_INS_SQL = "INSERT INTO alarm_event (event_code,param1,param2,org_id,created_time) VALUES (?,?,?,?,?)";
    CachedConnection conn = JDatabaseManager.getConnection();
    PreparedStatement ps = null;
    ResultSet generatedKeys = null;
    try {
        ps = conn.prepareStatement(ALARM_EVENT_INS_SQL, ps.RETURN_GENERATED_KEYS);
        ps.setInt(1, driveDetail.getEventCode());
        ps.setString(2, vehicleRegNo);
        ps.setString(3, null);
        ps.setInt(4, organizationId);
        ps.setString(5, driveDetail.getCreateTime());
        ps.execute();
        generatedKeys = ps.getGeneratedKeys();
        if (generatedKeys.next()) {
            driveDetail.setStopDuration(generatedKeys.getInt(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
        logger.error("Error inserting into alarm_event : {}", e
                .getMessage());
        logger.info(ps.toString());
    } finally {
        if (ps != null) {
            try {

                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                logger.error("Error closing prepared statements : {}", e
                        .getMessage());
            }
        }
    }
    JDatabaseManager.freeConnection(conn);
}

Shouldn't you be freeing your connection in the finally block, not outside it (you'll leek a connection if you get a runtime exception of any kind)? — Jules, Oct 23 '13 at 22:21
@niraj - instead of ps.RETURN_GENERATED_KEYS we can write Statement.RETURN_GENERATED_KEYS because it is static variable in the java.sql.Statement class. — AmitG, Jan 08 '15 at 16:24

PreparedStatement with Statement.RETURN_GENERATED_KEYS

5 Answers5

Linked