How to get primary key value (auto-generated keys) from inserted queries using JDBC?

JDBC Object Oriented Programming Programming

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

You can retrieve the values of that particular column, generated by them 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)
);

Retrieving auto-generated values (PreparedStatement object)

Following JDBC program inserts 3 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();
      //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

Retrieving auto-generated values (Statement object)

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

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')";
      //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

Anvi Jain

Updated on: 30-Jul-2019

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started