198
9
Working with databases
This chapter covers
■
The link between Java’s JDBC API and JSP
■
Storing and retrieving JSP Beans with
an RDBMS system
■
Displaying database results with JSP
■
Maintaining persistent connections
JSP and JDBC 199
While long a bastion of large, well-funded enterprises, databases have found their
way into a much wider range of web sites in recent years. Along with their tradi-
tional role as back office data sources, most large-scale web sites employ databases
for at least some portion of the content. Ad management, users registration infor-
mation, community services, and contact lists are just some of the features com-
monly managed through a database.
JSPs and relational databases make a good
combination. The relational database gives us the organizational capabilities and the
performance necessary to manage large amounts of dynamic data, while
JSP gives us
a convenient way to present it. By combining the power of a relational database
with the flexibility of
JSP
for content presentation and front-end design you can
quickly develop rich, interactive web applications.
9.1 JSP and JDBC
Unlike other web scripting languages such as ColdFusion, Server Side JavaScript,
and
PHP, JSP does not define its own set of tags for database access. Rather than
develop yet another mechanism for database access, the designers of
JSP chose to
leverage Java’s powerful, popular, database
API
—
JDBC
.
When a
JSP application needs to communicate with a database, it does so
through a vendor-provided driver class written to the
JDBC API. Accessing a data-
base in
JSP
then is nothing new; it sticks to this tried and true workhorse from Sun.
In practice, as we’ll learn in chapter 10, we’ll often isolate database access inside a
servlet or a Bean, keeping the details hidden from the presentation aspects of the
JSP page. Both of these approaches are illustrated in figure 9.1
Learning
JDBC is beyond the scope of this book, and a wealth of valuable infor-
mation already exists on the topic. If you aren’t familiar with Java’s
JDBC API, a
number of online tutorials can be found on Sun’s
JDBC web site, http://
java.sun.com/products/jdbc. Check online or at your favorite bookstore if you
need more information. In this chapter we’ll focus instead on the relationship
between
JSP and JDBC.
NOTE
The
JDBC
classes are part of the
java.sql
package, which must be im-
ported into any Java class from which you wish to access JDBC, including
your
JSP
pages. Additional, optional extensions for the 2.0 version of the
JDBC
API
can be found in the
javax.sql
package, if it is installed on your
system. If your
JDBC
driver is not in your
JSP
container’s class path, you
will have to either import it into your page or refer to it through its fully
qualified class name.
200
CHAPTER 9
Working with databases
9.1.1 JNDI and data sources
In ColdFusion and other template/scripting systems you access a database through
a single identifier that corresponds to a preconfigured database connection (or con-
nection pool) assigned by the system’s administrator. This allows you to eliminate
database connection information from your code, referring to your database
sources by a logical name such as
EmployeeDB
or
SalesDatabase
. The details of
connecting to the database are not exposed to your code. If a new driver class
becomes available, the database server moves, or the login information changes,
only the resource description needs to be reconfigured. Any components or code
referencing this named resource will not have to be touched.
JSP does not define its own database resource management system; instead you
can rely on
JDBC 2.0’s
Datasource
interface and Java Naming and Directory Inter-
face (
JNDI) technology for naming and location services. JNDI can be used to
shield your application code from the database details such as the driver class, the
username, password, and connection
URI. To create a database connection with
JNDI, specify a resource name which corresponds to an entry in a database or nam-
ing service, and receive the information necessary to establish a connection with
your database. This shields your
JSP code and supporting components from
changes to the database’s configuration. More information on using
JNDI is avail-
able from Sun, at http://java.sun.com/products/jndi. Here’s an example of creat-
ing a connection from a data source defined in the
JNDI registry:
Request
Request
JSP JSP
Servlet
Database access
directly from
a JSP page
Database access handled
by a servlet; results
passed to JSP page
JDBC driver
JDBC API
Database
Figure 9.1 Database access options in JSP
JSP and JDBC 201
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/SalesDB");
Connection con = ds.getConnection("username", "password");
We can further improve upon this abstraction, and further simplify database access,
through custom tags, which use
JNDI to allow simple access to named database
resources in a manner familiar to ColdFusion and other tag-style languages.
9.1.2 Prepared statements
Prepared statements allow us to develop a Structured Query Language (
SQL)
query
template that we can reuse to handle similar requests with different values between
each execution. Essentially we create the query, which can be any sort of
SQL
state-
ment, leaving any variable values undefined. We can then specify values for our
undefined elements before executing the query, and repeat as necessary. Prepared
statements are created from a
Connection
object, just like regular
Statement
objects. In the
SQL
, replace any variable values with a question mark.
String query = "SELECT * FROM GAME_RECORDS WHERE SCORE > ? AND TEAM = ?";
PreparedStatement statement = connection.prepareStatement(query);
Before we can execute the statement we must specify a value for all of our missing
parameters. The
PreparedStatement
object supports a number of methods, each
tied to setting a value of a specific type—
int
,
long,
String
, and so forth. Each
method takes two arguments, an index value indicating which missing parameter
you are specifying, and the value itself. The first parameter has an index value of 1
(not 0) so to specify a query that selects all high scores > 10,000 for the “Gold”
team we use the following statements to set the values and execute the query:
statement.setInt(1, 10000); // Score
statement.setString(2, "Gold"); // Team
ResultSet results = statement.executeQuery();
Once you have defined a prepared statement you can reuse it simply by changing
parameters, as needed. There is no need to create a new prepared statement
instance as long as the basic query is unchanged. So, we can execute several queries
without having to create a statement object. We can even share a single prepared
statement among an application’s components or a servlet’s users. When using pre-
pared statements, the RDBMS engine has to parse the SQL statement only once,
rather than again and again with each new request. This results in more efficient
database operations.
Not only is this more efficient in terms of database access, object creation, and
memory allocation but the resulting code is cleaner and more easily understood.
202
CHAPTER 9
Working with databases
Consider this example again, but this time the queries are not hard coded, but
come from a bean,
userBean
, which has been initialized from an input form.
statement.setInt(1, userBean.getScore()); // Score
statement.setString(2, userBean.getTeam()); // Team
ResultSet results = statement.execute();
The alternative is to build each SQL statement from strings, which can quickly get
confusing, especially with complex queries. Consider the following example again,
this time without the benefit of a prepared statement:
Statement statement = connection.getStatement();
String query = "SELECT * FROM GAME_RECORDS WHERE SCORE > " +
userBean.getScore() + " AND TEAM = ‘" +
userBean.getTeam() + "’";
ResultSet results = Statement.executeQuery(query);
Another, perhaps even more important, benefit of using prepared statements is evi-
denced here. When you insert a value into a prepared statement with one of its set-
ter methods you do not have to worry about proper quoting of strings, escaping of
special characters, and conversions of dates and other values into the proper format
for your particular database. This is particularly important for
JSP
s that are likely to
be collecting search terms input directly from users through form elements and are
particularly vulnerable to special characters and unpredictable input. Since each
database might have its own formatting peculiarities, especially for dates, using pre-
pared statements can help further distance your code from dealing with any one
particular database.
9.2 Database driven JSPs
There are a number of ways to develop database driven applications through JSP.
In this chapter, we’re concentrating on the database interaction itself, and less on
program architecture.
JSP application design will be covered in chapter 10 and
again in chapter 11 which will feature a walk-through example of a database
driven
JSP project.
9.2.1 Creating JSP components from table data
You may have recognized a similarity between the tables of a relational database and
simple JavaBean components. When building your applications think of tables as
being analogous to JavaBeans. While JavaBeans have properties, data from a table
has columns. A table’s schema is like the class that defines a JavaBean—defining the
names and types data that instances will hold. Like Java classes, tables are templates
