使用JPA在sqlite中自动增量值错误 [英] Wrong autoincrement value in sqlite using JPA

问题描述

我正在使用sqlite，JPA和eclipseLink进行一个简单的项目.

I'm working on a simple project using sqlite, JPA and eclipseLink.

首先，我在数据库中使用以下命令创建我的Person表:

First I create my Person table in my database with this:

CREATE TABLE Person ( 
idPerson  INTEGER PRIMARY KEY AUTOINCREMENT,
firstname      TEXT  DEFAULT 'NULL',
birthdate DATETIME        DEFAULT 'NULL'
)

，然后添加一个新的测试条目(以生成sqlite_sequence表)

and then add a new test entry (in order to generate the sqlite_sequence table)

INSERT INTO [Person] ([firstname], [birthdate]) VALUES ('Foo', '1145-11-12 00:00:00')

所有后续插入操作都是使用JPA完成的，其中在Person类中，我对个人ID使用以下表示法:

All the successive insertion are done using JPA, where in the Person class I use this notation for the person id:

@GeneratedValue(generator="sqlite_person")
@TableGenerator(name="sqlite_person", table="sqlite_sequence",
    pkColumnName="name", valueColumnName="seq",
    pkColumnValue="Person")
@Column(name="idPerson")
private int id;

第一次插入JPA是可以的(也就是说，新插入的新人的ID = 2)，但是我得到的增量是50，而不是只有1(所以，插入的第三个人的ID = 52，第四个插入的ID为52，依此类推)上).

The first JPA insertion is ok (that is, new new inserted person has id = 2) but then I get an increment of 50 instead of only 1 (so the third inserted person has id = 52, the fourth 102 and so on).

我读到对该表进行修改很可能会干扰AUTOINCREMENT密钥生成算法" [参考]

即使理论上我没有修改该表，我的问题也与此相关吗? 有什么建议可以解决问题吗?

Is my problem related to this, even if in theory I'm not modifying that table? Any kind of suggestion in order to resolve the problem?

推荐答案

最后，为了解决此问题，我为TableGenerator注释添加了两个可选元素(即initialValue，allocationSize) .
因此，ID的新注释是这样的:

At the end, in order to solve the problem, I just added two optional elements for the TableGenerator annotation (i.e. initialValue, allocationSize).
So the new annotation for the ID is like this:

@GeneratedValue(generator="sqlite_person")
@TableGenerator(name="sqlite_person", table="sqlite_sequence",
    pkColumnName="name", valueColumnName="seq",
    pkColumnValue="Person",
    initialValue=1, allocationSize=1)
@Column(name="idPerson")
private int id;

我认为它也可以在没有初始值的情况下工作，但是这样我也避免插入随机条目(因为当我创建Person表时，似乎已经自动生成了sqlite_sequence表)

I think it works also without the initial value, but like this I also avoid to insert random entries (because it seems that the sqlite_sequence table is automatically generated already when I create the Person table)

这篇关于使用JPA在sqlite中自动增量值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！