Hsql -如果spring boot应用程序中不存在模式，则创建模式

Question

我在我的Spring启动应用程序中使用嵌入式hsql db，并且仅当db中不存在时才想创建架构。

假设模式名称为XXX，

我有下面的单语句模式hsql。sql文件：

CREATE SCHEMA XXX IF NOT EXISTS

然而，这与下面的堆栈跟踪有关:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/xxx/schema-hsql.sql]: CREATE SCHEMA XXX IF NOT EXISTS; nested exception is java.sql.SQLSyntaxErrorException: unexpected token: IF

at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:494)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:231)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:157)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runSchemaScripts(DataSourceInitializer.java:81)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.init(DataSourceInitializer.java:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:354)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:305)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:133)
    ... 65 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: IF
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:909)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473)
    ... 77 more
Caused by: org.hsqldb.HsqlException: unexpected token: IF
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserDDL.getCompiledStatementBody(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreateSchema(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 81 more

实现这一点的正确语法是什么？

谢谢

Answer 1

您还可以在“hibernate.cfg.xml”中指定如下：

<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>

以下是完整的示例：

<session-factory>

    <!-- Database connection settings -->
    <property name="connection.driver_class">org.h2.Driver</property>
    <property name="connection.url">jdbc:h2:file:db/personh2db;DB_CLOSE_DELAY=-1;MVCC=TRUE</property>
    <property name="connection.username">sa</property>
    <property name="connection.password"/>

    <!-- JDBC connection pool (use the built-in) -->
    <property name="connection.pool_size">1</property>

    <!-- SQL dialect -->
    <property name="dialect">org.hibernate.dialect.H2Dialect</property>

    <!-- Disable the second-level cache  -->
    <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

    <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">create</property>

    <mapping resource="com/example/model/Person.hbm.xml"/>

</session-factory>

hbm2ddl.auto的可能选项列表是，

< li >验证:验证模式，不对数据库进行任何更改。 < li >更新:更新模式。 < li >创建:创建模式，销毁以前的数据。 < li >创建-删除:在会话结束时删除架构。

您可以在＜code＞应用程序中配置Hibernate属性。属性。

这是application.properties的留档：https://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html

您可以找到hbm2ddl的示例。此处自动配置：http://docs.spring.io/spring-boot/docs/current/reference/html/howto-data-access.html#howto-配置jpa属性

Answer 2

使用

CREATE SCHEMA IF NOT EXISTS XXX

而不是你的 SQL

CREATE SCHEMA XXX IF NOT EXISTS

并使用org.hsqldb::hsqldb v.2.4.0

请参见：Hsql-如果在Spring启动应用程序中不存在，则创建架构