PostgreSQL 串行类型的 Hibernate 注释

Hibernate annotation for PostgreSQL serial type

提问by Aditya R

I have a PostgreSQL table in which I have a column inv_seqdeclared as serial.

我有一个 PostgreSQL 表，其中有一列inv_seq声明为serial.

I have a Hibernate bean class to map the table. All the other columns are read properly except this column. Here is the declaration in the Hibernate bean class:

我有一个 Hibernate bean 类来映射表。除此列外，所有其他列均已正确读取。这是 Hibernate bean 类中的声明：

....
  ....
        @GeneratedValue(strategy=javax.persistence.GenerationType.AUTO)
        @Column(name = "inv_seq")
        public Integer getInvoiceSeq() {
            return invoiceSeq;
        }

         public void setInvoiceSeq(Integer invoiceSeq) {
        this.invoiceSeq = invoiceSeq;
    }
  ....
....

Is the declaration correct?
I am able to see the sequential numbers generated by the column in the database, but I am not able to access them in the java class.

声明是否正确？
我能够在数据库中看到由列生成的序列号，但我无法在 java 类中访问它们。

Please help.

请帮忙。

回答by Craig Ringer

Danger:Your question implies that you may be making a design mistake - you are trying to use a database sequence for a "business" value that is presented to users, in this case invoice numbers.

危险：您的问题暗示您可能犯了一个设计错误 - 您正在尝试将数据库序列用于呈现给用户的“业务”值，在本例中为发票编号。

Don't use a sequence if you need to anything more than test the value for equality. It has no order. It has no "distance" from another value. It's just equal, or not equal.

如果您只需要测试相等的值，请不要使用序列。它没有顺序。它与另一个值没有“距离”。它只是相等，或者不相等。

Rollback:Sequences are not generally appropriate for such uses because changes to sequences are't rolled back with transaction ROLLBACK. See the footers on functions-sequenceand CREATE SEQUENCE.

回滚：序列通常不适合此类用途，因为对序列的更改不会随事务回滚ROLLBACK。请参阅功能序列和的页脚CREATE SEQUENCE。

Rollbacks are expected and normal. They occur due to:

回滚是正常的。它们的发生是由于：

• deadlocks caused by conflicting update order or other locks between two transactions;
• optimistic locking rollbacks in Hibernate;
• transient client errors;
• server maintenance by the DBA;
• serialization conflicts in SERIALIZABLEor snapshot isolation transactions

• 两个事务之间的更新顺序或其他锁冲突导致的死锁；
• Hibernate 中的乐观锁定回滚；
• 暂时的客户端错误；
• 由 DBA 维护服务器；
• SERIALIZABLE或快照隔离事务中的序列化冲突

... and more.

... 和更多。

Your application will have "holes" in the invoice numbering where those rollbacks occur. Additionally, there is no ordering guarantee, so it's entirely possible that a transaction with a later sequence number will commit earlier (sometimes muchearlier) than one with a later number.

您的应用程序将在发生这些回滚的发票编号中存在“漏洞”。此外，没有顺序保证，所以这是完全有可能与后面的序列号的事务将更早（有时多比具有后数更早）。

Chunking:

分块：

It's also normal for some applications, including Hibernate, to grab more than one value from a sequence at a time and hand them out to transactions internally. That's permissible because you are not supposed to expect sequence-generated values to have any meaningful order or be comparable in any way except for equality. For invoice numbering, you want ordering too, so you won't be at allhappy if Hibernate grabs values 5900-5999 and starts handing them out from 5999 counting downor alternately up-then-down, so your invoice numbers go: n, n+1, n+49, n+2, n+48, ... n+50, n+99, n+51, n+98, [n+52 lost to rollback], n+97, .... Yes, the high-then-low allocator exists in Hibernate.

对于某些应用程序（包括 Hibernate）来说，一次从一个序列中获取多个值并在内部将它们分发给事务也是正常的。这是允许的，因为您不应该期望序列生成的值具有任何有意义的顺序或以任何方式进行比较，但相等性除外。对于发票的编号，你要订货，所以你不会在所有的幸福，如果休眠抓起值5900-5999和5999计数开始递出来下来或交替上再往下，所以你的发票号去：N， n+1, n+49, n+2, n+48, ... n+50, n+99, n+51, n+98, [n+52 回滚失败], n+97, .. .. 是的，Hibernate 中存在先高后低分配器。

It doesn't help that unless you define individual @SequenceGenerators in your mappings, Hibernate likes to share a single sequence for everygenerated ID, too. Ugly.

除非您@SequenceGenerator在映射中定义单独的s ，否则Hibernate 也喜欢为每个生成的 ID共享一个序列，这无济于事。丑陋的。

Correct use:

正确使用：

A sequence is only appropriate if you onlyrequire the numbering to be unique. If you also need it to be monotonic and ordinal, you should think about using an ordinary table with a counter field via UPDATE ... RETURNINGor SELECT ... FOR UPDATE("pessimistic locking" in Hibernate) or via Hibernate optimistic locking. That way you can guarantee gapless increments without holes or out-of-order entries.

仅当您只要求编号唯一时，才适合使用序列。如果您还需要它是单调和有序的，您应该考虑通过UPDATE ... RETURNING或SELECT ... FOR UPDATE（Hibernate 中的“悲观锁定”）或通过 Hibernate 乐观锁定使用带有计数器字段的普通表。这样你就可以保证没有空洞或乱序条目的无间隙增量。

What to do instead:

该怎么做：

Create a table just for a counter. Have a single row in it, and update it as you read it. That'll lock it, preventing other transactions from getting an ID until yours commits.

为计数器创建一个表。在其中包含一行，并在阅读时更新它。这将锁定它，防止其他事务在您提交之前获取 ID。

Because it forces all your transactions to operate serially, try to keep transactions that generate invoice IDs short and avoid doing more work in them than you need to.

因为它会强制您的所有交易按顺序运行，所以尽量保持生成发票 ID 的交易简短，并避免在其中做比您需要的更多的工作。

CREATE TABLE invoice_number (
    last_invoice_number integer primary key
);

-- PostgreSQL specific hack you can use to make
-- really sure only one row ever exists
CREATE UNIQUE INDEX there_can_be_only_one 
ON invoice_number( (1) );

-- Start the sequence so the first returned value is 1
INSERT INTO invoice_number(last_invoice_number) VALUES (0);

-- To get a number; PostgreSQL specific but cleaner.
-- Use as a native query from Hibernate.
UPDATE invoice_number
SET last_invoice_number = last_invoice_number + 1
RETURNING last_invoice_number;

Alternately, you can:

或者，您可以：

• Define an entity for invoice_number, add a @Versioncolumn, and let optimistic locking take care of conflicts;
• Define an entity for invoice_number and use explicit pessimistic locking in Hibernate to do a select ... for update then an update.

• 为invoice_number定义一个实体，添加一@Version列，让乐观锁处理冲突；
• 为invoice_number 定义一个实体，并在Hibernate 中使用显式悲观锁定来执行select ... for update 然后是更新。

All these options will serialize your transactions - either by rolling back conflicts using @Version, or blocking them (locking) until the lock holder commits.?Either way, gapless sequences will reallyslow that area of your application down, so only use gapless sequences when you have to.

所有这些选项都将序列化您的事务 - 通过使用 @Version 回滚冲突，或阻止它们（锁定）直到锁持有者提交。？无论哪种方式，无间隙序列都会真正减慢您应用程序的该区域，因此仅使用无间隙序列当你不得不这样做的时候。

@GenerationType.TABLE: It's tempting to use @GenerationType.TABLEwith a @TableGenerator(initialValue=1, ...). Unfortunately, while GenerationType.TABLE lets you specify an allocation size via @TableGenerator, it doesn't provide any guarantees about ordering or rollback behaviour. See the JPA 2.0 spec, section 11.1.46, and 11.1.17. In particular "This specification does not define the exact behavior of these strategies.and footnote 102 "Portable applications should not use the GeneratedValue annotation on other persistent fields or properties [than @Idprimary keys]". So it is unsafe to use @GenerationType.TABLEfor numbering that you require to be gapless or numbering that isn't on a primary key property unless your JPA provider makes more guarantees than the standard.

@GenerationType.TABLE：很容易@GenerationType.TABLE与@TableGenerator(initialValue=1, ...). 不幸的是，虽然 GenerationType.TABLE 允许您通过 @TableGenerator 指定分配大小，但它不提供有关排序或回滚行为的任何保证。请参阅 JPA 2.0 规范，第 11.1.46 和 11.1.17 节。特别是“本规范没有定义这些策略的确切行为。和脚注 102 “便携式应用程序不应在其他持久字段或属性上使用 GeneratedValue 注释 [比@Id主键]”。因此，将其@GenerationType.TABLE用于编号是不安全的除非您的 JPA 提供程序提供比标准更多的保证，否则要求是无间隙的或不在主键属性上的编号。

If you're stuck with a sequence:

如果你被一个序列卡住了：

The poster notes that they have existing apps using the DB that use a sequence already, so they're stuck with it.

海报指出，他们有使用数据库的现有应用程序已经使用了一个序列，所以他们坚持使用它。

The JPA standard doesn't guarantee that you can use generated columns except on @Id, you can (a) ignore that and go ahead so long as your provider does let you, or (b) do the insert with a default value and re-read from the database. The latter is safer:

JPA 标准不保证您可以使用除@Id 之外的生成列，您可以 (a) 忽略它并继续，只要您的提供者允许您，或者 (b) 使用默认值进行插入并重新- 从数据库中读取。后者更安全：

    @Column(name = "inv_seq", insertable=false, updatable=false)
    public Integer getInvoiceSeq() {
        return invoiceSeq;
    }

Because of insertable=falsethe provider won't try to specify a value for the column. You can now set a suitable DEFAULTin the database, like nextval('some_sequence')and it'll be honoured. You might have to re-read the entity from the database with EntityManager.refresh()after persisting it - I'm not sure if the persistence provider will do that for you and I haven't checked the spec or written a demo program.

因为insertable=false提供者不会尝试为列指定值。您现在可以DEFAULT在数据库中设置一个合适的，就像nextval('some_sequence')它会受到尊重。EntityManager.refresh()在持久化实体后，您可能必须从数据库中重新读取实体- 我不确定持久性提供程序是否会为您执行此操作，而且我还没有检查规范或编写演示程序。

The only downside is that it seems the column can't be made @ NotNull or nullable=false, as the provider doesn't understand that the database has a default for the column. It can still be NOT NULLin the database.

唯一的缺点是该列似乎无法通过 @NotNull 或 来创建nullable=false，因为提供程序不了解数据库对该列具有默认值。它仍然可以NOT NULL在数据库中。

If you're lucky your other apps will also use the standard approach of either omitting the sequence column from the INSERT's column list or explicitly specifying the keyword DEFAULTas the value, instead of calling nextval. It won't be hard to find that out by enabling log_statement = 'all'in postgresql.confand searching the logs. If they do, then you can actually switch everything to gapless if you decide you need to by replacing your DEFAULTwith a BEFORE INSERT ... FOR EACH ROWtrigger function that sets NEW.invoice_numberfrom the counter table.

如果幸运的话，您的其他应用程序也将使用标准方法，即从INSERT的列列表中省略序列列或明确指定关键字DEFAULT作为值，而不是调用nextval. 这不会是很难找到了通过使log_statement = 'all'在postgresql.conf和搜索日志。如果他们这样做，那么您实际上可以将所有内容切换到无间隙，如果您决定需要通过从计数器表设置DEFAULT的BEFORE INSERT ... FOR EACH ROW触发器函数替换您的NEW.invoice_number。

回答by ams

I have found that hibernate 3.6 tries to use a single sequence for all entities when you set it to AUTO so in my application I use IDENTITY as the generation strategy.

我发现当您将 hibernate 3.6 设置为 AUTO 时，它会尝试对所有实体使用单个序列，因此在我的应用程序中，我使用 IDENTITY 作为生成策略。

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

@Craig had some very good points about invoice numbers needing to incrementing if you are presenting them to users and he suggested using a table for that. If you do end up using a table to store the next id you might be able to use a mapping similar to this one.

@Craig 有一些非常好的关于发票编号需要递增的观点，如果您将它们呈现给用户，他建议为此使用表格。如果您最终使用表来存储下一个 id，您可能可以使用与此类似的映射。

@Column(name="Id")
@GeneratedValue(strategy=GenerationType.TABLE,generator="user_table_generator")
@TableGenerator(
    name="user_table_generator", 
    table="keys",
    schema="primarykeys",
    pkColumnName="key_name",
    pkColumnValue="xxx",
    valueColumnName="key_value",
    initialValue=1,
    allocationSize=1)
private Integer id; 

回答by KeithL

Depending on your situation, this may not work. There is a bug opened against Hibernate that documents this behavior.

根据您的情况，这可能不起作用。有一个针对 Hibernate 的错误记录了这种行为。

http://opensource.atlassian.com/projects/hibernate/browse/HHH-4159

http://opensource.atlassian.com/projects/hibernate/browse/HHH-4159

If you are open to using a mapping file instead of annotations, I have been able to recreate the issue (NULL's in SERIAL columns that are not part of the primary key). Using the "generated" attribute of the property element causes Hibernate to re-read the row after an insert to pick up the generated column value:

如果您愿意使用映射文件而不是注释，我已经能够重新创建问题（SERIAL 列中的 NULL 不是主键的一部分）。使用属性元素的“生成”属性会导致 Hibernate 在插入后重新读取行以获取生成的列值：

<class name="Ticket" table="ticket_t">
    <id name="id" column="ticket_id">
        <generator class="identity"/>
    </id>
    <property name="customerName" column="customer_name"/>
    <property name="sequence" column="sequence" generated="always"/>
</class>

回答by Andre

I use Postgres + Hibernate in my projects and this is what I do:

我在我的项目中使用 Postgres + Hibernate，这就是我所做的：

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY, generator = "hibernate_sequence")
@SequenceGenerator(name = "hibernate_sequence", sequenceName = "hibernate_sequence")
@Column(name = "id", unique = true, nullable = false)
protected Long id;

public Long getId() {
    return id;
}
public void setId(Long id) {
    this.id = id;
}

It works just fine for me.

它对我来说很好用。