时间戳

首页 > 解决方案 > 将sql文件放在spring boot应用程序中的哪个位置以生成H2数据库

spring-boot - 将sql文件放在spring boot应用程序中的哪个位置以生成H2数据库

问题描述

不清楚将 SQL 文件放在哪里以便初始化我的 H2 数据库。

在我的application-h2.properties文件中，我有：

# H2
spring.h2.console.enabled=true
spring.h2.console.path=/h2
# Datasource
#spring.datasource.url=jdbc:h2:file:~/test
spring.datasource.url=jdbc:h2:mem:testdb;Mode=Oracle
spring.datasource.platform=h2
spring.datasource.username=user
spring.datasource.password=user
spring.jpa.hibernate.ddl-auto=none
spring.datasource.continue-on-error=true
spring.datasource.initialization-mode=always
spring.datasource.driver-class-name=org.h2.Driver
spring.profiles.active=h2
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

我的 SQL 文件是从 sqlDeveloper 生成的纯 oracle SQL。我试图在 H2 控制台中剪切并粘贴它，但它不接受它。我希望这种方式会奏效。

------------------------更新1------------ ---

架构.sql

schema.sql]: CREATE SEQUENCE "foo"."ADDRESSID_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE SEQUENCE ""foo"".""ADDRESSID_SEQ"" MINVALUE[*] 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"; SQL statement:

数据.sql

data.sql]: CREATE SEQUENCE "foo"."ADDRESSID_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE SEQUENCE ""foo"".""ADDRESSID_SEQ"" MINVALUE[*] 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"; SQL statement:
CREATE SEQUENCE "foo"."ADDRESSID_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE [42000-148]

错误

Error creating bean with name 'entityManagerFactory': Post-processing of FactoryBean's singleton object failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #3

这是同一个sql文件，我只是重命名了它。它有创建和插入。但是，如果我命名它，schema.sql或者data.sql它仍然在第三行失败。创建用户或模式时，它似乎没有失败。

------------------更新2----------------

 CREATE USER foo ifentified by foo;

  CREATE SCHEMA foo;

  CREATE TABLE foo.ADDRESS
   (ADDRESS_ID NUMBER(22,0),
    CUSTOMER_ID NUMBER(*,0),
    COMPANY_NAME VARCHAR2(100 BYTE),
    ADDITIONAL_ADDRESS_INFO VARCHAR2(100 BYTE),
    STREET VARCHAR2(100 BYTE),
    ADDITIONAL_STREET_INFO VARCHAR2(100 BYTE),
    HOUSE_NUMBER VARCHAR2(20 BYTE),
    ZIP VARCHAR2(20 BYTE),
    CITY VARCHAR2(50 BYTE),
    STATE VARCHAR2(20 BYTE),
    COUNTRY_CODE CHAR(2 BYTE),
    PHONE VARCHAR2(20 BYTE),
    CREATED_AT TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP,
    MODIFIED_AT TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP,
    VALIDATED_AT TIMESTAMP (6),
    VALIDATION_RESULT VARCHAR2(100 CHAR)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING


   COMMENT ON COLUMN foo.ADDRESS.ADDRESS_ID IS 'primary key for table address';
   COMMENT ON COLUMN foo.ADDRESS.CUSTOMER_ID IS 'foreign key for table customer';
   COMMENT ON COLUMN foo.ADDRESS.CREATED_AT IS 'initially created at';
   COMMENT ON COLUMN foo.ADDRESS.MODIFIED_AT IS 'date of last modification';

错误

Syntax error in SQL statement "CREATE TABLE FOO.ADDRESS (ADDRESS_ID NUMBER(22,0), CUSTOMER_ID NUMBER(*[*],0), COMPANY_NAME VARCHAR2(100 BYTE), ADDITIONAL_ADDRESS_INFO VARCHAR2(100 BYTE), STREET VARCHAR2(100 BYTE), ADDITIONAL_STREET_INFO VARCHAR2(100 BYTE), HOUSE_NUMBER VARCHAR2(20 BYTE), ZIP VARCHAR2(20 BYTE), CITY VARCHAR2(50 BYTE), STATE VARCHAR2(20 BYTE), COUNTRY_CODE CHAR(2 BYTE), PHONE VARCHAR2(20 BYTE), CREATED_AT TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, MODIFIED_AT TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, VALIDATED_AT TIMESTAMP (6), VALIDATION_RESULT VARCHAR2(100 CHAR) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING COMMENT ON COLUMN FOO.ADDRESS.ADDRESS_ID IS 'primary key for table address' "; expected "long"; SQL statement：

标签： spring-bootjpah2

解决方案

您应该命名文件data.sql并将其保存在src/main/resources文件夹中。在此位置，将自动检测并执行文件。如果您想保留默认模式生成（由带@Entity注释的类定义），则可以这样做。

如果您还想手动生成模式，您可以创建文件schema.sql，在其中放置用于创建模式的所有详细信息。

推荐阅读