在MySQL和Hibernate中使用序列 [英] Using sequence in MySQL and Hibernate

问题描述

我正在开发一个使用Hibernate和MySQL的项目。我打算使用序列来为数据库中的所有表生成ID。 （很难描述我的问题，所以我会以你为例）。

例如：我有2张表A& B.首先，我向表A插入10条记录，它们的ID将为1到10 。然后，我向表B插入10条记录，并且我想他们的ID将是11-20 ，而不是1-10。这意味着生成的ID值将被数据库中所有表中的所有记录计数，而不是单个表中。

那么我怎么能在MySQL中使用这个概念呢？声明和语法？
在Hibernate中，如何使用数据模型上的策略和生成器将生成的策略应用于数据库？
非常感谢！

解决方案

您在Mysql中没有序列，但您可以使用'TABLE 'id generation

  @ javax.persistence.TableGenerator（
 name =EMP_GEN，
 table = GENERATOR_TABLE，
 pkColumnName =key，
 valueColumnName =hi
 pkColumnValue =EMP，
 allocationSize = 20 
）
 
 @Entity 
 public class Klass {
 
 @Id 
 @GeneratedValue（strategy = GenerationType.TABLE，generator = EMP_GEN）
 @Column（name = ID）
私人长ID; 
} 
  

您可能需要添加片段 @javax。所有实体中的persistence.TableGenerator

非常感谢。但是，我们是否有另一种方式来使用该片段而不将其添加到每个实体？当在数据库上创建表时，如何为数据库管理系统声明ID主键列以自动生成类似的值？ - napoleonit76

不幸的是，我不知道一个优雅的方法来做到这一点:(在我的项目中，我们使用一个序列在几个实体中，我们需要在每个类中声明序列
您可以尝试的一件事（但我不太喜欢）是为所有实体创建一个超类，并且该超类只包含ID字段和注释我觉得我已经看到了这个项目，但我不是100％肯定。

关于告诉数据库使用这种策略，我认为这是不可能的，你可以尝试添加一个触发器并将其应用于模式中的每个表，当你在表中插入一个新行时触发器会触发，从 generator table ，然后将其添加到行中，我真的不知道这是否可行，并且我确信如果您有两个并发会话创建行，则这不起作用。 / p>

您能否重新考虑您的策略并使用正常的自动输入功能？为ID添加列，并将序列号放在表的另一列中？

I'm working on a project that uses Hibernate and MySQL. I intend to use sequence to generate ID for all tables in database. (It's hard to describe my question, so I will show you as an example).

For example: I have 2 tables A & B. Firstly, I insert 10 records to table A, and their IDs will be 1 to 10. Then, I insert 10 records to table B, and I want their IDs will be 11-20, not 1-10. It means the generated ID value will be counted by all records in all tables in databases, not in a single table.

So how can I use that concept in MySQL? Declare and syntax? In Hibernate, how can I use the strategy and generator on data model to apply that generated strategy in database? Thank you so much!

解决方案

You don't have sequences in Mysql, but you can use the 'TABLE' id generation

@javax.persistence.TableGenerator(
    name="EMP_GEN",
    table="GENERATOR_TABLE",
    pkColumnName = "key",
    valueColumnName = "hi"
    pkColumnValue="EMP",
    allocationSize=20
)

@Entity
public class Klass {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE, generator=EMP_GEN)
    @Column(name = "ID")
    private Long id;
}

You might need to add the snippet @javax.persistence.TableGenerator [...] in all your entities

Edit

Thank you so much. But do we have another way to use that snippet without adding it to each entity? And when creating tables on database, how can I declare the ID primary key column for DBMS to auto-generate the value like that? – napoleonit76

Unfortunately, I don't know an elegant way to do this :(. In my project, we use the a sequence in several entities, and we need to declare the sequence in each class. One thing you could try (but I don't really like) is to create a super class for all of your entities, and that superclass only contains the ID field and the annotations. I have the feeling that I've seen this in a project, but I'm not 100% sure.

About telling the DB to use that strategy, I don't think it's really possible. You can try to add a trigger and apply it to each table in the schema. The trigger will fire when you insert a new row in the table, pick a value from the generator table, and add it to the row. I honestly don't know if this might work, and I'm 99% sure that this won't work if you have 2 concurrent sessions creating rows.

Can you rethink your strategy and use normal auto-increment columns for the ids and put the sequential number in another column of your tables?

这篇关于在MySQL和Hibernate中使用序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！