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 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)
Updated on: 29-Jun-2020
1K+ Views
Advertisements