Tutorial :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.


Solution: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


Solution: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.


Solution: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.


Solution:4

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »