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)