JDBC Tutorials - Herong's Tutorial Examples - v3.13, by Herong Yang
Tables with Primary Key Column "GENERATED ... AS IDENTITY"
This section describes how to create a table primary key column defined as 'GENERATED ... AS IDENTITY'.
Similar to Microsoft SQL Server, Derby supports IDENTITY columns to help you creating auto-incremented sequence values for primary key columns. Derby offers two variations of IDENTITY columns:
In order to try IDENTITY 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 IDENTITY column:
/* DerbyIdentityColumn.java * Copyright (c) HerongYang.com. All Rights Reserved. */ import java.sql.*; public class DerbyIdentityColumn { public static void main(String [] args) { Connection con = null; try { con = DriverManager.getConnection("jdbc:derby://localhost/TestDB"); // Creating a database table Statement sta = con.createStatement(); int count = sta.executeUpdate( "CREATE TABLE Profile (" + " ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," + " FirstName VARCHAR(20) NOT NULL," + " LastName VARCHAR(20)," + " Point REAL DEFAULT 0.0," + " BirthDate DATE DEFAULT '1999-12-31'," + " ModTime TIMESTAMP DEFAULT '2016-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 example program for the first time, table Profile will be created. If you run it again, you will get an exception:
herong> java -cp %DERBY_HOME%\lib\derbyclient.jar DerbyIdentityColumn.java herong> java -cp $DERBY_HOME/lib/derbyclient.jar DerbyIdentityColumn.java Table created. herong> java -cp %DERBY_HOME%\lib\derbyclient.jar DerbyIdentityColumn.java herong> java -cp $DERBY_HOME/lib/derbyclient.jar DerbyIdentityColumn.java Exception: Table/View 'PROFILE' already exists in Schema 'APP'.
Table of Contents
JDBC (Java Database Connectivity) Introduction
Installing and Running Derby (Java DB)
Derby (Java DB) JDBC DataSource Objects
►Derby (Java DB) - DML Statements
►Tables with Primary Key Column "GENERATED ... AS IDENTITY"
"INSERT INTO" Statements with INDENTITY Columns
Handling Date and Timestamp Values
Derby (Java DB) - ResultSet Objects of Queries
Derby (Java DB) - PreparedStatement
Summary of JDBC Drivers and Database Servers