Skip navigation links

MySQL Newsletter - Subscribe Today!

Using AUTO_INCREMENT keys with Connector/J 3.0

Delicious Digg StumbleUpon Reddit Twitter Facebook

By Mark Matthews

One of the major features available in the latest version of MySQL's JDBC Driver, Connector/J, is the implementation of version 3.0 of the JDBC API that is available with JDK-1.4.0 and later.

There are many changes in JDBC-3.0 that make you, the Java developer using MySQL job a little easier.

Today, we'll talk about one feature in particular, the ability to retrieve the values of database-generated keys.

Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported 'auto increment' or identity columns.

With older JDBC drivers for MySQL, you could always use a MySQL- specific method on the Statement interface, or issue the query 'SELECT LAST_INSERT_ID()' after issuing an 'INSERT' to a table that had an AUTO_INCREMENT key.

Using the MySQL-specific method call isn't portable, and issuing a 'SELECT' to get the AUTO_INCREMENT key's value requires another round- trip to the database, which isn't as efficient as possible.

The following code snippet demonstrates the three different ways to retrieve AUTO_INCREMENT values.

First, we demonstrate the use of the new JDBC-3.0 method 'getGeneratedKeys()' which is now the preferred method to use if you need to retrieve AUTO_INCREMENT keys and have access to JDBC-3.0.

The second example shows how you can retrieve the same value using a standard 'SELECT LAST_INSERT_ID()' query.

The final example shows how updatable result sets can retrieve the AUTO_INCREMENT value when using the method 'insertRow()'.

To run the examples yourself, you need to download and install the 'beta' version of MySQL Connector/J, as well as JDK-1.4.0 or newer.

Statement stmt = null;
ResultSet rs = null;

try {
    //
    // Create a Statement instance that we can use for
    // 'normal' result sets as well as an 'updatable'
    // one, assuming you have a Connection 'conn' to
    // a MySQL database already available
    //

    stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                java.sql.ResultSet.CONCUR_UPDATABLE);

    //
    // Issue the DDL queries for the table for this example
    //

    stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
    stmt.executeUpdate(
            "CREATE TABLE autoIncTutorial ("
            + "priKey INT NOT NULL AUTO_INCREMENT, "
            + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

    //
    // Insert one row that will generate an AUTO INCREMENT
    // key in the 'priKey' field
    //

    stmt.executeUpdate(
            "INSERT INTO autoIncTutorial (dataField) "
            + "values ('Can I Get the Auto Increment Field?')",
            Statement.RETURN_GENERATED_KEYS);

    //
    // Example of using Statement.getGeneratedKeys()
    // to retrieve the value of an auto-increment
    // value
    //

    int autoIncKeyFromApi = -1;

    rs = stmt.getGeneratedKeys();

    if (rs.next()) {
        autoIncKeyFromApi = rs.getInt(1);
    } else {

        // throw an exception from here
    }

    rs.close();

    //
    // Example of using the MySQL LAST_INSERT_ID()
    // function to do the same thing as getGeneratedKeys()
    //

    int autoIncKeyFromFunc = -1;
    rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

    if (rs.next()) {
        autoIncKeyFromFunc = rs.getInt(1);
    } else {
        // throw an exception from here
    }

    rs.close();

    //
    // Example of retrieving an AUTO INCREMENT key
    // from an updatable result set
    //

    rs = stmt.executeQuery("SELECT priKey, dataField "
       + "FROM autoIncTutorial");

    rs.moveToInsertRow();

    rs.updateString("dataField", "AUTO INCREMENT here?");
    rs.insertRow();

    //
    // the driver adds rows at the end
    //

    rs.last();

    //
    // We should now be on the row we just inserted
    //

    int autoIncKeyFromRS = rs.getInt("priKey");

    rs.close();

    rs = null;

    System.out.println("Key returned from getGeneratedKeys():"
        + autoIncKeyFromApi);

    System.out.println("Key returned from " + "'SELECT LAST_INSERT_ID()': "
        + autoIncKeyFromFunc);

    System.out.println("Key returned for inserted row: "
        + autoIncKeyFromRS);

} finally {

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

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

When you run the example code above, you should get the following output:

Key returned from getGeneratedKeys(): 1
Key returned from 'SELECT LAST_INSERT_ID()': 1
Key returned for inserted row: 2

Notice that the first two values, '1' are the same, as we just used two different ways to retrieve the AUTO_INCREMENT value for the same inserted row.

The third value, '2' is for a newly-inserted row.

You should be aware, that at times, it can be tricky to use the 'SELECT LAST_INSERT_ID()' query, as that function's value is scoped to a connection. So, if some other query happens on the same connection, the value will be overwritten. On the other hand, the 'getGeneratedKeys()' method is scoped by the Statement instance, so it can be used even if other queries happen on the same connection, but not on the same Statement instance.

Now that there is a standard way to retrieve these auto-generated key values in JDBC-3.0, many third-party tools support it. Some of the more useful Open Source tools that support MySQL and AUTO_INCREMENT keys are:

Hopefully, you've seen something new you can use with your Java projects in this article. Look for more new feature articles covering MySQL Connector/J 3.0 in future newsletters, and be sure to check out MySQL Connector/J 3.0 to try the new features for yourself!