Create a unique serial number for an entity in one day

Question

I need to create unique numbers for objects inserted in a table. Each number consists of the date the object was created and the serial number: date + sn. Serial numbers should be reset early 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 to do this using JPA over Hibernate (they are currently used for all interactions with the database) and a safe transaction method (entities can be inserted at the same time) in the MySQL database?

Of course, I would appreciate a description of all the other approaches. Thank.

Answer 1

You can use a trigger before inserting.

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 ;

, newdayid 8 ( - ), , .

Answer 2

, , @Johan, @Generated(GenerationTime.INSERT) Hibernate.

2.4.3.5.

. ,

creationDate @PrePersit , JPA

@PrePersit

, . .

(Getter setter ommit )

@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();
    }

}