How to configure SQLite in Tomcat 6?

Question

Can you please provide the steps on how to use sqlite in tomcat 6? I am using Xerial sqlite jdbc driver. In my application, I have got multiple sqlite databases (.db files) and would need to connect to a different sqlite database depending on what user logs in ? Where can I put all the .db files - with in the webapp root's directory or any where on the system or with in WEB-INF?

Thanks,

Deep

Answer 1

I just went through configuring sqlite3 with Tomcat 7. Everything is working now, so thought I'd share my setup.
- Download the JDBC driver (org.sqlite.JDBC) that lives in sqlite-jdbc-3.7.2.jar (or whatever the latest version is). https://bitbucket.org/xerial/sqlite-jdbc/downloads
and copy it to yourTomcat/lib
- You can copy the sqlite db anywhere you want to. For my setup, I created a 'dbs' directory under my tomcat install, and put it there.

Now set up your app. If you don't have a META-INF/context.xml file, then create one. This is a minimal file:

<?xml version="1.0" encoding="UTF-8"?>
<Context>  
  <Resource name="jdbc/yourdb" 
            auth="Container" 
            type="javax.sql.DataSource" 
            driverClassName="org.sqlite.JDBC"
            url="jdbc:sqlite:/${catalina.home}/dbs/yourDB.db"
            factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory">
  </Resource>
</Context>

Then add the following to WEB-INF/web.xml:

<resource-ref>
    <description>Reviews Database</description>
    <res-ref-name>jdbc/yourdb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

At this point, you should be good to go. Here is some sample code for accessing the database (I have a table 'admin' with a column 'username'):

public String getName() {
    LOG.info("getting name : " + this.name);
    try {
          Context ctx = new InitialContext();
          DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/yourdb");
          Connection conn = ds.getConnection();
          Statement stat = conn.createStatement();
          ResultSet rs = stat.executeQuery("select username from admin");
          this.name = rs.getString(1);
    } catch (SQLException se) {
          LOG.info(se.toString());
    } catch (NamingException ne) {
          LOG.info(ne.toString());
    }
    return this.name;
}

Note: some distros of tomcat don't come with tomcat.dbcp by default, if you run into problems it may be easier to reference the dbcp class that comes with commons, org.apache.commons.dbcp.BasicDataSourceFactory. I had this problem with tomcat.dbcp not included in my tomcat7 installation and once I switched the reference in context.xml everything was working fine.