在一天内为实体生成唯一的序列号

发布于 2024-12-02 06:15:02 字数 438 浏览 0 评论 0原文

我需要为插入表中的实体生成唯一的编号。每个数字由实体创建日期和序列号组成：日期 + sn。序列号必须在第二天开始时重置。

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

如何在 MySQL 数据库中使用 Hibernate 上的 JPA（目前它们用于所有数据库交互）以及事务安全方式（可以同时插入实体）来完成此操作？

当然，我会欣赏所有其他方法的描述。谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美羊羊 2024-12-09 06:15:02

您可以在插入之前使用触发器。

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 位（或其他数字）可能是个好主意，如果您不想这样做，只需使用注释掉的行即可。

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.

回复收藏 0 原文

赠意 2024-12-09 06:15:02

您可以结合使用 @Johan 建议的数据库生成值和 @Generate(GenerationTime.INSERT) Hibernate Annotation Extensions。

2.4.3.5。生成的属性
某些属性是在插入或更新时由您生成的
数据库。 Hibernate 可以处理此类属性并触发
随后选择读取这些属性

creationDate 字段可以在 @PrePersit 回调事件

@PrePersit
在实体管理器持久化操作之前执行的实际上是
执行或级联。此调用与 persist 同步
操作。

示例（为了更清晰起见，省略 Getter 和 setter）

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

}

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

}

回复收藏 0 原文

~没有更多了~