如何将一个实体对象保存到一个已初始化的带有生成索引的h2表中?

huangapple go评论74阅读模式
英文:

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;
}

然后,我继承了CrudRepositorypublic 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,目前都不起作用:AUTOSEQUENCEIDENTITY都有问题。

  • 对于SEQUENCE,出现了找不到序列的错误。
  • 对于IDENTITY,出现了NULL不允许的错误。
  • 对于TABLE,出现了无法读取或初始化高值的错误。

综合尝试了其他问题的解决方法:

  • 对于IDENTITY,添加了hibernate.dialect=org.hibernate.dialect.H2Dialectapplication.properties,但没有改变错误信息。
  • 添加了spring.jpa.hibernate.use-new-id-generator-mappings=false,但对于IDENTITYSEQUENCE没有改变。
  • spring.jpa.hibernate.ddl-autonone改为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, &#39;foo&#39;), 
  (1, &#39;ba&#39;);

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 = &quot;debug&quot;)
@Data
@NoArgsConstructor
public class DebugE {

	@Id
	//@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name=&quot;id&quot;)
	private Long id;

	@Column(name=&quot;dummycol&quot;)
	private String dummy;
}

Then, I subclass CrudRepository: public interface DebugRepository extends CrudRepository&lt;DebugE, Long&gt; and add an entity object to the table

@Autowired
DebugRepository cr;
...
	DebugE d = new DebugE();
    d.setId(computeFreeId(cr))
	d.setDummy(&quot;foo1&quot;);
	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 &quot;HIBERNATE_SEQUENCE&quot; 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 &quot;HIBERNATE_SEQUENCE&quot; 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 &quot;ID&quot;; 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 &quot;ID&quot;; 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 &quot;HIBERNATE_SEQUENCES&quot; 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 adding hibernate.dialect=org.hibernate.dialect.H2Dialect to application.properties.
    Gives a warning: &#39;hibernate.dialect&#39; is an unknown property. and no change in error message
  • https://stackoverflow.com/a/63775719/3014199
    adding spring.jpa.hibernate.use-new-id-generator-mappings=false
    No change for IDENTITY,SEQUENCE.
    Similar error for TABLE:
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 &quot;HIBERNATE_SEQUENCES&quot; not found; SQL statement:
select sequence_next_hi_value from hibernate_sequences where sequence_name = &#39;debug&#39; for update [42102-200]
  • https://stackoverflow.com/a/63127119/3014199
    Setting spring.jpa.hibernate.ddl-auto from none to create-drop works(i.e. ids are generated, rows are added to table) but now the table is no longer initialized from data.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 &#39;org.springframework.boot&#39; version &#39;2.3.3.RELEASE&#39;
	id &#39;io.spring.dependency-management&#39; version &#39;1.0.10.RELEASE&#39;
	id &#39;java&#39;
	id &quot;io.freefair.lombok&quot; version &quot;5.2.1&quot;
}


group = &#39;com.my.project&#39;
version = &#39;0.0.1-SNAPSHOT&#39;
sourceCompatibility = &#39;11&#39;

repositories {
	mavenCentral()
}

dependencies {
	implementation &#39;org.springframework.boot:spring-boot-starter-web&#39;
	implementation &#39;org.springframework.boot:spring-boot-starter-actuator&#39;
	implementation &#39;org.springframework.boot:spring-boot-starter-data-jpa&#39;
	runtimeOnly &#39;com.h2database:h2&#39;
}

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.

huangapple
  • 本文由 发表于 2020年9月13日 03:11:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/63863991.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定