Deploying the H2 database to the WEB-INF directory

Question

I have a built-in H2 database that I would like to put in the WEB-INF directory of the web application.

What is the correct way to reference this in a JDBC url?

Ideally, I would like to find a solution that will work for both WAR and extended WAR (if possible).

Thanks for the help!

FYI, I tried the following:

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

But this leads to:

 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 parameter to: JDBC: h2: ./ WEB-INF / data / MyDB; CIPHER = AEC

The results are in the following error, which clearly shows his attempt to put my database in the Tomcat bin directory, and not in the real WEB-INF directory, where I want:

 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 an embedded solution 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 has been tested on H2 1.3.176 on Tomcat8. It should work with H2 1.4 and CIPHER=AES , if the embedded database is already in the war file, I think.

The idea is this: you need to get the absolute path, and this 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. To do this, we use getServletContext().getRealPath("/") and add /WEB-INF/data/myDB to it according to your needs.

I have not tested the CIPHER=AES , as I have never used it.

Update:

A good reference to the servlet context is complicated. You can use a raw request, get a basic session, and then go into the servlet context.

But it would be nice if the embedded H2 database opened as soon as the application was deployed / launched in Tomcat and closed correctly as soon as the application is stopped.

To do this, you must use a listener. Here I suggest updating my previous answer. This time, the solution is bundled with AES CIPHER, and it is easy to connect to your code.

Suggestion: The listener's Java code can be easily changed to start the H2 tcp server, useful for enabling automatic mixed mode (built-in + tcp).

Add 3 lines to 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 { } } }