001: /*
002: * (C) Copyright 2004 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: package com.nabhinc.portlet.survey;
020:
021: import java.io.IOException;
022: import java.sql.Connection;
023: import java.sql.PreparedStatement;
024: import java.sql.ResultSet;
025: import java.sql.SQLException;
026: import java.util.Vector;
027:
028: import javax.portlet.PortletException;
029: import javax.portlet.RenderRequest;
030: import javax.portlet.RenderResponse;
031:
032: import com.nabhinc.portlet.mvcportlet.core.BaseRequestProcessor;
033: import com.nabhinc.portlet.mvcportlet.core.RenderConfig;
034: import com.nabhinc.portlet.mvcportlet.core.RenderProcessor;
035: import com.nabhinc.util.db.DBUtil;
036:
037: /**
038: *
039: *
040: * @author Padmanabh Dabke
041: * (c) 2004 Nabh Information Systems, Inc. All Rights Reserved.
042: */
043: public class ViewSurveysRenderProcessor extends BaseRequestProcessor
044: implements RenderProcessor {
045:
046: /* (non-Javadoc)
047: * @see com.nabhinc.portlet.mvcportlet.core.RenderProcessor#process(javax.portlet.RenderRequest, javax.portlet.RenderResponse, com.nabhinc.portlet.mvcportlet.core.RenderConfig)
048: */
049: public String process(RenderRequest request,
050: RenderResponse response, RenderConfig renderConfig)
051: throws PortletException, IOException {
052:
053: String surveySQL = "SELECT surveyid, surveytitle FROM SB_SURVEYS ORDER BY stime DESC";
054: String votesSQL = "SELECT SUM(optionvotes) FROM SB_SURVEY_DATA WHERE surveyid = ?";
055: String commentsSQL = "SELECT COUNT(*) FROM SB_SURVEY_COMMENTS WHERE surveyid = ?";
056:
057: Connection conn = null;
058: ResultSet resultSet = null;
059: ResultSet resultSet2 = null;
060: PreparedStatement st1 = null;
061: PreparedStatement st2 = null;
062: PreparedStatement st3 = null;
063: Vector surveyVec = new Vector();
064: try {
065: conn = brpConfig.getDataSource().getConnection();
066:
067: st1 = conn.prepareStatement(surveySQL);
068: st2 = conn.prepareStatement(votesSQL);
069: st3 = conn.prepareStatement(commentsSQL);
070:
071: resultSet = st1.executeQuery();
072: while (resultSet.next()) {
073: // Get survey ID and title
074: int surveyID = resultSet.getInt(1);
075: String surveyTitle = resultSet.getString(2);
076:
077: // Get total votes for current survey
078: st2.setInt(1, surveyID);
079: resultSet2 = st2.executeQuery();
080: resultSet2.next();
081: int numVotes = resultSet2.getInt(1);
082: resultSet2.close();
083: resultSet2 = null;
084:
085: // Get number of comments for this survey
086: st3.setInt(1, surveyID);
087: resultSet2 = st3.executeQuery();
088: resultSet2.next();
089: int numComments = resultSet2.getInt(1);
090: resultSet2.close();
091: resultSet2 = null;
092:
093: // Construct the data point
094: SurveyInfo sInfo = new SurveyInfo(surveyID,
095: surveyTitle, numVotes, numComments);
096: surveyVec.addElement(sInfo);
097:
098: }
099:
100: request.setAttribute("survey.surveys", surveyVec);
101:
102: } catch (SQLException ex) {
103: throw new PortletException("Database exception.", ex);
104: } finally {
105: DBUtil.close(resultSet);
106: DBUtil.close(resultSet2);
107: DBUtil.close(st1);
108: DBUtil.close(st2);
109: DBUtil.close(st3);
110: DBUtil.close(conn);
111: }
112:
113: return null;
114: }
115:
116: }
|