- Trending Categories
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 set the initial value of an auto-incremented column in MySQL 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.
Setting the initial value
By default, the initial value of the auto-incremented column will be 1. You can change it using the ALTER TABLE query as shown below −
alter table table_name AUTO_INCREMENT = 1001
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) );
Following JDBC program sets the initial value of the auto-incremented column to 1001 and inserts 6 records into it.
Example
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
public class SettingInitialValue_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......");
//Setting the initial value of the auto-incremented column
Statement stmt = con.createStatement();
stmt.execute("alter table Sales AUTO_INCREMENT = 1001");
//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();
}
}Output
Connection established...... Records inserted......
Verification
If you verify the contents of the Sales table using SELECT statement, you can see the inserted records with ID value starting from 1001 as −
mysql> select * from sales; +------+-------------+--------------+--------------+--------------+-------+----------------+ | ID | ProductName | CustomerName | DispatchDate | DeliveryTime | Price | Location | +------+-------------+--------------+--------------+--------------+-------+----------------+ | 1001 | Key-Board | Raja | 2019-09-01 | 11:00:00 | 7000 | Hyderabad | | 1002 | Earphones | Roja | 2019-05-01 | 11:00:00 | 2000 | Vishakhapatnam | | 1003 | Mouse | Puja | 2019-03-01 | 10:59:59 | 3000 | Vijayawada | | 1004 | Mobile | Vanaja | 2019-03-01 | 10:10:52 | 9000 | Chennai | | 1005 | Headset | Jalaja | 2019-04-06 | 11:08:59 | 6000 | Goa | +------+-------------+--------------+--------------+--------------+-------+----------------+ 5 rows in set (0.00 sec)
Updated on: 30-Jul-2019
1K+ Views
Advertisements