英文:
How to save an Entity object into an initialized h2 table with generated index?
问题
我有一个使用Spring Boot的项目,其中包含一个H2数据库。一个名为debug
的表是从schema.sql
初始化的:
DROP TABLE IF EXISTS debug;
CREATE TABLE debug (
id BIGINT PRIMARY KEY,
dummycol VARCHAR(250) NOT NULL
);
data.sql
内容如下:
INSERT INTO debug (id, dummycol) VALUES
(0, 'foo'),
(1, 'ba');
为了使这个工作,我需要在application.properties
中设置spring.jpa.hibernate.ddl-auto=none
。根据文档,这对于schema.sql
是必要的。但我发现如果将其设置为其他值,例如create-drop
,尽管会生成模式(我假设是从注解的类生成的),加载脚本data.sql
会被忽略,因此在启动后表将为空。
我定义了一个实体类:
@Entity
@Table(name = "debug")
@Data
@NoArgsConstructor
public class DebugE {
@Id
//@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="id")
private Long id;
@Column(name="dummycol")
private String dummy;
}
然后,我继承了CrudRepository
:public interface DebugRepository extends CrudRepository<DebugE, Long>
,并向表中添加了一个实体对象:
@Autowired
DebugRepository cr;
...
DebugE d = new DebugE();
d.setId(computeFreeId(cr))
d.setDummy("foo1");
cr.save(d);
到目前为止,一切正常,但现在我想要id
自动生成。为此,我将@GeneratedValue
添加到类中,并注释掉了d.setId
,因为我期望框架会为我完成这一步。至于GenerationType
,目前都不起作用:AUTO
、SEQUENCE
和IDENTITY
都有问题。
- 对于
SEQUENCE
,出现了找不到序列的错误。 - 对于
IDENTITY
,出现了NULL
不允许的错误。 - 对于
TABLE
,出现了无法读取或初始化高值的错误。
综合尝试了其他问题的解决方法:
- 对于
IDENTITY
,添加了hibernate.dialect=org.hibernate.dialect.H2Dialect
到application.properties
,但没有改变错误信息。 - 添加了
spring.jpa.hibernate.use-new-id-generator-mappings=false
,但对于IDENTITY
和SEQUENCE
没有改变。 - 将
spring.jpa.hibernate.ddl-auto
从none
改为create-drop
可以工作(即生成ID,向表中添加行),但此时表不再从data.sql
初始化。
最终的解决方案是:
在application.properties
中设置spring.jpa.hibernate.ddl-auto=none
,在实体类中使用@GeneratedValue(strategy=GenerationType.IDENTITY)
,并在schema.sql
中为id
字段添加AUTO_INCREMENT
属性。
英文:
I have a spring boot project with a h2 database. A table debug
is initialized from schema.sql
:
DROP TABLE IF EXISTS debug;
CREATE TABLE debug (
id BIGINT PRIMARY KEY,
dummycol VARCHAR(250) NOT NULL
);
data.sql
:
INSERT INTO debug (id, dummycol) VALUES
(0, 'foo'),
(1, 'ba');
For that to work I have to put spring.jpa.hibernate.ddl-auto=none
in application.properties. According to the documentation this is neccessary for schema.sql
but I found that with another value such as create-drop
while the schema is generated (I assume from the annotated classes) the loading script data.sql
is ignored so the tables are empty after startup.
I define an Entity class:
@Entity
@Table(name = "debug")
@Data
@NoArgsConstructor
public class DebugE {
@Id
//@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="id")
private Long id;
@Column(name="dummycol")
private String dummy;
}
Then, I subclass CrudRepository
: public interface DebugRepository extends CrudRepository<DebugE, Long>
and add an entity object to the table
@Autowired
DebugRepository cr;
...
DebugE d = new DebugE();
d.setId(computeFreeId(cr))
d.setDummy("foo1");
cr.save(d);
So far everything works, but now I would like to id
to be set automatically.
For that I add @GeneratedValue
to the class and comment out d.setId
because I expect the framework to do that for me. I have no preference towards a GenerationType
, so far neither works:
AUTO
,SEQUENCE
:
o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90036, SQLState: 90036
o.h.engine.jdbc.spi.SqlExceptionHelper : Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:
call next value for hibernate_sequence [90036-200]
o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [call next value for hibernate_sequence]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root cause
org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:
call next value for hibernate_sequence [90036-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:576) ~[h2-1.4.200.jar:1.4.200]
IDENTITY
:
o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 23502, SQLState: 23502
o.h.engine.jdbc.spi.SqlExceptionHelper : NULL not allowed for column "ID"; SQL statement:
insert into debug (id, dummycol) values (null, ?) [23502-200]
o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause
org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into debug (id, dummycol) values (null, ?) [23502-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:459) ~[h2-1.4.200.jar:1.4.200]
TABLE
:
o.hibernate.id.enhanced.TableGenerator : HHH000351: Could not read or init a hi value
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "HIBERNATE_SEQUENCES" not found; SQL statement:
select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update [42102-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
Attempts based on other questions:
- https://stackoverflow.com/a/39094773
strategy=GenerationType.IDENTITY
and addinghibernate.dialect=org.hibernate.dialect.H2Dialect
toapplication.properties
.
Gives a warning:'hibernate.dialect' is an unknown property.
and no change in error message - https://stackoverflow.com/a/63775719/3014199
addingspring.jpa.hibernate.use-new-id-generator-mappings=false
No change forIDENTITY
,SEQUENCE
.
Similar error forTABLE
:
org.hibernate.orm.deprecation : HHH90000015: Found use of deprecated [org.hibernate.id.MultipleHiLoPerTableGenerator] table-based id generator; use org.hibernate.id.enhanced.TableGenerator instead. See Hibernate Domain Model Mapping Guide for details.
o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42102, SQLState: 42S02
o.h.engine.jdbc.spi.SqlExceptionHelper : Table "HIBERNATE_SEQUENCES" not found; SQL statement:
select sequence_next_hi_value from hibernate_sequences where sequence_name = 'debug' for update [42102-200]
- https://stackoverflow.com/a/63127119/3014199
Settingspring.jpa.hibernate.ddl-auto
fromnone
tocreate-drop
works(i.e. ids are generated, rows are added to table) but now the table is no longer initialized fromdata.sql
.
additional details
application.properties
:
spring.datasource.url=jdbc:h2:mem:h2db
spring.h2.console.enabled=true
spring.h2.console.path=/h2
#spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.initialization-mode=embedded
#hibernate.dialect=org.hibernate.dialect.H2Dialect
#spring.jpa.hibernate.use-new-id-generator-mappings=false
build.gradle
:
plugins {
id 'org.springframework.boot' version '2.3.3.RELEASE'
id 'io.spring.dependency-management' version '1.0.10.RELEASE'
id 'java'
id "io.freefair.lombok" version "5.2.1"
}
group = 'com.my.project'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
repositories {
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.springframework.boot:spring-boot-starter-actuator'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
runtimeOnly 'com.h2database:h2'
}
Solution
spring.jpa.hibernate.ddl-auto=none
in application.properties
@GeneratedValue(strategy=GenerationType.IDENTITY)
in the class and
id BIGINT PRIMARY KEY AUTO_INCREMENT
in schema.sql made it work.
答案1
得分: 1
如果您想在同时使用 *sql 文件来生成 id,您需要在创建表时为您的 id 字段定义 auto_increment 属性。
因为当您使用生成策略 - Identity 时,Hibernate 认为您的列在表一侧是自增的。
英文:
If you want to generate the id and at the same time using *sql files, you need define the auto_increment property to your id field in creation table.
Because when you using generation strategy - Identity, hibernate believes that your columns is auto increments on a table side.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论