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

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 = 

            "<tr><td>" + 

              "The statement executed successfully." + 

            "</td></tr>";

        else        // this is a DML statement

          message = 

            "<tr><td>" + 

              "The statement executed successfully.<br>" + 

              i + " row(s) affected." + 

            "</td></tr>";

        }

        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: <br>"

                + 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);

    }

}