Executing H2 under Spring Boot

Question

I've generated a Spring Boot web application using Spring Initializer, embedded Tomcat, Thymeleaf template engine, and package as an executable JAR file.

Technologies used:

Spring Boot 1.4.2.RELEASE, Spring 4.3.4.RELEASE, Thymeleaf 2.1.5.RELEASE, Tomcat Embed 8.5.6, Maven 3, Java 8

This is a bean I call when starting the DB

@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();
        }

When I execute this insert in

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);

I got this error

user lacks privilege or object not found: PARSEDATETIME

Executing the same query in the Data Source Explorer -> DataBase Connections -> SQL Scrapbook everything is fine !

adding SHOW CREATE FUNCTION PARSEDATETIME in the script:

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

and CREATE FUNCTION 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: (

and with the proposed example :

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

ask at https://gitter.im/spring-projects/spring-boot and update answer — rajadilipkolli, Mar 10 '17 at 18:37
Besides that: is there a particular reason why you don't use autoconfig ? — Marged, Mar 13 '17 at 05:55
I tested, it works with the TIMESTAMP column. `CREATE TABLE test ( id INT NOT NULL, birthday TIMESTAMP ); INSERT INTO test values (1, CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'));` on data.sql — Liping Huang, Mar 14 '17 at 09:50

score 3 · Accepted Answer · edited May 23 '17 at 12:25

I tried to reproduce your issue by creating Spring Boot project from scratch with spring-boot-starter-data-jpa and h2 dependencies. I did two things:

1) Placed your scripts in /resources with schema.sql and data.sql names in order to create and populate database correspondingly. By default Spring Boot will load SQL from those locations as described here.

2) I have configured testdb H2 database in application.properties like this:

# 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/

The result is that H2 database is populated by sample data you provided without any errors (I didn't configure DataSource as you did in PersistenceConfig and nothing more/nothing else).

If you want to stick to custom SQL scripts location, consider configuring your DataSource following this answer https://stackoverflow.com/a/41644743/2402959.

score 1 · Answer 2 · answered Mar 13 '17 at 04:51

For some reason, the installation of the Stored Function PARSEDATETIME die not allow you to access it. Please provide SHOW CREATE FUNCTION PARSEDATETIME. And look through spring's stuff.

Or, more likely, PARSEDATETIME is a Java function, not a MySQL function.

Note: The place where you are using it implies that it is a MySQL function. To use it as a Java function you need to 'bind' it into the INSERT.

score 1 · Answer 3 · answered Mar 16 '17 at 20:49

Did you try changing your insert statement from

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);

to

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, CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), 1);

?

As @Liping huang suggested could you please add the DDL content contained within your schema script (H2.schema.sql) to your post? Thanks. — M. Rizzo, Mar 17 '17 at 14:39

Executing H2 under Spring Boot

3 Answers3