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 [email protected]/org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
    at [email protected]/org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
    at [email protected]/org.h2.message.DbException.get(DbException.java:216)
    at [email protected]/org.h2.message.DbException.convertIOException(DbException.java:461)
    at [email protected]/org.h2.command.dml.ScriptBase.openInput(ScriptBase.java:168)
    at [email protected]/org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:52)
    at [email protected]/org.h2.command.CommandContainer.update(CommandContainer.java:174)
    at [email protected]/org.h2.command.Command.executeUpdate(Command.java:252)
    at [email protected]/org.h2.engine.Engine.openSession(Engine.java:279)
    at [email protected]/org.h2.engine.Engine.createSession(Engine.java:201)
    at [email protected]/org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:338)
    at [email protected]/org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:122)
    at [email protected]/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 [email protected]/org.h2.store.fs.disk.FilePathDisk.newInputStream(FilePathDisk.java:386)
    at [email protected]/org.h2.store.fs.FileUtils.newInputStream(FileUtils.java:267)
    at [email protected]/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.

Herb

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 – Trim

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. – Herb

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? – Trim

@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). – Herb

I made a minimal, reproducible example : 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 ? – Grassplot

Where are you putting this config file jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'classpath:desktop/core/database/databaseCreation.sql'/location ? – Same

@ShekharRai You can see it is in src/main/resources/desktop/core/database as shown in picture. Or are you asking on something else? – Herb

I meant the config file, like application.properties or that command you are executing from.. – Same

@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. – Herb

Have you tried with forward slash before desktop like classpath:/desktop... – Same

@Shekhar Rai Yes, its written bold at the end of question. – Herb

@Grassplot 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. – Herb

Answer 1

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:

• https://www.oracle.com/corporate/features/understanding-java-9-modules.html
• Loading classes and resources in Java 9
• Accessing resource files in a java jigsaw module
• How to access resource using class loader in Java 9
• Accessing resource files from external modules

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.

Jinja

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? – Herb

I updated answer regarding one way to use modules with resources, along with more info. – Jinja

Thanks! opens desktop.core.database; was missing from module-info.java. Adding it results in that i can use classpath:<any folders>/<my sql file> in init=runscript from. – Herb