How to insert data into a table with auto-incremented columns using JDBC?

While inserting data into a table with auto-incremented column, just leave that particular column out and insert remaining values by specifying the remaining columns using the following syntax of the INSERT statement −

INSERT into table_name (column_name1, column_name2....) values(value1, value2....)

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 establishes a connection with the database and inserts 5 records into the sales table −

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
public class InsertingData_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......");
      //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);
      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.execute();
      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.execute();
      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.execute();
      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.execute();
      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.execute();
      System.out.println("Records inserted......");
   }
}

Output

Connection established......
Records inserted......

Verification

If you verify the contents of the Sales table using SELECT statement, you can see the inserted records as −

mysql> select * from sales;
+----+-------------+--------------+--------------+--------------+-------+----------------+
| ID | ProductName | CustomerName | DispatchDate | DeliveryTime | Price | Location       |
+----+-------------+--------------+--------------+--------------+-------+----------------+
| 1  | Key-Board   | Raja         | 2019-09-01    | 11:00:00    | 7000   | Hyderabad     |
| 2  | Earphones   | Roja         | 2019-05-01    | 11:00:00    | 2000   | Vishakhapatnam|
| 3  | Mouse       | Puja         | 2019-03-01    | 10:59:59    | 3000   | Vijayawada    |
| 4  | Mobile      | Vanaja       | 2019-03-01    | 10:10:52    | 9000   | Chennai       |
| 5  | Headset     | Jalaja       | 2019-04-06    | 11:08:59    | 6000   | Goa           |
+----+-------------+--------------+--------------+--------------+-------+----------------+
5 rows in set (0.00 sec)