001: /*
002: * (C) Copyright 2006 Nabh Information Systems, Inc.
003: *
004: * All copyright notices regarding Nabh's products MUST remain
005: * intact in the scripts and in the outputted HTML.
006: * This program is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public License
008: * as published by the Free Software Foundation; either version 2.1
009: * of the License, or (at your option) any later version.
010: *
011: * This program is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: * GNU Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public License
017: * along with this program; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
019: *
020: */
021: package com.nabhinc.portlet.mvcportlet.renderprocessor;
022:
023: import java.io.IOException;
024: import java.sql.Connection;
025: import java.sql.Date;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.ResultSetMetaData;
029: import java.sql.SQLException;
030: import java.sql.Timestamp;
031: import java.sql.Types;
032: import java.text.ParseException;
033: import java.util.Vector;
034:
035: import javax.portlet.PortletException;
036: import javax.portlet.RenderRequest;
037: import javax.portlet.RenderResponse;
038:
039: import org.w3c.dom.Element;
040:
041: import com.nabhinc.portlet.mvcportlet.core.BaseRequestProcessor;
042: import com.nabhinc.portlet.mvcportlet.core.ControllerPortletConfig;
043: import com.nabhinc.portlet.mvcportlet.core.RenderConfig;
044: import com.nabhinc.portlet.mvcportlet.core.RenderProcessor;
045: import com.nabhinc.util.StringUtil;
046: import com.nabhinc.util.XMLUtil;
047: import com.nabhinc.util.db.DBConfigUtil;
048: import com.nabhinc.util.db.DBParamUtil;
049: import com.nabhinc.util.db.DBUtil;
050: import com.nabhinc.util.i18n.DateTimeFormatUtil;
051:
052: /**
053: * Fetches a list of records and sets it to a render request attribute (default is
054: * "mvcportlet.records").
055: * <ul>
056: * <li>sql - Query SQL to get a list of records.</li>
057: * <li>count-sql - SQL used to count number of records are returned from the above sql.</li>
058: * <li>params - Comma separated list of names of SQL parameters to be
059: * inserted. In general, these are the request parameter names. The
060: * following specialtokens are used to insert other values:
061: * <ul>
062: * <li>param-types - Comma separated list of SQL parameter types. This
063: * parameter must be specified if "params" are specified. Possible parameter
064: * types are: VARCHAR, INTEGER, DECIMAL, BOOLEAN, SMALLINT, DATE,
065: * TIME, TIMESTAMP, FLOAT, DOUBLE, ARRAY, BIGINT, BINARY, BIT,
066: * BLOB, CHAR, CLOB, LONGVARBINARY, LONGVARCHAR, JAVA_OBJECT
067: * </li>
068: * </ul>
069: * </li>
070: * </ul>
071: *
072: * @author Padmanabh Dabke
073: * (c) 2006 Nabh Information Systems, Inc. All Rights Reserved.
074: */
075: public class RecordLister extends BaseRequestProcessor implements
076: RenderProcessor {
077: public static final String PAGE_INDEX_PARAMETER = "pageindex";
078: public static final String PAGE_SIZE_PARAMETER = "pagesize";
079: public static final String ORDER_BY_PARAMETER = "orderby";
080: public static final String DESCENDING_PARAMETER = "descending";
081: public static final String PAGE_INDEX_ATTRIBUTE = "mvcportlet.pageindex";
082: public static final String PAGE_COUNT_ATTRIBUTE = "mvcportlet.pagecount";
083: private String srSQL = null;
084: private String srCountSQL = null;
085: private String[] srParams = null;
086: private int[] srParamTypes = null;
087: private String srAttributeName = "mvcportlet.records";
088:
089: public void init(Element config, ControllerPortletConfig cpConfig)
090: throws PortletException {
091: super .init(config, cpConfig);
092: srSQL = XMLUtil.getSubElementText(config, "sql");
093: if (srSQL == null) {
094: throw new PortletException(
095: "Missing required parameter: sql");
096: }
097:
098: srCountSQL = XMLUtil.getSubElementText(config, "count-sql");
099: if (srCountSQL == null) {
100: throw new PortletException(
101: "Missing required parameter: count-sql");
102: }
103:
104: String params = XMLUtil.getSubElementText(config, "params");
105: if (params != null) {
106: srParams = StringUtil.split(params, ",");
107: String paramTypes = XMLUtil.getSubElementText(config,
108: "param-types");
109: if (paramTypes == null) {
110: throw new PortletException(
111: "You must specify param-types if you specify params.");
112: } else {
113: String[] typeArray = StringUtil.split(paramTypes, ",");
114: if (typeArray.length != srParams.length) {
115: throw new PortletException(
116: "Number of param-types must be equal to number of params.");
117: } else {
118: srParamTypes = new int[typeArray.length];
119: for (int i = 0; i < typeArray.length; i++) {
120: try {
121: srParamTypes[i] = DBConfigUtil
122: .getSQLType(typeArray[i]);
123: } catch (Exception ex) {
124: throw new PortletException(
125: "Failed to parse parameter type.",
126: ex);
127: }
128: }
129: }
130: }
131: }
132:
133: // Check if a custom attribute name for record vector
134: String attribName = XMLUtil.getSubElementText(config,
135: "attribute-name");
136: if (attribName != null)
137: srAttributeName = attribName;
138: }
139:
140: /**
141: * Retrieves database records based on the. If one or more records are found, it sets request attribute
142: * "mvcportlet.records" to a vector of object arrays corresponding to
143: * the records. If no records are found, it returns "no-such-record".
144: */
145: public String process(RenderRequest request,
146: RenderResponse response, RenderConfig config)
147: throws PortletException, IOException {
148:
149: Connection conn = null;
150: ResultSet results = null;
151: PreparedStatement st = null;
152: int totalCount = 0;
153: int pageSize = 10;
154: int pageIndex = 0;
155: String orderBy = null;
156: boolean isDescending = false;
157:
158: if (request.getParameter(PAGE_INDEX_PARAMETER) != null) {
159: pageIndex = Integer.parseInt(request
160: .getParameter(PAGE_INDEX_PARAMETER));
161: }
162: request.setAttribute(PAGE_INDEX_ATTRIBUTE, new Integer(
163: pageIndex));
164:
165: orderBy = request.getParameter(ORDER_BY_PARAMETER);
166: boolean descending = "true".equals(request
167: .getParameter(DESCENDING_PARAMETER));
168:
169: String pageSizeStr = request.getPreferences().getValue(
170: PAGE_SIZE_PARAMETER, "10");
171: pageSize = Integer.parseInt(pageSizeStr);
172: try {
173: conn = brpConfig.getDataSource().getConnection();
174: st = conn.prepareStatement(srCountSQL);
175: results = st.executeQuery();
176: results.next();
177: totalCount = results.getInt(1);
178: if (totalCount == 0)
179: return "no-such-record";
180:
181: // Calculate page count
182: int pageCount = totalCount / pageSize;
183: if (totalCount % pageSize != 0)
184: pageCount++;
185: request.setAttribute(PAGE_COUNT_ATTRIBUTE, new Integer(
186: pageCount));
187:
188: // Add order clause to the sql if necessary
189: String sql = srSQL;
190: String desc = isDescending ? " DESC" : " ASC";
191: if (orderBy != null) {
192: sql = srSQL + " ORDER BY " + orderBy + desc;
193: }
194: st = conn.prepareStatement(sql);
195: if (srParams != null) {
196: for (int i = 0; i < srParams.length; i++) {
197: DBParamUtil.setSQLParam(st, i, srParams[i],
198: srParamTypes[i], request, -1);
199: }
200: }
201: Vector records = new Vector();
202: results = st.executeQuery();
203:
204: // Skip records before the current page.
205: int startIndex = pageIndex * pageSize;
206: for (int i = 0; i < startIndex; i++) {
207: if (!results.next())
208: break;
209: }
210: ResultSetMetaData metaData = results.getMetaData();
211: int numColumns = metaData.getColumnCount();
212: int numRows = 0;
213: while (results.next() && numRows < pageSize) {
214: numRows++;
215: String[] record = new String[numColumns];
216: Date d = null;
217: Timestamp tm = null;
218: for (int i = 0; i < numColumns; i++) {
219: int colIndex = i + 1;
220: int colType = metaData.getColumnType(colIndex);
221: switch (colType) {
222: case Types.DATE:
223: d = results.getDate(colIndex);
224: if (d == null)
225: record[i] = "";
226: else
227: record[i] = DateTimeFormatUtil
228: .getDateFormat(request.getLocale())
229: .format(d);
230: break;
231: case Types.TIMESTAMP:
232: tm = results.getTimestamp(colIndex);
233: if (tm == null)
234: record[i] = "";
235: else
236: record[i] = DateTimeFormatUtil
237: .getDateTimeFormat(
238: request.getLocale())
239: .format(tm);
240: default:
241: record[i] = results.getString(colIndex);
242:
243: }
244: }
245: records.addElement(record);
246: }
247: request.setAttribute(srAttributeName, records);
248:
249: return "success";
250: } catch (SQLException sqe) {
251: throw new PortletException("Database exception.", sqe);
252: } catch (ParseException pex) {
253: throw new PortletException("Malformed request parameter.",
254: pex);
255: } finally {
256: DBUtil.close(results);
257: DBUtil.close(st);
258: DBUtil.close(conn);
259: }
260: }
261:
262: }
|