All content and software implementations are not Ericsson-supported products. Note that Ericsson does not represent nor hold responsibility for the content from this area.

This month's tip deals with adding a database connection to Tomcat and using it from JNDI. We will explain how to add a database connection from Tomcat GUI, as well as through configuration files, and how to use the connection from the source code.

There are two ways of adding a database connection to the Tomcat server, through the web interface of Tomcat or through configuration files. Both ways are explained here.

Working with the web interface for Tomcat administration is quite simple:

1. Launch the Tomcat Web Server Administration tool by typing the appropriate URL in your web browser; for example: http://localhost:8080/admin                              
2. On the left side, select "Datasources" in the Resources Menu.                              
3. Choose "Create New Data Source" in the available actions.                              
4. You will see a list of the parameters required to make the database connection. Enter these parameters:
   
   JNDI name: required to access the database connection using Java Naming and Directory Interfaces, for example the JNDI name could be "PointBaseJNDI".
   
   Data Source URL: the path of the database server. For example, if the database is Pointbase, the URL would be "jdbc:pointbase:server://machinename:9092/pointbasedb", where "pointbasedb" is the name of the pointbase database used in this example.
   
   For Microsoft Access, the URL would look like this "jdbc:odbc:AccessDsn", where "AccessDsn" is the name of the ODBC data source, defined in the Data Sources inside the Windows Control Panel as shown in the screenshot below.
   
   
   For Microsoft Access, a JDBC-ODBC driver is used. A JDBC-ODBC driver works as a bridge for translating JDBC commands to the ODBC commands.
   
   JDBC Driver Class: the class required for handling the connection with the database, this class comes from the JDBC driver. For example, the driver class would be "sun.jdbc.odbc.JdbcOdbcDriver" for Microsoft Access and for Pointbase it would be "com.pointbase.xa.xaDataSource".
   
   User Name & Password: required for authentication with the database.
   
   Max. Active Connections: required by Tomcat for managing the connection pool with the database. The server stops creating new connections when this value is reached.
   
   Max. Idle Connections: this value specifies that the server should close the idle connections except this many connections so that there are not too many idle connections occupying resources.
   
   Max. Wait for Connection: specifies the timeout in milliseconds. The server will stop an attempt to create the connection with the database when this value is reached.
   
   Validation Query: you can specify an SQL query to validate connections before the connection is returned to the client.               
5. After entering the above parameters, save the settings and press the commit button the make persistent changes.

The second way to define a data source is to directly enter the required information in the %CATALINA_HOME%\conf\server.xml file under the GlobalNamingResources tag. Here is the connection information for Pointbase. You can replace the Driver class and URL for Microsoft Access as explained earlier.

Resource name="PointBaseJNDI" type="javax.sql.DataSource"/>

ResourceParams name="PointBaseJNDI">
parameter>
  name>maxWait/name>
  value>5000/value>
/parameter>
parameter>
  name>maxActive/name>
  value>4/value>
/parameter>
parameter>
  name>password/name>
  value>password/value>
/parameter>
parameter>
  name>url/name>
  value>jdbc:pointbase:pointbasedb/value>
/parameter>
parameter>
  name>driverClassName/name>
  value>com.pointbase.jdbc.jdbcUniversalDriver/value>
/parameter>
parameter>
  name>maxIdle/name>
  value>2/value>
/parameter>
parameter>
  name>username/name>
  value>username/value>
/parameter>
/ResourceParams>

To access the JNDI resource, we need to create a reference of the resource in the application. We can either create a local reference or a global reference to the JNDI resource.

For this example, we chose to create a global reference to the JNDI resource in the ROOT context of Tomcat. To do this, we can again use the Web Administration Tool.


As shown in the diagram above, we created a global reference to the JNDI resource ("PointBaseJNDI") created earlier. The resource reference name is "PointBaseJNDIRef". This name would be used in the application to access the database.

To manually add the XML global reference we can add the following line under the Context tag of %CATALINA_HOME%\conf\Catalina\localhost\ROOT.xml file.

ResourceLink global="PointBaseJNDI" name="PointBaseJNDIRef" type="javax.sql.DataSource"/>

This code snippet shows how the JNDI resource can be used to get the database connection from a JSP page.

Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("PointBaseJNDIRef");
Connection conn = ds.getConnection();
conn.close();

Here is the zip file that includes the complete JSP code, server.xml and ROOT.xml files used in this example.

Rehman Adil