英文:
Manually insert Data from database in table which has hibernate_sequence @GeneratedValue(strategy = GenerationType.TABLE)
问题
我有一个带有ID的表，使用@GeneratedValue(strategy = GenerationType.TABLE)。需要在PostgreSQL生产数据库中手动插入800条记录。我们从Oracle迁移到PostgreSQL，一些用户错误地访问了旧链接，并且数据被插入了Oracle数据库。请解释在这种情况下如何维护hibernate_sequence。因为这是生产数据库。
英文:
I have table with ID @GeneratedValue(strategy = GenerationType.TABLE). Need to insert 800 records manually on postgresql production database.We had migration from oracle to postgresql , few User mistakenly access old link and data inserted in oracle . Please explain how to maintain hibernate_sequence in this case. As its production database.
答案1
得分: 1
在第一步中，检查您的密钥生成表和 SEQUENCE_NAME。
如果您使用默认设置，您将看到如下内容 - 根据不同的配置调整后续步骤。
select * from hibernate_sequences;
SEQUENCE_NAME NEXT_VAL
--------------- ----------
default nnnnn
现在，将 NEXT_VAL 增加所请求的跳过记录数。为了避免通过 Hibernate 进行并发插入的问题，我使用了 LOCK TABLE - 所有来自 Hibernate 的插入都会被阻塞，直到您完成下面的操作。
lock table hibernate_sequences in exclusive mode;
select * from hibernate_sequences;
-- 记住 NEXT_VAL 的值 nnnn
update hibernate_sequences
set NEXT_VAL = NEXT_VAL + 800
where SEQUENCE_NAME = 'default';
commit;
现在，您有了 800 个 ID，从 nnnn + 1 到 nnnn + 800，可以用于您跳过的行。
请注意，如果您使用表 hibernate_sequences 的所有者连接，LOCK 将起作用。如果应用程序关闭并且没有插入操作，则可以完全跳过 LOCK。
使用与 Hibernate show_sql 中相同的 INSERT，并通过您的数据传递保留的 ID。
示例
-- Hibernate: insert into AUTHOR (name, AUTHOR_ID) values (?, ?)
insert into AUTHOR (name, AUTHOR_ID) values ('Psik', nnnn + 1);
insert into AUTHOR (name, AUTHOR_ID) values ('Tuzka', nnnn+2);
警告
正如评论中所提到的，您应该计划切换到 SEQUENCE 映射，这将使跳过行的添加变得更加简单。另请参阅为什么不应该在 JPA 和 Hibernate 中使用表标识生成器。
尽管如此，TABLE 生成器对于某些情况可能仍能很好地工作，例如在低并发插入数的情况下。类似的论证也可以是：如果性能很重要，为什么不应该使用 JPA 和 Hibernate。
英文:
In the first step check your key generation table and the SEQUENCE_NAME.
If you use the default setting you will see something as follows - adapt the further steps for different configuration.
select * from hibernate_sequences;
SEQUENCE_NAME NEXT_VAL
--------------- ----------
default nnnnn
Now increase the NEXT_VAL by the requested number of the skipped records. To avoid the problem of concurent insert via Hibernate I use LOCK TABLE - all insert from Hibernate are blocked until you finish the action bellow.
lock table hibernate_sequences in exclusive mode;
select * from hibernate_sequences;
-- remember the value of NEXT_VAL nnnn
update hibernate_sequences
set NEXT_VAL = NEXT_VAL + 800
where SEQUENCE_NAME = 'default';
commit;
Now you have 800 ID's nnnn + 1 .. nnnn + 800 to be used for your skipped rows.
Note that the LOCK will work if you connect with the OWNER of the table hibernate_sequences. You may skip the LOCK at all if the application is down and no insert can be performed.
Use the identical INSERT as you see in Hibernate show_sql and with your data pass the reserved ID.
Example
-- Hibernate: insert into AUTHOR (name, AUTHOR_ID) values (?, ?)
insert into AUTHOR (name, AUTHOR_ID) values ('Psik', nnnn +1);
insert into AUTHOR (name, AUTHOR_ID) values ('Tuzka', nnnn+2);
Warning
As mentioned in comment, you should plan to switch to SEQUENCE mapping, which would make the adding of skipped rows even simpler. See also why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate.
Having said that, the TABLE generator could work fine for some use cases e.g. with low number of concurrent inserts. Using similar argumentation the claim could be: why-you-should-never-use-the-jpa-and-hibernate if performance matter.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论