21 June 2013

How to get the auto generated primary key of the inserted row

Hi there is one common problem in the development I saw was how to retrieve the auto generated key for the row that we insert into the database.

Normally what developers does is first they will insert the data into the database and then immediately they try to fetch the primary key by querying the database asking for max number of the primary key column as it is auto sequenced and always the max number will be the just inserted row's primary key.

This does not work all the time because in a multi threaded environment it is possible that two clients insert the rows into the same table at same time. So this fails at that point of time.

Solution in java:-

Instead just executing the query using Statement object in jdbc use the following methods that are there since 1.4. The following are the methods present in Statement class part of the sql package.

Methods in Statement Class:-

 intexecuteUpdate(String sql, int autoGeneratedKeys)
          Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
 intexecuteUpdate(String sql, int[] columnIndexes)
          Executes the given SQL statement and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
 intexecuteUpdate(String sql, String[] columnNames) 

For using with prepared statement the following are the methods in Connection class.

For Using With PreparedStatement Method in Connection Class:-

 PreparedStatementprepareStatement(String sql, int autoGeneratedKeys)
          Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys.

when using the above methods they make available of the any auto generated keys available for the insert made by that Statement object.

To retrieve the auto generated keys from the Statement object use the following method of the Statement class.

Method in Statement Class To Retrieve Keys:-

          Retrieves any auto-generated keys created as a result of executing this Statement object.

Post a Comment