Hi,
I'm trying to create entries in a Database using an entity bean, but without specifying the primary key so that the Database will just increment it automatically. I can't seem to do this, as I get an error message saying that the primary key cannot be null when starting postcreate().

Can anyone help me, as I don't want to have to lookup the maximum primary key everytime I do an insert.

Thanks.

Which database are you using. If it is SQL (and I beleive this also applies to Access) have the primary key defined as an AUTONUMBER data type. if it is Oracle or Other database which doesnot support auto number i use another strategy...

create a table known as APP_DB_PROPERTIES with fields
[prop id] VARCHAR or [prop name] VARCHAR
[prop type] INT or VARCHAR
[prop value] VARCHAR

this table will be populated with values like
<contents of [scott].[propoperties]>
'xtable_next_primary_key_value', 'N', '1076'
'ytable_next_primary_key_value', 'N', '2043'
'login_hits_since_marked_time', 'L', '7879'
'login_hits_marked_time', 'D', '10/10/2000 12:34:00.000 PM'

or if you chose the numeric types
have additional tables to store propety names and data type names

<contets of [scott].[better_properties]>
100, 0, '1076'
107, 0, '2043'
147, 1, '7879'
123, 7, '10/10/2000 12:34:00.000 PM'

<contents of [scott].[prop names]
100, 'xtable_next_primary_key_value'
.
.
107, 'ytable_next_primary_key_value'
.
.
147, 'login_hits_since_marked_time'
.
.
<contents of [scott].[prop types]>
0, 'numeric'
1, 'long'
.
.

ok now that you have the req tables create a trigger on the table for which you want auto incrementing keys to be generatred as a 'BEFORE INSERT' trigger.... i think you know whwere i am getting at... get the value of the next key from the properties table and remember update the value in the properties table.

A better way would be to have stored procedures that do the insert or update for you, that way you can lock the propertes table in a row selection mode have a mini transaction and release the lock. but it will work both ways

I struggeled with the same problem some time ago. I solved the issue by *not* using automatic database primary key generation. Instead I wrote a simple primary key generator in Java and inserted the primary key into the table with the rest of the row. After all, as long as the primary key is unique, I don't care about the actual primary key value.

You can create a simple pk generator framework by defining a sequencer interface and implement sequencer implementations satisfying your requirements (for example generating unique numbers, lookup on a database counter, java counters)

See the discussion at http://theserverside.com/patterns/thread.jsp?thread_id=220

I wanted to know how one generate PK for tables using java application.I want have the code for this.


Regards

Samit Mathur

This can be achieved by two ways using oracle sequence

1) To use the the jdbc sequence class to get the new value and use it in the prepareStatement.

2) Prepare a statement like
ps = con.prepareStatement("insert into ejbAccounts (id, bal) values (?,abc.nextval)");
      ps.setString(1, accountId);

where abc is the oracle sequence id. I've tested the 2nd approch.