Home arrow Java arrow Page 5 - Build a Servlet-Based Application that Executes SQL Statements Against a Database
JAVA

Build a Servlet-Based Application that Executes SQL Statements Against a Database


Dynamically interact with an SQL database using JSP and Servlets. Joel's article takes you through the processing of building your own "SQL Gateway"

Author Info:
By: Joel Murach
Rating: 4 stars4 stars4 stars4 stars4 stars / 16
September 21, 2003
TABLE OF CONTENTS:
  1. · Build a Servlet-Based Application that Executes SQL Statements Against a Database
  2. · Prerequisites
  3. · The User Interface
  4. · The Code for the JSP
  5. · The Code for the Servlet
  6. · The Code for the Utility Class
  7. · Conclusion and Related Links/Resources

print this article
SEARCH DEVARTICLES

Build a Servlet-Based Application that Executes SQL Statements Against a Database - The Code for the Servlet
(Page 5 of 7 )

The SQLGatewayServlet, which is stored in the murach.sql package, 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. Then, a new thread is spawned for each user that uses this servlet.

In this example, the servlet uses a driver for the MySQL database to open a connection to a database named "murach" that's 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, you can go to 
www.mysql.com. In addition, there's an introduction to MySQL in chapter 10 of Murach's Java Servlets and JSP.

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 earlier 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, and 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, and 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 that's shown later in this article, and it returns the result set formatted with the 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, which returns the number of rows that were affected. If the number of rows is 0, the SQL statement was a DDL statement like a DROP TABLE or CREATE TABLE statement. 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 an SQLException, the catch block sets the message variable to display information about the SQLException. If, for example, you enter an SQL statement that contains incorrect syntax, this 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 earlier in this article.

        RequestDispatcher dispatcher =
            getServletContext().getRequestDispatcher(
                "/sql/sql_gateway.jsp");
        dispatcher.forward(request, response);
    }
}


blog comments powered by Disqus
JAVA ARTICLES

- Java Too Insecure, Says Microsoft Researcher
- Google Beats Oracle in Java Ruling
- Deploying Multiple Java Applets as One
- Deploying Java Applets
- Understanding Deployment Frameworks
- Database Programming in Java Using JDBC
- Extension Interfaces and SAX
- Entities, Handlers and SAX
- Advanced SAX
- Conversions and Java Print Streams
- Formatters and Java Print Streams
- Java Print Streams
- Wildcards, Arrays, and Generics in Java
- Wildcards and Generic Methods in Java
- Finishing the Project: Java Web Development ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials