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

问题描述

我有gwt应用程序连接到后端的postgres数据库，以及一个java类'判断'映射数据库中的'判断'，当我试图执行db判断时，它抛出以下错误：

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

我的判断类看起来像这样

my Judgement class looks like this

@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;
...

我的表格判断是：

and my table judgements is:

   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)

我有一个SEQUENCE名字'judgements_id_seq '在DB

and i have a SEQUENCE name 'judgements_id_seq' in DB

有谁能告诉我什么问题？谢谢。

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

推荐答案

Hibernate的PostgreSQL方言不是很明亮。它不知道每个序列的序列，并假设它有一个全局数据库范围的序列，称为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.

请注意， time will 会导致每次插入的额外往返行程。据我所知，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.

BTW，如果您将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.

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