Embedding Databases in a WAR File

Lately I needed a Java Web Application to have its own small database. I know the common/good practice is to have Web apps connect to a DB through a database pool but this requires some user configuration (at least copying the DB driver to the lib folder of the app server) when deploying the app. Since the Web app was supposed to be deploy-and-play this was not an option.

Thus, we decided to embed a SQLite database in the app. This worked fine as long as I was locally testing the application. However, when deploying to a production Tomcat instance I started getting these exceptions:

java.sql.SQLException: opening db: 'restfulepcis.db': Permission denied
at org.sqlite.Conn. init(Conn.java:117)
at org.sqlite.Conn. init(Conn.java:49)
at org.sqlite.JDBC.connect(JDBC.java:86)

The reason is pretty clear, on the production server I did not have permission to write to the Tomcat working directory.

I was left with two solutions:
1) have the directory where the database has to be created as a config parameter
2) find a directory that’s universally accessible from an app in an app server.
Since 1) involved user-configuration it was not an option.

There is actually a folder to which any Web app can write, the folder where the app is deployed (or at least my knowledge). The location of this folder can be found by calling:


ServletContext context = servletContext.getRealPath("/")


from a servlet. Since my app is a JAX-RS (RESTful) application I can obtain it by CDI (Contexts and Dependency Injection):

@Context
ServletContext servletContext;

/**
* Returns a representation of the EPCIS REST Adapter home resource according to the requested mime type
*
* @param context
* @return an instance of javax.ws.rs.core.Resource
*/
@GET
public Resource getRESTfulEPCIS(@Context UriInfo context) {
System.setProperty("sqlite.system.home", servletContext.getRealPath("/"));
RESTfulEPCISBusinessLogic logic = new RESTfulEPCISBusinessLogic();
return logic.getRESTfulEPCIS(context);
}


I then load the sqlite.system.home from my database manager class (which is not a Servlet) and use it as path in my database connection URI:


...
String sqliteHome = System.getProperty("sqlite.system.home");
...
private Connection getConnection() {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection("jdbc:sqlite:" + sqliteHome + DB_NAME + ".db");
...


Note that for Apache Derby, you can make it even easier by simply setting the derby.system.home in the servlet class. Then, you do not need to read it manually from your database manager class as it is read automatically when loading the driver:

System.setProperty("derby.system.home", servletContext.getRealPath("/"));