Generate unique sequence number for entity during one day

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?

O开发者_开发百科f course, I will appreciate the descriptions of all other approaches. Thanks.

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.

You can use a combination of database generated value like @Johan propose and the use of the @Generated(GenerationTime.INSERT) Hibernate Annotation Extensions.

2.4.3.5. Generated properties

Some properties are generated at insert or update time by your database. Hibernate can deal with such properties and triggers a subsequent select to read these properties

The creationDate field can be set in the @PrePersit callback event supported by JPA

@PrePersit

Executed before the entity manager persist operation is actually executed or cascaded. This call is synchronous with the persist operation.

Example (Getter and setter ommit for better clarity)

@Entity
public class Entity{

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

    @Generated(GenerationTime.INSERT) @Column(insertable = false)
    private String uniqueNumber;

    @Temporal(TemporalType.TIMESTAMP)
    private Date creationDate

    @PrePersist
    public void prePersit()
    {
        creationDate = new Date();
    }

}