Since this message contains the rows and columns for an HTML table, it’s coded within the Table tags.
The Code for the Servlet
The SQLGatewayServlet, which is stored in the murach.sql package contained in the code download, starts by importing the java.sql package so it can use the JDBC classes. In addition, it declares a Connection object so the database connection can be used by all of the methods in the servlet:
package murach.sql;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class SQLGatewayServlet extends HttpServlet{
    private Connection connection;
When the servlet engine places this servlet into service, the init
method opens the connection to the database:
    public void init() throws ServletException{
        try{
            Class.forName("org.gjt.mm.mysql.Driver");
            String dbURL = "jdbc:mysql://localhost/murach";
            String username = "root";
            String password = "";
            connection = DriverManager.getConnection(
                dbURL, username, password);
        }
        catch(ClassNotFoundException e){
            System.out.println("Database driver not found.");
        }
        catch(SQLException e){
            System.out.println(
              "Error opening the db connection: "
                + e.getMessage());
        }
    }
Usually, this occurs when the first user uses the application. That way, the database connection will be open and available for all subsequent users, with a new thread created for each user that uses the servlet.
In this example, the servlet uses a driver for the MySQL database to open a connection to a database named “murach”, which is running on the same server as the servlet. In addition, this servlet uses MySQL’s default username of “root” and a blank password. However, you can modify this code to connect to just about any type of database running on any type of server. Either way, you’ll need to make sure that an appropriate driver for the database is installed on the server. (For more information about getting, installing, and configuring MySQL, go to www.mysql.com. In addition, Chapter 10 of Murach’s Java Servlets and JSP provides an introduction to MySQL.)
Before the servlet engine takes a servlet out of service, the destroy
method closes the database connection and frees up the resources required by the connection:
    public void destroy() {
        try{
            connection.close();
        }
        catch(SQLException e){
            System.out.println(
              "Error closing the db connection: "
                + e.getMessage());
        }
    }
When the JSP shown previously calls the doPost
method, this method calls the doGet
method:
    public void doPost(HttpServletRequest request,
                    HttpServletResponse response)
                    throws IOException, ServletException{
        doGet(request, response);
    }
Within the doGet
method, the first statement gets the SQL statement that the user entered in the JSP. The second statement declares the message variable:
    public void doGet(HttpServletRequest request,
                    HttpServletResponse response)
                    throws IOException, ServletException{
        String sqlStatement =
            request.getParameter("sqlStatement");
        String message = "";
Then, within the try block, the first statement uses the Connection object to create a Statement object. The next two statements use the trim
and substring
methods of a String object to return the first six letters of the SQL statement that the user entered:
    try{
        Statement statement = connection.createStatement();
        sqlStatement = sqlStatement.trim();
        String sqlType = sqlStatement.substring(0, 6);
If the first six letters of the SQL statement are “select”, the executeQuery
method of the Statement object returns a ResultSet object. Then, this object is passed to the getHtmlRows
method of the SQLUtil class (which is described later in this article), and it returns the result set formatted with HTML tags for rows and columns:
    if (sqlType.equalsIgnoreCase("select")){
        ResultSet resultSet =
            statement.executeQuery(sqlStatement);
        message =
            SQLUtil.getHtmlRows(resultSet);
    }
However, if the first six letters of the SQL statement aren’t “select”, the executeUpdate
method of the Statement object is called. It returns the number of rows that were affected. If the number of rows is 0, the SQL statement was a DDL statement like DROP TABLE or CREATE TABLE. Otherwise, the SQL statement was an INSERT, UPDATE, or DELETE statement. Either way, the code sets the message variable to an appropriate message:
    else{
        int i = statement.executeUpdate(sqlStatement);
        if (i == 0) // this is a DDL statement
          message =
            "" +               "The statement executed successfully." +             " |
";
        else        // this is a DML statement
          message =
            "" +               "The statement executed successfully. " +               i + " row(s) affected." +             " |
";
        }
        statement.close();
    }
If any of the statements within the try block throw a SQLException, the catch block sets the message variable to display information about the SQLException. If, for example, you enter a SQL statement that contains incorrect syntax, the following message will help you troubleshoot your syntax problem:
    catch(SQLException e){
        message = "Error executing the SQL statement:
"
                + e.getMessage();
    }
After the catch block, the next three statements get the session object and set the sqlStatement and message variables as attributes of that object:
        HttpSession session = request.getSession();
        session.setAttribute("message", message);
        session.setAttribute("sqlStatement", sqlStatement);
Then, the last two statements return a RequestDispatcher object that forwards the request and response objects to the JSP shown previously in this article:
        RequestDispatcher dispatcher =
            getServletContext().getRequestDispatcher(
                "/sql/sql_gateway.jsp");
        dispatcher.forward(request, response);
    }
}
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();
        htmlRows.append("");
        for (int i = 1; i             htmlRows.append(""                 + metaData.getColumnName(i) + " | ");
        htmlRows.append("
");
        while (results.next()){
            htmlRows.append("");
            for (int i = 1; i                 htmlRows.append(""                     + results.getString(i) + " | ");
        }
        htmlRows.append("
");
        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.