Build a Servlet-based Application That Executes SQL Statements Via the Web

his tutorial shows how to use a Java servlet, a JavaServer Page (JSP), and a static Java class to create a SQL gateway application that enables users to interactively execute any standard SQL statement against a database running on a server. Using this application, you can work with a database as you’re developing an application.

If you work with a database that’s hosted by an Internet service provider (ISP), you’re probably already familiar with the concept of a SQL gateway application. The ISP probably provides an HTML-based interface for working with the database that’s similar to this application. If not, you can upload the application to serve this function. Either way, building the gateway will teach you how to use JDBC within a servlet to work with a database that’s running on a server.

Editor’s Note: DevX advises against using the SQL gateway application described in this article to post a Web page that allows anyone who can access it to run commands against your database. Such pages can be useful for learning purposes or during development, but they leave you exposed to malicious coders.

This tutorial is an excerpt from Chapter 11 of Murach’s Java Servlets and JSP by Andrea Steelman and Joel Murach. It requires a basic understanding of the Java language, servlets, and JavaServer Pages, and access to an appropriate servlet/JSP container and database server on the server that you’re using. The source code has been tested using Tomcat 4.0 and MySQL, but it should work with other servlet/JSP containers. It also should work for most database servers, if you supply a valid driver and connection string for that database.

The Source Code
First, download the source code for this application, so you can open it in your favorite text editor and see how it all fits together.

The User Interface
Figure 1 shows the user interface for the SQL gateway application after it has executed an INSERT statement:

The User Interface for the SQL Gateway Application
Figure 1: The User Interface for the SQL Gateway Application

As you can see, a message at the bottom of the page indicates the number of rows that were affected by the statement. If the SQL statement is a SELECT statement that runs successfully, the result set will be displayed within an HTML table, as shown in Figure 2.

Result Set Displayed Within an HTML Table
Figure 2: Result Set Displayed Within an HTML Table

Of course, if the SQL statement doesn’t execute successfully, the application will return a message that displays information about the exception that was thrown.

The Code for the JSP
The JSP code starts with a scriptlet, which contains Java code that retrieves two attributes from the session object:

   String sqlStatement =
      (String) session.getAttribute("sqlStatement");
   if (sqlStatement == null)
      sqlStatement = "";
   String message =
      (String) session.getAttribute("message");
   if (message == null)
      message = "";

The first attribute is the string that contains the SQL statement, and the second is the string that contains the result message. If these attributes contain null values, they haven’t been set yet. So this code sets the sqlStatement and message variables to empty strings.

This JSP also contains an HTML form that contains a text area and a submit button:

   SQL statement: