Recent Pos

Reading Auto Generated Key Using JDBC


reading auto generated key using JDBC
⇒ In designing the database table it is well known that we want to have a primary key column. In many cases we prefer having an integer value which is sequentially generated as a primary key. And these are preferred to generated in the database server itself. These are referred as Auto Generated values.

⇒ In addition we may even find some other columns also automatically generated in the database.

⇒ The JDBC 2.0 introduces support for reading the auto generated key from the database without the need for executing an additional select query.

Using Statement to get the Generated keys:

⇒ The following are two steps to use this:

Step 1: Execute the insert SQL

Step 2: Get the generated keys
1. Executing the insert SQL:

⇒ Here we want to use any of the following three methods to execute the insert SQL:
method 1.

⇒ The second argument int value can be any one of the following:

Statement.RETURN_GENERATED_KEYS: This specifies to return all the generated keys inserting this record.

Statement.NO_GENERATED_KEYS: This specifies not to return any generated value.

Note: Return value of this method (int) describes the number of records effected by this statement same as executeUpdate(String sql) method return, not the generated value. To get the generated value follow Step 2.
method 2.

⇒ In this case we need to specify the names of the columns that we want to get the generated values of. This is useful in case if the table has many columns auto generated and we have requirement of only getting the value of one or few columns.
method 3.

⇒ Same as above method except this will take column index instead of name.
2. Getting the generated keys:

⇒ After we execute the query using any one of the three methods discussed above we can use the following method for getting the generated keys:

⇒ The returned ResultSet will have only one row and the columns as per the actual table columns.

⇒ To run this example with MySQL create the following table:

Note: In case of Oracle we don’t have the support for auto increment in this case we need to create a TRIGGER with a SEQUENCE, The trigger to call on insert.

Auto Key Generator with PreparedStatement:

⇒ In case of using the PreparedStatement we have the following three steps:

Step 1: Create the PreparedStatement

Step 2: Set the values to parameters

Step 3: Execute and get the generated keys
1. Creating PreparedStatement:

⇒ We know that the following method of Connection to create PreparedStatement:

⇒ In case of getting the generated keys we need to use any of the following methods of Connection to create PreparedStatement:

2. Set the values to parameters:

⇒ Setting the values to the parameters is same as done earlier. i.e. setXxx() methods of PreparedStatement.

3. Execute and get the generated keys:

⇒ We use the executeUpdate() method as normal to execute the statement and after this we shall use getGeneratedKeys() method reading the generated keys as done with Statement.

<-Previous: JDBC Advanced ResultSet – Scrollable & Updatable ResultSet | | Next->The SQL 99 Types – BLOB (Binary Large Object)

No Comments

Post a Comment