How to generate unique no. with java and mysql

[Mullin]

I need to generate a unique no. in the format like
yyyymmddxxxxxx (xxxxxx 6-digitl running number)

I think of create a table with two columns

date value
20050405 120
20050406 99

Everytime, the application will increase the value+1 based on the same
date. If not date found, create one record and value = 1

The problem is that since there's no transaction at mysql 4.1.x that
even the application
1. insert value
2. max() to get
max() may not get the value just insert BECAUSE there may be
MULTI-threads/users

How can implement so with Java and mysql?

[Jasper Bryant-Greene]

You could use a transaction-safe MySQL storage engine like InnoDB.

[steve]

"Mullin" wrote:

I need to generate a unique no. in the format like
yyyymmddxxxxxx (xxxxxx 6-digitl running number)

I think of create a table with two columns

date value
20050405 120
20050406 99

Everytime, the application will increase the value+1 based on
the same
date. If not date found, create one record and value = 1

The problem is that since there's no transaction at mysql
4.1.x that
even the application
1. insert value
2. max() to get
max() may not get the value just insert BECAUSE there may be
MULTI-threads/users

How can implement so with Java and mysql?

Create an auto-incrementing primary key field, and insert into it, and
grab the just incremented number back. This is always unique, even if
multiple processes are accessing.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-genera...ict212741.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=729728