Как получить идентификатор вставки в JDBC?
Я бы хотел INSERT запись в базе данных (в моем случае это Microsoft SQL Server) с использованием JDBC в Java. В то же время я хочу получить идентификатор вставки. Как я могу добиться этого, используя JDBC API?
15 ответов
Если это автоматически сгенерированный ключ, то вы можете использовать Statement#getGeneratedKeys() за это. Вы должны позвонить на то же Statement как тот, который используется для INSERT, Сначала вам нужно создать заявление, используя Statement.RETURN_GENERATED_KEYS уведомить драйвер JDBC о возврате ключей.
Вот основной пример:
public void create(User user) throws SQLException {
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
Statement.RETURN_GENERATED_KEYS);
) {
statement.setString(1, user.getName());
statement.setString(2, user.getPassword());
statement.setString(3, user.getEmail());
// ...
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating user failed, no rows affected.");
}
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
user.setId(generatedKeys.getLong(1));
}
else {
throw new SQLException("Creating user failed, no ID obtained.");
}
}
}
}
Обратите внимание, что вы зависите от драйвера JDBC относительно того, работает ли он. В настоящее время большинство последних версий будет работать, но, если я прав, драйвер Oracle JDBC все еще несколько проблематичен с этим. MySQL и DB2 уже поддерживали это целую вечность. PostgreSQL начал поддерживать его не так давно. Я не могу комментировать MSSQL, так как никогда не использовал его.
Для Oracle вы можете вызвать CallableStatement с RETURNING пункт или SELECT CURRVAL(sequencename) (или любой другой специфичный для DB синтаксис) сразу после INSERT в той же транзакции, чтобы получить последний сгенерированный ключ. Смотрите также этот ответ.
Создать сгенерированный столбец
String generatedColumns[] = { "ID" };Передайте эту сгенерированную колонку вашему заявлению
PreparedStatement stmtInsert = conn.prepareStatement(insertSQL, generatedColumns);использование
ResultSetобъект для получения GeneratedKeys в оператореResultSet rs = stmtInsert.getGeneratedKeys(); if (rs.next()) { long id = rs.getLong(1); System.out.println("Inserted ID -" + id); // display inserted record }
По словам "Неподдерживаемая функция" ошибка с использованием Statement.RETURN_GENERATED_KEYS, Попробуй это:
String[] returnId = { "BATCHID" };
String sql = "INSERT INTO BATCH (BATCHNAME) VALUES ('aaaaaaa')";
PreparedStatement statement = connection
.prepareStatement(sql, returnId);
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating user failed, no rows affected.");
}
try (ResultSet rs = statement.getGeneratedKeys()) {
if (rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();
}
Где BRANCHID - автоматически сгенерированный идентификатор
Я использую Microsoft SQL Server 2008 R2 из однопоточного приложения на основе JDBC и извлекаю последний идентификатор без использования свойства RETURN_GENERATED_KEYS или какого-либо PreparedStatement. Выглядит примерно так:
private int insertQueryReturnInt(String SQLQy) {
ResultSet generatedKeys = null;
int generatedKey = -1;
try {
Statement statement = conn.createStatement();
statement.execute(SQLQy);
} catch (Exception e) {
errorDescription = "Failed to insert SQL query: " + SQLQy + "( " + e.toString() + ")";
return -1;
}
try {
generatedKey = Integer.parseInt(readOneValue("SELECT @@IDENTITY"));
} catch (Exception e) {
errorDescription = "Failed to get ID of just-inserted SQL query: " + SQLQy + "( " + e.toString() + ")";
return -1;
}
return generatedKey;
}
В этом сообщении блога хорошо представлены три основных параметра "последнего идентификатора" SQL Server: http://msjawahar.wordpress.com/2008/01/25/how-to-find-the-last-identity-value-inserted-in-the-sql-server/ - еще два не нужны.
Вместо комментария я просто хочу ответить на пост.
Интерфейс java.sql.PreparedStatement
columnIndexes "Вы можете использовать функцию prepareStatement, которая принимает columnIndexes и оператор SQL. Там, где столбцам разрешены постоянные флаги, это Statement.RETURN_GENERATED_KEYS 1 или Statement.NO_GENERATED_KEYS[2], оператор SQL, который может содержать один или несколько символов "?" В параметрах заполнители.
Синтакс "
Connection.prepareStatement(String sql, int autoGeneratedKeys) Connection.prepareStatement(String sql, int[] columnIndexes)Пример:
PreparedStatement pstmt = conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS );
columnNames " Перечислите столбец Names как
'id', 'uniqueID', ..., в целевой таблице, которая содержит автоматически сгенерированные ключи, которые должны быть возвращены. Драйвер будет игнорировать их, если оператор SQL не являетсяINSERTзаявление.Синтакс "
Connection.prepareStatement(String sql, String[] columnNames)Пример:
String columnNames[] = new String[] { "id" }; PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
Полный пример:
public static void insertAutoIncrement_SQL(String UserName, String Language, String Message) {
String DB_URL = "jdbc:mysql://localhost:3306/test", DB_User = "root", DB_Password = "";
String insertSQL = "INSERT INTO `unicodeinfo`( `UserName`, `Language`, `Message`) VALUES (?,?,?)";
//"INSERT INTO `unicodeinfo`(`id`, `UserName`, `Language`, `Message`) VALUES (?,?,?,?)";
int primkey = 0 ;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection(DB_URL, DB_User, DB_Password);
String columnNames[] = new String[] { "id" };
PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
pstmt.setString(1, UserName );
pstmt.setString(2, Language );
pstmt.setString(3, Message );
if (pstmt.executeUpdate() > 0) {
// Retrieves any auto-generated keys created as a result of executing this Statement object
java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
if ( generatedKeys.next() ) {
primkey = generatedKeys.getInt(1);
}
}
System.out.println("Record updated with id = "+primkey);
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
Вы можете использовать следующий код Java, чтобы получить новый вставленный идентификатор.
ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, quizid);
ps.setInt(2, userid);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
lastInsertId = rs.getInt(1);
}
Я использую SQLServer 2008, но у меня есть ограничение разработки: я не могу использовать новый драйвер для него, я должен использовать "com.microsoft.jdbc.sqlserver.SQLServerDriver" (я не могу использовать "com.microsoft.sqlserver.jdbc".SQLServerDriver").
Вот почему решение conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) бросил java.lang.AbstractMethodError для меня. В этой ситуации возможное решение, которое я нашел, - это старое, предложенное Microsoft: как получить значение @@IDENTITY с помощью JDBC
import java.sql.*;
import java.io.*;
public class IdentitySample
{
public static void main(String args[])
{
try
{
String URL = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs";
String userName = "yourUser";
String password = "yourPassword";
System.out.println( "Trying to connect to: " + URL);
//Register JDBC Driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
//Connect to SQL Server
Connection con = null;
con = DriverManager.getConnection(URL,userName,password);
System.out.println("Successfully connected to server");
//Create statement and Execute using either a stored procecure or batch statement
CallableStatement callstmt = null;
callstmt = con.prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY");
callstmt.setString(1, "testInputBatch");
System.out.println("Batch statement successfully executed");
callstmt.execute();
int iUpdCount = callstmt.getUpdateCount();
boolean bMoreResults = true;
ResultSet rs = null;
int myIdentVal = -1; //to store the @@IDENTITY
//While there are still more results or update counts
//available, continue processing resultsets
while (bMoreResults || iUpdCount!=-1)
{
//NOTE: in order for output parameters to be available,
//all resultsets must be processed
rs = callstmt.getResultSet();
//if rs is not null, we know we can get the results from the SELECT @@IDENTITY
if (rs != null)
{
rs.next();
myIdentVal = rs.getInt(1);
}
//Do something with the results here (not shown)
//get the next resultset, if there is one
//this call also implicitly closes the previously obtained ResultSet
bMoreResults = callstmt.getMoreResults();
iUpdCount = callstmt.getUpdateCount();
}
System.out.println( "@@IDENTITY is: " + myIdentVal);
//Close statement and connection
callstmt.close();
con.close();
}
catch (Exception ex)
{
ex.printStackTrace();
}
try
{
System.out.println("Press any key to quit...");
System.in.read();
}
catch (Exception e)
{
}
}
}
Это решение сработало для меня!
Надеюсь, это поможет!
Можно использовать с нормальным Statementтакже (не только PreparedStatement)
Statement statement = conn.createStatement();
int updateCount = statement.executeUpdate("insert into x...)", Statement.RETURN_GENERATED_KEYS);
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
return generatedKeys.getLong(1);
}
else {
throw new SQLException("Creating failed, no ID obtained.");
}
}
С NativeQuery в Hibernate вам нужно возвращать ResultList вместо SingleResult, потому что Hibernate изменяет собственный запрос
INSERT INTO bla (a,b) VALUES (2,3) RETURNING id
лайк
INSERT INTO bla (a,b) VALUES (2,3) RETURNING id LIMIT 1
если вы попытаетесь получить единственный результат, который заставит большинство баз данных (по крайней мере PostgreSQL) выдать синтаксическую ошибку. После этого вы можете получить полученный идентификатор из списка (который обычно содержит ровно один элемент).
Большинство других предложили использовать для этого JDBC API, но лично я нахожу это довольно болезненным для большинства драйверов. Когда на самом деле вы можете просто использовать нативную функцию T-SQL, предложение :
try (
Statement s = c.createStatement();
ResultSet rs = s.executeQuery(
"""
INSERT INTO t (a, b)
OUTPUT id
VALUES (1, 2)
"""
);
) {
while (rs.next())
System.out.println("ID = " + rs.getLong(1));
}
Это самое простое решение для SQL Server, а также для некоторых других диалектов SQL (например, Firebird, MariaDB, PostgreSQL, где вы могли бы использоватьRETURNINGвместоOUTPUT).
Вы можете использоватьexecuteQuery(query)функция
и добавитьRETURNING *вместо этого предложение к вашему запросу*вы можете указать имя столбца
Например
сначала создай таблицу
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
создать запрос
val query: String = "INSERT INTO users (name) VALUES ("Bob"), ("Makr"), ("Adam") RETURNING id;"
применить запрос
val statement = connection.prepareStatement(query)
val result = statement.executeQuery()
val keys = buildList {
while (result.next()) { add(result.getLong(1)) }
}
ключи - это ваш результат. Таким образом, вы можете вернуть любой набор столбцов или полный объект.
В моем случае ->
ConnectionClass objConnectionClass=new ConnectionClass();
con=objConnectionClass.getDataBaseConnection();
pstmtGetAdd=con.prepareStatement(SQL_INSERT_ADDRESS_QUERY,Statement.RETURN_GENERATED_KEYS);
pstmtGetAdd.setString(1, objRegisterVO.getAddress());
pstmtGetAdd.setInt(2, Integer.parseInt(objRegisterVO.getCityId()));
int addId=pstmtGetAdd.executeUpdate();
if(addId>0)
{
ResultSet rsVal=pstmtGetAdd.getGeneratedKeys();
rsVal.next();
addId=rsVal.getInt(1);
}
Если вы используете Spring JDBC, вы можете использовать класс Spring GeneratedKeyHolder для получения вставленного идентификатора.
Посмотрите этот ответ... Как получить вставленный идентификатор с помощью Spring Jdbctemplate.update(String sql, obj...args)
Если вы используете JDBC (проверено с MySQL) и вам просто нужен последний вставленный идентификатор, есть простой способ его получить. Метод, который я использую, следующий:
public static Integer insert(ConnectionImpl connection, String insertQuery){
Integer lastInsertId = -1;
try{
final PreparedStatement ps = connection.prepareStatement(insertQuery);
ps.executeUpdate(insertQuery);
final com.mysql.jdbc.PreparedStatement psFinal = (com.mysql.jdbc.PreparedStatement) ps;
lastInsertId = (int) psFinal.getLastInsertID();
connection.close();
} catch(SQLException ex){
System.err.println("Error: "+ex);
}
return lastInsertId;
}
Также (и на всякий случай) метод получения
ConnectionImplзаключается в следующем:
public static ConnectionImpl getConnectionImpl(){
ConnectionImpl conexion = null;
final String dbName = "database_name";
final String dbPort = "3306";
final String dbIPAddress = "127.0.0.1";
final String connectionPath = "jdbc:mysql://"+dbIPAddress+":"+dbPort+"/"+dbName+"?autoReconnect=true&useSSL=false";
final String dbUser = "database_user";
final String dbPassword = "database_password";
try{
conexion = (ConnectionImpl) DriverManager.getConnection(connectionPath, dbUser, dbPassword);
}catch(SQLException e){
System.err.println(e);
}
return conexion;
}
Не забудьте добавить коннектор/J в библиотеки, на которые ссылается проект.
В моем случае версия Connector/J — это 5.1.42. Возможно, вам придется внести некоторые изменения в
connectionPathесли вы хотите использовать более современную версию коннектора/J, например, версию 8.0.28.
В файл не забудьте импортировать следующие ресурсы:
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.ConnectionImpl;
Надеюсь, это будет полезно.
Connection cn = DriverManager.getConnection("Host","user","pass");
Statement st = cn.createStatement("Ur Requet Sql");
int ret = st.execute();