Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 



Getting Auto-Increment Column Values

Auto-increment Feature

The auto-increment feature (AUTO_INCREMENT) is a column-related feature that increments the numeric value of each row. For more information, see "CUBRID SQL Guide > Table Definition > CREATE TABLE > Column Definition" in Creating Tables. This feature can be defined only for numeric domains (SMALLINT, INTEGER, DECIMAL(p, 0), NUMERIC(p, 0)).

The auto-increment feature is recognized as an automatically created key in a JDBC program. To retrieve the key, you need to specify the time to insert a row from which the automatically created key value is to be retrieved. To perform it, you must set the flag by calling Connection.prepareStatement and Statement.execute. In this case, the command to be executed should be the INSERT statement or INSERT within SELECT statement. For other commands, the JDBC driver ignores the flag-setting parameter.

Steps
  1. Use one of the followings to indicate whether or not to return a key created automatically. The following method forms are used for tables of the database server that supports the auto-increment columns. Each method form can be applied only to a single-row INSERT statement.
    • Create a PreparedStatement object by referring to the followings:
      Connection.prepareStatement(sql statement, Statement.RETURN_GENERATED_KEYS);
    • To insert a row using the Statement.execute method, use one of the forms of the Statement.execute method by referring to the followings:
      Statement.execute(sql statement, Statement.RETURN_GENERATED_KEYS);
  2. Retrieve a ResultSet object that contains an automatically created key value by calling the PreparedStatement.getGeneratedKeys or Statement.getGeneratedKeys method. Note that the data type of the automatically created key in ResultSet is DECIMAL regardless of the data type of the given domain.
Example

The following example shows how to create a table with the auto-increment feature, enter data into the table so that automatically created key values are entered into auto-increment columns, and check whether the key values are successfully retrieved by using the Statement.getGeneratedKeys() method. Each step is explained in the comments for commands that correspond to the steps above.

import java.sql.*;

import java.math.*;

import cubrid.jdbc.driver.*;

 

Connection con;

Statement stmt;

ResultSet rs;

java.math.BigDecimal iDColVar;

...

stmt = con.createStatement();     // Create a Statement object

 

stmt.executeUpdate(

"CREATE TABLE EMP_PHONE (EMPNO CHAR(6), PHONENO CHAR(4), "

+   "IDENTCOL INTEGER AUTO_INCREMENT)"); // Create table with identity column

 

stmt.execute(

"INSERT INTO EMP_PHONE (EMPNO, PHONENO) "

+   "VALUES ('000010', '5555')",          // Insert a row  <Step 1>

Statement.RETURN_GENERATED_KEYS);            // Indicate you want automatically

 

 

rs = stmt.getGeneratedKeys();    // generated keys

                                       // Retrieve the automatically  <Step 2>

                                       // generated key value in a ResultSet.

                                       // Only one row is returned.

                                       // Create ResultSet for query

while (rs.next()) {

  java.math.BigDecimal idColVar = rs.getBigDecimal(1);    

                                       // Get automatically generated key

                                       // value

  System.out.println("automatically generated key value = " + idColVar);

}

rs.close();                          // Close ResultSet

stmt.close();                        // Close Statement