CS636 Using a Database from a Web App via tomcat's JNDI catalog    

DataSource object: this code efficiently manages the short-lived database connections needed by web apps, via “connection pooling.”  It uses JDBC to the database for each such connection.

Tomcat maintains a JNDI (Java Naming and Directory Interface) catalog for its web apps. We can get tomcat to create a DataSource object for our Oracle server and put it in the JNDI catalog, so our web app can look it up by JNDI name "java:comp/env/jdbc/dbs3". We want another DataSource object for MySql, and a third for HSQLDB, so we can use that DB instead of Oracle, for fast testing.

Tomcat’s conf/context.xml: configuration for tomcat’s JNDI catalog: needs edits to work
on topcat: just username and password, mysql database name
at home: also dbs3.cs.umb.edu -> localhost, topcat.cs.umb.edu -> localhost, 3306 -> 3333 for tunnels

<!--ADDED for CS636: JNDI names for DataSource objects for our three database servers -->
<!--oracle on dbs3 -->
<Resource auth="Container" defaultAutoCommit="false" defaultTransactionIsolation="SERIALIZABLE" driverClassName="oracle.jdbc.OracleDriver" maxActive="8" maxIdle="4" name="jdbc/dbs3" password="xxxxx" type="javax.sql.DataSource" url="jdbc:oracle:thin@dbs3.cs.umb.edu:1521:dbs3" username="xxxxx"/>
<!--  mysql on topcat-->
<Resource auth="Container" defaultAutoCommit="false" defaultTransactionIsolation="SERIALIZABLE" driverClassName="com.mysql.jdbc.Driver" maxActive="8" maxIdle="4" name="jdbc/mysql" password="xxxxx" type="javax.sql.DataSource" url="jdbc:mysql://topcat.cs.umb.edu:3306/xxxxxdb" username="xxxxx"/>
<!--HSQL on local machine -->
<Resource auth="Container" defaultAutoCommit="false" defaultTransactionIsolation="SERIALIZABLE" driverClassName="org.hsqldb.jdbcDriver" maxActive="8" maxIdle="4" name="jdbc/hsql" password="" type="javax.sql.DataSource" url="jdbc:hsqldb:hsql://localhost" username="sa"/>

In a non-JPA web app such as pizza3, we can get access to the DataSource object by its JNDI name this way:
      InitialContext ic = new InitialContext(); // finds tomcat's JNDI repository in its JVM
   dataSource = (DataSource) ic.lookup("java:comp/env/" + jndiName);

Then when a request comes in, we can get a Connection by  Connection dbConn = dataSource.getConnection();

For running the project code outside the web container: we can create a DataSource object, and configure it with username, password, database url, etc. The code for this is in config/StandaloneDataSourceFactory.java. Then the code in configureServices() is the same for both in-container and out-of-container cases.

Advanced Topics (rest of doc not needed for PA2)

When we are using JPA, we can get it to do the JNDI lookup for us. All we have to do is put the right line in persistence.xml, as follows.

Using JPA to look up the JNDI resource:  web-persistence.xml of pizza4 for Oracle:   

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    <persistence-unit name="pizza3el" transaction-type="RESOURCE_LOCAL">
        <!-- use @Cacheable on invariant entities to enable safe shared caching (in web case) -->
           <!-- using <non-jta-source>'s JNI name "java:comp/env/jdbc/dbs3", JPA can
            look up a DataSource object held by tomcat, which has the url, user,
            password info configured at this spot for the client-server case-->    
            <!-- Configure simple SQL logging for demonstration. -->
            <property name="eclipselink.logging.level" value="FINE" />
            <property name="eclipselink.logging.thread" value="false" />
            <property name="eclipselink.logging.session" value="false" />
            <property name="eclipselink.logging.exceptions" value="false" />
            <property name="eclipselink.logging.timestamp" value="false" />

With this configuration, JPA/Eclipselink does the JNDI lookup during its initialization.

What is this “non-JTA” terminology? JTA is the JEE Java Transaction API, for distributed transactions, that is, transaction involving multiple databases (e.g. Oracle at two sites) or other resources that get involved in transactions. We’re using a single database (Oracle site or mysql site), so we don’t need JTA.  This also explains the RESOURCE_LOCAL transaction type, also meaning non-JTA, using only one transactional resource

Note that we are not required to access databases from a web app via JNDI.  We can use JDBC directly and if using JPA, we can put the JDBC parameters into the persisence.xml and use them the same way we set up in pizza2.  The advantage of using the JNDI lookup is that the DataSource object has a Connection pool shared by all web apps in the server, which is more efficient. In addition, it puts the configuration of the actual database in use outside the app deployment, under the control of the applications server manager, and gets the username and password for the database out of the app project, into a safer spot (we hope).

Web apps can do email too

The same mechanism can be used for other external resources needed by the web app. For example, a javax.mail.Session object for sending email, where the needed username and password are then put in context.xml, and given a JNDI name, instead of used in the application source. See Murach, pg. 460-481, and on pg. 481, near end of the method where username and password are used in web app code, not a good idea, but at least this is showing email going out from a web app. This can be rewritten to use JNDI, so that the username and password are not in the web app code.