我试图在使用 JDBC 准备好的语句插入时获取最后插入的行 ID。我有一个自动递增主键列作为表中的标识列。我的代码如下:

public static String insertMeetingToDB(String organizer,String subject,String location,String start_date_time,String end_date_time,String description) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Integer last_inserted_id=0;


String insertTableSQL = "INSERT INTO MEETINGS"
                   + "(ORGANIZER_EMAIL, SUBJECT, MEETING_LOCATION, START_DATE_TIME, END_DATE_TIME, MEETING_DESCRIPTION) VALUES"
                   + "(?,?,?,?,?,?)";

SimpleDateFormat from = new SimpleDateFormat("yyyyMMdd'T'HHmmss");
from.setTimeZone(TimeZone.getTimeZone("IST"));  //--CONVERTING DATE/TIME TO INDIAN STANDARD TIME

SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd' 'HH:mm:ss");

Date input_start_date_val =  from.parse(start_date_time);
Date input_end_date_val =  from.parse(end_date_time);

String input_start_date =  datetimeFormat.format(input_start_date_val);
String input_end_date =  datetimeFormat.format(input_end_date_val);

try {

        dbConnection = getConnection();

        //--INSERTING MEETING DETAILS
        preparedStatement = dbConnection.prepareStatement(insertTableSQL, preparedStatement.RETURN_GENERATED_KEYS);

        preparedStatement.setString(1, organizer);
        preparedStatement.setString(2, subject); 
        preparedStatement.setString(3, location);
        preparedStatement.setTimestamp(4, java.sql.Timestamp.valueOf(input_start_date));
        preparedStatement.setTimestamp(5, java.sql.Timestamp.valueOf(input_end_date));
        preparedStatement.setString(6, description);

        // execute insert SQL stetement
        preparedStatement.executeUpdate();

        ResultSet rs = preparedStatement.getGeneratedKeys();
        if(rs.next())
        {
            last_inserted_id = rs.getInt(1);
        }

        return last_inserted_id.toString();

} catch (SQLException e) {
        return e.getMessage()+" ERROR CODE: "+e.getErrorCode();
} finally {
        if (preparedStatement != null) {
                preparedStatement.close();
        }
        if (dbConnection != null) {
                dbConnection.close();
                dbConnection = null; 
        }
  }
}

如果我删除这一行，那么我就不会收到这个错误:

last_inserted_id = rs.getInt(1);

但是在谷歌搜索之后，这段代码似乎没问题，它应该返回我最后插入的行 ID。

表格说明:

CREATE TABLE MEETINGS (
  MEETING_ID  NUMBER GENERATED ALWAYS AS IDENTITY,
  ORGANIZER_EMAIL VARCHAR2(100),
  SUBJECT VARCHAR2(250),
  START_DATE_TIME TIMESTAMP,
  END_DATE_TIME TIMESTAMP,
  ATTENDEES_LIST_CONFIDENTIAL CHAR(1),
  ATTENDEES_CONF_CONFIDENTIAL CHAR(1),
  ATTENDEES_COUNT_INTERNAL NUMBER(11),
  ATTENDEES_COUNT_EXTERNAL NUMBER(11),
  CONFIRMED_COUNT_INTERNAL NUMBER(11),
  CONFIRMED_COUNT_EXTERNAL NUMBER(11),
  PREVIOUS_MEETING_ID NUMBER(20),
  APPOINTMENT_SOURCE CHAR(1),
  MEETING_LOCATION VARCHAR(100),
  LATITUDE FLOAT(10),
  LONGITUDE FLOAT(10),
  MEETING_DESCRIPTION VARCHAR2(1000),
  PRIMARY KEY(MEETING_ID)
);

最佳答案

The Oracle JDBC documentation说:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieve other columns.

您没有指定列(如他们的示例代码所示)，因此您正在检索 ROWID；尝试使用 getInt() 获取它会导致您看到的错误。 (我实际上看到了 Invalid column type: getInt not implemented for class oracle.jdbc.driver.RowidAccessor ERROR CODE: 17004 但我认为这取决于不同的驱动程序版本)。

您需要指定获取自动生成值的列。如果它被称为 MEETING_ID 那么你会做:

    String returnCols[] = { "MEETING_ID" };
    preparedStatement = dbConnection.prepareStatement(insertTableSQL, returnCols);

...传递列数组 - 在这种情况下只有一个 - 而不是 RETURN_GENERATED_KEYS 标志。

然后 rs.getInt(1) 将检索该数值。

关于java - 为什么请求无效转换错误代码 : 17132?，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32112907/