java - org.h2.message.DbException.getJdbcSQLException

Question

我有一个不在 Postgres 数据库中创建表的实体。该实体提供如下：

@Getter
@Setter
@ToString
@Entity
@Table(name = "rule_history")
@AllArgsConstructor
@NoArgsConstructor

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class RuleHistory extends Auditable<UUID> {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private UUID id;

    @JsonBackReference
    @ManyToOne
    @JoinColumn(name = "ruleExecution_id")
    private RuleExecution ruleExecution;

    @Schema(description = "The Rule that was evaluated")
    @OneToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "rule_id", referencedColumnName = "id")
    private Rule rule;

    @Column(name = "rule_entity")
    private RuleTarget ruleTarget;

    @Column(name = "entity_id")
    private UUID entityId;

    @Type(type = "jsonb")
    @Column(name = "rule_response" ,columnDefinition = "json")
    private String ruleResponse;

}

这是我在运行 Spring Boot 应用程序时遇到的错误：

2020-11-11 19:11:48.665  WARN 63758 --- [         task-1] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "create table rule_history (id binary not null, create_date timestamp, created_by binary, updated_by binary, modify_date timestamp, entity_id binary, rule_response json, rule_entity integer, rule_id binary, rule_execution_id binary, primary key (id))" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table rule_history (id binary not null, create_date timestamp, created_by binary, updated_by binary, modify_date timestamp, entity_id binary, rule_response json, rule_entity integer, rule_id binary, rule_execution_id binary, primary key (id))" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:423) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:314) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:156) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]
Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "JSON"; SQL statement:
create table rule_history (id binary not null, create_date timestamp, created_by binary, updated_by binary, modify_date timestamp, entity_id binary, rule_response json, rule_entity integer, rule_id binary, rule_execution_id binary, primary key (id)) [50004-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseColumnWithType(Parser.java:4169) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseColumnForTable(Parser.java:4012) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseCreateTable(Parser.java:6155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseCreate(Parser.java:4351) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parsePrepared(Parser.java:366) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:321) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:293) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.prepareCommand(Parser.java:258) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    ... 18 common frames omitted

2020-11-11 19:11:48.754  WARN 63758 --- [         task-1] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "alter table rule_history add constraint FKf9mbhet9oxa9sf64wcfjax56i foreign key (rule_id) references rule" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table rule_history add constraint FKf9mbhet9oxa9sf64wcfjax56i foreign key (rule_id) references rule" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:423) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:374) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:156) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]
Caused by: org.h2.jdbc.JdbcSQLException: Table "RULE_HISTORY" not found; SQL statement:
alter table rule_history add constraint FKf9mbhet9oxa9sf64wcfjax56i foreign key (rule_id) references rule [42102-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:103) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:77) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.CommandContainer.update(CommandContainer.java:101) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Command.executeUpdate(Command.java:260) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:192) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
    ... 18 common frames omitted

我认为错误声明中有一行很重要：

Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "JSON"; SQL statement:

这是项目中使用的 application.properties 文件：

logging.level.web=TRACE
logging.level.org.springframework.web=TRACE
springdoc.api-docs.path=/api-docs
springdoc.swagger-ui.path=/swagger-ui.html
springdoc.swagger-ui.disable-swagger-default-url=true


spring.datasource.url=jdbc:postgresql://localhost:5432/cilling
#spring.datasource.url=jdbc:postgresql://postgres:5432/cilling
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=false

management.endpoints.web.exposure.include=health,info
management.endpoints.web.exposure.exclude=
management.endpoint.health.show-details=always
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true


server.port=8080


spring.security.securityEnabled=1
spring.api.security.jwt.secret=d-83scwew3435f
spring.api.security.jwt.expirationInMinute=1
spring.api.security.jwt.refreshExpirationInMinute=9000000


spring.sendgrid.api-key=d-83scwew3435f
spring.monetizenow.io.mail.sender.email=[e-mail]
spring.monetizenow.io.mail.sender.name=Team
user.reg.email.verify.sengrid.template.id=d-83scwew3435f
user.reg.email.verify.base.url=http://localhost/verify-email
user.pwd.reset.sengrid.template.id=d-83scwew3435f
user.password.reset.email.base.url=http://localhost/password-reset
user.otp.email.sengrid.template.id=d-83scwew3435f


file.upload-dir=/opt/DonytyzAppPhoto
spring.servlet.multipart.enabled=true
spring.servlet.multipart.file-size-threshold=2KB
spring.servlet.multipart.max-file-size=500KB
spring.servlet.multipart.max-request-size=500KB

我该如何纠正？

标签： javahibernatejpa