Embedding an H2 Database within the WEB-INF Directory

Question

I have an embedded H2 Database I'd like to put in the WEB-INF directory of a web application.

What is the proper way to refer to this in a JDBC url?

Ideally I'd like a solution that would work both for a WAR, and an expanded WAR (If possible).

Thank-you for your help!

FYI, I've tried the following:

jdbc:h2:/WEB-INF/data/myDB;CIPHER=AES

But this results in:

org.h2.jdbc.JdbcSQLException: A file path that is implicitly relative to the current working directory is not allowed in the database URL "jdbc:h2:/WEB-INF/data/myDB;CIPHER=AES". Use an absolute path, ~/name, ./name, or the baseDir setting instead. [90011-187]

Changing this to: jdbc:h2:./WEB-INF/data/myDB;CIPHER=AES

Results in the following error, which clearly shows its trying to put my database in Tomcat's bin directory, rather than the true WEB-INF directory where I want it:

org.h2.jdbc.JdbcSQLException: Error while creating file "C:/Program Files/Apache Software Foundation/Tomcat 7.0/bin/WEB-INF" [90062-187]

Shit

Depending on which application servers you will need to support you should be careful with putting files in webinf. Websphere is very picky in this area. Do you write to the database or is it readonly ? Better allow storing the database in a separate directory and allow configuration of the path – Pentacle

Its read-only, and should only be deployed on Tomcat, preferably in WAR format. – Shit

You are not allowed to write in program files, try a different path – Pentacle

I understand I'm not allowed to write in Program Files. The WEB-INF directory is also not located under the bin directory of Tomcat either. My point in showing the error is that the relative paths do not have the desired effect. – Shit

It would not hurt if you examples match reality ;-) – Pentacle

Marged, The Program Files is listed as an example of an error when I try a syntax attempting to put my database in the WEB-INF directory. I do not understand why there is any confusion, but I'll edit my post to make it even more clear. – Shit

is there a way to get the root dir of the expanded war file in tomcat? if so, then you could use this variable to change your jdbc url. Apparently you're using h2 1.4.x, so relative path may not work well. I've only tried with h2 1.3.176. I would advise using a servlet listener instead and use the tcp mode of h2. You'll get better control of the location of your physical database. – Therefor

Christian, that sounds like an interesting idea. I will have to look into it. If that will allow me to store things in WEB-INF, that could definitely be an acceptable answer. – Shit

@Shit perhaps the solution can be found in my answer – Pentacle

@Pentacle Your answer does not address having the DB in a WAR. Using a context listener as Christian Michon suggested sounds like a much more viable answer as a result. That is the route I will be pursuing if there are no better options. When I switch over to that, I will post the answer here if Christian hasn't already posted an answer to accept. – Shit

@Shit You can store the DB in the WAR and Tomcat will expand the WAR and then you can access the DB. This is what is working here since 2011. If you want to read the database like a ressource, that is stored in a WAR, this won't work, yes. So please enlighten me in which dimension this does not answer your question. I read it again and still don't see where my answer and your question don't match. – Pentacle

@Marged: please see my comment to your answer. Your answer implies static paths which must be written in java code itself. What happens if someone decides to expand the war file with another name than connector-manager? – Therefor

@ChristianMICHON This is what I described as a downside to my suggested solution. I will delete my answer because yours has more advantages as it dynamically determines the path. – Pentacle

@Pentacle thanks for the pointers on Catalina internal variables – Therefor

Answer 1

I managed to make the embedded solution work without AES like this:

try {
    Class.forName("org.h2.Driver");
    Connection conn = DriverManager.getConnection(
        "jdbc:h2:" + getServletContext().getRealPath("/") + 
        "/WEB-INF/data/myDB", "sa", "");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES");
    while (rs.next()) {
    }
    rs.close();
    stmt.close();
    conn.close();
} catch(SQLException e) {
} catch(ClassNotFoundException e) {
} finally {
}

This was tested with H2 1.3.176 on Tomcat8. It should work with H2 1.4 and CIPHER=AES provided the embedded database is already inside the war file I guess.

The idea is the following: you need to get the absolute path, and that deployment path may not be the same depending on how you deployed the war file.

So we need to use the servlet context and request the real path. For this we use getServletContext().getRealPath("/") and append /WEB-INF/data/myDB to it as per your needs.

I did not test the CIPHER=AES part as I've never used it.

Update:

Getting a good reference to the servlet context is tricky. One could use a raw request, get the underlying session and then get to the servlet context.

But it would be good to have the embedded H2 database opened as soon as the application is deployed/started in Tomcat, and closed properly as soon as the application is stopped.

In order to perform that, the use of a listener is needed. Here's what I propose as an update to my previous answer. This time the solution is complete with AES CIPHER and it should be easy to plug into your code.

Suggestion: the listener java code can be easily modified to start a H2 tcp server as well, useful to enable the automatic mixed mode (embedded+tcp).

Add 3 lines to the file web.xml:

<listener>
  <listener-class>com.mine.MyServletContextListener</listener-class>
</listener>

File MyServletContextListener.java:

package com.mine;

import javax.servlet.*;
import java.sql.*;

public class MyServletContextListener implements ServletContextListener {
  Connection conn;

  public void contextInitialized(ServletContextEvent sce) {

    try {
      Class.forName("org.h2.Driver");
      conn = DriverManager.getConnection( "jdbc:h2:" + sce.getServletContext().getRealPath("/") + "/WEB-INF/data/myDB;CIPHER=AES", "sa", "aespassword dbpassword");
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES");
      while (rs.next()) {
      }
      rs.close();
      stmt.close();
    } catch(SQLException e) {
    } catch(ClassNotFoundException e) {
    } finally {
    }

  }

  public void contextDestroyed(ServletContextEvent sce) {

    try {
      conn.close();
    } catch(SQLException e) {
    } finally {
    }

  }

}

Therefor

@Doug: in the end I did not go the tcp mode with servlet. I guess this will get you some better performances using the embedded mode regardless where the war was expanded. – Therefor

@Shit have you tested my suggested fix? – Therefor

@Shit Please make sure you put this code in a class extending HttpServlet, else the getServletContext() will not be available. As I do not have the complete view of your code, your mileage may vary... – Therefor

@Shit I fixed how to get the servlet context without using requests, using a listener instead. – Therefor

Sorry, I'm just getting back to this :-). I temporarily worked around this by putting the db in memory, but I will be switching over to the context-listener option. This is definitely much more what I'm looking for, but the Context Listener examples I found on the net still didn't handle the WEB-INF database. Thank-you for the time you put into putting together a great answer! – Shit

@Shit You're welcome! I will reuse this code in one of my project soon, so your question helped me re-prioritizing my backlog too. :-D – Therefor