jsp_addressbook/WEB-INF/code/ListQueryServlet.java

// ListQueryServlet.java
// Servlet that will use its request parameters to to generate a
// collection of DataBeans with an SQL query. The SQL connection
// is a persistent part of the HttpSession.

package com.bullseyecomputing.servlets;

import java.io.*;
import java.util.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

import com.bullseyecomputing.beans.AddressListBean;

public class ListQueryServlet extends HttpServlet {

  ServletContext context;
  ResultSet results;

  public void init(ServletConfig config) throws ServletException {
    super.init(config);

    // Grab a handle to our context
    context = getServletContext();

    // Get the jdbcDriver from an init param, and try to load it
    String jdbcDriver = getInitParameter("jdbcDriver");
    try {
      Class.forName(jdbcDriver);
    }
    catch(ClassNotFoundException e) {
      context.log("ListQueryServlet: jdbcDriver failure");
    }
  }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

    HttpSession session = request.getSession(false);
    ConnectionListener dbSession;
    Statement transaction;
    RequestDispatcher dispatcher;

    // If we have no session, we're in the wrong place
    if(session == null) {
      dispatcher = request.getRequestDispatcher("/index.jsp");
      dispatcher.forward(request, response);
    }

    // Look in the session environment for our database connection
    dbSession = (ConnectionListener) session.getAttribute("dbConnection");
    // Create one if needed
    if(dbSession == null) {
      String jdbcURL = getInitParameter("jdbcURL");
      String jdbcUser = getInitParameter("jdbcUser");
      String jdbcPassword = getInitParameter("jdbcPassword");
      try {
	Connection conn = DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPassword);
	dbSession = new ConnectionListener(conn);
	session.setAttribute("dbConnection", dbSession);
      }
      catch(SQLException e) {
	    context.log("Error creating database connection");
      }
    }

    // Get a local handle to the connection stored in the session
    Connection db = dbSession.getConnection();

    // Formulate our query based on init and request parameters
    String selected = getInitParameter("selected");
    String table = getInitParameter("table");
    String newFilter = request.getParameter("filter");
    String newOrder = request.getParameter("orderby");
    String newLimit = request.getParameter("limit");

    if(newFilter != null) {                                                  // add error checking for filter value
      session.setAttribute("filter", newFilter);
    }
    if(newOrder != null) {                                                   // add error checking for orderby value
      String oldOrder = (String) session.getAttribute("orderby");
      String direction = (String) session.getAttribute("direction");
      context.log("oldOrder = "+oldOrder+" "+direction);

      // Set initial orderby values
      if(oldOrder == null) { oldOrder = ""; }
      if(direction == null) { direction = "asc"; }

      // Toggle direction
      if(oldOrder.equals(newOrder)) {
	context.log("toggle direction");
	if(direction.equals("asc")) {
	  direction = "desc";
	}
	else {
	  direction = "asc";
	}
      }
      else {
	context.log("'"+oldOrder+"' != '"+newOrder+"'");
      }

      // Push values into session
      session.setAttribute("orderby", newOrder);
      session.setAttribute("direction", direction);
      context.log("newOrder = "+newOrder+" "+direction);
    }
    if(newLimit != null) {                                                   // add error checking for limit value
      session.setAttribute("limit", newLimit);
    }

    // Get values from session
    String filter = (String) session.getAttribute("filter");
    String orderby = (String) session.getAttribute("orderby");
    String direction = (String) session.getAttribute("direction");
    String limit = (String) session.getAttribute("limit");

    StringBuffer query = new StringBuffer();
    query.append("select ").append(selected);
    query.append(" from ").append(table);

    if(filter != null && filter != "") {
      StringTokenizer str = new StringTokenizer(filter,":");
      query.append(" where ").append(str.nextToken());
      query.append(" ~* '").append(str.nextToken()).append("'");
      // Multiple filters?
      while(str.hasMoreTokens()) {
	query.append(" and ").append(str.nextToken());
	query.append(" ~* '").append(str.nextToken()).append("'");
      }
    }
    if(orderby != null) {
      query.append(" order by ").append(orderby);
    }
    if(direction != null) {
      query.append(" ").append(direction);
    }
    if(limit != null) {
      query.append(" limit ").append(limit);
    }

    // Execute our calculated SQL query
    try {
      transaction = db.createStatement();
      context.log(query.toString());
      results = transaction.executeQuery(query.toString());

      // Traverse the ResultSet and create an ArrayList of DataBeans
      ArrayList resultList = new ArrayList();
      String dbField = "";

      while(results.next()) {
	
	// Create a new DataBean for each row of data
	AddressListBean address = new AddressListBean();

	// Push the result data into the DataBean
	address.setIdNumber(Integer.toString(results.getInt(1)));
	address.setName(results.getString(2));
	address.setStreet(results.getString(3));
	dbField = results.getString(4).trim();
	address.setCity(dbField.equals("") ? "�" : dbField);
	dbField = results.getString(5).trim();
	address.setState(dbField.equals("") ? "�" : dbField);
	dbField = results.getString(6).trim();
	address.setZipcode(dbField.equals("") ? "�" : dbField);
	dbField = results.getString(7).trim();
	address.setPlus4(dbField.equals("") ? "�" : dbField);

	// Add the DataBean to the ArrayList
	resultList.add(address);
      }

      // Push the ArrayList into the session environment
      session.setAttribute("resultList", resultList);

      // Close the query transaction
      results.close();
      transaction.close();
    }
    catch(SQLException e) {
      context.log("Error processing SQL Query", e);
    }

    // Forward on to the corresponding jsp
    dispatcher = context.getRequestDispatcher("/addressList.jsp");
    dispatcher.forward(request, response);
  }
}