- 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 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