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  amp  amp  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

User · Answer

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  amp  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[java] PreparedStatement with Statement.RETURN_GENERATED_KEYS

Examples related to java

Examples related to jdbc