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

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

How to save an Entity object into an initialized h2 table with generated index?

问题

我有一个使用Spring Boot的项目，其中包含一个H2数据库。一个名为debug的表是从schema.sql初始化的：

  1. DROP TABLE IF EXISTS debug;
  3. CREATE TABLE debug (
  4.   id BIGINT PRIMARY KEY,
  5.   dummycol VARCHAR(250) NOT NULL
  6. );

data.sql内容如下：

  1. INSERT INTO debug (id, dummycol) VALUES
  2.   (0, 'foo'), 
  3.   (1, 'ba');

为了使这个工作，我需要在application.properties中设置spring.jpa.hibernate.ddl-auto=none。根据文档，这对于schema.sql是必要的。但我发现如果将其设置为其他值，例如create-drop，尽管会生成模式（我假设是从注解的类生成的），加载脚本data.sql会被忽略，因此在启动后表将为空。

我定义了一个实体类：

  1. @Entity
  2. @Table(name = "debug")
  3. @Data
  4. @NoArgsConstructor
  5. public class DebugE {
  7. 	@Id
  8. 	//@GeneratedValue(strategy=GenerationType.AUTO)
  9. 	@Column(name="id")
  10. 	private Long id;
  12. 	@Column(name="dummycol")
  13. 	private String dummy;
  14. }

然后，我继承了CrudRepository：public interface DebugRepository extends CrudRepository<DebugE, Long>，并向表中添加了一个实体对象：

  1. @Autowired
  2. DebugRepository cr;
  3. ...
  4. 	DebugE d = new DebugE();
  5.     d.setId(computeFreeId(cr))
  6. 	d.setDummy("foo1");
  7. 	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:

  1. DROP TABLE IF EXISTS debug;
  2.   
  3. CREATE TABLE debug (
  4.   id BIGINT PRIMARY KEY,
  5.   dummycol VARCHAR(250) NOT NULL
  6. );

data.sql:

  1. INSERT INTO debug (id, dummycol) VALUES
  2.   (0, &#39;foo&#39;), 
  3.   (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:

  1. @Entity
  2. @Table(name = &quot;debug&quot;)
  3. @Data
  4. @NoArgsConstructor
  5. public class DebugE {
  7. 	@Id
  8. 	//@GeneratedValue(strategy=GenerationType.AUTO)
  9. 	@Column(name=&quot;id&quot;)
  10. 	private Long id;
  12. 	@Column(name=&quot;dummycol&quot;)
  13. 	private String dummy;
  14. }

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

  1. @Autowired
  2. DebugRepository cr;
  3. ...
  4. 	DebugE d = new DebugE();
  5.     d.setId(computeFreeId(cr))
  6. 	d.setDummy(&quot;foo1&quot;);
  7. 	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:

  1. o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 90036, SQLState: 90036
  2. o.h.engine.jdbc.spi.SqlExceptionHelper   : Sequence &quot;HIBERNATE_SEQUENCE&quot; not found; SQL statement:
  3. call next value for hibernate_sequence [90036-200]
  4. 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
  6. org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence &quot;HIBERNATE_SEQUENCE&quot; not found; SQL statement:
  7. call next value for hibernate_sequence [90036-200]
  8. 	at org.h2.message.DbException.getJdbcSQLException(DbException.java:576) ~[h2-1.4.200.jar:1.4.200]

IDENTITY:

  1. o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23502, SQLState: 23502
  2. o.h.engine.jdbc.spi.SqlExceptionHelper   : NULL not allowed for column &quot;ID&quot;; SQL statement:
  3. insert into debug (id, dummycol) values (null, ?) [23502-200]
  4. 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
  6. org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column &quot;ID&quot;; SQL statement:
  7. insert into debug (id, dummycol) values (null, ?) [23502-200]
  8. 	at org.h2.message.DbException.getJdbcSQLException(DbException.java:459) ~[h2-1.4.200.jar:1.4.200]

TABLE:

  1.  o.hibernate.id.enhanced.TableGenerator   : HHH000351: Could not read or init a hi value
  3. org.h2.jdbc.JdbcSQLSyntaxErrorException: Table &quot;HIBERNATE_SEQUENCES&quot; not found; SQL statement:
  4. select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update [42102-200]
  5. 	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:
  1. 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.
  2. o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42102, SQLState: 42S02
  3. o.h.engine.jdbc.spi.SqlExceptionHelper   : Table &quot;HIBERNATE_SEQUENCES&quot; not found; SQL statement:
  4. 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:

  1. spring.datasource.url=jdbc:h2:mem:h2db
  2. spring.h2.console.enabled=true
  3. spring.h2.console.path=/h2
  5. #spring.jpa.hibernate.ddl-auto=none
  6. spring.jpa.hibernate.ddl-auto=create-drop
  7. spring.datasource.initialization-mode=embedded
  8. #hibernate.dialect=org.hibernate.dialect.H2Dialect
  9. #spring.jpa.hibernate.use-new-id-generator-mappings=false

build.gradle:

  1. plugins {
  2. 	id &#39;org.springframework.boot&#39; version &#39;2.3.3.RELEASE&#39;
  3. 	id &#39;io.spring.dependency-management&#39; version &#39;1.0.10.RELEASE&#39;
  4. 	id &#39;java&#39;
  5. 	id &quot;io.freefair.lombok&quot; version &quot;5.2.1&quot;
  6. }
  9. group = &#39;com.my.project&#39;
  10. version = &#39;0.0.1-SNAPSHOT&#39;
  11. sourceCompatibility = &#39;11&#39;
  13. repositories {
  14. 	mavenCentral()
  15. }
  17. dependencies {
  18. 	implementation &#39;org.springframework.boot:spring-boot-starter-web&#39;
  19. 	implementation &#39;org.springframework.boot:spring-boot-starter-actuator&#39;
  20. 	implementation &#39;org.springframework.boot:spring-boot-starter-data-jpa&#39;
  21. 	runtimeOnly &#39;com.h2database:h2&#39;
  22. }

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
  • h2
  • hibernate
  • java
  • spring
  • spring-data-jpa
匿名

发表评论

匿名网友

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

确定