How to retrieve auto-incremented value generated by Statement 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 generated by the Statement object

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

You can retrieve the values of that particular column, generated by the current Statement 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 multiple records into this table by executing the multiple-row INSERT statement using Statement 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 Statement object using the createStatement() method of the connection interface.

//Creating the Statement object Statement stmt = con.createStatement();

• Execute the multi-row INSERT query using the executeUpdate() method.To this method pass the multi-row INSERT statement in string format as one parameter and, Statement.RETURN_GENERATED_KEYS as other parameter as −

//Executing the INSERT statement stmt.executeUpdate(insertQuery, Statement.RETURN_GENERATED_KEYS);

• 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.getInt(1)); }

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

Example

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class RetrievingData_AutoIncrement {    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......");       //Creating the Statement object Statement stmt = con.createStatement();       //Query to insert multiple rows       String insertQuery = "insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values"       + "('Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'India'), "       + "('Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam'), "       + "('Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada'), "       + "('Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai'), "       + "('Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa')";       //Executing the INSERT statement stmt.executeUpdate(insertQuery, Statement.RETURN_GENERATED_KEYS);       System.out.println("Records inserted......");       //Retrieving the auto-generated (auto-incremented) keys ResultSet rs = stmt.getGeneratedKeys();       System.out.println("Values of auto-generated keys: ");       while(rs.next()) {          System.out.println(rs.getInt(1));       }    } }

Output

Connection established...... Records inserted...... Values of generated keys: 1 2 3 4 5