How to get Primary Key Of Inserted Record in JDBC?

 
When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. The below example shows how to get this key after insert statement.

After perfoming executeUpdate() method on PreparedStatement, call getGeneratedKeys() method on PreparedStatement.

The getGeneratedKeys() provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query and a second round-trip to the server. SQL Server allows only a single auto increment column per table.

The ResultSet that is returned by getGeneratedKeys method will have only one column, with the returned column name of GENERATED_KEYS.

If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set.

When you insert rows by executeUpdate or execute an INSERT statement or an INSERT within SELECT statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a flag in a Connection.prepareStatement, Statement.executeUpdate, or Statement.execute method call. The statement that is executed must be an INSERT statement or an INSERT within SELECT statement, Otherwise, the JDBC driver ignores the parameter that sets the flag.

For example:

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBCGetAutoIncKeys
{
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION
                                  ="jdbc:oracle:thin:@::";
private static final String DB_USER = "username";
private static final String DB_PASSWORD = "password";

static
{
try
    {
    Class.forName(DB_DRIVER);
    }
	
	catch (ClassNotFoundException e)
    {
            System.out.println("Please add Oracle JDBC Driver in your classpath ");
            e.printStackTrace();
    }

    System.out.println("Oracle JDBC Driver Registered!");
}

private static Connection getConnection()
{
	Connection connection = null;
	try
	{
        connection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
	}

	catch (Exception e)
	{
		e.printStackTrace();
	}
	return connection;
}

public static void main(String[] arg)
{

	PreparedStatement stmt = null;
	ResultSet rs = null;
	Connection conn = null;

	try
	{
		conn = getConnection();
		stmt = conn.prepareStatement("DROP TABLE IF EXISTS EMPLOYEE");
		stmt.executeUpdate();
		stmt.executeUpdate("CREATE TABLE EMPLOYEE ("
				+ "EMP_ID INT NOT NULL AUTO_INCREMENT, "
				+ "name VARCHAR(64), dept VARCHAR(64),"
				+ " salary INT, PRIMARY KEY (EMP_ID))");

		String query = "INSERT INTO EMPLOYEE (name, dept, salary) values (?,?,?)";
		stmt = conn
			.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
		stmt.setString(1, "Mahi");
		stmt.setString(2, "Automobile");
		stmt.setInt(3, 1000000);
		stmt.executeUpdate();

		rs = stmt.getGeneratedKeys();

		if (rs != null && rs.next())
		{
		System.out.println("Generated Emp Id: " + rs.getInt(1));
		}
		rs.close();
		}

		catch (Exception e)
		{
		e.printStackTrace();
		}

		finally
		{
			if (rs != null)
			{
				try
				{
				rs.close();
				}
				catch (SQLException e)
				{
				e.printStackTrace();
				}
			}

			if (stmt != null)
			{
				try
				{
				stmt.close();
				}
				catch (SQLException e)
				{
				e.printStackTrace();
				}
			}
		}
	}

}