配置JPA让PostgreSQL生成主键值 [英] Configure JPA to let PostgreSQL generate the primary key value

问题描述

所以我们的项目使用PostgreSQL数据库，我们使用JPA来操作数据库。
我们已经使用Netbeans 7.1.2中的自动创建程序从数据库创建了实体。

So our project use PostgreSQL database and we use JPA for operating the database. We have created the entities from the database with automatic creator in Netbeans 7.1.2.

稍微更改后，我们的主键值被描述为：

After small changes our primary key values are described as:

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Basic(optional = false)
@NotNull
@Column(name = "idwebuser", nullable = false)
private Integer idwebuser;

问题是现在应用程序不灵活，因为我们直接修改数据库（使用SQL）或者其他工具）而不是通过Java应用程序 - 生成的值低于实际的数据库ID值 - 因此我们在创建新实体期间出错。

The problem is that now the application is not flexible, because when we modify the database directly (using SQL or another tool) instead of going thru the Java app - the Generated Value is lower than actual database ID value - and so we get error during the creation of new entities.

是否有可能JPA可以让数据库自动生成ID，然后在创建过程后获取它？
或者什么是更好的解决方案？
谢谢。

Is there a possibility that the JPA could just let the database generate the ID automatically and then obtain it after the creation process? Or what could be a better solution? Thanks.

编辑
更具体地说：
我们有一个用户表，我的问题是使用任何类型的策略生成类型，JPA正在插入一个具有由其生成器ID指定的新实体。这对我来说是错误的，因为如果我自己对表进行更改，通过添加新条目，应用程序的GeneratedValue低于当前ID - 这导致我们出现重复ID异常。
我们可以修理它吗？）？

EDIT More specifically: We have a table of users and my problem is that using any type of strategy generationtype, the JPA is inserting a new entity with a specified by it's generator id. Which is wrong for me, because if I make changes to the table on my own, by adding new entries, the GeneratedValue for application is lower than the current ID - what leads us to exception with duplicated ID. Can we fix it ;)?

关于答案的简短说明
我有一点谎言因为我们使用了PG管理员 - >查看前100行和编辑的行而不是使用选择。但是，事实证明，这个编辑器以某种方式跳过了更新ID的过程，所以即使在DB中，当我们编写一个正确的INSERT时，它也会被执行不正确的ID！所以它基本上是我们使用的编辑器比数据库和应用程序更多的问题......

a short note on the answer There was a little lie from my side because we've used a PG Admin -> View first 100 Rows and edited rows from there instead of using select. ANYWAY, it turns out that this editor somehow skips the process of updating the ID and so even in DB when we write a proper INSERT it is EXECUTED with improper ID! So it was basically more a problem of the editor we used than the database and application...

现在它甚至可以使用 @GeneratedValue（策略） = GenerationType.IDENTITY）

推荐答案

根据表格定义：

CREATE TABLE webuser(
    idwebuser SERIAL PRIMARY KEY,
    ...
)

使用映射：

@Entity
@Table(name="webuser")
class Webuser {

    @Id
    @SequenceGenerator(name="webuser_idwebuser_seq",
                       sequenceName="webuser_idwebuser_seq",
                       allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator="webuser_idwebuser_seq")
    @Column(name = "idwebuser", updatable=false)
    private Integer id;

    // ....

}

命名 tablename_columname_seq 是 SERIAL 命名的PostgreSQL默认序列，我建议你坚持下去。

The naming tablename_columname_seq is the PostgreSQL default sequence naming for SERIAL and I recommend that you stick to it.

如果您需要Hibernate与其他客户端合作到数据库，那么 allocationSize = 1 非常重要。

The allocationSize=1 is important if you need Hibernate to co-operate with other clients to the database.

请注意，如果事务回滚，此序列中将包含间隙。由于各种原因，事务可以回滚。您的应用程序应设计为能够应对此问题。

Note that this sequence will have "gaps" in it if transactions roll back. Transactions can roll back for all sorts of reasons. Your application should be designed to cope with this.

• 永远不要假设任何id n 有一个id n-1 或 n + 1


• 永远不要假设id n 在id小于 n 之前或在id大于<$之后添加或提交C $ C>ñ。如果你对如何使用序列非常小心，你可以这样做，但你永远不应该尝试;改为在表中记录时间戳。


• 永远不要添加或减去ID。比较它们是否相等而不是别的。

• Never assume that for any id n there is an id n-1 or n+1
• Never assume that the id n was added or committed before an id less than n or after an id greater than n. If you're really careful with how you use sequences you can do this, but you should never try; record a timestamp in your table instead.
• Never add to or subtract from an ID. Compare them for equality and nothing else.

参见序列的PostgreSQL文档和串行数据类型。

他们解释说上面的表定义基本上是一个快捷方式： / p>

They explain that the table definition above is basically a shortcut for:

CREATE SEQUENCE idwebuser_id_seq;
CREATE TABLE webuser(
    idwebuser integer primary key default nextval('idwebuser_id_seq'),
    ...
)
ALTER SEQUENCE idwebuser_id_seq OWNED BY webuser.idwebuser;

...这应该有助于解释为什么我们添加了 @SequenceGenerator 用于描述序列的注释。

... which should help explain why we have added a @SequenceGenerator annotation to describe the sequence.

如果你真的必须有一个无间隙序列（对于例如，检查或发票编号）请参阅无间隙序列，但严重的是，请避免使用此设计，永远不会将它用作主键。

If you really must have a gap-less sequence (for example, cheque or invoice numbering) see gapless sequences but seriously, avoid this design, and never use it for a primary key.

注意：如果你的表定义看起来像这样：

Note: If your table definition looks like this instead:

CREATE TABLE webuser(
    idwebuser integer primary key,
    ...
)

你使用（不安全，不要使用）：

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT max(idwebuser) FROM webuser)+1, ...
);

或（不安全，从不这样做）：

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT count(idwebuser) FROM webuser), ...
);

然后你做错了并且应该切换到一个序列（如上所示）或使用锁定计数器表的正确无间隙序列实现（再次参见上文并参见Google中的无间隙序列postgresql）。如果数据库上有多个连接，则上述操作都是错误的。

then you're doing it wrong and should switch to a sequence (as shown above) or to a correct gapless sequence implementation using a locked counter table (again, see above and see "gapless sequence postgresql" in Google). Both the above do the wrong thing if there's ever more than one connection working on the database.

这篇关于配置JPA让PostgreSQL生成主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！