How to retrieve auto-incremented value generated by PreparedStatement using JDBC?

While creating a table, in certain scenarios, we need values to column such as ID, to be generated/incremented automatically. Various databases support this feature in different ways.

In MySQL database you can declare a column auto increment using the following syntax.

CREATE TABLE table_name(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   column_name1 data_type1,
   column_name2 data_type2,
   column_name3 data_type3,
   column_name4 data_type4,
   ............ ...........
);

While inserting records in a table there is no need to insert value under the auto-incremented column. These will be generated automatically.

For example, in a table if we have a column with name ID and data type INT, which is auto-incremented and, if we already have 6 records in that table. When you insert the next record using the INSERT statement the ID value of the new record will be 7 and the ID value of its next record will be 8.

(You can specify the initial value and interval for these auto-incremented columns).

Retrieving the auto-incremented values

If you insert records into a table which contains auto-incremented column, using a PreparedStatement object.

You can retrieve the values of that particular column, generated by the current PreparedStatement object using the getGeneratedKeys() method.

Example

Let us create a table with name sales in MySQL database, with one of the columns as auto-incremented, using CREATE statement as shown below −

CREATE TABLE Sales(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   ProductName VARCHAR (20),
   CustomerName VARCHAR (20),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(20)
);

Now, to insert records into this table using PreparedStatement object and, to retrieve the auto-incremented values generated by it −

• Register the Driver class of the desired database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

• Create a Connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

• Create a PreparedStatement object using the prepareStatement() method of the connection interface.

To this method pass the INSERT statement with bind variables in string format as one parameter and, Statement.RETURN_GENERATED_KEYS as other parameter as −

//Query to Insert values to the sales table
String insertQuery = "INSERT INTO Sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) VALUES (?, ?, ?, ?, ?, ?)";
//Creating a PreparedStatement object
PreparedStatement pstmt = con.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);

• Set values of each record to the bind variables using the setXXX() methods and, add it to batch.

pstmt.setString(1, "Key-Board");
pstmt.setString(2, "Raja");
pstmt.setDate(3, new Date(1567315800000L));
pstmt.setTime(4, new Time(1567315800000L));
pstmt.setInt(5, 7000);
pstmt.setString(6, "Hyderabad");
pstmt.addBatch();
pstmt.setString(1, "Earphones");
pstmt.setString(2, "Roja");
pstmt.setDate(3, new Date(1556688600000L));
pstmt.setTime(4, new Time(1556688600000L));
pstmt.setInt(5, 2000);
pstmt.setString(6, "Vishakhapatnam");
pstmt.addBatch();
........... ...........

After adding values of all the records to the batch, execute the batch using the executeBatch() method.

pstmt.executeBatch();

• Finally, get the auto-incremented keys generated by this PreparedStatement object using the getGeneratedKeys() method.

ResultSet rs = pstmt.getGeneratedKeys();
while (rs.next()) {
   System.out.println(rs.getString(1));
}

Following JDBC program inserts 5 records into the Sales table (created above) using PreparedStatement, retrieves and displays the auto-incremented values generated by it.

Example

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
public class RetrievingData_AutoIncrement_Pstmt {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sample_database";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Query to Insert values to the sales table
      String insertQuery = "INSERT INTO Sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) VALUES (?, ?, ?, ?, ?, ?)";
      //Creating a PreparedStatement object
      PreparedStatement pstmt = con.prepareStatement(insertQuery,Statement.RETURN_GENERATED_KEYS);
      pstmt.setString(1, "Key-Board");
      pstmt.setString(2, "Raja");
      pstmt.setDate(3, new Date(1567315800000L));
      pstmt.setTime(4, new Time(1567315800000L));
      pstmt.setInt(5, 7000);
      pstmt.setString(6, "Hyderabad");
      pstmt.addBatch();
      pstmt.setString(1, "Earphones");
      pstmt.setString(2, "Roja");
      pstmt.setDate(3, new Date(1556688600000L));
      pstmt.setTime(4, new Time(1556688600000L));
      pstmt.setInt(5, 2000);
      pstmt.setString(6, "Vishakhapatnam");
      pstmt.addBatch();
      pstmt.setString(1, "Mouse");
      pstmt.setString(2, "Puja");
      pstmt.setDate(3, new Date(1551418199000L));
      pstmt.setTime(4, new Time(1551418199000L));
      pstmt.setInt(5, 3000);
      pstmt.setString(6, "Vijayawada");
      pstmt.addBatch();
      pstmt.setString(1, "Mobile");
      pstmt.setString(2, "Vanaja");
      pstmt.setDate(3, new Date(1551415252000L));
      pstmt.setTime(4, new Time(1551415252000L));
      pstmt.setInt(5, 9000);
      pstmt.setString(6, "Chennai");
      pstmt.addBatch();
      pstmt.setString(1, "Headset");
      pstmt.setString(2, "Jalaja");
      pstmt.setDate(3, new Date(1554529139000L));
      pstmt.setTime(4, new Time(1554529139000L));
      pstmt.setInt(5, 6000);
      pstmt.setString(6, "Goa");
      pstmt.addBatch();
      System.out.println("Records inserted......");
      //Executing the batch
      pstmt.executeBatch();
      //Auto-incremented values generated by the current PreparedStatement object
      ResultSet res = pstmt.getGeneratedKeys();
      System.out.println("Auto-incremented values of the column ID generated by the current PreparedStatement object: ");
      while (res.next()) {
         System.out.println(res.getString(1));
      }
   }
}

Output

Connection established......
Records inserted......
Auto-incremented values of the column ID generated by the current PreparedStatement object:
1
2
3
4
5