Auto generate key on JDBC insert in SQL Server

Question

Is there a general, cross RDMS, way I can have a key auto generated on a JDBC insert? For example if I have a table with a primary key, id, and an int value:

create table test (
  id int not null,
  myNum int null
)

and do an insert

PreparedStatement statement = connection.prepareStatement("insert into test(myNum) values(?)", Statement.RETURN_GENERATED_KEYS);
statement.setInt(1, 555);
statement.executeUpdate();
        statement.close();

I get an java.sql.SQLException: Cannot insert the value NULL into column 'id'.

I have a feeling this is entirely RDMS dependent. We are using using SQL Server 2005 and I have set

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 1) ON [PRIMARY]

in the table with no luck.

Answer 1

This is completely database dependent. There are two main options: 1 - DBMSs that allow an auto-increment keyword next to the primary key definition and 2 - DBMSs that provide sequence generators (that you then can use to generate the new values for the PK, for instance by writing a "before insert" trigger that automatically inserts the new value in the column before completing the insertion ).

As far as I know:

1. Firebird uses sequences
2. DB2 allows to define a column as "GENERATED BY DEFAULT AS IDENTITY";
3. Interbase uses sequences (called generators)
4. MySQL has the "AUTO_INCREMENT" clause
5. Oracle uses sequences
6. PostgreSQL uses sequences
7. SQLServer has the "IDENTITY(1,1)" clause