This section describes how to create a new table with an IDENTITY column for PreparedStatement testing.
IDENTITY column is a nice feature provided by SQL Server that provides auto-incremented sequence values
for the specified column in a table. Usually, a primary key column is defined as an IDENTITY column.
In order to try IDENTITY columns and provide a table for PreparedStatement tests,
I wrote the following program to create a table called "Profile" with the primary key column defined as an IDENTITY column:
/**
* SqlServerIdentityColumn.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
import javax.sql.*;
public class SqlServerIdentityColumn {
public static void main(String [] args) {
Connection con = null;
try {
com.microsoft.sqlserver.jdbc.SQLServerDataSource ds
= new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
ds.setServerName("localhost");
ds.setPortNumber(1269);
ds.setDatabaseName("AdventureWorksLT");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Creating a database table
Statement sta = con.createStatement();
int count = sta.executeUpdate(
"CREATE TABLE Profile ("
+ " ID INTEGER PRIMARY KEY IDENTITY,"
+ " FirstName VARCHAR(20) NOT NULL,"
+ " LastName VARCHAR(20),"
+ " Point REAL DEFAULT 0.0,"
+ " BirthDate DATETIME DEFAULT '1988-12-31',"
+ " ModTime DATETIME 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());
}
}
}
