在Spring Boot下执行H2

Question

我使用Spring初始化器、嵌入式Tomcat、Thymeleaf模板引擎和作为可执行JAR文件的包生成了一个Spring Boot web应用程序。

这是我在启动DB时调用的bean

@SpringBootApplication
@EnableAutoConfiguration
@Import({SecurityConfig.class})
public class BookApplication {

    public static void main(String[] args) {
        SpringApplication.run(BookApplication.class, args);
    }
}



@Configuration
public class PersistenceConfig {

...

    /**
         * Creates an in-memory "books" database populated 
         * with test data for fast testing
         */
        @Bean
        public DataSource dataSource(){
            return
                (new EmbeddedDatabaseBuilder())
                .addScript("classpath:db/H2.schema.sql")
                .addScript("classpath:db/H2.data.sql")
                .build();
        }

在

CREATE TABLE IF NOT EXISTS t_time_lapse (
      id          bigint  PRIMARY KEY,
      name        varchar(50) NOT NULL,
      description varchar(200) NOT NULL,
      sunday      boolean DEFAULT NULL,
      monday      boolean DEFAULT NULL,
      tuesday     boolean DEFAULT NULL,
      wednesday   boolean DEFAULT NULL,
      thursday    boolean DEFAULT NULL,
      friday      boolean DEFAULT NULL,
      saturday    boolean DEFAULT NULL,
      init_period date    NOT NULL ,
      end_period  date    NOT NULL ,
      init_time   time    DEFAULT NULL,
      end_time    time    DEFAULT NULL,
      company_id  bigint DEFAULT NULL,
      FOREIGN KEY (company_id)     REFERENCES public.t_company(id)
 );



insert into T_TIME_LAPSE (ID, NAME, DESCRIPTION, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY, INIT_PERIOD, END_PERIOD, INIT_TIME, END_TIME, COMPANY_ID) 
    values (9090,'key', 'key', 1,1,1,1,1,1,1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), 1);

我得到了这个错误

user lacks privilege or object not found: PARSEDATETIME

Failed to execute SQL script statement #1 of class path resource [db/H2.data.sql]: SHOW CREATE FUNCTION PARSEDATETIME; nested exception is java.sql.SQLSyntaxErrorException: unexpected token: SHOW

和创建函数parsedateTime；

Failed to execute SQL script statement #1 of class path resource [db/H2.data.sql]: CREATE FUNCTION PARSEDATETIME; nested exception is java.sql.SQLSyntaxErrorException: unexpected end of statement:  required: (

和建议的例子：

Failed to execute SQL script statement #2 of class path resource [db/H2.data.sql]: INSERT INTO test values (1, CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT')); nested exception is java.sql.SQLSyntaxErrorException: unexpected token: CALL

Answer 1

我试图通过使用spring-boot-starter-data-jpa和H2依赖项从头创建Spring Boot项目来重现您的问题。我做了两件事：

1）使用schema.SQL和data.SQL名称将脚本放置在/resources中，以便相应地创建和填充数据库。

2）我在application.properties中配置了TestDBH2数据库，如下所示：

# H2 database configuration
spring.datasource.url = jdbc:h2:file:~/testdb;DB_CLOSE_ON_EXIT=FALSE

# Enable SQL script scanning in /resources folder
spring.jpa.hibernate.ddl-auto=none

# Enable H2 console under http://localhost:8080/console/ for dev purposes
spring.h2.console.enabled=true
spring.h2.console.path=/console/

结果是，您提供的示例数据填充了H2数据库，没有任何错误（我没有像您在persistenceConfig中那样配置datasource，仅此而已/仅此而已）。

如果要坚持自定义SQL脚本位置，请考虑按照以下答案配置数据源https://stackoverflow.com/a/41644743/2402959。