Distributing a Sqlite Database in a Tomcat WAR File

Given a pre-existing Sqlite database – that’s another story for another time – including it in a WAR file to be built by Maven and installed on Tomcat 8 is simple:  simply include the database in the application’s WEB-INF folder and presto, it’s there!

Referencing Sqlite in a Maven project is also simple – just add the dependency to the POM:

<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.7.2</version>
</dependency>

Unfortunately, Tomcat did not have a Sqllite library available to it, so I included the Sqlite library in the WEB-INF\lib folder, which is part of the classpath for Tomcat applications. 

During the build, Maven packaged up the JAR into the WAR file and, after deployment, the redistribution libraries are extracted to the app’s /lib folder under Tomcat:

Practically done, I thought.  But hold the celebration: Opening the database from the Java servlet is a more challenging problem. 

(Typical of Java, easy things are harder than they should be and should-be-easy things are a nightmare.)

The root issue at this point is finding the Sqlite database on the Tomcat server.  What should the file system path be in the connection string?  On a foreign server, like the one my team will be using for our project, who can say where on disk the application and its resources will be?

The database location is obviously relative to the application, so the problem seemed surmountable.  .NET has simple mechanisms for an application to determine where it is running; Java less so.  One of the answers to a Stack Overflow question on the subject helped me.  The accepted answer is of no help since we can’t count on a database file being deployed on the server, but MattC’s answer was more useful:  Use JNDI to read a configuration file and explode a Tomcat-relative path.

Following Matt’s advice seemed easy.  First, add a META-INF/context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Context> 
  <Resource name="jdbc/testdb"
            auth="Container"
            type="javax.sql.DataSource"
            driverClassName="org.sqlite.JDBC"
            url="jdbc:sqlite:${catalina.base}/webapps/sqlwebexample/WEB-INF/db/test.db"
            factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory">
  </Resource>
</Context>

Second, add a resource reference to the WEB-INF/web.xml:

<resource-ref>
  <description>CompanyDatabase</description>
  <res-ref-name>jdbc/testdb</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

Third, use a JNDI context to look up the resource path and resolve the Tomcat root path macro by changing

c = DriverManager.getConnection("jdbc:sqlite:./test.db");

to

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/testdb");
c = ds.getConnection();

No problem.

That is, of course, where the problems began.  After making these changes, Maven was unable to build the project because the javax.naming library was not available to supply the JNDI classes.  Maven didn’t have it either, meaning I had to download it from Oracle manually and add it to the project’s local Maven repository. 

This is yet another story, the short version of which is that I ended up with a C:\Deve\CS6440\sqlwebexample\lib\javax\naming\jndi\1.2.1\jndi-1.2.1.jar file referenced by Maven.

(This is also how I referenced the Microsoft SQL Server JDBC driver in yet another story, so the technique definitely has its time in the sun.)

With JNDI in place Maven built the WAR file and for a moment my confidence was high.  But I was quickly brought down to earth by this run-time exception:

ClassNotFoundException: org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory…

WTF?  I’m in Tomcat, so surely it knows about its own libs.  This one got confusing because there are at least 2 dbcp libraries being supported and several old versions as well and I found myself adding another JAR file to the local Maven repo before an answer to this underappreciated Stack Overflow gem clued me in to Tomcat 8’s use of yet another version of the dbcp library in the META-INF/context.xml file:

factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory">

At this point the committed reader will note that Matt’s information came from a question about Tomcat 6.  A real Java developer probably would have caught this one much sooner.

Anyway, another tweak, another WAR file, and another exception:

java.lang.AbstractMethodError: com.sqlite.jdbc.Connection.isValid(I)Z

WTF again!  Scrounging about it seemed that the error had to do with connection pool validation and version 3 vs. version 4 drivers.  Eventually I found this snipped in the Tomcat docs:

validationQuery

(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can’t throw a SQLException. The default value is null. Example values are SELECT 1 (mysql), select 1 from dual (oracle), SELECT 1 (MS Sql Server)

So it was back to META-INF/context.xml to add this attribute to the Resource tag:

validationQuery="SELECT 1"

And with that, finally, success:  a Java servlet app deployed to Tomcat with a pre-populated, embedded Sqlite database up and running!

Lesson Learned:  However much Visual Studio and Eclipse may suck at times, they’re a heck of a lot better than having to hand-configure this sort of business like I’m doing now.