Database Connection Tomcat Server 5.0 and 5.5 Step 16B

Connecting to the database using a Tomcat server is done differently depending on which version of Tomcat you are using. I found it to be easier with version 5.0. For both of them, you need to make sure you have the mySQL jar file in the common/lib directory of Tomcat or the jar file for any other database you are attempting to work with.

You can download the mySQL jar file from this site:
http://dev.mysql.com/downloads/connector/j/5.1.html
***********************************
Connect To mySQL with
Tomcat 5.0
***********************************
You need to create a xml file in your Tomcat/conf/Catalina/localhost directory.
You will need to name it the same name as the war file that you deploy.

In our case we will call it UserBook.xml.

Paste the following code into this file.

UserBook.xml

<?xml version='1.0' encoding='utf-8'?>
<Context docBase="UserBook"
path="/UserBook" reloadable="true">
<Resource name="jdbc/catalog"

type="
javax.sql.DataSource"/>
<ResourceParams name="jdbc/catalog">
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/catalog</value>
</parameter>

<parameter>
<name>maxIdle</name>
<value>5</value>
</parameter>

<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>

<paramenter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</paramenter>

<parameter>
<name>maxActive</name>
<value>-1</value>
</parameter>

<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>

<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>

<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory<
/value>
</parameter>

<parameter>
<name>username</name>
<value>UserBook</value>
</parameter>

<parameter>
<name>password</name>
<value>UserBook123</value>
</parameter>

</ResourceParams>


</Context>

The docBase should be the name of the war file that is deployed. The path is the path of the war file directory. The resource name is the name you will use in your code to access this connection. You need to properly set your parameters. The url is the location of the database. In our case, we are accessing the database on our local machine and the database name is catalog. You set other parameters such as removeAbandoned which removes the abandoned connections to the database which is important. You also should put the database username and the database password.

***********************************
Configure to Connect To mySQL
with Tomcat 5.5
***********************************

You need to create a xml file in your Tomcat/conf/Catalina/localhost directory.
You will need to name it the same name as the war file that you deploy.
In our case, we will call it UserBook.xml. Paste the following code into this file. This will create a global connection pool for all of your applications.

UserBook.xml


<?xml version='1.0' encoding='utf-8'?>
<Context docBase="${Catalina_Home}/webapps/UserBook"
privileged="true" antiResourceLocking="false"
antiJARLocking="false" >
<Resource name="jdbc/catalog" auth="Container"
type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/catalog?autoReconnect=true"
username="UserBook" password="UserBook123"
maxActive="20" maxIdle="10" maxWait="-1"/> <
ResourceLink name="jdbc/catalog" global="jdbc/catalog"
type="javax.sql.DataSource" /> </Context>



You use the variable {Catalina_Home} to get to the location of your project.
The ResourceLink is linking your UserBook application to your data connection
in your server.xml file.

In your Tomcat/conf folder you should see a file called server.xml.
Open this file up and paste the following code under the
globalnamingresources tag.

Add to server.xml


<Resource name="jdbc/catalog" auth="Container"
type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/catalog?autoReconnect=true"
username="UserBook" password="UserBook123" maxActive="20"
maxIdle="10" maxWait="-1"/>



Make sure the username and password are correct and the resource name
is the same name as the resource name in your UserBook.xml
folder in your tomcat/catalina/localhost directory.

Add this to your web.xml file in your project WEB-INF folder under the servlet tag.


<resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/catalog</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>


***********************************
Code in both versions of Tomcat to
access the database connections.
***********************************

I create a package in my project called com.util and there I put a class called JDBCManager. This is the class I will use to create my database connection.

JDBCManager.java



package com.util;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
/**
* Written by Greg Dias
*
* Purpose: This class is used to connect to a database using
* a JDBC connection.
*/
public class JDBCManager
{
private String connectionName;
private Connection connection;
private String query;
public JDBCManager()
{
connectionName = "";
connection = null;
query = "";
}
/**
* Purpose: Will need to set the JDBC Name
* according to the name from the application
* web.xml file.
* @param name
*/
public void setJDBCName(String name)
{
connectionName = name;
}
/**
* Purpose: Should be called to setup the connection.
* @throws Exception
*/
public void setUp()
throws Exception
{
try
{
Context ctx = new InitialContext();
if(ctx == null)
throw new Exception("Boom - No Context");
DataSource ds = (DataSource)ctx.lookup((new StringBuilder()).append("java:comp/env/").append(connectionName).toString());
if(ds != null)
connection = ds.getConnection();
ctx.close();
}
catch(Exception e)
{
throw new Exception((new StringBuilder()).append("Error in setUp").append(e).toString());
}
}
/**
* Purpose: Is used to set the query.
* @param query
*/
public void setQuery(String query)
{
this.query = query;
}
/**
* Purpose: Returns the result set that contains the data
* from the query.
* @return ResultSet
* @throws Exception
*/
public ResultSet getSet()
throws Exception
{
ResultSet rst = null;
try
{
if(connection != null)
{
Statement stmt = connection.createStatement();
rst = stmt.executeQuery(query);
}
}
catch(Exception e)
{
throw new Exception((new StringBuilder()).append("Error getting Result Set").append(e).toString());
}
return rst;
}
/**
* Purpose: Executes the query without returning a result set.
* @throws Exception
*/
public void execute()
throws Exception
{
try
{
Statement stmt = connection.createStatement();
stmt.execute(query);
stmt.close();
}
catch(Exception e)
{
throw new Exception((new StringBuilder()).append("Error executing query").append(e).toString());
}
}
/**
* Purpose: Must be called to tear down the connection.
*
*/
public void tearDown()
{
try
{
connection.close();
}
catch(Exception e) { }
connection = null;
query = "";
connectionName = "";
}
/**
* Purpose: Returns the query as a string.
* @return String
*/
public String getQuery()
{
return query;
}

}


To test your connection, add this coded to your setUser method in your UserAction class. This will test if you successfully connected to your database using my JDBCManager class.

Test your connection with following code:



JDBCManager managerJDBC = new JDBCManager();
managerJDBC.setJDBCName("jdbc/catalog");
managerJDBC.setUp();
managerJDBC.setQuery("select BookName from book");
System.out.println("Query: "+ managerJDBC.getQuery());
try
{
ResultSet rst = managerJDBC.getSet();
while(rst.next())
{
System.out.println("Book Name: "+ rst.getString("BookName"));
}
}
catch(Exception e)
{
System.out.println("Error with result set: "+e);
}
managerJDBC.tearDown();


Start Tomcat and go to your localhost and see if the bookname prints out in your console from your database. If it does, then you have successfully connected to the database.
Go To Hibernate Step 16C