在 DerbyDB 上使用 jpa 自动生成 Primary_key 时出现问题

发布于 2024-09-28 21:03:49 字数 848 浏览 6 评论 0原文

我有 jpa 注释的实体类，如：

@Configurable
@Entity
@Table(name="PLAYERS")
public class Player
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="ID")
    private Integer id;
    @Column(name="NAME")
    private String name;

    @PersistenceContext
        public transient EntityManager entityManager;

    ...
}

这一直工作得很好，直到我决定使用如下语法创建带有备份的 yaml 数据的表：

createNativeQuery("INSERT INTO PLAYERS ...")

成功创建后，当我尝试使用以下命令创建实体时：

Player player = new Player();
player.setName("new player");
player.persist();

我收到错误：

SQL Error: -1, SQLState: 23505

与 Primary_keys 的重复相关，因为为新实体生成的 id = 1（与从备份数据检索的行相同）。当然，我可以使用 jpa/java 语法从备份文件中检索数据，但在这种情况下，我无法控制插入数据的主键等。如何解决这个问题？有没有办法在插入备份数据后更新 id_generator ？

原文

I have jpa annotated entity class like:

@Configurable
@Entity
@Table(name="PLAYERS")
public class Player
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="ID")
    private Integer id;
    @Column(name="NAME")
    private String name;

    @PersistenceContext
        public transient EntityManager entityManager;

    ...
}

This has worked fine until I decided to create table with backuped yaml data using syntax like:

createNativeQuery("INSERT INTO PLAYERS ...")

After successful creation when I try to create an entity with:

Player player = new Player();
player.setName("new player");
player.persist();

i got error:

SQL Error: -1, SQLState: 23505

related to the duplication of primary_keys, because id generated for new entity = 1 (the same as row retrived from backuped data). Of course I can retrive data from backup file by using jpa/java syntax but in this case I have no control over primary keys of inserted data etc.
How to solve this problem ? Is there any way to update id_generator after the insertion of backuped data ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

电影里的梦 2024-10-05 21:03:49

由于您手动插入数据，因此需要更改表以更改标识列的起始值：

ALTER TABLE PLAYERS ALTER COLUMN ID RESTART WITH 1234;

其中 1234 是备份数据的最大 id。

更多详细信息，请参阅 ALTER TABLE 语句的文档：

使用整数常量重新启动
指定下一个值
为标识列生成。
RESTART WITH 对于表很有用
有一个身份列是
定义为默认生成并且
其上定义了唯一的键
身份栏。因为生成者
DEFAULT 允许手动插入和
系统生成的值是
可能手动插入值
可能与系统生成冲突
价值观。为了解决此类冲突，
使用 RESTART WITH 语法来指定
将生成的下一个值
对于身份列。考虑
下面的例子，其中涉及到
自动生成的组合
数据和手动插入的数据：
创建表 tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
在 tauto(i) 上创建唯一索引 tautoInd
插入 tauto(k) 值 1,2
系统会自动生成
标识列的值。??但
现在你需要手动插入一些
数据写入标识列：
插入 tauto 值 (3,3)
插入 tauto 值 (4,4)
插入 tauto 值 (5,5)
标识列已使用值 1
此时已经到了5了。？如果你现在
希望系统产生一个价值，
系统会生成3，即
将导致唯一的键异常
因为值 3 已经是
手动插入。？？？为了补偿
对于手动插入，发出 ALTER
身份的 TABLE 语句
RESTART WITH 6 列：
更改表 tauto 更改列 i 重新启动 6

Because you inserted data manually, you need to alter the table to change the start value of the identity column:

ALTER TABLE PLAYERS ALTER COLUMN ID RESTART WITH 1234;

Where 1234 is the max id of your backup data.

More details in the documentation of the ALTER TABLE statement:

RESTART WITH integer-constant
specifies the next value to be
generated for the identity column.
RESTART WITH is useful for a table
that has an identity column that was
defined as GENERATED BY DEFAULT and
that has a unique key defined on that
identity column. Because GENERATED BY
DEFAULT allows both manual inserts and
system generated values, it is
possible that manually inserted values
can conflict with system generated
values. To work around such conflicts,
use the RESTART WITH syntax to specify
the next value that will be generated
for the identity column. Consider the
following example, which involves a
combination of automatically generated
data and manually inserted data:
CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2
The system will automatically generate
values for the identity column.?? But
now you need to manually insert some
data into the identity column:
INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)
The identity column has used values 1
through 5 at this point.?? If you now
want the system to generate a value,
the system will generate a 3, which
will result in a unique key exception
because the value 3 has already been
manually inserted.????To compensate
for the manual inserts, issue an ALTER
TABLE statement for the identity
column with RESTART WITH 6:
ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6