Coverage Report

Coverage Report - org.restafarian.core.servlets.SelectListServletBase

Classes in this File

Line Coverage

Branch Coverage

Complexity

SelectListServletBase

0/208

0/74

2.259

 package org.restafarian.core.servlets;
 
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Date;
 import java.util.GregorianCalendar;
 import java.util.HashMap;
 import java.util.Hashtable;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 import java.util.TreeMap;
 
 import javax.naming.Context;
 import javax.naming.InitialContext;
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import javax.sql.DataSource;
 
 /**
  * <p>This abstract class is the base code for select list servlets.</p>
  */
 public abstract class SelectListServletBase extends RestServletBase {
         private static final long serialVersionUID = 1;
         private static final String FIELD_MAP_REQ_ATTR_KEY = "select.list.data.field.map";
         private String contextKey = "";
         private String dataSourceName = "";
         private String defaultStartsWith = "";
         private String defaultContains = "";
         private String defaultOrderBy = "id";
         private Map dataFields = new HashMap();
 
         /**
          * <p>The Servlet "doGet()" method.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param res the <code>HttpServletResponse</code> object
          * @throws ServletException
          * @throws IOException
          */
         public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
                 StringBuffer buffer = new StringBuffer();
 
                 req.setAttribute(FIELD_MAP_REQ_ATTR_KEY, getCurrentDataFields(req));
                 buffer.append("<?xml version=\"1.0\"?>\n");
                 buffer.append("<options size=\"");
                 List options = getOptions(req);
                 if (options != null) {
                         buffer.append(options.size());
                         buffer.append("\">\n");
                         buffer.append(formatOptions(req, options));
                 } else {
                         buffer.append("0\">\n");
                 }
                 buffer.append("</options>\n");
 
                 PrintWriter pw = res.getWriter();
                 pw.println(buffer);
         }
 
         /**
          * <p>The Servlet "doPost()" method.</p>
          * 
          * @param req the <code>HttpServletRequest</code> object
          * @param res the <code>HttpServletResponse</code> object
          * @throws ServletException
          * @throws IOException
          */
         public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
                 res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
         }
 
         /**
          * <p>The Servlet "doPut()" method.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param res the <code>HttpServletResponse</code> object
          * @throws ServletException
          * @throws IOException
          */
         public void doPut(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
                 res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
         }
 
         /**
          * <p>The Servlet "doDelete()" method.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param res the <code>HttpServletResponse</code> object
          * @throws ServletException
          * @throws IOException
          */
         public void doDelete(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
                 res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
         }
 
         /**
          * <p>This method returns the current map of data fields.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @return the current map of data fields
          */
         protected Map getCurrentDataFields(HttpServletRequest req) {
                 return dataFields;
         }
 
         /**
          * <p>This method formats the options.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param options the <code>List</code> of options
          * @return the formatted options
          */
         protected String formatOptions(HttpServletRequest req, List options) {
                 StringBuffer buffer = new StringBuffer();
 
                 int startIndex = 0;
                 int count = 0;
                 String start = req.getParameter("start");
                 if (start != null && start.length() > 0) {
                         startIndex = Integer.parseInt(start.trim());
                 }
                 String countString = req.getParameter("count");
                 if (countString != null && countString.length() > 0) {
                         count = Integer.parseInt(countString.trim());
                 }
                 if (count < 1) {
                         count = 10;
                 }
                 if (startIndex > 0) {
                         startIndex = startIndex - 1;
                 }
                 int endIndex = startIndex + count;
                 if (endIndex > options.size()) {
                         endIndex = options.size();
                 }
 
                 Map fields = (Map) req.getAttribute(FIELD_MAP_REQ_ATTR_KEY);
                 for (int x=startIndex; x<endIndex; x++) {
                         int index = x + 1;
                         Map thisItem = (Map) options.get(x);
                         buffer.append(" <option index=\"");
                         buffer.append(index);
                         buffer.append("\">\n");
                            Iterator i = fields.keySet().iterator();
                            while (i.hasNext()) {
                                    String fieldName = (String) i.next();
                                 buffer.append("  <field name=\"");
                                 buffer.append(fieldName);
                                 buffer.append("\">");
                                 if (thisItem.get(fieldName) != null) {
                                         buffer.append(thisItem.get(fieldName));
                                 }
                                 buffer.append("</field>\n");
                            }
                         buffer.append(" </option>\n");
                 }
 
                 return buffer.toString();
         }
 
         /**
          * <p>This method retrieves the requested options from the session.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @return the options
          */
         protected List getOptions(HttpServletRequest req) {
                 List options = new ArrayList();
                 Date rightNow = new Date();
                 String startsWith = req.getParameter("startsWith");
                 String contains = req.getParameter("contains");
                 String orderBy = req.getParameter("orderBy");
                 if (startsWith == null || startsWith.length() < 1) {
                         startsWith = defaultStartsWith;
                 }
                 if (contains == null || contains.length() < 1) {
                         contains = defaultContains;
                 }
                 if (orderBy == null || orderBy.length() < 1) {
                         orderBy = defaultOrderBy;
                 }
                 String cacheKey = startsWith + ":" + contains + ":" + orderBy + getCacheKeyQualifier(req);
                 Map queryCache = getQueryCache(req, contextKey);
                 if (queryCache.containsKey(cacheKey)) {
                         Map results = (Map) queryCache.get(cacheKey);
                         options = (List) results.get("options");
                         results.put("lastAccessed", rightNow);
                         Integer accessCount = (Integer) results.get("accessCount");
                         results.put("accessCount", new Integer(accessCount.intValue() + 1));
                 } else {
                         options = fetchOptions(req, startsWith, contains, orderBy);
                         if (options != null && options.size() > 0) {
                                 Map results = new HashMap();
                                 results.put("creationDate", rightNow);
                                 results.put("lastAccessed", rightNow);
                                 results.put("accessCount", new Integer(1));
                                 results.put("options", options);
                                 queryCache.put(cacheKey, results);
                         }
                 }
 
                 return options;
         }
 
         /**
          * <p>This method returns any additional qualifiers for the key to the query cache.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @return the cache key qualifier
          */
         protected String getCacheKeyQualifier(HttpServletRequest req) {
                 return "";
         }
 
         /**
          * <p>This method obtains the requested options from the data source.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param startsWith the "starts with" query parameter
          * @param contains the "contains" query parameter
          * @param orderBy the sort order
          * @return the options
          */
         protected List fetchOptions(HttpServletRequest req, String startsWith, String contains, String orderBy) { 
                 List options = new ArrayList();
 
                 DataSource dataSource = getDataSource();
                 Connection conn = null;
                 Statement stmt = null;
                 ResultSet rs = null;
                 String qs = getQueryStatement(req, startsWith, contains, orderBy);
                 Map items = new TreeMap();
                 Map fields = (Map) req.getAttribute(FIELD_MAP_REQ_ATTR_KEY);
                 try {
                         conn = dataSource.getConnection();
                         conn.setAutoCommit(false);
                         stmt = conn.createStatement();
                         rs = stmt.executeQuery(qs);
                         while (rs.next()) {
                                 Map thisItem = new HashMap();
                                    Iterator i = fields.keySet().iterator();
                                    while (i.hasNext()) {
                                            String fieldName = (String) i.next();
                                            String tableField = (String) fields.get(fieldName);
                                            thisItem.put(fieldName, filter(rs.getString(tableField)));
                                    }
                                 if (orderBy == null || orderBy.length() < 1) {
                                         items.put(thisItem.get("id"), thisItem);
                                 } else {
                                           options.add(thisItem);
                                 }
                         }
                 } catch (SQLException e) {
                         System.out.println("SQL statement: " + qs);
                         System.out.println("SQL error: " + e.toString() + "; " +  e.getMessage());
                         e.printStackTrace();
                 } finally {
                         if (rs != null) {
                                 try {
                                         rs.close();
                                 } catch (SQLException sqle) {
                                         System.out.println("SQL error: " + sqle.toString() + "; " +  sqle.getMessage());
                                         sqle.printStackTrace();
                                 }
                                 rs = null;
                         }
                         if (stmt != null) {
                                 try {
                                         stmt.close();
                                 } catch (SQLException sqle) {
                                         System.out.println("SQL error: " + sqle.toString() + "; " +  sqle.getMessage());
                                         sqle.printStackTrace();
                                 }
                                 stmt = null;
                         }
                         if (conn != null) {
                                 try {
                                         conn.commit();
                                         conn.close();
                                 } catch (SQLException sqle) {
                                         System.out.println("SQL error: " + sqle.toString() + "; " +  sqle.getMessage());
                                         sqle.printStackTrace();
                                 }
                                 conn = null;
                         }
                 }
 
                 if (orderBy == null || orderBy.length() < 1) {
                         if (!items.isEmpty()) {
                                 Iterator i = items.keySet().iterator();
                                 while (i.hasNext()) {
                                         String key = i.next().toString();
                                           options.add(items.get(key));
                                 }
                         }
                 }
 
                 return options;
         }
 
         /**
          * <p>This method creates the SQL statement.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param startsWith the "starts with" query parameter
          * @param contains the "contains" query parameter
          * @param orderBy the sort order
          * @return the SQL statement
          */
         protected String getQueryStatement(HttpServletRequest req, String startsWith, String contains, String orderBy) {
                 return getQueryStatement(startsWith, contains, orderBy);
         }
 
         /**
          * <p>This method creates the SQL statement.</p>
          *
          * @param startsWith the "starts with" query parameter
          * @param contains the "contains" query parameter
          * @param orderBy the sort order
          * @return the SQL statement
          */
         protected abstract String getQueryStatement(String startsWith, String contains, String orderBy);
 
         /**
          * <p>Fetch the query cache from the servlet context, or creates a new one.</p>
          *
          * @param req the <code>HttpServletRequest</code> object
          * @param contextKey the servlet context key 
          */
         protected static Map getQueryCache(HttpServletRequest req, String contextKey) {
                 Map queryCache = (Map) req.getSession().getServletContext().getAttribute(contextKey);
 
                 // if no cache, then make one
                 if (queryCache == null) {
                         queryCache = new HashMap();
                         queryCache.put("_lastCacheReviewDateTime", new Date());
                         req.getSession().getServletContext().setAttribute(contextKey, queryCache);
                 }
 
                 Date lastCacheReviewDate = (Date) queryCache.get("_lastCacheReviewDateTime");
                 if (lastCacheReviewDate.before(getReviewCompareDate())) {
                         reviewQueryCache(queryCache);
                 }
 
                 return queryCache;
         }
 
         /**
          * <p>Review the query cache and remove expired items.</p>
          */
         protected static void reviewQueryCache(Map queryCache) {
                 Date cutoffDate = getReviewCompareDate();
 
                 List toBeRemoved = new ArrayList();
                 Iterator i = queryCache.keySet().iterator();
                 while (i.hasNext()) {
                         String key = (String) i.next();
                         if (!"_lastCacheReviewDateTime".equals(key)) {
                                 Map results = (Map) queryCache.get(key);
                                 Date creationDate = (Date) results.get("creationDate");
                                 if (creationDate.before(cutoffDate)) {
                                         toBeRemoved.add(key);
                                 }
                         }
                 }
                 i = toBeRemoved.iterator();
                 while (i.hasNext()) {
                         queryCache.remove(i.next());
                 }
 
                 queryCache.put("_lastCacheReviewDateTime", new Date());
         }
 
         /**
          * <p>Fetch the query cache from the servlet context, or creates a new one.</p>
          */
         protected static Date getReviewCompareDate() {
                 Calendar calendar = new GregorianCalendar();
                 calendar.setTime(new Date());
                 calendar.add(Calendar.DATE, -1);
                 return calendar.getTime();
         }
 
         /**
          * <p>This method is used to look up the <code>DataSource</code>
          * by name.</p>
          * 
          * @return <code>dataSource</code> - the <code>DataSource</code>
          */
         protected DataSource getDataSource() {
                 DataSource dataSource = null;
 
                 try {
                         Context ctx = new InitialContext(new Hashtable());
                         dataSource = (DataSource) ctx.lookup(dataSourceName);
                 } catch (Throwable t) {
                         t.printStackTrace();
                 }
 
                 return dataSource;
         }
 
         /**
          * @return Returns the contextKey.
          */
         public String getContextKey() {
                 return contextKey;
         }
         /**
          * @param contextKey The contextKey to set.
          */
         public void setContextKey(String contextKey) {
                 this.contextKey = contextKey;
         }
         /**
          * @return Returns the dataFields.
          */
         public Map getDataFields() {
                 return dataFields;
         }
         /**
          * @param dataFields The dataFields to set.
          */
         public void setDataFields(Map dataFields) {
                 this.dataFields = dataFields;
         }
         /**
          * @return Returns the dataSourceName.
          */
         public String getDataSourceName() {
                 return dataSourceName;
         }
         /**
          * @param dataSourceName The dataSourceName to set.
          */
         public void setDataSourceName(String dataSourceName) {
                 this.dataSourceName = dataSourceName;
         }
         /**
          * @return Returns the defaultContains.
          */
         public String getDefaultContains() {
                 return defaultContains;
         }
         /**
          * @param defaultContains The defaultContains to set.
          */
         public void setDefaultContains(String defaultContains) {
                 this.defaultContains = defaultContains;
         }
         /**
          * @return Returns the defaultOrderBy.
          */
         public String getDefaultOrderBy() {
                 return defaultOrderBy;
         }
         /**
          * @param defaultOrderBy The defaultOrderBy to set.
          */
         public void setDefaultOrderBy(String defaultOrderBy) {
                 this.defaultOrderBy = defaultOrderBy;
         }
         /**
          * @return Returns the defaultStartsWith.
          */
         public String getDefaultStartsWith() {
                 return defaultStartsWith;
         }
         /**
          * @param defaultStartsWith The defaultStartsWith to set.
          */
         public void setDefaultStartsWith(String defaultStartsWith) {
                 this.defaultStartsWith = defaultStartsWith;
         }
 }

1		package org.restafarian.core.servlets;
2
3		import java.io.IOException;
4		import java.io.PrintWriter;
5		import java.sql.Connection;
6		import java.sql.ResultSet;
7		import java.sql.SQLException;
8		import java.sql.Statement;
9		import java.util.ArrayList;
10		import java.util.Calendar;
11		import java.util.Date;
12		import java.util.GregorianCalendar;
13		import java.util.HashMap;
14		import java.util.Hashtable;
15		import java.util.Iterator;
16		import java.util.List;
17		import java.util.Map;
18		import java.util.TreeMap;
19
20		import javax.naming.Context;
21		import javax.naming.InitialContext;
22		import javax.servlet.ServletException;
23		import javax.servlet.http.HttpServletRequest;
24		import javax.servlet.http.HttpServletResponse;
25		import javax.sql.DataSource;
26
27		/**
28		* <p>This abstract class is the base code for select list servlets.</p>
29		*/
30	0	public abstract class SelectListServletBase extends RestServletBase {
31		private static final long serialVersionUID = 1;
32		private static final String FIELD_MAP_REQ_ATTR_KEY = "select.list.data.field.map";
33	0	private String contextKey = "";
34	0	private String dataSourceName = "";
35	0	private String defaultStartsWith = "";
36	0	private String defaultContains = "";
37	0	private String defaultOrderBy = "id";
38	0	private Map dataFields = new HashMap();
39
40		/**
41		* <p>The Servlet "doGet()" method.</p>
42		*
43		* @param req the <code>HttpServletRequest</code> object
44		* @param res the <code>HttpServletResponse</code> object
45		* @throws ServletException
46		* @throws IOException
47		*/
48		public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
49	0	StringBuffer buffer = new StringBuffer();
50
51	0	req.setAttribute(FIELD_MAP_REQ_ATTR_KEY, getCurrentDataFields(req));
52	0	buffer.append("<?xml version=\"1.0\"?>\n");
53	0	buffer.append("<options size=\"");
54	0	List options = getOptions(req);
55	0	if (options != null) {
56	0	buffer.append(options.size());
57	0	buffer.append("\">\n");
58	0	buffer.append(formatOptions(req, options));
59		} else {
60	0	buffer.append("0\">\n");
61		}
62	0	buffer.append("</options>\n");
63
64	0	PrintWriter pw = res.getWriter();
65	0	pw.println(buffer);
66	0	}
67
68		/**
69		* <p>The Servlet "doPost()" method.</p>
70		*
71		* @param req the <code>HttpServletRequest</code> object
72		* @param res the <code>HttpServletResponse</code> object
73		* @throws ServletException
74		* @throws IOException
75		*/
76		public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
77	0	res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
78	0	}
79
80		/**
81		* <p>The Servlet "doPut()" method.</p>
82		*
83		* @param req the <code>HttpServletRequest</code> object
84		* @param res the <code>HttpServletResponse</code> object
85		* @throws ServletException
86		* @throws IOException
87		*/
88		public void doPut(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
89	0	res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
90	0	}
91
92		/**
93		* <p>The Servlet "doDelete()" method.</p>
94		*
95		* @param req the <code>HttpServletRequest</code> object
96		* @param res the <code>HttpServletResponse</code> object
97		* @throws ServletException
98		* @throws IOException
99		*/
100		public void doDelete(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
101	0	res.sendError(405, "Method Not Allowed. Use the \"GET\" method for this URL");
102	0	}
103
104		/**
105		* <p>This method returns the current map of data fields.</p>
106		*
107		* @param req the <code>HttpServletRequest</code> object
108		* @return the current map of data fields
109		*/
110		protected Map getCurrentDataFields(HttpServletRequest req) {
111	0	return dataFields;
112		}
113
114		/**
115		* <p>This method formats the options.</p>
116		*
117		* @param req the <code>HttpServletRequest</code> object
118		* @param options the <code>List</code> of options
119		* @return the formatted options
120		*/
121		protected String formatOptions(HttpServletRequest req, List options) {
122	0	StringBuffer buffer = new StringBuffer();
123
124	0	int startIndex = 0;
125	0	int count = 0;
126	0	String start = req.getParameter("start");
127	0	if (start != null && start.length() > 0) {
128	0	startIndex = Integer.parseInt(start.trim());
129		}
130	0	String countString = req.getParameter("count");
131	0	if (countString != null && countString.length() > 0) {
132	0	count = Integer.parseInt(countString.trim());
133		}
134	0	if (count < 1) {
135	0	count = 10;
136		}
137	0	if (startIndex > 0) {
138	0	startIndex = startIndex - 1;
139		}
140	0	int endIndex = startIndex + count;
141	0	if (endIndex > options.size()) {
142	0	endIndex = options.size();
143		}
144
145	0	Map fields = (Map) req.getAttribute(FIELD_MAP_REQ_ATTR_KEY);
146	0	for (int x=startIndex; x<endIndex; x++) {
147	0	int index = x + 1;
148	0	Map thisItem = (Map) options.get(x);
149	0	buffer.append(" <option index=\"");
150	0	buffer.append(index);
151	0	buffer.append("\">\n");
152	0	Iterator i = fields.keySet().iterator();
153	0	while (i.hasNext()) {
154	0	String fieldName = (String) i.next();
155	0	buffer.append(" <field name=\"");
156	0	buffer.append(fieldName);
157	0	buffer.append("\">");
158	0	if (thisItem.get(fieldName) != null) {
159	0	buffer.append(thisItem.get(fieldName));
160		}
161	0	buffer.append("</field>\n");
162	0	}
163	0	buffer.append(" </option>\n");
164		}
165
166	0	return buffer.toString();
167		}
168
169		/**
170		* <p>This method retrieves the requested options from the session.</p>
171		*
172		* @param req the <code>HttpServletRequest</code> object
173		* @return the options
174		*/
175		protected List getOptions(HttpServletRequest req) {
176	0	List options = new ArrayList();
177	0	Date rightNow = new Date();
178	0	String startsWith = req.getParameter("startsWith");
179	0	String contains = req.getParameter("contains");
180	0	String orderBy = req.getParameter("orderBy");
181	0	if (startsWith == null \|\| startsWith.length() < 1) {
182	0	startsWith = defaultStartsWith;
183		}
184	0	if (contains == null \|\| contains.length() < 1) {
185	0	contains = defaultContains;
186		}
187	0	if (orderBy == null \|\| orderBy.length() < 1) {
188	0	orderBy = defaultOrderBy;
189		}
190	0	String cacheKey = startsWith + ":" + contains + ":" + orderBy + getCacheKeyQualifier(req);
191	0	Map queryCache = getQueryCache(req, contextKey);
192	0	if (queryCache.containsKey(cacheKey)) {
193	0	Map results = (Map) queryCache.get(cacheKey);
194	0	options = (List) results.get("options");
195	0	results.put("lastAccessed", rightNow);
196	0	Integer accessCount = (Integer) results.get("accessCount");
197	0	results.put("accessCount", new Integer(accessCount.intValue() + 1));
198	0	} else {
199	0	options = fetchOptions(req, startsWith, contains, orderBy);
200	0	if (options != null && options.size() > 0) {
201	0	Map results = new HashMap();
202	0	results.put("creationDate", rightNow);
203	0	results.put("lastAccessed", rightNow);
204	0	results.put("accessCount", new Integer(1));
205	0	results.put("options", options);
206	0	queryCache.put(cacheKey, results);
207		}
208		}
209
210	0	return options;
211		}
212
213		/**
214		* <p>This method returns any additional qualifiers for the key to the query cache.</p>
215		*
216		* @param req the <code>HttpServletRequest</code> object
217		* @return the cache key qualifier
218		*/
219		protected String getCacheKeyQualifier(HttpServletRequest req) {
220	0	return "";
221		}
222
223		/**
224		* <p>This method obtains the requested options from the data source.</p>
225		*
226		* @param req the <code>HttpServletRequest</code> object
227		* @param startsWith the "starts with" query parameter
228		* @param contains the "contains" query parameter
229		* @param orderBy the sort order
230		* @return the options
231		*/
232		protected List fetchOptions(HttpServletRequest req, String startsWith, String contains, String orderBy) {
233	0	List options = new ArrayList();
234
235	0	DataSource dataSource = getDataSource();
236	0	Connection conn = null;
237	0	Statement stmt = null;
238	0	ResultSet rs = null;
239	0	String qs = getQueryStatement(req, startsWith, contains, orderBy);
240	0	Map items = new TreeMap();
241	0	Map fields = (Map) req.getAttribute(FIELD_MAP_REQ_ATTR_KEY);
242		try {
243	0	conn = dataSource.getConnection();
244	0	conn.setAutoCommit(false);
245	0	stmt = conn.createStatement();
246	0	rs = stmt.executeQuery(qs);
247	0	while (rs.next()) {
248	0	Map thisItem = new HashMap();
249	0	Iterator i = fields.keySet().iterator();
250	0	while (i.hasNext()) {
251	0	String fieldName = (String) i.next();
252	0	String tableField = (String) fields.get(fieldName);
253	0	thisItem.put(fieldName, filter(rs.getString(tableField)));
254	0	}
255	0	if (orderBy == null \|\| orderBy.length() < 1) {
256	0	items.put(thisItem.get("id"), thisItem);
257		} else {
258	0	options.add(thisItem);
259		}
260	0	}
261	0	} catch (SQLException e) {
262	0	System.out.println("SQL statement: " + qs);
263	0	System.out.println("SQL error: " + e.toString() + "; " + e.getMessage());
264	0	e.printStackTrace();
265	0	} finally {
266	0	if (rs != null) {
267		try {
268	0	rs.close();
269	0	} catch (SQLException sqle) {
270	0	System.out.println("SQL error: " + sqle.toString() + "; " + sqle.getMessage());
271	0	sqle.printStackTrace();
272	0	}
273	0	rs = null;
274		}
275	0	if (stmt != null) {
276		try {
277	0	stmt.close();
278	0	} catch (SQLException sqle) {
279	0	System.out.println("SQL error: " + sqle.toString() + "; " + sqle.getMessage());
280	0	sqle.printStackTrace();
281	0	}
282	0	stmt = null;
283		}
284	0	if (conn != null) {
285		try {
286	0	conn.commit();
287	0	conn.close();
288	0	} catch (SQLException sqle) {
289	0	System.out.println("SQL error: " + sqle.toString() + "; " + sqle.getMessage());
290	0	sqle.printStackTrace();
291	0	}
292	0	conn = null;
293		}
294	0	}
295
296	0	if (orderBy == null \|\| orderBy.length() < 1) {
297	0	if (!items.isEmpty()) {
298	0	Iterator i = items.keySet().iterator();
299	0	while (i.hasNext()) {
300	0	String key = i.next().toString();
301	0	options.add(items.get(key));
302	0	}
303		}
304		}
305
306	0	return options;
307		}
308
309		/**
310		* <p>This method creates the SQL statement.</p>
311		*
312		* @param req the <code>HttpServletRequest</code> object
313		* @param startsWith the "starts with" query parameter
314		* @param contains the "contains" query parameter
315		* @param orderBy the sort order
316		* @return the SQL statement
317		*/
318		protected String getQueryStatement(HttpServletRequest req, String startsWith, String contains, String orderBy) {
319	0	return getQueryStatement(startsWith, contains, orderBy);
320		}
321
322		/**
323		* <p>This method creates the SQL statement.</p>
324		*
325		* @param startsWith the "starts with" query parameter
326		* @param contains the "contains" query parameter
327		* @param orderBy the sort order
328		* @return the SQL statement
329		*/
330		protected abstract String getQueryStatement(String startsWith, String contains, String orderBy);
331
332		/**
333		* <p>Fetch the query cache from the servlet context, or creates a new one.</p>
334		*
335		* @param req the <code>HttpServletRequest</code> object
336		* @param contextKey the servlet context key
337		*/
338		protected static Map getQueryCache(HttpServletRequest req, String contextKey) {
339	0	Map queryCache = (Map) req.getSession().getServletContext().getAttribute(contextKey);
340
341		// if no cache, then make one
342	0	if (queryCache == null) {
343	0	queryCache = new HashMap();
344	0	queryCache.put("_lastCacheReviewDateTime", new Date());
345	0	req.getSession().getServletContext().setAttribute(contextKey, queryCache);
346		}
347
348	0	Date lastCacheReviewDate = (Date) queryCache.get("_lastCacheReviewDateTime");
349	0	if (lastCacheReviewDate.before(getReviewCompareDate())) {
350	0	reviewQueryCache(queryCache);
351		}
352
353	0	return queryCache;
354		}
355
356		/**
357		* <p>Review the query cache and remove expired items.</p>
358		*/
359		protected static void reviewQueryCache(Map queryCache) {
360	0	Date cutoffDate = getReviewCompareDate();
361
362	0	List toBeRemoved = new ArrayList();
363	0	Iterator i = queryCache.keySet().iterator();
364	0	while (i.hasNext()) {
365	0	String key = (String) i.next();
366	0	if (!"_lastCacheReviewDateTime".equals(key)) {
367	0	Map results = (Map) queryCache.get(key);
368	0	Date creationDate = (Date) results.get("creationDate");
369	0	if (creationDate.before(cutoffDate)) {
370	0	toBeRemoved.add(key);
371		}
372		}
373	0	}
374	0	i = toBeRemoved.iterator();
375	0	while (i.hasNext()) {
376	0	queryCache.remove(i.next());
377		}
378
379	0	queryCache.put("_lastCacheReviewDateTime", new Date());
380	0	}
381
382		/**
383		* <p>Fetch the query cache from the servlet context, or creates a new one.</p>
384		*/
385		protected static Date getReviewCompareDate() {
386	0	Calendar calendar = new GregorianCalendar();
387	0	calendar.setTime(new Date());
388	0	calendar.add(Calendar.DATE, -1);
389	0	return calendar.getTime();
390		}
391
392		/**
393		* <p>This method is used to look up the <code>DataSource</code>
394		* by name.</p>
395		*
396		* @return <code>dataSource</code> - the <code>DataSource</code>
397		*/
398		protected DataSource getDataSource() {
399	0	DataSource dataSource = null;
400
401		try {
402	0	Context ctx = new InitialContext(new Hashtable());
403	0	dataSource = (DataSource) ctx.lookup(dataSourceName);
404	0	} catch (Throwable t) {
405	0	t.printStackTrace();
406	0	}
407
408	0	return dataSource;
409		}
410
411		/**
412		* @return Returns the contextKey.
413		*/
414		public String getContextKey() {
415	0	return contextKey;
416		}
417		/**
418		* @param contextKey The contextKey to set.
419		*/
420		public void setContextKey(String contextKey) {
421	0	this.contextKey = contextKey;
422	0	}
423		/**
424		* @return Returns the dataFields.
425		*/
426		public Map getDataFields() {
427	0	return dataFields;
428		}
429		/**
430		* @param dataFields The dataFields to set.
431		*/
432		public void setDataFields(Map dataFields) {
433	0	this.dataFields = dataFields;
434	0	}
435		/**
436		* @return Returns the dataSourceName.
437		*/
438		public String getDataSourceName() {
439	0	return dataSourceName;
440		}
441		/**
442		* @param dataSourceName The dataSourceName to set.
443		*/
444		public void setDataSourceName(String dataSourceName) {
445	0	this.dataSourceName = dataSourceName;
446	0	}
447		/**
448		* @return Returns the defaultContains.
449		*/
450		public String getDefaultContains() {
451	0	return defaultContains;
452		}
453		/**
454		* @param defaultContains The defaultContains to set.
455		*/
456		public void setDefaultContains(String defaultContains) {
457	0	this.defaultContains = defaultContains;
458	0	}
459		/**
460		* @return Returns the defaultOrderBy.
461		*/
462		public String getDefaultOrderBy() {
463	0	return defaultOrderBy;
464		}
465		/**
466		* @param defaultOrderBy The defaultOrderBy to set.
467		*/
468		public void setDefaultOrderBy(String defaultOrderBy) {
469	0	this.defaultOrderBy = defaultOrderBy;
470	0	}
471		/**
472		* @return Returns the defaultStartsWith.
473		*/
474		public String getDefaultStartsWith() {
475	0	return defaultStartsWith;
476		}
477		/**
478		* @param defaultStartsWith The defaultStartsWith to set.
479		*/
480		public void setDefaultStartsWith(String defaultStartsWith) {
481	0	this.defaultStartsWith = defaultStartsWith;
482	0	}
483		}