带有 Statement.RETURN_GENERATION_KEYS 的 PreparedStatement

一些 JDBC 驱动程序返回 Statement.RETURN_GENERATED_KEYS的唯一方法是执行以下操作:

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

有办法对 PreparedStatement做同样的事情吗？

剪辑

我之所以问我是否可以对 PreparedStatement做同样的事情，是因为考虑到以下情况:

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

在 USER表中有一个 PRIMARY KEY (USER_ID)，它是 BIGINT AUTOINCREMENT(因此在 SQL_CREATE字符串中没有看到它)。

现在，我使用 PreparedStatement.setXXXX(index, value)填充 ?。我想返回 ResultSet rs = PreparedStatement.getGeneratedKeys()。我如何实现这一点？

143820 次浏览
小开

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.

小开
最佳答案

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

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);
}
小开 
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);
}
小开 
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);
}

提交答案