Get SQLite3 to work with Java with Apache Tomcat7 on Ubuntu Linux

SQLite3 is a simple and small relational database management system that works on a single file as a database. Very usefull for mini projects and hugely popular with apps on Android and other embedded systems.

I will be outlining steps here required to setup SQLite3 with Java and Tomcat on Ubuntu Linux machine.

Install SQLite3

Most modern linux distributions will have SQLite3 in its repositories. For Ubuntu or Debian based systems try the command below.

sqlitebrowser is a GUI client for SQLite.

Next ensure you have tomcat and java installed. If not run the below command. I have chosen Tomcat7 and OpenJDK7 here.

 <role rolename="manager-gui"/>
 <role rolename="manager-script"/>
 <user username="tomcat" password="s3cret" roles="manager-gui,manager-script"/>

If you had to make the above changes restart tomcat service.

Just for FYI, here are the tomcat files and folders for reference

/var/lib/tomcat # under conf contains config files like tomcat-users.xml, server.xml to change port from 8080 to anything else. Also contains the webapps folder where the applications are installed.

/usr/share/tomca7 #Contains the binaries and libraries of tomcat

/var/log/tomcat7 # Contains tomcat log files for debugging

Now in order to get Java working with SQLite3, get the sqlite jdbc driver from here. I chose the jar version sqlite-jdbc-3.7.2.jar because I was getting the below error with the latest one.

Exception in thread "main" java.lang.UnsatisfiedLinkError: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V
 at org.sqlite.core.NativeDB._open(Native Method)
 at org.sqlite.core.DB.open(DB.java:161)
 at org.sqlite.core.CoreConnection.open(CoreConnection.java:145)
 at org.sqlite.core.CoreConnection.(CoreConnection.java:66)
 at org.sqlite.jdbc3.JDBC3Connection.(JDBC3Connection.java:21)
 at org.sqlite.jdbc4.JDBC4Connection.(JDBC4Connection.java:23)
 at org.sqlite.SQLiteConnection.(SQLiteConnection.java:45)
 at org.sqlite.JDBC.createConnection(JDBC.java:114)
 at org.sqlite.JDBC.connect(JDBC.java:88)
 at java.sql.DriverManager.getConnection(DriverManager.java:579)
 at java.sql.DriverManager.getConnection(DriverManager.java:243)