-2

I have made a simple java program that can generate ID using date as prefix and increment number as postfix like 150801 where 15 is the year, 08 is the month and 01 is an increment number. Since each users save the data in the database and get one unique generated ID, is it possible a users can get the same generated ID if two or more users save the data at the same time?

Akashah Amin
  • 113
  • 2
  • 11
  • 1
    I would recommend taking a look here : http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html . For your id, it really depends on how you increment your index, is it ThreadSafe ? – Michael Laffargue Aug 13 '15 at 08:27
  • 2
    That depends quite a bit on how the generated ID's are generated. You have to tell us how it's generated in order for us to be able to answer. – skyking Aug 13 '15 at 08:43
  • @MichaelLaffargue it is not ThreadSafe. I don't use UUID because the length is too long and not follow the format that I need. – Akashah Amin Aug 14 '15 at 17:53

6 Answers6

3

One easy workaround to your problem would be to let your database keep track of the unique identifier for you by using an autoincrement column.

Create your table along these lines:

CREATE TABLE your_table (
     id BIGINT NOT NULL AUTO_INCREMENT,
     username CHAR(30) NOT NULL,
     ... other fields go here ...
     PRIMARY KEY (id)
)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I don't plan to use database auto_increment function since it does not follow with required ID format. – Akashah Amin Aug 13 '15 at 08:49
  • 1
    @AkashahAmin The format you have specified only handles 100 new users per month. You are almost certainly going to have to change it. This is a very good solution. – user207421 Aug 13 '15 at 23:27
  • yeah, you right. I already change it to be 5 figure as a postfix, so it can be limited up to 99,999 unique id per month. I also discovered that you can use database trigger to generate unique id with required format, can be read on this [link](http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix). Soon I will post the answer. Thumbs up ! – Akashah Amin Aug 14 '15 at 03:21
2

You can use Java's UUID class to create UUID values as unique identifiers. You will be able to get the timestamp from the generated UUID if required at a later point of time.

UUID.randomUUID().toString()

You would be able to get the timestamp from this string at a later pint of time if required. How do I extract a date from a UUID using Java?

Community
  • 1
  • 1
jozzy
  • 2,863
  • 3
  • 15
  • 12
  • 1
    No you certainly would *not* get the timestamp from that string. The random version ([Version 4](https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_.28random.29)) of UUID does *not* contain any timestamp. Only some [versions of UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier#Variants_and_versions) have a timestamp embedded. Even then, extracting a timestamp would be an abuse of a UUID. UUIDs were never intended to be used as a date-time communication device. – Basil Bourque Aug 13 '15 at 22:21
  • I already thought to use UUID before but UUID length is too long. So I decide to create my own ID generator. – Akashah Amin Aug 14 '15 at 17:46
1

First, the code that's handling the increment number should be thread-safe, otherwise multiple users can have the same postfix.

Also, considering an identifier of this form, it means that for each month, there are only 99 unique increment number i.e. 99 unique identifier per month. That could be enough for your requirement but it is unlikely.

You should consider generating identifier using the UUID class.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
0

If your application is multithreaded then answer is YES. Set unique constraint on ID in your database and two users with the same ID will not be inserted. However I would recommend another ID generator which would generate more unique IDs than your one.

wawek
  • 1,577
  • 1
  • 13
  • 23
0

You need some kind of locking mechanism to prevent simultaneous users from getting the same ID.

There are several ways to achieve this:

  1. Make the ID column unique and retry with a new number if you get a constraint violation.
  2. Use a number generator provided by the database (Oracle sequence, AUTOINCREMENT for MS SQL Server etc).
  3. Create a table that holds the sequence value and lock the value as you read it (using SELECT xxx FOR UPDATE...).
Klas Lindbäck
  • 33,105
  • 5
  • 57
  • 82
0

I already make MySQL trigger to generated the unique ID with the format that I need.

CREATE TABLE mytable(
   id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
   name VARCHAR(20)
);

Here is the trigger script:

DELIMITER $$
CREATE TRIGGER table1_trigger
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
DECLARE postfix INT(4) ZEROFILL;
DECLARE newID varchar(10);

SET @prefix = DATE_FORMAT(CURDATE(),'%y%m');
SET @wlike = CONCAT(@prefix, '%');
SET @previous_id = (SELECT id FROM table1 WHERE id LIKE @wlike ORDER BY id DESC LIMIT 1);
SET postfix = CAST(SUBSTRING(@previous_id, 5, 8) AS SIGNED);

IF postfix != 9999 THEN
  SET postfix = postfix + 1;
END IF;

SET NEW.id = CONCAT(@prefix, postfix);
END$$
DELIMITER;
Akashah Amin
  • 113
  • 2
  • 11