Facilitate database access in your JSP application design with JDBC

Embedding JDBC in your JSP applications gives you a direct window into databases for executing queries, but the programming can be cumbersome. Here's how you can jump around the JDBC, tighten up your code, and make your SQL more flexible with JSP actions.

Previously, we noted that JSP applications are extremely modular, have high utility, and have formidable presentation-request capability. Building in smooth database access is the challenge, and the JDBC interface can do this nicely. However, JDBC code embedded in JSP code, with SQL commands embedded in the JDBC, can defeat the purpose of JSP, which is to create a clean and convenient API for server-side apps. To get around this, we can consider creating database interface components using JSP actions.

The ideal JSP design model is Model-View-Controller (MVC). This variation on conventional three-tier architecture slightly juggles the tiers: Model refers to application logic and data; View is presentation; and Controller is request processing. Following this model, a JSP app usually contains a page for each "line" in a client-server "dialog." In a typical app, you'll see a Query page, a Validate page, a Database Insert page, a Database Update page, and so on.

In the last article, we discussed how embedding JDBC in each page, to give each functionally distinct page its needed functionally distinct database access, is a technique that keeps the app fairly well organized. Creating SQL commands for execution, as arguments passed through JDBC commands, however, is a programming challenge, and can rapidly complicate your otherwise well-organized app.

Step around JDBC with JSP actions
Another approach to JSP database access is to create a collection of actions for database operations, without using JDBC. In taking this approach, you knock down two walls: first, you can eliminate the need for JDBC, which simplifies everything; second, you will be imposing organization (i.e., readability, flexibility, and maintainability) on your design and code.

You still need drivers (the same ones will do, though you can use ODBCs if you like), but you are basically simplifying what you did above. An "action" in a JSP app is a chunk of logic, generically written and made available to other JSP app writers, that you use like a subroutine. The point of using them is to standardize certain functions (database access is an obvious choice) and to minimize the amount of Java code embedded in your JSPs.

JSP provides a set of classes that collectively make up the tag extension. With this, you define an action by way of a tag handler that takes your action's attributes and plugs in property setter methods to match. There are two JSP-defined Java interfaces implemented by the tag handler class: Tag interface and BodyTag interface, implemented by the TagSupport class and BodyTagSupport class respectively (BodyTag extends Tag).

You'll want to build up a tag library for general JSP use, and in general you'll want to implement tag handlers that extend the support classes, though it's possible to simply extend some existing class that has functionality you desire by implementing one of the two Java interfaces. Here's how you do it:

First, implement a tag handler class:
package com.myactions;
(import statements go here)
public class MyActionTag extends TagSupport {

Build in whatever is needed in the way of attributes. Next, compile it and put the class file in your application's class library. Then you'll need a Tag Library Descriptor (TLD) file, if you don't already have one—this is an XML file that maps your action names to their corresponding tag handler classes.
       <bodycontent> (whatever)  </bodycontent>

What just happened? Well, you created an action named MyAction, which is TLD-mapped to the tag handler class com.myactions.MyActionTag. There is a defined attribute in it named myData. The TLD file needs to go in the application's TLDs directory.

When you call the action from a JSP page, the TLD tells JSP the correct class to use for the action. All the correct methods are called. That's all extremely handy, and done with relatively little code. And it has the virtue of being reusable.

But where does SQL come in? First, you need to set up database access as you did with JDBC, but this time you establish a connection. You can do it with javax interfaces, available in the JDBC 2.0 Optional kit. The class javax.sql.DataSource, part of JDBC 2.0, will give you your connection.

(One thing you'll need to do in setting up generic database access actions is to create a property-setting bean to set the values in JDBC statements, as described earlier. Since the statements are for database access, it will need to create rows and columns.)

So, again, where is the SQL? It's in the bean. You create a method in the bean, using JDBC createStatement and PreparedStatement. Make it a public Vector, and pass your SQL argument to one or the other as appropriate (use createStatement if your JSP page is forwarding a real-time, user-generated query, versus a canned one if a selection is being made from a menu, etc.).

Your database bean executes a SQL statement that is embedded in an action element body. You can pass an argument to it, or use it to execute a canned action, as needed. Your actions are enabled by tag handlers that implement classes with the supporting methods you'll need. Since all the JDBC is embedded in library code, you never have to explicitly use it in your JSP app.

Using actions may at first glance seem like more trouble, not less, than embedding SQL in JDBC and then embedding JDBC in JSP. But a moment's thought confirms that whatever you do in creating database access with JDBC-plus-SQL, you do over and over; what you do in creating SQL-based actions and storing them in a TLD, you do once, and then access as needed for all your JSP apps.

Next time, we'll look at passing data between JSP pages and sessions.

About Scott Robinson

Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence...

Editor's Picks