Auto-generated Keys

Auto-generated Keys

Auto-generated keys are values which distinctly point to each row in a table, and are automatically generated in the database.

In Altibase, a sequence can act as auto-generated keys. This section explains how to obtain the values of auto-generated keys in JDBC.

How to Use Auto-generated Keys

To obtain an auto-generated key, first the Statement object is executed with a method specifying the column for which auto-generated keys are to be obtained. The ResultSet of the auto-generated keys can be retrieved with the getGeneratedKeys() method.

Or, after having created the PreparedStatement object with a method specifying the column for which auto-generated keys are to be obtained and executing it, the ResultSet of the autogenerated keys can be retrieved with the getGeneratedKeys() method.

The following are Statement methods that execute SQL statements which retrieve autogenerated keys.

public boolean execute(String aSql, int aAutoGeneratedKeys) throws SQLException; 
public boolean execute(String aSql, int[] aColumnIndexes) throws SQLException; 
public boolean execute(String aSql, String[] aColumnNames) throws SQLException;

The following are Connection methods that create the PreparedStatement object which retrieve auto-generated keys.

public PreparedStatement prepareStatement(String aSql, int aAutoGeneratedKeys) throws SQLException; 
public PreparedStatement prepareStatement(String aSql, int[] aColumnIndexes) throws SQLException; 
public PreparedStatement prepareStatement(String aSql, String[] aColumnNames) throws SQLException;

After having executed a SQL statement in one of the above two ways, auto-generated keys can be obtained by a ResultSet object with the following Statement method.

public ResultSet getGeneratedKeys() throws SQLException;

Restrictions

When obtaining auto-generated keys in Altibase, the following restrictions apply:

• Its use is only supported for simple INSERT statements.

• Since Altibase does not support columns with the AUTO INCREMENT property, autogenerated keys can only be obtained from a sequence.

The following is an example of a SQL statement from which auto-generated keys can be obtained.

INSERT INTO t1 (id, val) VALUES (t1_id_seq.nextval, ?);

The following is an example of a SQL statement from which auto-generated keys cannot be obtained.

SELECT * FROM t1;
EXEC p1;

If a SQL statement which does not produce auto-generated keys is executed with the generator flag (Statement.RETURN_GENERATED_KEYS), the flag is ignored and the getGeneratedKeys() method returns an empty result set.

Example

sStmt.executeUpdate(sQstr, Statement.RETURN_GENERATED_KEYS);
ResultSet sKeys = sStmt.getGeneratedKeys();
while (sKeys.next())
{
    int sKey = sKeys.getInt(1);

    // do somethings...
}
sKeys.close();
sStmt.close();