Browse DevX
Sign up for e-mail newsletters from DevX


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

Learn how to use a Java servlet, a JavaServer Page, and a static Java class to create a SQL gateway application that enables users to interactively execute SQL statements against a database running on a server. This application allows you to work with a database as you're developing an application.

The Code for the Utility Class
The following is the code for the utility class named SQLUtil:

package murach.sql;

import java.sql.*;

public class SQLUtil{

SQLUtil contains a static method named getHtmlRows that is called by the servlet shown previously. Like the SQLGatewayServlet, this class is stored in the murach.sql package. The getHtmlRows method accepts a ResultSet object and returns a String object that contains the HTML code for all of the column headings and rows in the result set. To build the information for that String object, the getHtmlRows declares a StringBuffer object named htmlRows and appends data to it as the method is executed. At the end of the method, the toString method is used to convert the StringBuffer object to the String object that is returned to the servlet:

    public static synchronized String 
    getHtmlRows(ResultSet results) throws SQLException{
        StringBuffer htmlRows = new StringBuffer();
        ResultSetMetaData metaData = results.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++)
                + metaData.getColumnName(i) + "</td>");

        while (results.next()){
            for (int i = 1; i <= columnCount; i++)
                    + results.getString(i) + "</td>");
        return htmlRows.toString();

To get the column headings that are returned, the getHtmlRows method uses the getMetaData method of the ResultSet object to create a ResultSetMetaData object. This type of object contains information about the result set, including the number of columns and the names of the columns. To get that information, the getHtmlRows method uses the getHtmlRows and getColumnName methods of the ResultSetMetaData object.

To retrieve the data from the result set, the getHtmlRows method uses a for loop within a while loop to get the data for each column in each row. Within these loops, the code uses the getString method of the result set to get the data for each field. That converts the data to a string no matter what data type the field is.

Please note that this method is declared with a synchronized keyword. This prevents two or more threads of a servlet from executing the method at the same time.

Thanks for your registration, follow us on our social networks to keep up-to-date