How to get the insert ID in JDBC?

1. Create Generated Column

   String generatedColumns[] = { "ID" }; 

2. Pass this geneated Column to your statement

   PreparedStatement stmtInsert = conn.prepareStatement(insertSQL, generatedColumns); 

3. Use ResultSet object to fetch the GeneratedKeys on Statement

   ResultSet rs = stmtInsert.getGeneratedKeys(); if (rs.next()) { long id = rs.getLong(1); System.out.println("Inserted ID -" + id); // display inserted record } 

When encountering an 'Unsupported feature' error while using Statement.RETURN_GENERATED_KEYS, try this:

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

Where BATCHID is the auto generated id.

I'm hitting Microsoft SQL Server 2008 R2 from a single-threaded JDBC-based application and pulling back the last ID without using the RETURN_GENERATED_KEYS property or any PreparedStatement. Looks something like this:

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

This blog post nicely isolates three main SQL Server "last ID" options: http://msjawahar.wordpress.com/2008/01/25/how-to-find-the-last-identity-value-inserted-in-the-sql-server/ - haven't needed the other two yet.

Instead of a comment, I just want to answer post.

Interface java.sql.PreparedStatement

1. columnIndexes « You can use prepareStatement function that accepts columnIndexes and SQL statement. Where columnIndexes allowed constant flags are Statement.RETURN_GENERATED_KEYS1 or Statement.NO_GENERATED_KEYS[2], SQL statement that may contain one or more '?' IN parameter placeholders.

   SYNTAX «

   Connection.prepareStatement(String sql, int autoGeneratedKeys) Connection.prepareStatement(String sql, int[] columnIndexes) 

   Example:

   PreparedStatement pstmt = conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS ); 

2. columnNames « List out the columnNames like 'id', 'uniqueID', .... in the target table that contain the auto-generated keys that should be returned. The driver will ignore them if the SQL statement is not an INSERT statement.

   SYNTAX «

   Connection.prepareStatement(String sql, String[] columnNames) 

   Example:

   String columnNames[] = new String[] { "id" }; PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames ); 

Full Example:

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

I'm using SQLServer 2008, but I have a development limitation: I cannot use a new driver for it, I have to use "com.microsoft.jdbc.sqlserver.SQLServerDriver" (I cannot use "com.microsoft.sqlserver.jdbc.SQLServerDriver").

That's why the solution conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) threw a java.lang.AbstractMethodError for me. In this situation, a possible solution I found is the old one suggested by Microsoft: How To Retrieve @@IDENTITY Value Using 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) { } } } 

This solution worked for me!

I hope this helps!

You can use following java code to get new inserted id.

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

It is possible to use it with normal Statement's as well (not just 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."); } } 

With Hibernate's NativeQuery, you need to return a ResultList instead of a SingleResult, because Hibernate modifies a native query

INSERT INTO bla (a,b) VALUES (2,3) RETURNING id 

like

INSERT INTO bla (a,b) VALUES (2,3) RETURNING id LIMIT 1 

if you try to get a single result, which causes most databases (at least PostgreSQL) to throw a syntax error. Afterwards, you may fetch the resulting id from the list (which usually contains exactly one item).

Most others have suggested to use JDBC API for this, but personally, I find it quite painful to do with most drivers. When in fact, you can just use a native T-SQL feature, the OUTPUT clause:

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

This is the simplest solution for SQL Server as well as a few other SQL dialects (e.g. Firebird, MariaDB, PostgreSQL, where you'd use RETURNING instead of OUTPUT).

I've blogged about this topic more in detail here.

In my case ->

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

If you are using Spring JDBC, you can use Spring's GeneratedKeyHolder class to get the inserted ID.

See this answer... How to get inserted id using Spring Jdbctemplate.update(String sql, obj...args)

If you are using JDBC (tested with MySQL) and you just want the last inserted ID, there is an easy way to get it. The method I'm using is the following:

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

Also, (and just in case) the method to get the ConnectionImpl is the following:

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

Remember to add the connector/J to the project referenced libraries.

In my case, the connector/J version is the 5.1.42. Maybe you will have to apply some changes to the connectionPath if you want to use a more modern version of the connector/J such as with the version 8.0.28.

In the file, remember to import the following resources:

import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import com.mysql.jdbc.ConnectionImpl; 

Hope this will be helpful.

You can use executeQuery(query) function

and add RETURNING * clause to your query instead * you can specify column name

For example

create table first

CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL); 

create query

val query: String = "INSERT INTO users (name) VALUES ("Bob"), ("Makr"), ("Adam") RETURNING id;" 

apply query

val statement = connection.prepareStatement(query) val result = statement.executeQuery() val keys = buildList { while (result.next()) { add(result.getLong(1)) } } 

keys is your result. So you can return any column set or full object

To get the last inserted Id on a table in MSSQL there are various options:

What I use is this one:

IDENT_CURRENT(‘TableName’) 

There are some other options like SCOPE_IDENTITY() and @@IDENTITY as well but the above is much easier.

Example:

String sql = "SELECT IDENT_CURRENT('TableName') AS ID;"; try ( ResultSet rs = con.execSQL (sql, sqlParameters)) { while (rs.next ()) { ret = rs.getInt ("ID"); } } ............ 

For more info check this out

I hope this helps...