Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
187 Views
