休眠无法获得下一个序列值 [英] hibernate could not get next sequence value

问题描述

我有 gwt 应用程序连接到后端的 postgres 数据库，以及一个 java 类判断"映射数据库中的表判断"，当我尝试将判断持久化到数据库中时，它抛出了以下错误:

i have gwt application connect to postgres DB at the backend, and a java class 'Judgement' mapping the table 'judgements' in DB, when i tried to persistent a judgement into db, it threw the following errors:

Caused by: org.hibernate.exception.SQLGrammarException: could not get next sequence value
...
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist

我的判断课是这样的

@Entity
@Table(name = "JUDGEMENTS")
public class Judgement implements Serializable, Cloneable {

    private static final long serialVersionUID = -7049957706738879274L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "JUD_ID")
    private Long _judId;
...

而我的表判断是:

   Column    |            Type             |                        Modifiers                        
-------------+-----------------------------+---------------------------------------------------------
 jud_id      | bigint                      | not null default nextval('judgements_id_seq'::regclass)
 rating      | character varying(255)      | 
 last_update | timestamp without time zone | 
 user_id     | character varying(255)      | 
 id          | integer                     | 
Indexes:
    "judgements_pkey" PRIMARY KEY, btree (jud_id)
Foreign-key constraints:
    "judgements_id_fkey" FOREIGN KEY (id) REFERENCES recommendations(id)
    "judgements_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id)

我在数据库中有一个序列名称judgements_id_seq"

and i have a SEQUENCE name 'judgements_id_seq' in DB

谁能告诉我怎么了???谢谢.

can anyone tell me what's wrong??? thanks.

推荐答案

Hibernate 的 PostgreSQL 方言不是很聪明.它不知道您的每个 SERIAL 序列，并假设有一个名为hibernate_sequence"的全局数据库范围的序列可以使用.

Hibernate's PostgreSQL dialect isn't very bright. It doesn't know about your per-SERIAL sequences, and is assuming there's a global database-wide sequence called "hibernate_sequence" that it can use.

(UPDATE:当指定 GenerationType.IDENTITY 时，似乎较新的 Hibernate 版本可能使用默认的每表序列.测试您的版本并使用它而不是如果它适合您，请参见下文.)

(UPDATE: It appears that newer Hibernate versions may use the default per-table sequences when GenerationType.IDENTITY is specified. Test your version and use this instead of the below if it works for you.)

您需要更改映射以明确指定每个序列.这很烦人、重复而且毫无意义.

You need to change your mappings to explicitly specify each sequence. It's annoying, repetitive, and pointless.

@Entity
@Table(name = "JUDGEMENTS")
public class Judgement implements Serializable, Cloneable {

    private static final long serialVersionUID = -7049957706738879274L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="judgements_id_seq")
    @SequenceGenerator(name="judgements_id_seq", sequenceName="judgements_id_seq", allocationSize=1)
    @Column(name = "JUD_ID")
    private Long _judId;
...

allocationSize=1 非常重要.如果你省略它，Hibernate 会盲目地假设序列是用 INCREMENT 50 定义的，所以当它从序列中获取一个值时，它可以使用该值以及它下面的 49 个值 作为唯一生成的密钥.如果您的数据库序列增加 1 - 默认值 - 那么这将导致独特的违规，因为 Hibernate 尝试重新使用现有的键.

The allocationSize=1 is quite important. If you omit it, Hibernate will blindly assume that the sequence is defined with INCREMENT 50 so when it gets a value from a sequence it can use that value and the 49 values below it as unique generated keys. If your database sequences increment by 1 - the default - then this will result in unique violations as Hibernate tries to re-use existing keys.

请注意，一次获取一个密钥将导致每次插入的额外往返.据我所知，Hibernate 不能使用 INSERT ... RETURNING 来有效地返回生成的密钥，它显然也不能使用 JDBC 生成的密钥接口.如果你告诉它使用一个序列，它会调用 nextval 来获取值然后 insert 明确地，导致两次往返.为了降低成本，您可以在具有大量插入的键序列上设置更大的增量，记住在映射和基础数据库序列上设置它.这将导致 Hibernate 不那么频繁地调用 nextval 并缓存密钥块，以便在进行时分发.

Note that getting one key at a time will result in an additional round trip per insert. As far as I can tell Hibernate isn't capable of using INSERT ... RETURNING to efficiently return generated keys, nor can it apparently use the JDBC generated keys interface. If you tell it to use a sequence, it'll call nextval to get the value then insert that explicitly, resulting in two round trips. To reduce the cost of that, you can set a greater increment on key sequences with lots of inserts , remembering to set it on the mapping and the underlying database sequence. That'll cause Hibernate to call nextval less frequently and cache blocks of keys to hand out as it goes.

我相信你可以从上面看到，我不同意这里做出的 Hibernate 设计选择，至少从与 PostgreSQL 一起使用的角度来看是这样.他们应该使用 getGeneratedKeys 或使用 INSERT ... RETURNING 和 DEFAULT 作为键，让数据库处理这个，而无需 Hibernate自己为序列的名称或对它们的显式访问而烦恼.

I'm sure you can see from the above that I don't agree with the Hibernate design choices made here, at least from the perspective of using it with PostgreSQL. They should be using getGeneratedKeys or using INSERT ... RETURNING with DEFAULT for the key, letting the database take care of this without Hibernate having to trouble its self over the names of the sequences or explicit access to them.

顺便说一句，如果您将 Hibernate 与 Pg 一起使用，您可能还需要 Pg 的 oplock 触发器 以允许Hibernate 的乐观锁定与普通数据库锁定安全交互.如果没有它或类似的东西，您的 Hibernate 更新将倾向于破坏通过其他常规 SQL 客户端所做的更改.问我怎么知道的.

BTW, if you're using Hibernate with Pg you'll possibly also want an oplock trigger for Pg to allow Hibernate's optimistic locking to interact safely with normal database locking. Without it or something like it your Hibernate updates will tend to clobber changes made via other regular SQL clients. Ask me how I know.

这篇关于休眠无法获得下一个序列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！