Programming Tutorials

JDBC and Tomcat context settings

By: Norman Chap  

JDBC is a standard part of Java and provides a uniform API that can be used to access any relational database. The low-level JDBC API is the foundation for the other database access technologies discussed in this chapter, but many programmers use the JDBC API directly. If your application has fairly limited database access needs, JDBC might be all you need. The advantages of JDBC are simplicity and flexibility. There are only about 25 classes and interfaces in JDBC, and for the most part, to use them you need to know only the basics of SQL. It’s simple. You execute queries and updates written in standard SQL, and each query returns a ResultSet object containing the resulting rows and columns of data. The JDBC API is simple, but it still provides the flexibility to do just about anything you’ll need to do with a database.

The simplicity of JDBC is also a disadvantage. If you have a lot of queries and updates to do, using JDBC can be a lot of work. You’ll find yourself writing a lot of repetitive boilerplate code to build up query and update strings, iterate through the ResultSet objects returned by your queries, and map Java object fields to and from database table fields. In the next section, we’ll discuss how using an O/R persistence framework can eliminate the repetitive boilerplate coding required by JDBC.

JDBC gives you cross-database portability, which is wonderful, but that portability isn’t perfect. You still have to watch for SQL incompatibilities, data-type differences, and other problems. You still have to write a database creation script for each type of database you intend to support. JDBC is a relatively small and easy-to-use API.

Obtaining a JDBC Connection in a Web Application

All of the database access technologies that are discussed in this chapter are built on the foundation laid by JDBC. So, before you can use any of them you need to understand how to configure a JDBC database connection. Let’s discuss the two database connection mechanisms provided by JDBC: the java.sql.DriverManager and the javax.sql.DataSource.

Using the java.sql.DriverManager

The java.sql.DriverManager is a standard part of JDBC and a standard part of Java available to stand-alone Java programs, JSP applications, and application server-hosted Java EE applications in general.

If you’re going to use the DriverManager to obtain a database connection or if you’re configuring a software package that does, you’ll need to provide the following database connection parameters:

• The name of the JDBC driver class to be used
• The JDBC connection URL for your database
• Your database username-password combination

Using the DriverManager to obtain a database connection is a two-step process. First you must load your JDBC driver class by name, which causes it to become registered with the DriverManager. Second, you call the static DriverManager.getConnection() method, passing in your database connection parameters, and receiving in return a Connection ready for use. For example, the following code shows how to obtain a connection to MySQL by using the JDBC driver class org.gjt.mm.mysql.Driver:

Class.forName("org.gjt.mm.mysql.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/ag","username","password");

Note that if you explicitly load the class by using Class.forName(), you need to call the forName() method only once in your program. After the driver is loaded by the JVM, it is available to any code within the JVM.

Alternately, you can provide the driver class name as a system parameter to the JVM. The system parameter name is jdbc.drivers. Thus, to tell the JVM to load the MySQL driver, you would use a command line similar to the following:

java –Djdbc.drivers=org.gjt.mm.mysql.Driver MyProgram

If you need to load multiple drivers, they can be separated by using a colon as the delimiter. When you are using this technique, your code no longer needs to call Class.forName(). The JVM will automatically load the driver class for you (assuming that the class is on the CLASSPATH). However, those connection parameters are a problem. If you use the DriverManager, you’ll  have to manage those connection parameters. You know you cannot hard-code them in your Java classes or JSP pages and you cannot store them in the database, so you’ll probably end up storing them in a property file. When your application is installed, somebody will have to edit that property file.

Using a javax.sql.DataSource

The javax.sql.DataSource interface was introduced as part of the JDBC 2.0 Standard Extension to provide Java applications with a standard way to tap into the database connection management and connection-pooling functionality provided by Java application servers. If you use the javax.sql.DataSource approach, you no longer have to manage database connection parameters in your code. Instead, you declare the names of the data sources required by your application and you expect the administrator who installs your application to set up those data sources for you in the deployment environment. Within the container, the administrator configures a data source and binds it to a name.

In your application you need to declare this data source by adding a resource reference to the application’s web.xml file, as shown here:

<resource-ref>
<res-ref-name>jdbc/agdb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

After this is done, you can use the following code to look up this data source via JNDI and to obtain a database connection:

javax.naming.InitialContext ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds =
(javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/agdb");
Connection con = ds.getConnection();

Note that the lookup name is the name jdbc/agdb with an added prefix of java:comp/env/. This prefix indicates to the container that the resource is an internal resource.

Setting Up a javax.sql.DataSource

So, how do you set up one of these data sources? That depends on your application server, and every application server is a little different. Some application servers include a web interface that allows you to set up new data sources and to administer the connection pools associated with those data sources. Other application servers require you to edit configuration files.

For example, on the Tomcat servlet container, you can use either technique. You can
configure a global or application-specific data source by using Tomcat’s web-based administration
tool. On this web page, you enter the parameters for the JDBC driver class name, the connection URL, the JNDI name, the database URL, and the username and password for the database. After this preceding code snippets.

You can also create a global data source in a similar manner. You enter the same parameters as for an application specific data source.

After creating a global data source, you must configure a link from the application to the global data source. By using the Resource Links web page for the application, you enter the name used by your application to access the data source, and the JNDI name of the global resource. This maps the application name to the global name and allows different applications to use different names to access the global resource. 

You can manually configure your application’s data source. The configuration is stored in a file named context.xml in the application’s META-INF directory. The following is the Tomcat 5.0 context entry for this chapter’s example application :

<Context path="/ch09" docBase="ch09" debug="0">
<Resource name="jdbc/agdb" auth="Container" type="javax.sql.DataSource" />
<ResourceParams name="jdbc/agdb">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter><name>username</name><value>sa</value></parameter>
<parameter><name>password</name><value></value></parameter>
<parameter>
<name>driverClassName</name>
<value>org.hsqldb.jdbcDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:hsqldb:hsql://localhost</value>
</parameter>
</ResourceParams>
</Context>

Note Your application server may store the database configuration files in a different location and under a different name. Check your container’s documentation for information on how to configure database resources.

Within the Context element, you declare the data source as a resource named jdbc/agdb, and then you declare the parameters for the data source. As you can see, you specify the same parameters that you had hard-coded before into the JSP code. These include the JDBC driver class name, the database connection URL, the database username, and the corresponding password. If you need to change any of these values, you no longer have to modify the code of your application as you would when using the JDBC driver manager.

Note that before you can use JDBC, you need to ensure that your JDBC driver JAR file is in the right CLASSPATH. If you’re using the JDBC driver manager to obtain your connections, you can put your JDBC driver JAR in your application’s WEB-INF\lib directory. However, if you’re using a JDBC data source, you’ll need to ensure that your JDBC driver JAR is in your server’s CLASSPATH. On Tomcat, this means putting your JDBC driver JAR into the %TOMCAT_HOME%\common\lib directory.

Archived Comments