Liquibase + Postgresql + Spring Jpa:Id自动递增问题 [英] Liquibase + Postgresql + Spring Jpa : Id auto increment issue

问题描述

我在实体中具有以下ID描述:

I have the following Id description in the entity:

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

用于生成此ID的Liquibase指令如下:

Liquibase instruction for generate this id is following :

  <column name="id" autoIncrement="true"  type="INT">
     <constraints nullable="false" primaryKey="true" primaryKeyName="pk_entity"/>
  </column>

我也有liquibase脚本，这些脚本会在此表中插入预定义的值，例如

Also I have liquibase scripts that insert to this table predefined values, e.g.

    <insert tableName="entityTable" schemaName="public">
        <column name="id">1</column>    
        <!- other fields-->
    </insert>

当我尝试使用Jpa存储库插入没有ID的新记录时出现了问题. 我收到诸如重复ID"之类的消息的错误. 因此，我知道jpa(hibernate)不会使用postgresql序列来获取新的id值.而且我不想在实体的id描述中包含序列名称.我希望Postgresql本身可以解决这种情况. 而且我不会使用"hibernate_sequence". 因此，任何想法我都可以解决此问题. 谢谢.

The problem has appeared when I try to insert a new record without id using Jpa repository. I got an error with a message like "duplicate id". So, I understand that jpa(hibernate) doesn't use postgresql sequence for getting a new id value. And I don't want to include the sequence name to the entity's id description. I have hoped that this situation could be resolved by the postgresql itself. And I wouldn't to use the 'hibernate_sequence'. So, any idea how I can resolve this issue. Thank you.

推荐答案

Liquibase的指令autoIncrement="true"为PostgreSQL生成serial列.对于serial列，PostgreSQL将创建一个名称类似于tablename_colname_seq的序列.默认列值将从该序列中分配.

Liquibase's instruction autoIncrement="true" generates serial column for PostgreSQL. For serial column PostgreSQL will create a sequence with a name like tablename_colname_seq. Default column values will be assigned from this sequence.

但是，当您明确地将一个值插入序列列时，它不会影响序列生成器，并且其下一个值也不会更改.这样就可以生成重复的值，这正是您的情况.

But when you explicitly insert a value into serial column, it doesn't affect sequence generator, and its next value will not change. So it can generate a duplicate value, which is exactly your case.

为防止这种情况，在插入显式值后，您需要使用ALTER SEQUENCE语句或setval()函数来更改序列生成器的当前值，例如:

To prevent this after you inserted explicit values you need to change the current value of a sequence generator either with ALTER SEQUENCE statement or with setval() function, e.g.:

ALTER SEQUENCE tablename_colname_seq RESTART WITH 42;

SELECT setval('tablename_colname_seq', (SELECT max(colname) FROM tablename));

这应该可以解决问题.

这篇关于Liquibase + Postgresql + Spring Jpa:Id自动递增问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！