Generate unique sequence number for entity during one day

Question

I need to generate unique numbers for entities inserted into a table. Each number consists from entity creation date and serial number: date + sn. Serial numbers must be reset at the beginning of next day.

| id | creation date | unique number |
--------------------------------------
| 1  | Sep 1, 2010   | 201009011     |
| 2  | Sep 1, 2010   | 201009012     |
| 3  | Sep 2, 2010   | 201009021     |
| 4  | Sep 2, 2010   | 201009022     |

How can it be done using JPA over Hibernate (currently they are used for all database interactions) and in transaction safe way (entities can be inserted simultaneously) in MySQL database?

Of course, I will appreciate the descriptions of all other approaches. Thanks.

Answer 1

You can use a trigger before insert.

DELIMITER $$

CREATE TRIGGER bi_table1_each BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
  DECLARE creationdate DATE;
  DECLARE newdayid INTEGER;

  SET creationdate = new.`creation date`;
  SELECT count(*) + 1 INTO newdayid FROM table1 
    WHERE table1.`creation date` = creationdate;

  -- NEW.`unique number` = CONCAT(DATE_FORMAT(creationdate,'%Y%m%d'),newdayid);
  NEW.`unique number` = CONCAT(DATE_FORMAT(creationdate,'%Y%m%d')
                               RIGHT(CONCAT('00000000',newdayid),8));

END $$

DELIMITER ;

Note that it might be a good idea to fix the number of decimals in newdayid to 8 digits (or whatever), if you don't want that, just use the commented out line.