• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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)

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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