SQL(Java,h2):检索刚插入数据库的单个项的唯一ID的最佳方法是什么? [重复]

时间:2022-11-25 12:49:06

This question already has an answer here:

这个问题在这里已有答案:

My current method is this:

我目前的方法是这样的:

SELECT TOP 1 ID FROM DATAENTRY ORDER BY ID DESC

This assumes the latest inserted item always has the highest unique ID (primary key, autoincrementing). Something smells wrong here.

这假定最新插入的项始终具有最高的唯一ID(主键,自动增量)。这里闻起来有点不对劲。

Alternatives?

备择方案?

2 个解决方案

#1


24  

If the JDBC driver supports it, you can also just use Statement#getGeneratedKeys() for that.

如果JDBC驱动程序支持它,您也可以使用Statement#getGeneratedKeys()。

String sql = "INSERT INTO tbl (col) VALUES (?)";
preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, col);
preparedStatement.executeUpdate();
generatedKeys = preparedStatement.getGeneratedKeys();
if (generatedKeys.next()) {
    long id = generatedKeys.getLong(1);
} else {
    // Throw exception?
}

#2


5  

If using MySQL you can do

如果使用MySQL,你可以做到

select last_insert_id();

If using MS SQL

如果使用MS SQL

select scope_identity();

For H2, I believe it's

对于H2,我相信它

CALL SCOPE_IDENTITY();

but I don't have any experience with that DB

但是我对那个数据库没有任何经验

#1


24  

If the JDBC driver supports it, you can also just use Statement#getGeneratedKeys() for that.

如果JDBC驱动程序支持它,您也可以使用Statement#getGeneratedKeys()。

String sql = "INSERT INTO tbl (col) VALUES (?)";
preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, col);
preparedStatement.executeUpdate();
generatedKeys = preparedStatement.getGeneratedKeys();
if (generatedKeys.next()) {
    long id = generatedKeys.getLong(1);
} else {
    // Throw exception?
}

#2


5  

If using MySQL you can do

如果使用MySQL,你可以做到

select last_insert_id();

If using MS SQL

如果使用MS SQL

select scope_identity();

For H2, I believe it's

对于H2,我相信它

CALL SCOPE_IDENTITY();

but I don't have any experience with that DB

但是我对那个数据库没有任何经验