How to configure sqlite in tomcat ?

[dgiscool]

Hello,

Can you please provide the steps on how to use sqlite in tomcat ? In
my application, I am using multiple sqlite databases 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,
Deepak

[KewlCat]

Hello

Depending on how you access your databases the solution will differ. This is how I use my sqlite databases in Tomcat :

 * Define a Realm in the META-INF/context.xml file

    <Realm className="org.apache.catalina.realm.JDBCRealm"
                name="Realm MyDomain"
          driverName="SQLite.JDBCDriver"
       connectionURL="jdbc:sqlite:/${catalina.home}/webapps-mydomain/ROOT/WEB-INF/sql/mydomainusers.sqlite"
           userTable="users" userNameCol="login" userCredCol="pass"
       userRoleTable="users" roleNameCol="role" />

* Define a datasource in META-INF/context.xml file

    <Resource name="jdbc/DataSourceMyDomain" auth="Application" type="javax.sql.DataSource" driverClassName="SQLite.JDBCDriver"
               url="jdbc:sqlite:/${catalina.home}/webapps-mydomain/ROOT/WEB-INF/sql/mydomaindata.sqlite"
           factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" />

 * Define a resource-ref in the WEB-INF/web.xml file

    <resource-ref>
        <description>MyDomain Database</description>
        <res-ref-name>jdbc/DataSourceMyDomain</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Application</res-auth>
    </resource-ref>

 * Use the DataSource with a plain old Java object in my controllers

  Context myContext = new InitialContext();
  Context myContextEnv = (Context) myContext.lookup("java:comp/env");
  DataSource myDataSource = (DataSource) myContextEnv.lookup("jdbc/DataSourceMyDomain");
  myConnection = myDataSource.getConnection();
  etc.

Now, you might not need a Realm and/or you might want to define the datasource and realm inside the server-wide "conf/server.xml" file .... but I can guarantee that this solution works very well for me :-)

=^.^=

[KewlCat]

I missed the obvious, sorry. I did not answer your questions...
Since you wanted to connect to a different database based on the current user credentials (username, role, whatever...), while using my solution you just have to do a different lookup to the datasource (i.e. just change the string in the call to myContextEnv.lookup() ...) and you're good to go (or not, if the requests are different from one database to the other)
And, as you can see in my context.xml file, all my webapp's databases are stored within the webapp, inside the WEB-INF/sql directory.