Archived

This topic is now archived and is closed to further replies.

JensB

[java] Auto Number?

Recommended Posts

I am writing a Java application that uses JDBC to work with a database - The idea is that the app should be able to access any database engine (for instance MySQL). Therefore I don''t wanna use stored procedures, but then a problem arises - What about Counter / AutoNumber type of fileds: I insert a row into the db, the db engine will give it a unique number - How do I get this number back without using a stored procedure - Is there any good way, or shall I skip using the "Counter" functionallity of the database and do the numbering myself? any input appreciated, Jens

Share this post


Link to post
Share on other sites
Jens,

The standard way of providing primary Ids is to create them yourself, rather than allow the dB to do it.

The easiest way to do it is the following:

ID = day + month + hour + second + minute + randRange(0,100) + randRange(0,100);

(althought thats some strange pseudo code, and it reality, yoiu can place them in any order you like..or randomize the order? for extra security.)

But it will provide you with a 99.9999999...etc percent sure way of generating a unique ID (hasn''t failed me yet)

The added benefit is that you know the ID when you create the item, so you can use it as you see fit elsewhere.

Mark

----
Vector7 Software Engineering
www.vector7.com.au
----

Share this post


Link to post
Share on other sites
MArk,

This method failed me immediatly, but that happened when I generated a bunch of ids very quickly.

Any ideas on how to fix that (adding milliseconds to the equation didn''t help).

Jens

Share this post


Link to post
Share on other sites
If the database you''re using supports sequences (I''ve only used Oracle, and I don''t know if this is a regular relational database feature or Oracle-specific), you can do this:

  
// Assuming Statement stmt, and a sequence in the

// database named myseq

ResultSet rs;
rs = stmt.executeQuery ("select myseq.nextval from dual");
int id = rs.getInt(1);
// rs.close etc.



Obviously you would have to sprinkle the requisite try/catch-statements in there as well.

Neophyte

- Death awaits you all with nasty, big, pointy teeth. -

Share this post


Link to post
Share on other sites