Data Grid for JSP

/*------------------------------------------------------------------------------
 * PACKAGE: com.freeware.masters.actions
 * FILE   : ClientAction.java
 * CREATED: May 10, 2005 6:22:21 PM
 *------------------------------------------------------------------------------
 * Change Log:
 *-----------------------------------------------------------------------------*/
package com.freeware.masters.actions;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;

import com.freeware.masters.model.Client;

/**
 * Action class for client master pager
 * @author Prasad P. Khandekar
 * @version 1.0.0
 * @since 1.0.0
 */
public class ClientAction extends Action
{
	private static Log logger = LogFactory.getLog(ClientAction.class);

	public ActionForward execute(ActionMapping mapping, ActionForm form, 
								HttpServletRequest request,
								HttpServletResponse response) throws Exception
	{
		String         strSQL    = null;
		String         strAction = null;
		String         strRet    = "SUCCESS";
		String         strCol    = null;
		String         strOrd    = null;
		List           lstData   = null;
		DynaActionForm actForm   = null;

		try
		{
			strSQL = "SELECT cliclient, clicorporation, clidescription, " + 
					"cliaddress, clicontact, clicontactdesig, clitelephone, " +
					"clifax, cliemail, clienabled, clistate, cliauthorization, " + 
					"climaker, climakerstamp, clichecker, clicheckerstamp, " +
					"cliupdstamp, clirejectremark FROM clientmaster";

			if (logger.isDebugEnabled())
				logger.debug("Action Execution starts!");

			actForm = (DynaActionForm) form;
			strAction = getStringParam(actForm, "txtAction");
			strCol = getStringParam(actForm, "txtSortCol");
			strOrd = getStringParam(actForm, "txtSortOrd");
			if (null == strCol || "".equals(strCol)) strCol = "cliclient";
			if (null == strOrd || "".equals(strOrd)) strOrd = "ASC";
			if ("clientCode".equals(strCol)) 
				strCol = "cliclient";
			else if ("description".equals(strCol))
				strCol = "cliDescription";
			strSQL = strSQL + " ORDER BY " + strCol + " " + strOrd;
			if (logger.isDebugEnabled())
				logger.debug("Retrieving data using [" + strSQL + "]");
			lstData = fetchData(strSQL);
			request.setAttribute("DATALIST", lstData);
		}
		catch (Exception ex)
		{
			if (logger.isErrorEnabled())
				logger.error(ex);
		}
		if (logger.isDebugEnabled())
			logger.debug("Action Execution ends!");
		return mapping.findForward("success");
	}

	private String getStringParam(DynaActionForm pForm, String pParam)
	{
		String strRet = null;

		try
		{
			strRet = (String) pForm.get(pParam);
		}
		catch (Exception exIgnore)
		{
		}
		return strRet;
	}

	private List fetchData(String pstrSQL)
	{
		List       lstRet = null;
		Client     client = null;
		Statement  stmt   = null;
		ResultSet  rset   = null;
		Connection cnn    = null;

		cnn = getConnection();
		if (null == cnn) return null;

		try
		{
			stmt = cnn.createStatement();
			rset = stmt.executeQuery(pstrSQL);
			if (rset != null)
			{
				while (rset.next())
				{
					client = new Client();
					client.setClientCode(rset.getString(1));
					client.setCorporationCode(rset.getString(2));
					client.setDescription(rset.getString(3));
					client.setAddress(rset.getString(4));
					client.setContactPerson(rset.getString(5));
					client.setContactDesignation(rset.getString(6));
					client.setTelephone(rset.getString(7));
					client.setFax(rset.getString(8));
					client.setEMail(rset.getString(9));
					client.setEnabled(rset.getString(10));
					client.setState(rset.getInt(11));
					client.setAuthorization(rset.getInt(12));
					client.setMakerCode(rset.getString(13));
					client.setMakerStamp(rset.getTimestamp(14));
					client.setCheckerCode(rset.getString(15));
					client.setCheckerStamp(rset.getTimestamp(16));
					client.setUpdateStamp(rset.getTimestamp(17));
					if (lstRet == null) lstRet = new ArrayList();
					lstRet.add(client);
					client = null;
				}
			}
			if (logger.isDebugEnabled())
			{
				if (lstRet != null) 
					logger.debug("Retrieved " + lstRet.size() + " records!");
			}
					
		}
		catch (SQLException sqlEx)
		{
			if (logger.isErrorEnabled())
				logger.error(sqlEx);
		}
		finally
		{
			try
			{
				if (rset != null)
					rset.close();
			}
			catch (SQLException sqlEx)
			{
			}
			finally
			{
				rset = null;
			}

			try
			{
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException sqlEx)
			{
			}
			finally
			{
				stmt = null;
			}

			try
			{
				if (cnn != null)
					cnn.close();
			}
			catch (SQLException sqlEx)
			{
			}
			finally
			{
				cnn = null;
			}
		}
		return lstRet;
	}

	private Connection getConnection()
	{
		Context    ctx    = null;
		Context    envCtx = null;
		DataSource ds     = null;
		Connection conn   = null;

		try
		{
			if (logger.isDebugEnabled())
				logger.debug("Trying to retrieve conection!");
			ctx = new InitialContext();
			envCtx = (Context) ctx.lookup("java:comp/env");
			if (envCtx != null ) 
			{
				if (logger.isDebugEnabled())
					logger.debug("Trying to retrieve datasource!");
				ds = (DataSource) envCtx.lookup("jdbc/DBGrid");
				if (ds != null)
				{
					if (logger.isDebugEnabled())
						logger.debug("Retrieving connection!" + ds.toString());
					conn = ds.getConnection();
					if (conn != null)
						if (logger.isDebugEnabled())
							logger.debug("Connection obtained!");
				}
			}
		}
		catch (Exception exIgnore)
		{
			if (logger.isErrorEnabled())
				logger.error(exIgnore);
		}
		finally
		{
			if (ctx != null) ctx = null;
			if (envCtx != null) envCtx = null;
			if (ds != null) ds = null;
		}
		return conn;
	}
}