• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

How to generate unique value from java and mysql

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 no 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?
2 Solutions
you could do it with two fields, one for the date part and the other an auto number field
you'd need to merge them when you read rows from db.
Objects is right.  Have a look here for an example:



CREATE TABLE animals (
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
SELECT * FROM animals;

Which returns:

| id | name    |
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
mscprojhkAuthor Commented:
but, the problem as i stated that if how can i know make sure the value i query is correct if TWO users/threads insert and query at the same time. though insert will have two separate values, query may return SAME values or A got Value of B and B got value of A

i want to know how to get rid of this situation
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

the db will look after that for you, the auto numbered field will always be unique
> if TWO users/threads insert and query at the same time.

there is no query required, the value is incremented automatically by the db.
See the example tim posted above.
>> since there's no transaction at mysql 4.1.x
Not sure why do you say that?
MySQL supports transactions (and since 4.0.5 also all the isolation levels http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html)

preventing phantom reads will help you to solve the concurrency problem (http://www.databasejournal.com/features/mssql/article.php/1442301)
And for that you can set the connection isolation level to serializable (supported in mysql) when you apply this set of operations.

Though that can solve your problem I do agree with objects and TimYates that a simple and more elegant solution for you
is to use auto increament field for your second column.
When using JDBC 3.0 and above you can get the value of the auto generated column after you inserted a new record
by calling getGeneratedKeys() on the statement. MySQL driver Connector/J supports it (see http://netmirror.org/mirror/mysql.com/tech-resources/articles/autoincrement-with-connectorj.html)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now