HowTo configure a JNDI data source for lift and Tomcat

From Lift

The following is an example of configuring lift and Tomcat 6.x to use a JNDI data source. As with HowTo configure a JNDI data source for lift and Jetty -- from which we've borrowed heavily -- we'll assume the data source is a MySQL database.

First, if you haven't already, make sure you have a recent version of the mysql-connector JAR. The easiest way to do this is to add a dependency like the following to your pom.xml file and let Maven obtain it for you:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.0.8</version>
</dependency>

As Tomcat will be managing the database connections it needs to be given a copy of this JAR, which is done by copying the JAR file to $CATALINA_HOME/lib. If you are using a third-party database pooling library, it will also need to be copied to $CATALINA_HOME/lib.

Next we configure the data source inside a Tomcat <Context>. There are a number of ways to specify this, as described in the Apache Configuration Reference, and which one you choose depends on your requirements. In this example, we will configure the database as a <Context> inside the server.xml file.

Edit $CATALINA_BASE/conf/server.xml and locate (or create) the <Context> element for your application. For example:

...
<Host ... >
   <!-- In this example we are mapping the web applications from my-website-1.0-SNAPSHOT.war
        that has been dropped into the tomcat/webapps folder to http://localhost:8080/ 
        assuming port 8080 is the port Tomcat is listening on. -->
   <Context displayName="My Web App"
         docBase="my-website-1.0-SNAPSHOT" path="" reloadable="true">

       <Resource
           name="jdbc/MyDataSource" auth="Container" type="javax.sql.DataSource"
           username="usernameHere" password="passwordHere" 
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/databaseNameHere?autoReconnect=true"
           maxActive="8" maxIdle="8" maxWait="30000" 
           removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="true" />

     </Context>
</Host>
...

(Replacing databaseNameHere, usernameHere, and passwordHere with the strings specific to your database and giving MyDataSource another name if you like.)

This example uses the default connection pooling that ships with Tomcat 6. You can learn about the pooling settings via the DBCP Configuration documentation, and look at other pooling examples in the Tomcat 6.0 JNDI Datasources HOW-TO.

Rather than place the <Resource> inside the <Context> for a single web application, you could alternatively place the <Resource> configuration inside the <GlobalNamingResources> section of the server.xml file if you wanted to share the database configuration across multiple web applications.

At this point Tomcat knows about the data source, but your application does not. To tie the two together you also have to register your new data source in WEB-INF/web.xml. Add the following lines to the main <webapp>...</webapp> section of this file:

<resource-ref>
   <description>This is the true source of enlightenment.</description>
   <res-ref-name>jdbc/MyDataSource</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>

The final step in setting up the data source is to tell lift about it. Do this by adding a single line near the very beginning of Boot.scala:

DefaultConnectionIdentifier.jndiName = "jdbc/MyDataSource"

This line should be executed before, say, the one that looks something like if (!DB.jndiJdbcConnAvailable_?) DB.defineConnectionManager(DefaultConnectionIdentifier, DBVendor).

And that's it.

To test this out you could try the following:

Create a database, such as:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 

mysql> CREATE DATABASE databaseNameHere DEFAULT CHARACTER SET='utf8' DEFAULT COLLATE='utf8_general_ci';
Query OK, 1 row affected (0.38 sec)

mysql> GRANT ALL PRIVILEGES ON databaseNameHere.* TO 'usernameHere'@'localhost' IDENTIFIED BY 'passwordHere'; 
Query OK, 0 rows affected (0.25 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.07 sec)

mysql> quit
Bye

Run the lift default web site example, after making the modifications to Boot.scala described above:

$ mvn compile war:war
$ cp target/my-website-1.0-SNAPSHOT.war $CATALINA_BASE/webapps/
$ cd $CATALINA_BASE/bin
$ ./startup.sh

Check the $CATALINA_BASE/logs/catalina.out log and you'll see tables being created, which you can confirm by:

mysql> DESCRIBE users;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| firstname    | varchar(32)         | YES  |     | NULL    |                | 
| lastname     | varchar(32)         | YES  |     | NULL    |                | 
| email        | varchar(48)         | YES  | MUL | NULL    |                | 
| locale       | varchar(16)         | YES  |     | NULL    |                | 
| timezone     | varchar(32)         | YES  |     | NULL    |                | 
| password_pw  | varchar(48)         | YES  |     | NULL    |                | 
| password_slt | varchar(20)         | YES  |     | NULL    |                | 
| textarea     | varchar(2048)       | YES  |     | NULL    |                | 
| validated    | tinyint(1)          | YES  |     | NULL    |                | 
| superuser    | tinyint(1)          | YES  |     | NULL    |                | 
| uniqueid     | varchar(32)         | YES  | MUL | NULL    |                | 
+--------------+---------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

mysql>