Relative path to hsqldb files in a web app doesn't work?

Question

I'm using hsqldb for my Spring-based java webapp. I put database files (mydb.lck, mydb.properties,..) in src\main\java\data folder so that they're published into WEB-INF\classes\data.

In datasource configuration, I specify this relative path to JVM working directory. As guided in hsqldb documents.

portal.jdbc.url=jdbc:hsqldb:file:/data/mydb (Is this seperator right for Windows?)

But Spring seem not find this path and insist on claiming

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CUSTOMER org.hsqldb.jdbc.Util.sqlException(Unknown Source)

However, if I specify an absolute path, it works flawlessly

portal.jdbc.url=jdbc:hsqldb:file:d:\\TomcatServer\\apache-tomcat-7.0.10\\wtpwebapps\\myportal-app\\data\\mydb

Should I miss understanding JVM working directory on a web app? Any help is appreciated.

bnguyen82 · Accepted Answer · 2012-07-03T06:43:10.320

It seems that Tomcat doesn't provide us a properties variable (like "webroot") to refer to my application context. So my solutions is to register such a properties in Servlet context listener.

My code:

 public class WebAppPropertiesListener implements ServletContextListener{
    @Override
    public void contextInitialized(ServletContextEvent sce) {
        String rootPath = sce.getServletContext().getRealPath("/");
        System.setProperty("webroot", rootPath);

    }
    ...
 }

And add listener in web.xml before Spring context is triggered

<listener>
    <listener-class>com.iportal.util.WebAppPropertiesListener</listener-class>
</listener>

<listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

Then I put the property in Hsqldb setting.

portal.jdbc.url=jdbc:hsqldb:file:${webroot}WEB-INF/classes/data/mydb

Hope this helpful for someone who may run into the same problem.

score 1 · Answer 2 · answered Jul 02 '12 at 07:32

1

HSQLDB 2.2.8 and later allows a variable in the connection URL. The variable can be any system property, such as the web application directory path.

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_variables_url

answered Jul 02 '12 at 07:32

fredt

24,044
3
40
61

Thanks for help. But I don't find the propertie placeholder for web application directory path. Could you please point it out, Fredt? – bnguyen82 Jul 02 '12 at 07:58
See this one about Tomcat http://stackoverflow.com/questions/5115339/tomcat-opts-environment-variable-and-system-getenv – fredt Jul 02 '12 at 10:37

score 1 · Answer 3 · answered Jul 02 '12 at 08:19

1

Refer Section "Variables In Connection URL"

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html

Example

jdbc:hsqldb:file:${mydbpath};sql.enforce_types=true

answered Jul 02 '12 at 08:19

Rahul Agrawal

8,913
18
47
59

Thanks. I read through but I don't see how can I set variable mydbpath. Do you have any hints? – bnguyen82 Jul 02 '12 at 09:12
Define Environment Variable "user.home", and try first type of variable usage in connection path as "If the database part of a file: database begins with ~/ or ~\ the tilde character is replaced with the value of the system property "user.home" resulting in the database being created or accessed in this directory, or one of its subdirectories. In the example below, the database files for mydb are located in the user's home directory. jdbc:hsqldb:file:~/mydb;shutdown=true" – Rahul Agrawal Jul 02 '12 at 09:36

score 1 · Answer 4 · edited Jun 16 '20 at 08:45

1

Figured it out on Tomcat 8 thanks to fredt's answer.

url = "jdbc:hsqldb:file:" + mydbpath;

Where mydbpath is a variable with realtive path to the database specified. This somehow works

edited Jun 16 '20 at 08:45

Nikolas Charalambidis

40,893
16
117
183

answered Apr 05 '17 at 08:24

David Galaš

11
1

Relative path to hsqldb files in a web app doesn't work?

4 Answers4

Linked