Databases and Tomcat
Chapter 9: Databases and Tomcat
Using JDBC and Data Sources
Direct JDBC Database Access
Tomcat Data Sources
Data Access Objects (DAOs)
Most Web applications need to store information on a temporary or permanent basis. The most common repository for data storage is the ubiquitous relational database. In this chapter you will be using databases from within Tomcat Web applications using both direct JDBC access and the preferred approach of JNDI data sources. The Data Access Object (DAO) design pattern is shown as a method of encapsulating database access into a reusable component.
Using JDBC and Data Sources
Tomcat servlets and JSPs use JDBC in the same manner as any other Java program. In many Java programs, it is normal to ask the user to provide a username and password for database access. With Web applications, you would typically use a single database account for all users and encode the account name and password within either the servlet or JSP, or encapsulate database access using a data source (see the section "Tomcat Data Sources") or a DAO (see the section "Data Access Objects (DAOs)"). You must then add the business logic of the application to enforce any user security authorization and ensure the data integrity of the database.
The disadvantage to using JDBC from within a servlet is that the JDBC driver class, database connection string, user account name, and password are all hard-coded into the program. The hard-coded database details link the servlet to a specific database, complicating the move from a development environment (with test data) to a production environment (potentially using a different database supplier). Hard-coded details always reduce portability of code and should be avoided if at all possible.
A common approach in reducing coupling of the servlet to the database is to provide the database connection parameters using servlet initialization parameters (see the <init-params> element discussion in Chapter 7, "The Web Application Environment"). If in the future you change database vendors, move the database to a new server, move the tables into a new database, change the username, or change the password, you can do so without modifying the Java servlet (or JSP). This is a good technique; but, as you will be shown, there are other performance problems that suggest you shouldn't use direct JDBC access from within a servlet.
A connection pooling data source provides a solution to both the hard coding of database access details and the performance issues inherent in using direct database access. This chapter describes how to use Tomcat data sources after briefly discussing, and rejecting, using direct JDBC access from within a servlet.
Before we describe direct JDBC access and data sources, you need a bit of background information about the database tables and sample data used for the examples.
The Sample Database
Continuing with the currency converter example application, the code examples in this chapter use a currency definition table called Currency and a database exchange rate table called Exchange. The Currency table has columns for the ISO 4217 currency name as well as the currency's ISO country name and ISO language. The Exchange table has columns that define the three-character ISO 4217 code for the currency to convert from (src) and to (dst). The rate column defines the appropriate exchange rate. The two tables can be created in any SQL database with the following SQL:
create table Currency( language varchar(2), country varchar(2), name varchar(3) ); create table Exchange( src varchar(3), dst varchar(3), rate double );
You will need to create this table in your database and add some sample data in order to use the examples presented in this chapter. The following SQL will populate the table with suitable data:
insert into Currency values ('en','CA', 'CAD'); insert into Currency values ('de','DE', 'EUR'); insert into Currency values ('en','GB', 'GBP'); insert into Currency values ('en','US', 'USD'); insert into Exchange values ('CAD','EUR', 0.6955); insert into Exchange values ('CAD','USD', 0.6376); insert into Exchange values ('CAD','GBP', 0.4344); insert into Exchange values ('EUR','CAD', 1.4376); insert into Exchange values ('EUR','GBP', 0.6246); insert into Exchange values ('EUR','USD', 0.9166); insert into Exchange values ('GBP','CAD', 2.3019); insert into Exchange values ('GBP','EUR', 1.6011); insert into Exchange values ('GBP','USD', 1.4676); insert into Exchange values ('USD','CAD', 1.5685); insert into Exchange values ('USD','EUR', 1.0909); insert into Exchange values ('USD','GBP', 0.6813);
Tip - The CreateDB.java program available from the accompanying Web site for this book (browse to http://www.samspublishing.com and search for the ISBN 0-672-32439-3) can be used to create and populate the sample database tables.
Note - The examples in this chapter use the MySQL database available from http://www.mysql.com. MySQL is a popular, open source, SQL database available under the GNU General Public License (http://www.gnu.org/). Several JDBC drivers are available for MySQL. This chapter uses the MM.MySQL JDBC driver that is also provided under the GNU General Public License and is downloadable from the MySQL Web site. The MM.MySQL JDBC driver class name is org.gjt.mm.mysql.Driver. The examples use a JDBC connect string of jdbc:mysql://localhost/test to access the MySQL test database, which has been configured with a user account called root, password secret.
Now that you've finished the database configuration, let's take a closer look at using databases with Tomcat.
Page 1 of 6