How to Retrieve Automatically Generated Keys in JDBC?

You can retrieve automatically generated keys (also called auto-generated keys or auto increment) from a table using JDBC 3.0 methods getGeneratedKeys(). 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.

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

public class JDBCGetAutoIncKeys {

    private static final String DBURL = 
           "jdbc:mysql://localhost:3306/mydb?user=usr&password=sql" +
           "&useUnicode=true&characterEncoding=UTF-8";
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
  
    static {
        try {
            Class.forName(DBDRIVER).newInstance();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection() 
    {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DBURL);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) {
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                        java.sql.ResultSet.CONCUR_UPDATABLE);

            stmt.executeUpdate("DROP TABLE IF EXISTS autoincSample");
            stmt.executeUpdate(
                    "CREATE TABLE autoincSample ("
                    + "id INT NOT NULL AUTO_INCREMENT, "
                    + "data VARCHAR(64), PRIMARY KEY (id))");

            stmt.executeUpdate(
                    "INSERT INTO autoincSample (data) "
                    + "values ('Record ----- 1')",
                    Statement.RETURN_GENERATED_KEYS);

            rs = stmt.getGeneratedKeys();
            while (rs.next()) {
                System.out.println("Key returned from getGeneratedKeys():"
                        + rs.getInt(1));
            } 
            rs.close();

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

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }

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

}

Printer-friendly version | Mail this to a friend