Embedding an H2 Database within the WEB-INF Direct

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]

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 {
    }

  }

}