This section describes how to create a table with an AUTO_INCREMENT column.
Different database servers have different ways to support columns with automatically incremented values.
MySQL uses the key word AUTO_INCREMENT to specify an auto-incrementing column.
In order to try AUTO_INCREMENT columns and provide a test table for testing DML (Data Manipulation Statements),
I wrote the following program to create a table called "Profile" with the primary key column defined as an AUTO_INCREMENT column:
/**
* MySqlAutoIncrement.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
import javax.sql.*;
public class MySqlAutoIncrement {
public static void main(String [] args) {
Connection con = null;
try {
// Setting up the DataSource object
com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
ds.setUser("Herong");
ds.setPassword("TopSecret");
// Getting a connection object
con = ds.getConnection();
// Creating a database table
Statement sta = con.createStatement();
int count = sta.executeUpdate(
"CREATE TABLE Profile ("
+ " ID INTEGER PRIMARY KEY AUTO_INCREMENT,"
+ " FirstName VARCHAR(20) NOT NULL,"
+ " LastName VARCHAR(20),"
+ " Point REAL DEFAULT 0.0,"
+ " BirthDate DATE DEFAULT '1988-12-31',"
+ " ModTime TIMESTAMP DEFAULT '2006-12-31 23:59:59.999')");
System.out.println("Table created.");
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
}
}
}
When you run this program, table Profile will be created. But you run it again, you will get an exception:
