Create JSP actions for efficient database access

Web apps are increasingly real-time, often fronting sophisticated server-side systems. This is reflected in database access requirements, which grow ever more complex. Combine JSP with JDBC to give your server-side code a doorway to your databases.

The capacity of Java Server Pages (JSP) to facilitate rapid application development from the server side accounts for much of its popularity. The usual ponderous tactical challenges still loom, however, no matter what shortcuts JSP may provide for the Web application designer: mainly, getting into and out of databases and handing off data.

Database access via JSP need not be intimidating, however. Part of the problem is accepting that database access is more intimidating for Web apps than for traditional local OLAP systems in general, and realizing that a clean, abstract data access solution is essential in a world of multiple vendors of interfacing products, realized in a simple high-connectivity product, flexibly implemented. In this, JSP will serve you well.

What you can do
What are your database access requirements if you’re writing high-volume, data-intensive Web apps? If you’re using JSP, then you’re doing lots of server-side processing. You may have high-volume data requirements in the app, or a great many users, or both. Your concerns probably include optimization of performance; scalability; the impact of multiple queries per user; query complexity; and, if a single session is crunching lots of data, a high level of type conversion.

That’s a lot to handle, and even more so when you’re shooting for an app that is ideally independent of database flavor, as long as it’s relational. But despite its reputation as a presentation-and-request API, JSP will go down this road with you, because of its compatibility with the Java Database Connectivity API (JDBC). You can incorporate JDBC directly into JSP code, and JDBC will pass along statements for execution as database commands, regardless of the database, provided you have JDBC drivers in place.

Getting started
Here’s a general, abstract architecture for JSP application implementation. The preferred JSP design model is Model-View-Controller (MVC), a variation on conventional three-tier architecture that is well-suited to server applications. In the MVC design model for JSP, Model refers to application logic and data; View is presentation; and Controller is request processing.

When you design a JSP app, it is often the best course to create a page for each step in the client-server interaction. For example, in a typical app, there would be pages for each functionally distinct step in the exchange: a Data Entry page, a Validate Request page, a Post To Database page, a Database Response page, and pages for subfunctions of these pages (a Modify Record page, a Delete Record page, and so on).

You can embed JDBC in each page, to facilitate its required database operations. Doing this, however, you run the risk of complicating your code by mixing JSP and JDBC and—since JDBC is SQL-based—SQL statements along with everything else. That’s SQL wrapped in JDBC wrapped in JSP—enough to make your head spin around. If you choose this course, you’ll get everything you want functionally, but take care to keep your application logic clearly separated from your database access code.

Embedding JDBC
The JDBC API doesn’t directly talk to the database. There are drivers (specific to the database) that do the actual connection, and you can download these from the vendor’s Web site. In addition, there are four JDBC driver types, and if you decide to use JDBC, you need to choose the one that suits your needs. You will use a DriverManager class to handle the driver-database connection (check out the Java Development Kit, and J2EE’s JDBC Optional kit for all the extras).

You’ll establish your database connection using a DriverManager method called getConnection. You’ll use its URL parameter to identify the database:
public static Connection getConnection(jdbc:odbc:nameOfDatabase)

Now, tell the DriverManager about the driver (which should be in your classpath):

You’ve connected the database to your JSP app, but you’re still a way off from executing a database command. From this point, you generate statements in your JSP code that will create database commands—a two-stage process. It works like this:
public Statement createStatement(int resultSetType, int resultSetConcurrency)

The parameters are there to give you the authority to manipulate the result that is returned from your database query. When the first parameter is used, the result is viewable in the app; when the second is used, you can update the values accessed by the query (this is an incredibly powerful capability and is itself worthy of examination in a future article).

The complications ensue with the next two methods shown in Listing A.

Statement is for SQL commands you plant in quotes. PreparedStatement is for SQL statements with parameters you wish to manipulate in the course of your app. CallableStatement is for accessing SQL stored procedures. Are you beginning to get a sense of how these things can get complicated when you’re not looking? Note that you can undo transactions done with these methods, by calling the rollback method.

If you want to go with this method of database access, the only piece you’re still missing is:
ResultSet executeQuery(string sqlQuery)

(You use executeQuery for doing selects. You can also do an executeUpdate for updates, inserts and deletes). Your Statement interfaces, above, give you methods for executing SQL statements in strings. What ResultSet does for you is access the data returned from the query so you can play with it in your JSP app.

By breaking down your JSP app into singular, functionally distinct pages, and performing a single database operation in any given page, you greatly simplify your database operations and may even create generalized pages that may be used in future applications—even if you’re embedding SQL embedded in JDBC in those pages.

But you can do even more to make your JSP database access clean and maintainable. Embedding JDBC in your JSP code and communicating with the database by sending SQL commands through the interface will work well, but it creates programming challenges in getting the SQL commands through the door, without making your code overly complex. You can further isolate your database interface code, cleaning up your JSP apps while increasing flexibility in your SQL handling. In a follow-up article, I’ll look at how you can do this.

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