postgresql 配置JPA让PostgreSQL生成主键值

Configure JPA to let PostgreSQL generate the primary key value

提问by Atais

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.

所以我们的项目使用PostgreSQL数据库，我们使用JPA来操作数据库。我们已经在 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;

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.

问题是现在应用程序不灵活，因为当我们直接修改数据库（使用 SQL 或其他工具）而不是通过 Java 应用程序时 - 生成的值低于实际的数据库 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，然后在创建过程后获取它？或者什么是更好的解决方案？谢谢。

EDITMore 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 ;)?

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

a short note on the answerThere 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...

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

now it even works using @GeneratedValue(strategy=GenerationType.IDENTITY)

现在它甚至可以使用 @GeneratedValue(strategy=GenerationType.IDENTITY)

回答by Craig Ringer

Given the table definition:

鉴于表定义：

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

Use the mapping:

使用映射：

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

    // ....

}

The naming tablename_columname_seqis the PostgreSQL default sequence naming for SERIALand I recommend that you stick to it.

命名tablename_columname_seq是 PostgreSQL 默认序列命名SERIAL，我建议您坚持使用它。

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

allocationSize=1如果您需要 Hibernate 与其他客户端合作访问数据库，这一点很重要。

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.

请注意，如果事务回滚，此序列将有“间隙”。事务可能因各种原因回滚。你的应用程序应该被设计来处理这个问题。

• Never assume that for any id nthere is an id n-1or n+1
• Never assume that the id nwas added or committed before an id less than nor 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.

• 永远不要假设任何 idn都有一个 idn-1或n+1
• 永远不要假设 idn是在小于 idn或大于id 的 id 之前添加或提交的n。如果你真的很小心地使用序列，你可以这样做，但你不应该尝试；在你的表中记录一个时间戳。
• 切勿添加或减去 ID。比较它们是否相等，仅此而已。

See the PostgreSQL documentation for sequencesand the serial data types.

有关序列和串行数据类型的信息，请参阅PostgreSQL 文档。

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;

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

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

---

---

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

如果您确实必须有一个无间隙序列（例如，支票或发票编号），请查看无间隙序列，但要认真地避免这种设计，并且永远不要将其用作主键。

---

---

Note: If your table definition looks like this instead:

注意：如果您的表定义如下所示：

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

and you're inserting into it using the (unsafe, do not use):

并且您使用（不安全，请勿使用）插入其中：

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

or (unsafe, never do this):

或（不安全，永远不要这样做）：

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

then you're doing it wrongand should switch to a sequence (as shown above) or to a correctgapless 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.

那么你做错了，应该切换到一个序列（如上所示）或使用锁定计数器表的正确无间隙序列实现（同样，请参见上文并参见 Google 中的“无间隙序列 postgresql”）。如果有多个连接在数据库上工作，则上述两种方法都会做错事。

回答by Denis Zevakhin

It seems you have to use the sequence generator like:

看来您必须使用序列生成器，例如：

@GeneratedValue(generator="YOUR_SEQ",strategy=GenerationType.SEQUENCE)

回答by Leszek

Please, try to use GenerationType.TABLEinstead of GenerationType.IDENTITY. Database will create separate table which will be use to generate unique primary keys, it will also store last used id number.

请尝试使用GenerationType.TABLE而不是GenerationType.IDENTITY. 数据库将创建单独的表，用于生成唯一的主键，它还将存储上次使用的 ID 号。

回答by Dave Jarvis

You can also save yourself some effort by writing a script to perform a mass conversion of the generic GenerationType.IDENTITYto the solution proposed by the selected answer. The below script has some slight dependencies on how the Java source file is formatted and will make modifications without backups. Caveat emptor!

您还可以通过编写脚本来将泛型批量转换为GenerationType.IDENTITY所选答案提出的解决方案，从而为自己节省一些精力。下面的脚本对 Java 源文件的格式有一些轻微的依赖，并且将在没有备份的情况下进行修改。买者自负！

After running the script:

运行脚本后：

1. Search and replace import javax.persistence.Table;with import javax.persistence.Table; import javax.persistence.SequenceGenerator;.
2. Reformat the source code in NetBeans as follows:
   1. Select all the source files to format.
   2. Press Alt+Shift+F
   3. Confirm reformatting.

1. 搜索并替换import javax.persistence.Table;为 import javax.persistence.Table; import javax.persistence.SequenceGenerator;。
2. 重新格式化 NetBeans 中的源代码，如下所示：
   1. 选择所有要格式化的源文件。
   2. 按 Alt+Shift+F
   3. 确认重新格式化。

Save the following script as update-sequences.shor similar:

将以下脚本另存为update-sequences.sh或类似：

#!/bin/bash

# Change this to the directory name (package name) where the entities reside.
PACKAGE=com/domain/project/entities

# Change this to the path where the Java source files are located.
cd src/main/java

for i in $(find $PACKAGE/*.java -type f); do
  # Only process classes that have an IDENTITY sequence.
  if grep "GenerationType.IDENTITY" $i > /dev/null; then
    # Extract the table name line.
    LINE_TABLE_NAME=$(grep -m 1 @Table $i | awk '{print ;}')
    # Trim the quotes (if present).
    TABLE_NAME=${LINE_TABLE_NAME//\"}
    # Trim the comma (if present).
    TABLE_NAME=${TABLE_NAME//,}

    # Extract the column name line.
    LINE_COLUMN_NAME=$(grep -m 1 -C1 -A3 @Id $i | tail -1)
    COLUMN_NAME=$(echo $LINE_COLUMN_NAME | awk '{print ;}')
    COLUMN_NAME=${COLUMN_NAME//\"}
    COLUMN_NAME=${COLUMN_NAME//,}

    # PostgreSQL sequence name.
    SEQUENCE_NAME="${TABLE_NAME}_${COLUMN_NAME}_seq"

    LINE_SEQ_GENERATOR="@SequenceGenerator( name = \"$SEQUENCE_NAME\", sequenceName = \"$SEQUENCE_NAME\", allocationSize = 1 )"
    LINE_GENERATED_VAL="@GeneratedValue( strategy = GenerationType.SEQUENCE, generator = \"$SEQUENCE_NAME\" )"
    LINE_COLUMN="@Column( name = \"$COLUMN_NAME\", updatable = false )\n"

    # These will depend on source code formatting.
    DELIM_BEGIN="@GeneratedValue( strategy = GenerationType.IDENTITY )"
    # @Basic( optional = false ) is also replaced.
    DELIM_ENDED="@Column( name = \"$COLUMN_NAME\" )"

    # Replace these lines...
    #
    # $DELIM_BEGIN
    # $DELIM_ENDED
    #
    # With these lines...
    #
    # $LINE_SEQ_GENERATOR
    # $LINE_GENERATED_VAL
    # $LINE_COLUMN

    sed -i -n "/$DELIM_BEGIN/{:a;N;/$DELIM_ENDED/!ba;N;s/.*\n/$LINE_SEQ_GENERATOR\n$LINE_GENERATED_VAL\n$LINE_COLUMN/};p" $i
  else
    echo "Skipping $i ..."
  fi
done

When generating the CRUD application using NetBeans, the ID attributes won't include editable input fields.

使用 NetBeans 生成 CRUD 应用程序时，ID 属性将不包括可编辑的输入字段。

回答by hang gao

It work for me

它对我有用

1. create table like this, use SERIAL.

1. 像这样创建表，使用SERIAL。

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

1. add @GeneratedValue(strategy = GenerationType.IDENTITY) at id field.

2. 在 id 字段添加 @GeneratedValue(strategy = GenerationType.IDENTITY)。

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

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

    // ....

}