How to create a table with auto-increment 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,    ............ ........... );

MySQL query to create a table with auto-incremented column.

CREATE TABLE Sales(    ID INT PRIMARY KEY AUTO_INCREMENT,    ProductName VARCHAR (20) NOT NULL,    CustomerName VARCHAR (20) NOT NULL,    DispatchDate date,    DeliveryTime timestamp,    Price INT,    Location varchar(20) );

Example

Following JDBC program establishes a connection with MYSQL database and creates a query with an auto-incremented column.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class AutoIncrementedColumns_Oracle {    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/mydatabase";       Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");       System.out.println("Connection established......");       //Creating the Statement       Statement stmt = con.createStatement();       //Creating the sales table with auto-incremented values       String createQuery = "CREATE TABLE Sales("          + "ID INT PRIMARY KEY AUTO_INCREMENT, "          + "ProductName VARCHAR (20) NOT NULL, "          + "CustomerName VARCHAR (20) NOT NULL, "          + "DispatchDate date, "          + "DeliveryTime timestamp, "          + "Price INT, "          + "Location varchar(20))";       //Executing the query       stmt.execute(createQuery);       System.out.println("Table created......");    } }

Output

Connection established...... Table created......

Verification

The DESCRIBE command in MYSQL gives the description of a table, you can verify the creation of the table with auto-incremented column using it, as shown below −

mysql> describe sales; +--------------+-------------+------+-----+---------+----------------+ | Field        | Type        | Null | Key | Default | Extra          | +--------------+-------------+------+-----+---------+----------------+ | ID           | int(11)     | NO   | PRI | NULL    | auto_increment | | ProductName  | varchar(20) | NO   |     | NULL    |                | | CustomerName | varchar(20) | NO   |     | NULL    |               | | DispatchDate | date        | YES  |     | NULL    |               | | DeliveryTime | time        | YES  |     | NULL    | | | Price        | int(11)     | YES  |     | NULL    | | | Location     | varchar(20) | YES  |     | NULL    | | +--------------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)