problem with INIT=RUNSCRIPT and relative paths

Question

I use maven conventions for source paths (src/main src/test) and i have my sql scripts in src/main/resources/scripts.

I want to run my app with H2 memory and i'd like to use the jdbc url to initialize my db :

database.url=jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'src/main/resources/scripts/create.sql';

My problem is that this relative path (src/main/... ) does not work, and that H2 won't crash if the init=runscript command targets nothing.

Does someone know what is the path i should use to make this work ?

Thanks

Sudiesudnor

You wrote "H2 won't crash if the init=runscript command targets nothing" - I can't reproduce this (H2 throws an exception in this case). What version of H2 do you use? Could you try with the latest version? I'm not aware this was different before, but maybe it was. – Taliataliaferro

No you're right, now it crashes. I don't know what happened. – Sudiesudnor

Answer 1

You can use the following url:
"jdbc:h2:mem:sample;INIT=RUNSCRIPT FROM 'classpath:scripts/create.sql'"

With that one it is possible to run script from classpath. So you can just put it src/main/resources/scripts or src/test/resources/scripts in your maven (or something else) project.

Scottiescottish

I get MalformedURLException: unknown protocol: classpath on Java 11 – Smelt

This does not work on H2 2.1.210 when using folders in path after classpath:. #70863199 – Knighten

Update to previous comment that it does not work: it does not work while using java modular system (module-info.java) – Knighten

Answer 2

I'd suggest trying to use an absolute path for starters, just to check everything works. Afterwards, check your classpath. For example, bin/main/resources/scripts/create.sql, assuming bin is where your classes are compiled, and is on your classpath.

Since src, where your source lives, usually isn't on the classpath, this could be the source of your problem.

Merari

With the absolute path, it works, but not any combination of relative paths i tried. – Sudiesudnor

@Maxime: does H2 throw any error? Any sign of things not working from it's side mentioning that create.sql isn't found? Have you tried to put create.sql in the classpath? – Merari

sorry : With the absolute path, it works, but not any combination of relative paths i tried. The scripts are in mywar.war/WEB-INF/classes/scripts/.. I tried /WEB-INF/.. WEB-INF/.. etc no results :/ – Sudiesudnor

Since src, where your source lives, usually isn't on the classpath In Maven projects Whatever is inside src/main/resources usually is included in the classpath – Jayejaylene

Answer 3

For me it works if I keep the scripts under src/test/resources:

database.url=jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'src/test/resources/create.sql';

Anyway, in a JUnit context, if you use the conventional file paths schema.sql (for the DDL) and data.sql (for the DDL) under src/test/resources, you don't need the specify the scripts with the INIT variable in the connection string. You can simply use:

database.url=jdbc:h2:mem:testdb

and JUnit will automatically run the scripts when creating the H2 instance in memory.

Shortage

Answer 4

Your issue is you are specifying 'src/ You need to do ./src instead.

I had the same issue and this change fixed it:

spring.datasource.url=jdbc:h2:./src/main/resources/data.sql;INIT=RUNSCRIPT FROM './src/main/resources/data.sql'

If you are using Docker and Kubernetes,

add this line to Dockerfile

COPY src/main/resources/data.sql /app/src/main/resources/

Coppery