How to generate unique value from java and mysql

Posted on 2005-04-08
Last Modified: 2008-02-26
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?
Question by:mscprojhk
    LVL 92

    Accepted Solution

    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.
    LVL 35

    Assisted Solution

    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 |

    Author Comment

    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
    LVL 92

    Expert Comment

    the db will look after that for you, the auto numbered field will always be unique
    LVL 92

    Expert Comment

    > 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.
    LVL 15

    Expert Comment

    >> 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

    preventing phantom reads will help you to solve the concurrency problem (
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    scoreUp challenge 14 40
    bunnyEars2 challenge 6 45
    countX 22 48
    Understanding websocket example in spring 1 20
    If you have upgraded to Java2 update 10 on a Microsoft Windows client, you may have discovered that your Java application does not work as it did before.  For example, the colors of your Java2D graphic may be all wrong for no apparent reason. Aft…
    For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
    Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
    This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now