001: /*
002: * (C) Copyright 2000 - 2003 Nabh Information Systems, Inc.
003: *
004: * This program is free software; you can redistribute it and/or
005: * modify it under the terms of the GNU General Public License
006: * as published by the Free Software Foundation; either version 2
007: * of the License, or (at your option) any later version.
008: *
009: * This program is distributed in the hope that it will be useful,
010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: * GNU General Public License for more details.
013: *
014: * You should have received a copy of the GNU General Public License
015: * along with this program; if not, write to the Free Software
016: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: *
018: */
019:
020: package com.nabhinc.portlet.table;
021:
022: import java.io.IOException;
023: import java.sql.Connection;
024: import java.sql.PreparedStatement;
025: import java.sql.ResultSet;
026: import java.sql.Types;
027:
028: import javax.portlet.ActionRequest;
029: import javax.portlet.ActionResponse;
030: import javax.portlet.PortletConfig;
031: import javax.portlet.PortletException;
032: import javax.portlet.PortletPreferences;
033: import javax.portlet.PortletRequestDispatcher;
034: import javax.portlet.PortletSession;
035: import javax.portlet.RenderRequest;
036: import javax.portlet.RenderResponse;
037:
038: import com.nabhinc.portlet.base.BasePortlet;
039: import com.nabhinc.util.StringUtil;
040: import com.nabhinc.util.db.DBUtil;
041:
042: /**
043: * This portlet generates tabular displays typically used
044: * for showing emails, alerts, tasks, etc. The table data
045: * is obtained directly from a relational database using
046: * SQL strings set in the configuration.
047: *
048: * @author Padmanabh Dabke
049: * (c) 2003 Nabh Information Systems, Inc. All Rights Reserved.
050: */
051: public class TablePortlet extends BasePortlet {
052:
053: // Constants that define attribute and parameter names
054: public static final String TABLE_INFO_ATTRIB = "com.nabhinc.portlet.table.table_info";
055: public static final String RESULT_SET_ATTRIB = "com.nabhinc.portlet.table.result_set";
056: public static final String ERROR_MESSAGE_ATTRIB = "com.nabhinc.portlet.table.error_message";
057:
058: public static final String PAGE_NUMBER_PARAM = "page_number";
059: public static final String SORT_TYPE_PARAM = "sort_type";
060: public static final String ORDER_BY_PARAM = "order_by";
061: public static final String SELECTED_ITEMS_PARAM = "selected_items";
062: public static final String DISPLAY_STATE_PARAM = "display_state";
063: public static final String ROW_ID_PARAM = "row_id";
064: public static final String ROW_NAME_PARAM = "row_name";
065: public static final String RESET_PARAM = "reset";
066:
067: public static final String LIST_SQL_INIT_PARAM = "listSQL";
068: public static final String DATA_SOURCE_INIT_PARAM = "dataSource";
069: public static final String COUNT_SQL_INIT_PARAM = "countSQL";
070: public static final String EMPTY_TABLE_MSG_INIT_PARAM = "emptyTableMessage";
071: public static final String INSERT_USER_INIT_PARAM = "insertUser";
072: public static final String INSERT_PARAM_NAME_INIT_PARAM = "insertParamName";
073: public static final String INSERT_PARAM_VALUE_INIT_PARAM = "insertParamValue";
074: public static final String INSERT_PARAM_TYPE_INIT_PARAM = "insertParamType";
075: public static final String HEADERS_INIT_PARAM = "headers";
076: public static final String ACTION_LABEL_INIT_PARAM = "actionLabel";
077: public static final String ACTION_RESTRICT_SQL_INIT_PARAM = "actionRestrictSQL";
078: public static final String ACTION_SQL_INIT_PARAM = "actionSQL";
079: public static final String ACTION_RESTRICT_MSG_INIT_PARAM = "actionRestrictMessage";
080: public static final String ROW_DISPLAY_PAGE_INIT_PARAM = "rowDisplayPage";
081: public static final String ROW_DISPLAY_PAGE_EXT_INIT_PARAM = "rowDisplayPageExternal";
082: public static final String LINK_ID_PARAM_NAME_INIT_PARAM = "linkIDParamName";
083: public static final String LINK_NAME_PARAM_NAME_INIT_PARAM = "linkNameParamName";
084: public static final String LINK_COLUMN_INDEX_INIT_PARAM = "linkColumnIndex";
085: public static final String LINK_EXCLUDED_VALUES_INIT_PARAM = "linkExcludedValues";
086: public static final String LINK_NEW_WINDOW_INIT_PARAM = "linkNewWindow";
087: public static final String LINK_WINDOW_HEIGHT_INIT_PARAM = "linkWindowHeight";
088: public static final String LINK_WINDOW_WIDTH_INIT_PARAM = "linkWindowWidth";
089: public static final String PRIMARY_KEY_INDEX_INIT_PARAM = "primaryKeyIndex";
090: public static final String PRIMARY_KEY_TYPE_INIT_PARAM = "primaryKeyType";
091: public static final String SORT_COLUMN_INIT_PARAM = "sortColumn";
092: public static final String SORT_TYPE_INIT_PARAM = "sortType";
093: public static final String COLUMN_NAMES_INIT_PARAM = "columnNames";
094: public static final String COLUMN_FORMATS_INIT_PARAM = "columnFormats";
095: public static final String COLUMN_TYPES_INIT_PARAM = "columnTypes";
096: public static final String ROW_STATE_COLUMN_INDEX_INIT_PARAM = "rowStateColumnIndex";
097: public static final String TABLE_DISPLAY_PAGE_INIT_PARAM = "tableDisplayPage";
098: public static final String SET_ID_PAGE_INIT_PARAM = "setIDPage";
099: public static final String ERROR_PAGE_INIT_PARAM = "errorPage";
100: public static final String INCLUDE_NAME_INIT_PARAM = "includeName";
101: public static final String IMAGE_FOLDER_INIT_PARAM = "imageFolder";
102: public static final String CREATE_LABEL_INIT_PARAM = "createLabel";
103: public static final String CREATE_PAGE_INIT_PARAM = "createPage";
104: public static final String SEARCH_LABEL_INIT_PARAM = "searchLabel";
105: public static final String SEARCH_PAGE_INIT_PARAM = "searchPage";
106:
107: public static final String ROWS_PER_PAGE_PREF = "rowsPerPage";
108: public static final String DEFAULT_ROWS_PER_PAGE = "10";
109:
110: // Standard values for request parameters
111: public static final String DISPLAY_STATE_ROW = "row";
112: public static final String DISPLAY_STATE_CREATE = "create";
113: public static final String DISPLAY_STATE_SEARCH = "search";
114: public static final String DISPLAY_STATE_TABLE = "table";
115:
116: public static final String SORT_TYPE_ASCENDING = "ascending";
117: public static final String SORT_TYPE_DESCENDING = "descending";
118:
119: private TableInfo tpTableInfo = new TableInfo();
120:
121: public static class TableInfo {
122: public String dataSource = null;
123: public String listSQL = null;
124: public String countSQL = null;
125: public String actionSQL = null;
126: public String actionRestrictSQL = null;
127: public String actionRestrictMessage = "Some of the items could not be deleted due to delete restrictions.";
128: public String actionLabel = null;
129: public int primKeyIndex = -1;
130: public int primKeyType = java.sql.Types.VARCHAR;
131: public String sortCol = null;
132: public boolean sortDir = true;
133: public int linkIndex = -1;
134: public String[] headers = null;
135: public String[] columns = null;
136: public int[] columnTypes = null;
137: public String[] columnFormats = null;
138: public int currentPage = -1;
139: public boolean insertUser = false;
140: public String emptyMessage = "";
141: public boolean showNewWindow = false;
142: public int newWindowHeight = -1;
143: public int newWindowWidth = -1;
144: public int rowStateIndex = -1;
145: public boolean includeName = false;
146: public String insertParamName = null;
147: public String insertParamValue = null;
148: public int insertParamType = Types.INTEGER;
149: public String linkIDParamName = null;
150: public String linkNameParamName = null;
151: public String[] linkExcludedValues = null;
152: public String setIDJSP = "/portlets/table/setID.jsp";
153: public String tableDisplayJSP = "/portlets/table/table.jsp";
154: public String errorJSP = "/portlets/table/error.jsp";
155: public String rowDisplayJSP = null;
156: public String imageFolder = "/images";
157: public String createLabel = null;
158: public String createJSP = null;
159: public String searchLabel = null;
160: public String searchJSP = null;
161: public boolean isRowDisplayJSPExternal = false;
162:
163: }
164:
165: public void init(PortletConfig config) throws PortletException {
166: super .init(config);
167:
168: String temp = null;
169:
170: // Get required parameters: listSQL
171:
172: temp = config.getInitParameter(LIST_SQL_INIT_PARAM);
173: if (temp == null) {
174: error("init", "Missing required init parameter: "
175: + LIST_SQL_INIT_PARAM + ".");
176: throw new PortletException("Missing list SQL specification");
177: } else {
178: tpTableInfo.listSQL = temp;
179: }
180:
181: // Get required parameters: countSQL
182:
183: temp = config.getInitParameter(COUNT_SQL_INIT_PARAM);
184: if (temp == null) {
185: error("init", "Missing required init parameter: countSQL.");
186: throw new PortletException(
187: "Missing count SQL specification");
188: } else {
189: tpTableInfo.countSQL = temp;
190: }
191:
192: temp = config.getInitParameter(EMPTY_TABLE_MSG_INIT_PARAM);
193: if (temp != null)
194: tpTableInfo.emptyMessage = temp;
195:
196: temp = config.getInitParameter(INSERT_USER_INIT_PARAM);
197: if ("true".equalsIgnoreCase(temp))
198: tpTableInfo.insertUser = true;
199:
200: tpTableInfo.insertParamName = config
201: .getInitParameter(INSERT_PARAM_NAME_INIT_PARAM);
202: tpTableInfo.insertParamValue = config
203: .getInitParameter(INSERT_PARAM_VALUE_INIT_PARAM);
204: tpTableInfo.insertParamType = getSQLType(config
205: .getInitParameter(INSERT_PARAM_TYPE_INIT_PARAM));
206:
207: // Get table headers
208: temp = config.getInitParameter(HEADERS_INIT_PARAM);
209: if (temp != null) {
210: tpTableInfo.headers = StringUtil.split(temp, ",");
211: } else {
212: error("init", "Missing table headers");
213: throw new PortletException(
214: "Missing required init parameter: "
215: + HEADERS_INIT_PARAM + ".");
216: }
217:
218: // Set datasource if specified
219: temp = config.getInitParameter(DATA_SOURCE_INIT_PARAM);
220: if (temp != null) {
221: tpTableInfo.dataSource = temp;
222: } else {
223: error("init", "Missing SQL datasource name.");
224: throw new PortletException(
225: "Missing required init parameter: "
226: + DATA_SOURCE_INIT_PARAM + ".");
227: }
228:
229: // Set action related parameters
230: tpTableInfo.actionLabel = config
231: .getInitParameter(ACTION_LABEL_INIT_PARAM);
232: tpTableInfo.actionSQL = config
233: .getInitParameter(ACTION_SQL_INIT_PARAM);
234: tpTableInfo.actionRestrictSQL = config
235: .getInitParameter(ACTION_RESTRICT_SQL_INIT_PARAM);
236: String tempActionMsg = config
237: .getInitParameter(ACTION_RESTRICT_MSG_INIT_PARAM);
238: if (tempActionMsg != null)
239: tpTableInfo.actionRestrictMessage = tempActionMsg;
240:
241: // Set table link related parameters
242: tpTableInfo.rowDisplayJSP = config
243: .getInitParameter(ROW_DISPLAY_PAGE_INIT_PARAM);
244: if ("true".equalsIgnoreCase(config
245: .getInitParameter(ROW_DISPLAY_PAGE_EXT_INIT_PARAM))) {
246: tpTableInfo.isRowDisplayJSPExternal = true;
247: }
248: tpTableInfo.linkIDParamName = config
249: .getInitParameter(LINK_ID_PARAM_NAME_INIT_PARAM);
250: if (tpTableInfo.linkIDParamName == null
251: || "".equals(tpTableInfo.linkIDParamName))
252: tpTableInfo.linkIDParamName = TablePortlet.ROW_ID_PARAM;
253:
254: tpTableInfo.linkNameParamName = config
255: .getInitParameter(LINK_NAME_PARAM_NAME_INIT_PARAM);
256: if (tpTableInfo.linkNameParamName == null
257: || "".equals(tpTableInfo.linkNameParamName))
258: tpTableInfo.linkNameParamName = TablePortlet.ROW_NAME_PARAM;
259:
260: temp = config.getInitParameter(LINK_EXCLUDED_VALUES_INIT_PARAM);
261: if (temp != null)
262: tpTableInfo.linkExcludedValues = StringUtil
263: .split(temp, ",");
264:
265: temp = config.getInitParameter(LINK_COLUMN_INDEX_INIT_PARAM);
266: if (temp == null) {
267: tpTableInfo.linkIndex = -1;
268: } else {
269: tpTableInfo.linkIndex = Integer.parseInt(temp);
270: }
271:
272: temp = config.getInitParameter(LINK_NEW_WINDOW_INIT_PARAM);
273: if (temp != null && (!(temp.equals("")))
274: && temp.equalsIgnoreCase("true")) {
275: tpTableInfo.showNewWindow = true;
276: temp = config
277: .getInitParameter(LINK_WINDOW_HEIGHT_INIT_PARAM);
278: if (temp != null && (!"".equals(temp))) {
279: tpTableInfo.newWindowHeight = Integer.parseInt(temp);
280: }
281: temp = config
282: .getInitParameter(LINK_WINDOW_WIDTH_INIT_PARAM);
283: if (temp != null && (!"".equals(temp))) {
284: tpTableInfo.newWindowWidth = Integer.parseInt(temp);
285: }
286: }
287:
288: // Get identifier column if specified
289: temp = config.getInitParameter(PRIMARY_KEY_INDEX_INIT_PARAM);
290: if (temp != null) {
291: tpTableInfo.primKeyIndex = Integer.parseInt(temp);
292: }
293: temp = config.getInitParameter(PRIMARY_KEY_TYPE_INIT_PARAM);
294: if (temp != null) {
295: tpTableInfo.primKeyType = getSQLType(temp);
296: }
297:
298: // Try setting up sorting related stuff
299: tpTableInfo.sortCol = config
300: .getInitParameter(SORT_COLUMN_INIT_PARAM);
301: temp = config.getInitParameter(SORT_TYPE_INIT_PARAM);
302: if (temp != null
303: && SORT_TYPE_DESCENDING.equals(temp.toLowerCase()))
304: tpTableInfo.sortDir = false;
305:
306: temp = config.getInitParameter(COLUMN_NAMES_INIT_PARAM);
307: if (temp == null) {
308: tpTableInfo.columns = new String[tpTableInfo.headers.length];
309: for (int i = 0; i < tpTableInfo.columns.length; i++) {
310: tpTableInfo.columns[i] = null;
311: }
312: } else {
313: tpTableInfo.columns = StringUtil.split(temp, ",");
314: if (tpTableInfo.columns.length < tpTableInfo.headers.length) {
315: error("init",
316: "Number of columns must be greater than or equal to number of headers");
317: throw new PortletException(
318: "Number of columns != number of headers");
319: }
320: for (int i = 0; i < tpTableInfo.columns.length; i++) {
321: if ("null".equals(tpTableInfo.columns[i])
322: || "".equals(tpTableInfo.columns[i])) {
323: tpTableInfo.columns[i] = null;
324: }
325: }
326: }
327:
328: temp = config.getInitParameter(COLUMN_FORMATS_INIT_PARAM);
329: if (temp == null) {
330: tpTableInfo.columnFormats = new String[tpTableInfo.headers.length];
331: for (int i = 0; i < tpTableInfo.columnFormats.length; i++) {
332: tpTableInfo.columnFormats[i] = null;
333: }
334: } else {
335: tpTableInfo.columnFormats = StringUtil.split(temp, ",");
336: if (tpTableInfo.columnFormats.length != tpTableInfo.headers.length) {
337: error("init",
338: "Number of column formats is not equal to number of headers");
339: throw new PortletException(
340: "Number of column formats != number of headers");
341: }
342: for (int i = 0; i < tpTableInfo.columnFormats.length; i++) {
343: if ("null".equals(tpTableInfo.columnFormats[i])
344: || "".equals(tpTableInfo.columnFormats[i])) {
345: tpTableInfo.columnFormats[i] = null;
346: }
347: }
348: }
349:
350: temp = config.getInitParameter(COLUMN_TYPES_INIT_PARAM);
351: if (temp == null) {
352: tpTableInfo.columnTypes = new int[tpTableInfo.columns.length];
353: for (int i = 0; i < tpTableInfo.columnFormats.length; i++) {
354: tpTableInfo.columnTypes[i] = java.sql.Types.VARCHAR;
355: }
356: } else {
357: String[] cTypes = StringUtil.split(temp, ",");
358: if (cTypes.length != tpTableInfo.columns.length) {
359: error("init",
360: "Number of column types is not equal to number of column names");
361: throw new PortletException(
362: "Number of column types != number of column names");
363: }
364: tpTableInfo.columnTypes = new int[cTypes.length];
365: for (int i = 0; i < cTypes.length; i++) {
366: tpTableInfo.columnTypes[i] = getSQLType(cTypes[i]);
367: }
368: }
369:
370: temp = config
371: .getInitParameter(ROW_STATE_COLUMN_INDEX_INIT_PARAM);
372: if (temp != null) {
373: tpTableInfo.rowStateIndex = Integer.parseInt(temp);
374: }
375:
376: temp = config.getInitParameter(TABLE_DISPLAY_PAGE_INIT_PARAM);
377: if (temp != null) {
378: tpTableInfo.tableDisplayJSP = temp;
379: }
380:
381: temp = config.getInitParameter(SET_ID_PAGE_INIT_PARAM);
382: if (temp != null) {
383: tpTableInfo.setIDJSP = temp;
384: }
385:
386: temp = config.getInitParameter(ERROR_PAGE_INIT_PARAM);
387: if (temp != null) {
388: tpTableInfo.errorJSP = temp;
389: }
390:
391: if (config.getInitParameter(INCLUDE_NAME_INIT_PARAM) != null) {
392: tpTableInfo.includeName = true;
393: }
394:
395: temp = config.getInitParameter(IMAGE_FOLDER_INIT_PARAM);
396: if (temp != null) {
397: tpTableInfo.imageFolder = temp;
398: }
399:
400: tpTableInfo.createLabel = config
401: .getInitParameter(CREATE_LABEL_INIT_PARAM);
402: tpTableInfo.createJSP = config
403: .getInitParameter(CREATE_PAGE_INIT_PARAM);
404: tpTableInfo.searchLabel = config
405: .getInitParameter(SEARCH_LABEL_INIT_PARAM);
406: tpTableInfo.searchJSP = config
407: .getInitParameter(SEARCH_PAGE_INIT_PARAM);
408:
409: }
410:
411: private int getSQLType(String str) {
412: if (str == null)
413: return java.sql.Types.VARCHAR;
414: str = str.toUpperCase();
415: if (str.equals("VARCHAR"))
416: return java.sql.Types.VARCHAR;
417: else if (str.equals("INTEGER"))
418: return java.sql.Types.INTEGER;
419: else if (str.equals("DECIMAL"))
420: return java.sql.Types.DECIMAL;
421: else if (str.equals("NUMERIC"))
422: return java.sql.Types.NUMERIC;
423: else if (str.equals("BOOLEAN"))
424: return java.sql.Types.BOOLEAN;
425: else if (str.equals("SMALLINT"))
426: return java.sql.Types.SMALLINT;
427: else if (str.equals("DATE"))
428: return java.sql.Types.DATE;
429: else if (str.equals("TIME"))
430: return java.sql.Types.TIME;
431: else if (str.equals("TIMESTAMP"))
432: return java.sql.Types.TIMESTAMP;
433: else if (str.equals("FLOAT"))
434: return java.sql.Types.FLOAT;
435: else if (str.equals("DOUBLE"))
436: return java.sql.Types.DOUBLE;
437: else if (str.equals("ARRAY"))
438: return java.sql.Types.ARRAY;
439: else if (str.equals("BIGINT"))
440: return java.sql.Types.BIGINT;
441: else if (str.equals("BINARY"))
442: return java.sql.Types.BINARY;
443: else if (str.equals("BIT"))
444: return java.sql.Types.BIT;
445: else if (str.equals("BLOB"))
446: return java.sql.Types.BLOB;
447: else if (str.equals("CHAR"))
448: return java.sql.Types.CHAR;
449: else if (str.equals("CLOB"))
450: return java.sql.Types.CLOB;
451: else if (str.equals("LONGVARBINARY"))
452: return java.sql.Types.LONGVARBINARY;
453: else if (str.equals("LONGVARCHAR"))
454: return java.sql.Types.LONGVARCHAR;
455: else if (str.equals("JAVA_OBJECT"))
456: return java.sql.Types.JAVA_OBJECT;
457: else
458: return java.sql.Types.VARCHAR;
459: }
460:
461: private String getSQLTypeString(int code) {
462:
463: switch (code) {
464: case Types.VARCHAR:
465: return "VARCHAR";
466: case Types.INTEGER:
467: return "INTEGER";
468: case Types.DECIMAL:
469: return "DECIMAL";
470: case Types.NUMERIC:
471: return "NUMERIC";
472: case Types.BOOLEAN:
473: return "BOOLEAN";
474: case Types.SMALLINT:
475: return "SMALLINT";
476: case Types.DATE:
477: return "DATE";
478: case Types.TIME:
479: return "TIME";
480: case Types.TIMESTAMP:
481: return "TIMESTAMP";
482: case Types.FLOAT:
483: return "FLOAT";
484: case Types.DOUBLE:
485: return "DOUBLE";
486: case Types.ARRAY:
487: return "ARRAY";
488: case Types.BIGINT:
489: return "BIGINT";
490: case Types.BINARY:
491: return "BINARY";
492: case Types.BIT:
493: return "BIT";
494: case Types.BLOB:
495: return "BLOB";
496: case Types.CHAR:
497: return "CHAR";
498: case Types.CLOB:
499: return "CLOB";
500: case Types.LONGVARBINARY:
501: return "LONGVARBINARY";
502: case Types.LONGVARCHAR:
503: return "LONGVARCHAR";
504: case Types.JAVA_OBJECT:
505: return "JAVA_OBJECT";
506: default:
507: return "VARCHAR";
508: }
509: }
510:
511: public void processAction(ActionRequest request,
512: ActionResponse actionResponse) throws PortletException,
513: java.io.IOException {
514:
515: Connection conn = null;
516: ResultSet countResults = null;
517: ResultSet actionPrecondResults = null;
518: PreparedStatement delSt = null;
519: PreparedStatement countSt = null;
520: PreparedStatement actionPrecondSt = null;
521: boolean autoCommit = true;
522:
523: // Check if processAction is being called to set rows per page
524: String action = request.getParameter("action");
525: if ("edit_prefs".equals(action)) {
526: int maxRows = -1;
527: String maxRowsStr = request.getParameter("maxRows");
528: boolean validRows = false;
529: try {
530: maxRows = Integer.parseInt(maxRowsStr);
531: if (maxRows > 0) {
532: validRows = true;
533: }
534: } catch (Exception ex) {
535: // Ignore
536: }
537:
538: if (validRows) {
539: try {
540: PortletPreferences pref = request.getPreferences();
541: pref.setValue(ROWS_PER_PAGE_PREF, maxRowsStr);
542: pref.store();
543: actionResponse
544: .setRenderParameter("success", "true");
545: } catch (Exception ex) {
546: actionResponse.setRenderParameter("error",
547: "Failed to save preferences: "
548: + ex.toString());
549: }
550:
551: } else {
552: actionResponse.setRenderParameter("error",
553: "Invalid rows per page value.");
554: }
555:
556: }
557:
558: if (tpTableInfo.insertParamName != null) {
559: // Check if the request has a parameter by that name
560: tpTableInfo.insertParamValue = request
561: .getParameter(tpTableInfo.insertParamName);
562: if (tpTableInfo.insertParamValue == null) {
563: // Check if the request has that attribute
564: tpTableInfo.insertParamValue = request.getPreferences()
565: .getValue(tpTableInfo.insertParamName, null);
566: }
567: }
568:
569: try {
570: // Get database connection
571: conn = DBUtil.getConnection(tpTableInfo.dataSource);
572: autoCommit = conn.getAutoCommit();
573: conn.setAutoCommit(false);
574:
575: // Perform database action for marked entries
576:
577: String[] markedEntries = request
578: .getParameterValues(SELECTED_ITEMS_PARAM);
579: if (markedEntries != null) {
580: delSt = conn.prepareStatement(tpTableInfo.actionSQL);
581: if (tpTableInfo.actionRestrictSQL != null) {
582: actionPrecondSt = conn
583: .prepareStatement(tpTableInfo.actionRestrictSQL);
584: }
585:
586: int markPos = 1;
587: if (tpTableInfo.insertUser) {
588: delSt.setString(markPos, request.getRemoteUser());
589: markPos++;
590: }
591:
592: switch (tpTableInfo.primKeyType) {
593: case java.sql.Types.INTEGER:
594: for (int i = 0; i < markedEntries.length; i++) {
595: if (actionPrecondSt != null) {
596: actionPrecondSt.setInt(1, Integer
597: .parseInt(markedEntries[i]));
598: actionPrecondResults = actionPrecondSt
599: .executeQuery();
600: if (actionPrecondResults.next()) {
601: actionPrecondResults.close();
602: request
603: .setAttribute(
604: ERROR_MESSAGE_ATTRIB,
605: tpTableInfo.actionRestrictMessage);
606: continue;
607: } else {
608: actionPrecondResults.close();
609: }
610: }
611: delSt.setInt(markPos, Integer
612: .parseInt(markedEntries[i]));
613: delSt.execute();
614: }
615: break;
616: case java.sql.Types.VARCHAR:
617: default:
618: for (int i = 0; i < markedEntries.length; i++) {
619: if (actionPrecondSt != null) {
620: actionPrecondSt.setString(1,
621: markedEntries[i]);
622: actionPrecondResults = actionPrecondSt
623: .executeQuery();
624: if (actionPrecondResults.next()) {
625: actionPrecondResults.close();
626: request
627: .setAttribute(
628: ERROR_MESSAGE_ATTRIB,
629: tpTableInfo.actionRestrictMessage);
630: continue;
631: } else {
632: actionPrecondResults.close();
633: }
634: }
635: delSt.setString(markPos, markedEntries[i]);
636: delSt.execute();
637: }
638: break;
639: }
640: }
641:
642: countSt = conn.prepareStatement(tpTableInfo.countSQL);
643: int markPos = 1;
644: if (tpTableInfo.insertUser) {
645: countSt.setString(markPos, request.getRemoteUser());
646: markPos++;
647: }
648: if (tpTableInfo.insertParamName != null) {
649: switch (tpTableInfo.insertParamType) {
650: case java.sql.Types.INTEGER:
651: countSt.setInt(markPos, Integer
652: .parseInt(tpTableInfo.insertParamValue));
653: break;
654: default:
655: countSt.setString(markPos,
656: tpTableInfo.insertParamValue);
657: break;
658: }
659: markPos++;
660: }
661:
662: countResults = countSt.executeQuery();
663: countResults.next();
664: int rowCount = countResults.getInt(1);
665: int rowsPerPage = Integer
666: .parseInt(request.getPreferences().getValue(
667: ROWS_PER_PAGE_PREF, DEFAULT_ROWS_PER_PAGE));
668: int newNumPages = rowCount / rowsPerPage;
669: if (rowCount % rowsPerPage == 0)
670: newNumPages--;
671: String pageNumStr = (String) request.getPortletSession()
672: .getAttribute(PAGE_NUMBER_PARAM);
673: if (pageNumStr != null) {
674: int pageNum = Integer.parseInt(pageNumStr);
675: if (newNumPages < pageNum) {
676: request.getPortletSession().setAttribute(
677: PAGE_NUMBER_PARAM,
678: Integer.toString(newNumPages));
679: }
680: }
681:
682: conn.commit();
683:
684: } catch (Exception ex) {
685: try {
686: conn.rollback();
687: } catch (Exception exx) {
688: }
689: throw new PortletException(
690: "Failed to execute table action.", ex);
691: } finally {
692: try {
693: conn.setAutoCommit(autoCommit);
694: } catch (Exception ex) {
695: }
696: DBUtil.close(countResults);
697: DBUtil.close(actionPrecondResults);
698: DBUtil.close(delSt);
699: DBUtil.close(countSt);
700: DBUtil.close(actionPrecondSt);
701: DBUtil.close(conn);
702: }
703: }
704:
705: public void doView(RenderRequest request, RenderResponse response)
706: throws PortletException, IOException {
707:
708: if (DISPLAY_STATE_ROW.equals(request
709: .getParameter(DISPLAY_STATE_PARAM))) {
710: try {
711: PortletRequestDispatcher dispatcher = bpContext
712: .getRequestDispatcher(tpTableInfo.rowDisplayJSP);
713: dispatcher.include(request, response);
714: return;
715: } catch (IOException e) {
716: throw new PortletException(
717: "Failed to include content from row display JSP.",
718: e);
719: }
720:
721: } else if (DISPLAY_STATE_CREATE.equals(request
722: .getParameter(DISPLAY_STATE_PARAM))) {
723: try {
724: PortletRequestDispatcher dispatcher = bpContext
725: .getRequestDispatcher(tpTableInfo.createJSP);
726: dispatcher.include(request, response);
727: return;
728: } catch (IOException e) {
729: throw new PortletException(
730: "Failed to include content from create JSP.", e);
731: }
732: } else if (DISPLAY_STATE_SEARCH.equals(request
733: .getParameter(DISPLAY_STATE_PARAM))) {
734: try {
735: PortletRequestDispatcher dispatcher = bpContext
736: .getRequestDispatcher(tpTableInfo.searchJSP);
737: dispatcher.include(request, response);
738: return;
739: } catch (IOException e) {
740: throw new PortletException(
741: "Failed to include content from search JSP.", e);
742: }
743: }
744:
745: // Store table display parameters in portlet session.
746: PortletSession pSession = request.getPortletSession();
747: String sortType = request.getParameter(SORT_TYPE_PARAM);
748: if (sortType != null)
749: pSession.setAttribute(SORT_TYPE_PARAM, sortType);
750: String orderBy = request.getParameter(ORDER_BY_PARAM);
751: if (orderBy != null)
752: pSession.setAttribute(ORDER_BY_PARAM, orderBy);
753: String pageNumber = request.getParameter(PAGE_NUMBER_PARAM);
754: if (pageNumber != null)
755: pSession.setAttribute(PAGE_NUMBER_PARAM, pageNumber);
756:
757: java.sql.Connection conn = null;
758: boolean insertParam = false;
759: PortletPreferences pref = request.getPreferences();
760:
761: response.setContentType(request.getResponseContentType());
762:
763: String insertParamValue = null;
764: int insertParamType = tpTableInfo.insertParamType;
765: if (tpTableInfo.insertParamName != null) {
766: insertParam = true;
767: // Check if the request has a parameter by that name
768: insertParamValue = request
769: .getParameter(tpTableInfo.insertParamName);
770: if (insertParamValue == null) {
771: // Check if the request has that attribute
772: insertParamValue = pref.getValue(
773: tpTableInfo.insertParamName, null);
774: if (insertParamValue == null) {
775: insertParamValue = tpTableInfo.insertParamValue;
776: }
777: }
778: }
779:
780: if (insertParam && insertParamValue == null) {
781: try {
782: PortletRequestDispatcher dispatcher = bpContext
783: .getRequestDispatcher(tpTableInfo.setIDJSP);
784: dispatcher.include(request, response);
785: } catch (IOException e) {
786: throw new PortletException(
787: "Failed to include content from display JSP.",
788: e);
789: }
790: return;
791: }
792:
793: orderBy = (String) pSession.getAttribute(ORDER_BY_PARAM);
794:
795: //String idParamName = tpTableInfo.linkIDParamName == null ? "id" : tpTableInfo.linkIDParamName;
796:
797: if (orderBy == null) {
798: orderBy = tpTableInfo.sortCol;
799: }
800: boolean isAscending = true;
801:
802: sortType = (String) pSession.getAttribute(SORT_TYPE_PARAM);
803: if (sortType == null) {
804: isAscending = tpTableInfo.sortDir;
805: } else if (SORT_TYPE_DESCENDING.equals(sortType)) {
806: isAscending = false;
807: }
808:
809: // String sortImg = "<IMG valign=\"middle\" src=\"" + tpTableInfo.imageFolder + "/" + sortType + ".gif\">";
810: int pageNum = 0;
811:
812: String pageNumStr = request.getParameter(PAGE_NUMBER_PARAM);
813: if (pageNumStr != null) {
814: pageNum = Integer.parseInt(pageNumStr);
815: }
816: // int rowsPerPage = Integer.parseInt(request.getPreferences().getValue(ROWS_PER_PAGE_PREF, "10"));
817: // int startIndex = pageNum * rowsPerPage;
818: // int endIndex = startIndex + rowsPerPage - 1;
819: String listSQL = tpTableInfo.listSQL;
820: java.sql.ResultSet queryResults = null;
821:
822: try {
823: // Get results to be displayed
824: conn = DBUtil.getConnection(tpTableInfo.dataSource);
825: if (orderBy != null) {
826: listSQL = listSQL + " ORDER BY " + orderBy;
827: // default order is ascending
828: if (!isAscending)
829: listSQL = listSQL + " DESC";
830: }
831: java.sql.PreparedStatement st = conn
832: .prepareStatement(listSQL);
833: int markPos = 1;
834: if (tpTableInfo.insertUser) {
835: st.setString(markPos, request.getRemoteUser());
836: markPos++;
837: }
838: if (insertParam && listSQL.indexOf("?") > -1) {
839: switch (insertParamType) {
840: case java.sql.Types.INTEGER:
841: st.setInt(markPos, Integer
842: .parseInt(insertParamValue));
843: break;
844: default:
845: st.setString(markPos, insertParamValue);
846: break;
847: }
848: markPos++;
849: }
850:
851: queryResults = st.executeQuery();
852: } catch (Exception ex) {
853: try {
854: conn.close();
855: } catch (Exception ex1) {
856: }
857: throw new PortletException(
858: "Failed to obtain table data from the database.",
859: ex);
860: }
861:
862: try {
863: request.setAttribute(RESULT_SET_ATTRIB, queryResults);
864: request.setAttribute(TABLE_INFO_ATTRIB, tpTableInfo);
865: PortletRequestDispatcher dispatcher = bpContext
866: .getRequestDispatcher(tpTableInfo.tableDisplayJSP);
867: dispatcher.include(request, response);
868: } catch (IOException e) {
869: throw new PortletException(
870: "Failed to include content from display JSP.", e);
871: } finally {
872: try {
873: conn.close();
874: } catch (Exception ex1) {
875: }
876:
877: }
878:
879: }
880:
881: }
|