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

As far as I know it's database dependent. Likewise with inserting timestamps; some will insert the current time when you insert a null.

Answer 2

You need to set the id column in the test table to autocreate an identity. In the case of SQL Server, you need to set the IDENTITY() property on the ID column.

Answer 3

This is database dependant. Oracle requires a SEQUENCE to be created and on MySQL you just set the column as auto increment.

You could always use Hibernate.

Answer 4

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

Answer 5

Thank you all for the answers. It is RDMS dependent then and for SQL Server the correct table create syntax is

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

identity(a,b) means that the first key will be a and keys will be incremented by b.