Problem setting up H2 init runscript. File is not found when script is not in classpath root folder while using java modular system

Question

Following accepted answer for question regarding how to init runscript from resources folder: problem with INIT=RUNSCRIPT and relative paths.

Connection String:

jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'classpath:desktop/core/database/databaseCreation.sql'

However i get exception that file is not found even when file is present.

Exception:

org.h2.jdbc.JdbcSQLNonTransientException: IO Exception: "java.io.FileNotFoundException: resource /desktop/core/database/databaseCreation.sql"; "classpath:desktop/core/database/databaseCreation.sql"; SQL statement:
RUNSCRIPT FROM 'classpath:desktop/core/database/databaseCreation.sql' [90031-210]
    at com.h2database@2.1.210/org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
    at com.h2database@2.1.210/org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
    at com.h2database@2.1.210/org.h2.message.DbException.get(DbException.java:216)
    at com.h2database@2.1.210/org.h2.message.DbException.convertIOException(DbException.java:461)
    at com.h2database@2.1.210/org.h2.command.dml.ScriptBase.openInput(ScriptBase.java:168)
    at com.h2database@2.1.210/org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:52)
    at com.h2database@2.1.210/org.h2.command.CommandContainer.update(CommandContainer.java:174)
    at com.h2database@2.1.210/org.h2.command.Command.executeUpdate(Command.java:252)
    at com.h2database@2.1.210/org.h2.engine.Engine.openSession(Engine.java:279)
    at com.h2database@2.1.210/org.h2.engine.Engine.createSession(Engine.java:201)
    at com.h2database@2.1.210/org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:338)
    at com.h2database@2.1.210/org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:122)
    at com.h2database@2.1.210/org.h2.Driver.connect(Driver.java:59)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)
    at ispfdesktop/desktop.core.database.DatabaseManager.connectToInMemoryDatabase(DatabaseManager.java:184)
    at ispfdesktop/desktop.core.database.CRUDTests.setUpBeforeClass(CRUDTests.java:21)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptLifecycleMethod(TimeoutExtension.java:126)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptBeforeAllMethod(TimeoutExtension.java:68)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$invokeBeforeAllMethods$9(ClassBasedTestDescriptor.java:384)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.invokeBeforeAllMethods(ClassBasedTestDescriptor.java:382)
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.before(ClassBasedTestDescriptor.java:196)
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.before(ClassBasedTestDescriptor.java:78)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:136)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:84)
    at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:98)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: java.io.FileNotFoundException: resource /desktop/core/database/databaseCreation.sql
    at com.h2database@2.1.210/org.h2.store.fs.disk.FilePathDisk.newInputStream(FilePathDisk.java:386)
    at com.h2database@2.1.210/org.h2.store.fs.FileUtils.newInputStream(FileUtils.java:267)
    at com.h2database@2.1.210/org.h2.command.dml.ScriptBase.openInput(ScriptBase.java:166)
    ... 69 more

Trying to access file using getResource (DatabaseManager class is in desktop.core.database package) tells that file exists:

String file = DatabaseManager.class.getResource("databaseCreation.sql").getFile();
boolean yes = new File(file).exists(); // yes is true

Tried on both Windows(10) and Linux (Kubuntu) platform and using h2 of version 2.1.210.

UPDATE #1

File is found when it is in root classpath folder classpath:databaseCreation.sql, but still not when under some subfolder like desktop/core/database.

Appending '/' before first subfolder does not work.

Tried to put sql file to all folders.

'classpath:desktop/core/database/databaseCreation.sql' does not work

'classpath:desktop/core/databaseCreation.sql' does not work

'classpath:desktop/databaseCreation.sql' does not work

only this works

'classpath:databaseCreation.sql'

Without using classpath:

Without using classpath, the file can be in any subfolders, but this way, i need to use absolute path to the file:

jdbc:h2:mem:;INIT=RUNSCRIPT FROM '<absolute path here>'

Leaving the question open since the referenced question has outdated answer, which is not working on current H2 version and want to know how to get it work using classpath.

UPDATE #2

In order to replicate, you need to use java modular system (module-info.java) in the project.

For debugging purposes, what happens if you take that file and put it directly under src/main/resources and in you do `RUNSCRIPT FROM 'classpath:databaseCreation.sql` Just curious if it is a Path problem? Maybe it requires `/desktop` instead of `desktop` — JCompetence, Jan 26 '22 at 11:46
Moving file to `src/main/resource` and `classpath:databaseCreation.sql` works!!! However appending `/` before desktop/co... does not. I am curious why it worked on linked question when accepted answer shows usage of subfolder (named: scripts). Still leaving question opened since i would like to run it from that subfolders desktop/core/database. — Wortig, Jan 26 '22 at 12:07
You pose an interesting question. I guess it might depend on how you are packaging? For example, how does your target look like in terms of folder structure? Does your desktop exists under /target/desktop like this? — JCompetence, Jan 26 '22 at 12:09
@SMA Yes it does. All folders, resources and .class files are there as expected. (databaseCreation.sql in target/classes/desktop/core/database/). If it did not exist, my example code checking for that file with .getResource method would return null (throw NullPointerException since i call .getFile() on it). — Wortig, Jan 26 '22 at 12:15
I made a minimal, reproducible example : https://github.com/lookslikeitsnot/stackoverflow-70862699 with described layout but cannot replicate the error. Could you check the repo to see if it matches your code ? — Lookslikeitsnot, Feb 12 '22 at 10:27
Where are you putting this config file `jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'classpath:desktop/core/database/databaseCreation.sql'`/location ? — Shekhar Rai, Feb 13 '22 at 08:29
@ShekharRai You can see it is in src/main/resources/desktop/core/database as shown in picture. Or are you asking on something else? — Wortig, Feb 14 '22 at 09:03
I meant the config file, like application.properties or that command you are executing from.. — Shekhar Rai, Feb 14 '22 at 09:14
@ShekharRai It is not config file. I am building a String that is passed to DriverManager.getConnection() (jdbc api). So what you see: "jdbc:h2:mem.... is just String. — Wortig, Feb 14 '22 at 10:54
Have you tried with forward slash before desktop like `classpath:/desktop...` — Shekhar Rai, Feb 14 '22 at 11:45
@Lookslikeitsnot I created new Maven project using JUnit 5, H2 with same version, copied sources to same packages and it worked (the test where i connect to database)... so i did not know why it work since i did not do anything to java build path or add any exclusions in first project... then i found out that there is no module-info.java and as i added it to newly created project - it does not work... same error. So using java modular system (adding module-info.java) should replicate the problem. — Wortig, Feb 16 '22 at 10:00

kaliatech · Accepted Answer · 2022-02-16T16:28:05.740

UPDATE: (module-info.java)

Per your comment, your original setup used JDK 9+ modules. That's a complex and confusing topic. It would be easiest to simply remove module-info.java and not use modules. If you intend to use modules and keep resources in a separate directory (module), there are multiple options with no one clear choice. Perhaps the easiest option would be to open the "package" containing the resource. Something like this worked in my local test:

module myAppModule {
    exports desktop.core.database;

    opens desktop.core.database;

    requires java.sql;
    requires org.junit.jupiter.api;
}

More info:

ORIGINAL

I could not replicate your problem using a very simple maven setup with only the H2 runtime as a dependency (same version, 2.1.210) along with JUnit 5. It worked as expected.

The stack trace shows this is being run as a test from within Eclipse. I could replicate your issue exactly in Eclipse if I manually added src/main/resources as a Java Build Path source folder, but with an exclusion of **/ which would exclude subdirectories of resources.

If you are using maven, perhaps try running on command line with mvn test to verify that it is not your Eclipse setup.

Otherwise, you will need to post more info to get help.

I created new Maven project using JUnit 5, H2 with same version as you, copied sources to same packages and it worked (the test where i connect to database)... so i did not know why it work since i did not do anything to java build path or add any exclusions in first project... then i found out that there is no module-info.java and as i added it to newly created project, it does not work... same error. So module-info.java causes the problem. Any tip how to make it work without using absolute path while using java modular system? — Wortig, Feb 16 '22 at 09:57
I updated answer regarding one way to use modules with resources, along with more info. — kaliatech, Feb 16 '22 at 16:28
Thanks! opens desktop.core.database; was missing from module-info.java. Adding it results in that i can use classpath:/ in init=runscript from. — Wortig, Feb 17 '22 at 07:33

Problem setting up H2 init runscript. File is not found when script is not in classpath root folder while using java modular system

1 Answers1

Linked