0001: package com.sun.portal.app.collab.survey.model;
0002:
0003: import java.sql.*;
0004: import java.util.*;
0005: import java.util.logging.*;
0006: import javax.sql.*;
0007: import javax.naming.*;
0008: import com.sun.portal.log.common.PortalLogger;
0009: import com.sun.portal.search.demo.SearchDatabase;
0010: import com.sun.portal.search.soif.SOIF;
0011: import com.sun.portal.app.collab.survey.util.SurveyUtil;
0012: import com.sun.portal.app.collab.survey.util.Resources;
0013: import com.sun.portal.app.collab.survey.faces.SurveysFacesPortlet;
0014: import javax.faces.context.FacesContext;
0015: import javax.portlet.PortletURL;
0016:
0017: public class SurveyDB {
0018:
0019: private static Logger logger = PortalLogger
0020: .getLogger(SurveyDB.class);
0021: private static final String db = "java:comp/env/jdbc/SurveyDB";
0022: private boolean supportsGenereatedKeys = false;
0023:
0024: protected Connection getConnection() throws SurveyException {
0025: try {
0026: logger.fine("Getting database connection");
0027: InitialContext ctx;
0028: ctx = new InitialContext();
0029: DataSource ds = (DataSource) ctx.lookup(db);
0030: Connection conn = ds.getConnection();
0031: supportsGenereatedKeys = conn.getMetaData()
0032: .supportsGetGeneratedKeys();
0033: return conn;
0034: } catch (Exception e) {
0035: logger
0036: .severe("Could not get database connection for surveys");
0037: logException("Exception while connecting to db", e);
0038: throw new SurveyException("nodbconn", e);
0039: }
0040: }
0041:
0042: private static int getGeneratedKey(String key, String table,
0043: PreparedStatement ps, Connection conn) throws SQLException {
0044:
0045: int keyval = -1;
0046:
0047: ResultSet rs = ps.getGeneratedKeys();
0048: rs.next();
0049:
0050: ResultSetMetaData rsmd = rs.getMetaData();
0051: String colName = rsmd.getColumnName(1);
0052:
0053: if (colName.equalsIgnoreCase("ROWID")) { // oracle
0054: String rsql = "select " + key + " from " + table
0055: + " where rowid='" + rs.getString(1) + "'";
0056: Statement s = conn.prepareStatement(rsql);
0057: ResultSet srs = s.executeQuery(rsql);
0058: srs.next();
0059: keyval = srs.getInt(1);
0060: } else { // derby and others
0061: keyval = rs.getInt(1);
0062: }
0063: rs.close();
0064:
0065: return keyval;
0066: }
0067:
0068: /*
0069: * method that inserts a entry in Survey Table, and return it's survey_id
0070: * return -1 if troubles occured
0071: */
0072: private int insertSurvey(Connection conn, SurveyModel survey)
0073: throws SQLException {
0074: int survey_id = -1;
0075: String sql = "insert into surveys(survey_name, survey_description, "
0076: + "community_id, start_date, end_date, survey_owner, survey_type)"
0077: + "values(?,?,?,?,?,?,?)";
0078: PreparedStatement ps = (supportsGenereatedKeys) ? conn
0079: .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
0080: : conn.prepareStatement(sql);
0081: ps.setString(1, survey.getSurveyName().trim());
0082: ps.setString(2, survey.getSurveyDescription().trim());
0083: ps.setString(3, survey.getCommunityId().trim());
0084: ps.setDate(4,
0085: new java.sql.Date(survey.getStartDate().getTime()));
0086: ps.setDate(5, new java.sql.Date(survey.getEndDate().getTime()));
0087: ps.setString(6, survey.getSurveyOwner().trim());
0088: ps.setString(7, survey.isPoll() ? "POLL" : "SURVEY");
0089: if (!ps.execute()) {
0090: if (ps.getUpdateCount() != -1) {
0091: if (!supportsGenereatedKeys) {
0092: String sIdSql = "select * from surveys order by survey_id desc";
0093: Statement idStmt = conn.createStatement();
0094: idStmt.setFetchSize(10);
0095: idStmt.setMaxRows(10);
0096: if (idStmt.execute(sIdSql)) {
0097: // reading resultSet
0098: ResultSet rsId = idStmt.getResultSet();
0099: if (rsId != null) {
0100: long s = System.currentTimeMillis();
0101: while (rsId.next()) {
0102: if (survey.getSurveyName().trim()
0103: .equals(
0104: rsId.getString(
0105: "survey_name")
0106: .trim())
0107: && survey
0108: .getSurveyOwner()
0109: .trim()
0110: .equals(
0111: rsId
0112: .getString(
0113: "survey_owner")
0114: .trim())
0115: && survey
0116: .getCommunityId()
0117: .trim()
0118: .equals(
0119: rsId
0120: .getString(
0121: "community_id")
0122: .trim())
0123: && survey
0124: .getSurveyDescription()
0125: .trim()
0126: .equals(
0127: rsId
0128: .getString(
0129: "survey_description")
0130: .trim())) {
0131: survey_id = rsId.getInt(1);
0132: break;
0133: }
0134: }
0135: long e = System.currentTimeMillis();
0136: if (logger.isLoggable(Level.FINE)) {
0137: logger
0138: .fine("inserted survey lookup took "
0139: + String.valueOf(e - s)
0140: + "ms survey_id=["
0141: + survey_id + "]");
0142: }
0143: rsId.close();
0144: } else {
0145: if (logger.isLoggable(Level.FINE)) {
0146: logger
0147: .fine("failed to getting generated Key : "
0148: + sIdSql);
0149: }
0150: }
0151: } else {
0152: if (logger.isLoggable(Level.FINE)) {
0153: logger
0154: .fine("failed to get last 10 surveys");
0155: }
0156: }
0157: idStmt.close();
0158: } else {
0159: survey_id = getGeneratedKey("survey_id", "surveys",
0160: ps, conn);
0161: }
0162: } else {
0163: if (logger.isLoggable(Level.FINE)) {
0164: logger.fine("insert count invalid");
0165: }
0166: }
0167: } else {
0168: if (logger.isLoggable(Level.FINE)) {
0169: logger.fine("failed executing insert of survey");
0170: }
0171: }
0172: ps.close();
0173: if (logger.isLoggable(Level.FINE)) {
0174: logger.fine("inserted survey_id=[" + survey_id + "]");
0175: }
0176: return survey_id;
0177: }
0178:
0179: public void create(SurveyModel survey) throws SurveyException {
0180: // Create the survey in the db, using data from the bean
0181: logger.fine("Creating survey in DB: survey_name = "
0182: + survey.getSurveyName());
0183: Connection conn = null;
0184: try {
0185: conn = getConnection();
0186: logger.fine("Got db connection");
0187: conn.setAutoCommit(false);
0188: boolean doCommit = true;
0189:
0190: // Transaction begins
0191:
0192: // First insert the survey
0193: int survey_id = insertSurvey(conn, survey);
0194: if (survey_id != -1) {
0195: survey.setSurveyId(survey_id);
0196: } else {
0197: doCommit = false;
0198: if (logger.isLoggable(Level.FINE)) {
0199: logger.fine("failed to get id of inserted survey");
0200: }
0201: }
0202:
0203: if (doCommit) {
0204: StringBuffer text4Index = new StringBuffer();
0205: // Second, batch insert the questions
0206: String sql = "insert into questions(survey_id, question_order, question_text,"
0207: + "is_mandatory, has_open_answer, is_multiselect)"
0208: + "values(?,?,?,?,?,?)";
0209: PreparedStatement ps = conn.prepareStatement(sql);
0210: QuestionModel[] questions = survey.getQuestions();
0211: for (int i = 0; i < questions.length; i++) {
0212: ps.setInt(1, survey.getSurveyId());
0213: ps.setInt(2, questions[i].getQuestionOrder());
0214: ps.setString(3, questions[i].getQuestionText());
0215: text4Index.append(questions[i].getQuestionText()
0216: + "\n");
0217: ps.setBoolean(4, questions[i].isMandatory());
0218: ps.setBoolean(5, questions[i].isOpen());
0219: ps.setBoolean(6, questions[i].isMultiselect());
0220: ps.addBatch();
0221: } // end loop on questions
0222: int[] qCounts = ps.executeBatch();
0223: for (int s = 0; s < qCounts.length; s++) {
0224: if (qCounts[s] == Statement.EXECUTE_FAILED) {
0225: doCommit = false;
0226: if (logger.isLoggable(Level.FINE)) {
0227: logger.fine("failed to insert questions ["
0228: + questions[s].getQuestionText()
0229: + "]");
0230: }
0231: break;
0232: }
0233: }
0234: ps.close();
0235: if (doCommit) {
0236: // retrieve the id of the questions inserted
0237: String questionIdSql = "select question_id from questions where survey_id = ? order by question_id asc";
0238: PreparedStatement qIdPs = conn
0239: .prepareStatement(questionIdSql);
0240: qIdPs.setInt(1, survey.getSurveyId());
0241: if (qIdPs.execute()) {
0242: ResultSet rsQuestionId = qIdPs.getResultSet();
0243: int qNdx = 0;
0244: int qKey = -1;
0245: while (rsQuestionId.next()) {
0246: qKey = rsQuestionId.getInt(1);
0247: questions[qNdx].setQuestionId(qKey);
0248: //batch insert the answers for this question
0249: AnswerModel[] answers = questions[qNdx]
0250: .getAnswers();
0251: String asql = "insert into answers(question_id, answer_order, answer_text) values(?,?,?)";
0252: PreparedStatement aps = conn
0253: .prepareStatement(asql);
0254: for (int j = 0; j < answers.length; j++) {
0255: aps.setInt(1, qKey);
0256: aps.setInt(2, j + 1);
0257: aps.setString(3, answers[j]
0258: .getAnswerText());
0259: aps.addBatch();
0260: }
0261: int[] aCounts = aps.executeBatch();
0262: for (int s = 0; s < aCounts.length; s++) {
0263: if (aCounts[s] == Statement.EXECUTE_FAILED) {
0264: doCommit = false;
0265: if (logger.isLoggable(Level.FINE)) {
0266: logger
0267: .fine("failed to insert answer["
0268: + answers[s]
0269: .getAnswerOrder()
0270: + "]="
0271: + answers[s]
0272: .getAnswerText());
0273: }
0274: break;
0275: }
0276: }
0277: aps.close();
0278: if (doCommit) {
0279: //retrieve the inserted answers Id.
0280: String answerIdSql = "select answer_id from answers where question_id = ? order by answer_id asc";
0281: PreparedStatement aIdPs = conn
0282: .prepareStatement(answerIdSql);
0283: aIdPs.setInt(1, qKey);
0284: if (aIdPs.execute()) {
0285: ResultSet rsAnswerId = aIdPs
0286: .getResultSet();
0287: int aNdx = 0;
0288: int aKey = -1;
0289: while (rsAnswerId.next()) {
0290: aKey = rsAnswerId.getInt(1);
0291: answers[aNdx].setAnswerId(aKey);
0292: aNdx++;
0293: }// end while answers
0294: } else {
0295: doCommit = false;
0296: if (logger.isLoggable(Level.FINE)) {
0297: logger
0298: .fine("failed retrieving answers' id");
0299: }
0300: break;
0301: }
0302: aIdPs.close();
0303: } else {
0304: if (logger.isLoggable(Level.FINE)) {
0305: logger
0306: .fine("not commiting survey because error on answers");
0307: }
0308: }
0309: qNdx++;
0310: } //end while questions
0311: } else {
0312: doCommit = false;
0313: if (logger.isLoggable(Level.FINE)) {
0314: logger
0315: .fine("failed retrieving questions' id");
0316: }
0317: }
0318: } else {
0319: if (logger.isLoggable(Level.FINE)) {
0320: logger
0321: .fine("Not commiting because error on questions");
0322: }
0323: }
0324:
0325: if (logger.isLoggable(Level.FINE)) {
0326: logger.fine("Done inserting questions");
0327: }
0328: if (doCommit) {
0329: conn.commit();
0330: SurveyUtil util = new SurveyUtil();
0331: SearchDatabase searchDb = util.getSearchDatabase();
0332: if (searchDb != null) {
0333: SOIF s = new SOIF("DOCUMENT", "surveys::"
0334: + survey.getSurveyId());
0335: s.insert("title", survey.getSurveyName());
0336: s.insert("description", survey
0337: .getSurveyDescription());
0338: s.insert("author", survey.getSurveyOwner());
0339: s.insert("partial-text", text4Index.toString());
0340: FacesContext context = FacesContext
0341: .getCurrentInstance();
0342: Map sessionMap = context.getExternalContext()
0343: .getSessionMap();
0344: PortletURL actionURL = (PortletURL) sessionMap
0345: .get("actionUrl4Index");
0346: //actionURL.setParameter("surveyId", Integer.toString(survey.getSurveyId());
0347: actionURL.setParameter(
0348: SurveysFacesPortlet.searchRequestID,
0349: Integer.toString(survey.getSurveyId()));
0350: s
0351: .insert("rd-display-url", actionURL
0352: .toString());
0353: try {
0354: searchDb.insert(s);
0355: } catch (Exception e) {
0356: if (logger.isLoggable(Level.FINE)) {
0357: logger
0358: .fine("failed indexing survey id=["
0359: + survey.getSurveyId()
0360: + "] - "
0361: + e.getMessage());
0362: }
0363: }
0364: }
0365: } else {
0366: conn.rollback();
0367: conn.commit();
0368: if (logger.isLoggable(Level.FINE)) {
0369: logger
0370: .fine("do not commit whole survey creation");
0371: }
0372: }
0373:
0374: }
0375: } catch (SQLException e) {
0376: try {
0377: logException("Error while creating survey:", e);
0378: if (conn != null && !conn.isClosed()) {
0379: conn.rollback();
0380: conn.commit();
0381: }
0382: throw new SurveyException("create-survey-error", e);
0383: } catch (SQLException ex) {
0384: logException(
0385: "Error while rolling back tansaction for create()",
0386: ex);
0387: throw new SurveyException("rollback-failure", ex);
0388: }
0389: } finally {
0390: try {
0391: if (conn != null && !conn.isClosed()) {
0392: conn.close();
0393: }
0394: } catch (SQLException ex) {
0395: logException("Exception while closing connection", ex);
0396: throw new SurveyException("create-failure", ex);
0397: }
0398: }
0399: }
0400:
0401: public void delete(int surveyId) throws SurveyException {
0402:
0403: // Delete the survey from the db
0404: Connection conn = null;
0405: try {
0406: logger.fine("Begin deleting survey " + surveyId);
0407: conn = getConnection();
0408: conn.setAutoCommit(false);
0409: SurveyModel survey = new SurveyModel();
0410: survey.setSurveyId(surveyId);
0411: deleteResponses(conn, surveyId);
0412: QuestionModel questions[] = survey.getQuestions();
0413: if (questions != null) {
0414: for (int i = 0; i < questions.length; i++) {
0415: AnswerModel[] answers = questions[i].getAnswers();
0416: if (answers != null) {
0417: for (int j = 0; j < answers.length; j++) {
0418: deleteAnswer(conn, answers[j].getAnswerId());
0419: }
0420: }
0421: deleteQuestion(conn, questions[i].getQuestionId());
0422: }
0423:
0424: deleteSurvey(conn, surveyId);
0425: }
0426:
0427: conn.commit();
0428: SurveyUtil util = new SurveyUtil();
0429: SearchDatabase searchDb = util.getSearchDatabase();
0430: if (searchDb != null) {
0431: try {
0432: searchDb.delete("surveys::" + surveyId);
0433: } catch (Exception e) {
0434: e.printStackTrace();
0435: }
0436: }
0437: logger.fine("Done deleting survey " + surveyId);
0438: } catch (SQLException e) {
0439: logException("Error while deleting survey ", e);
0440: try {
0441: if (conn != null && !conn.isClosed()) {
0442: conn.rollback();
0443: conn.commit();
0444: }
0445: throw new SurveyException("delete-failure", e);
0446: } catch (SQLException ex) {
0447: logException("Exception while rolling back delete", ex);
0448: throw new SurveyException("rollback-failure", ex);
0449: }
0450: } finally {
0451: try {
0452: if (conn != null && !conn.isClosed()) {
0453: conn.close();
0454: }
0455: } catch (SQLException ex) {
0456: logException("Exception while closing connection", ex);
0457: throw new SurveyException("delete-failure", ex);
0458: }
0459: }
0460: }
0461:
0462: public void delete(String ctyId) throws SurveyException {
0463:
0464: // Delete all the communitie's surveys from the db
0465: Connection conn = null;
0466: try {
0467: ArrayList surveys = search(ctyId, null, null, null,
0468: LIST_TYPE_BOTH, LIST_STATUS_ALL, 0);
0469: if (surveys != null && surveys.size() > 0) {
0470: conn = getConnection();
0471: conn.setAutoCommit(false);
0472: SurveyModel sm = null;
0473: Iterator si = surveys.listIterator();
0474: while (si.hasNext()) {
0475: sm = (SurveyModel) si.next();
0476: deleteResponses(conn, sm.getSurveyId());
0477: QuestionModel questions[] = sm.getQuestions();
0478: if (questions != null) {
0479: for (int i = 0; i < questions.length; i++) {
0480: AnswerModel[] answers = questions[i]
0481: .getAnswers();
0482: if (answers != null) {
0483: for (int j = 0; j < answers.length; j++) {
0484: deleteAnswer(conn, answers[j]
0485: .getAnswerId());
0486: }
0487: }
0488: deleteQuestion(conn, questions[i]
0489: .getQuestionId());
0490: }
0491:
0492: deleteSurvey(conn, sm.getSurveyId());
0493: }
0494: }
0495: conn.commit();
0496: }
0497: logger.fine("Done deleting surveys for community " + ctyId);
0498: } catch (SQLException e) {
0499: logException("Error while deleting survey ", e);
0500: try {
0501: if (conn != null && !conn.isClosed()) {
0502: conn.rollback();
0503: conn.commit();
0504: }
0505: throw new SurveyException("delete-surveys-failure", e);
0506: } catch (SQLException ex) {
0507: logException("Exception while rolling back delete", ex);
0508: throw new SurveyException("rollback-failure", ex);
0509: }
0510: } finally {
0511: try {
0512: if (conn != null && !conn.isClosed()) {
0513: conn.close();
0514: }
0515: } catch (SQLException ex) {
0516: logException("Exception while closing connection", ex);
0517: throw new SurveyException("delete-failure", ex);
0518: }
0519: }
0520: }
0521:
0522: protected void deleteAnswer(Connection conn, int answerId)
0523: throws SQLException {
0524: logger.fine("Deleting answer: " + answerId);
0525: String sql = "delete from answers where answer_id = "
0526: + answerId;
0527: Statement s = conn.createStatement();
0528: s.executeUpdate(sql);
0529: s.close();
0530: }
0531:
0532: protected void deleteQuestion(Connection conn, int questionId)
0533: throws SQLException {
0534: logger.fine("Deleting question: " + questionId);
0535: String sql = "delete from questions where question_id = "
0536: + questionId;
0537: Statement s = conn.createStatement();
0538: s.executeUpdate(sql);
0539: s.close();
0540: }
0541:
0542: protected void deleteSurvey(Connection conn, int surveyId)
0543: throws SQLException {
0544: logger.fine("Deleting survey: " + surveyId);
0545: String sql = "delete from surveys where survey_id = "
0546: + surveyId;
0547: Statement s = conn.createStatement();
0548: s.executeUpdate(sql);
0549: s.close();
0550: }
0551:
0552: protected void deleteResponses(Connection conn, int surveyId)
0553: throws SQLException {
0554: logger.fine("Deleting responses for survey: " + surveyId);
0555: String sql = "delete from responses where survey_id = "
0556: + surveyId;
0557: Statement s = conn.createStatement();
0558: s.executeUpdate(sql);
0559: s.close();
0560: }
0561:
0562: public ArrayList list(String communityId, int type, int status,
0563: int maxnum) throws SurveyException {
0564: // Returns an ArrayList containing SurveyModel objects
0565: logger.fine("Listing");
0566: return search(communityId, null, null, null, type, status,
0567: maxnum);
0568: }
0569:
0570: public ArrayList search(String communityId, String searchstr,
0571: java.util.Date startdate, java.util.Date enddate, int type,
0572: int status, int maxnum) throws SurveyException {
0573: // Returns an ArrayList containing SurveyModel objects
0574: String sql;
0575: Connection conn = null;
0576:
0577: try {
0578: conn = getConnection();
0579: sql = "select survey_id, survey_name, survey_description,"
0580: + "survey_owner, community_id, start_date, end_date, survey_type "
0581: + "from surveys";
0582: switch (type) {
0583: case LIST_TYPE_POLLS:
0584: logger.fine("searching for polls only");
0585: sql = sql + " where survey_type='POLL'";
0586: break;
0587: case LIST_TYPE_SURVEYS:
0588: logger.fine("searching for surveys only");
0589: sql = sql + " where survey_type='SURVEY'";
0590: break;
0591: case LIST_TYPE_BOTH:
0592: logger.fine("searching for both polls/surveys ");
0593: sql = sql
0594: + " where (survey_type='SURVEY' or survey_type='POLL')";
0595: break;
0596: }
0597:
0598: sql = sql + " and (community_id = '" + communityId + "') ";
0599:
0600: String filter;
0601: if (searchstr != null && searchstr.length() > 0) {
0602: filter = " and ((survey_name like '%" + searchstr
0603: + "%' ESCAPE '|')"
0604: + " or (survey_description like '%" + searchstr
0605: + "%' ESCAPE '|')) ";
0606: sql = sql + filter;
0607:
0608: }
0609: if (startdate != null) {
0610: logger.fine("Adding start date to filter");
0611: filter = " and start_date >= '" + startdate.toString()
0612: + "' ";
0613: sql = sql + filter;
0614: }
0615: if (enddate != null) {
0616: logger.fine("Adding end date to filter");
0617: filter = " and end_date <= '" + enddate.toString()
0618: + "' ";
0619: sql = sql + filter;
0620: }
0621: if (status != LIST_STATUS_ALL) {
0622: logger.fine("Looking for OPEN surveys/polls only");
0623: filter = " and end_date "
0624: + ((status == LIST_STATUS_OPEN) ? ">=" : "<=")
0625: + "'" + new java.util.Date().toString() + "' ";
0626: sql = sql + filter;
0627: }
0628: logger.finest("SQL for search is : " + sql);
0629: Statement s;
0630: s = conn.createStatement();
0631: ResultSet rs = s.executeQuery(sql);
0632: ArrayList l = new ArrayList();
0633: SurveyModel survey;
0634: while (rs.next()) {
0635: survey = new SurveyModel();
0636: survey.setSurveyId(rs.getInt("survey_id"));
0637: survey.setSurveyName(rs.getString("survey_name"));
0638: survey.setSurveyDescription(rs
0639: .getString("survey_description"));
0640: survey.setSurveyOwner(rs.getString("survey_owner"));
0641: survey.setCommunityId(rs.getString("community_id"));
0642: survey.setStartDate(rs.getDate("start_date"));
0643: survey.setEndDate(rs.getDate("end_date"));
0644: survey.setPoll("POLL".equalsIgnoreCase(rs
0645: .getString("survey_type")) ? true : false);
0646: l.add(survey);
0647: if (maxnum > 0 && l.size() >= maxnum) {
0648: break;
0649: }
0650: }
0651: rs.close();
0652: return l;
0653: } catch (SQLException e) {
0654: logException("Exception while searching database", e);
0655: throw new SurveyException("search-failure", e);
0656: } finally {
0657: try {
0658: if (conn != null && !conn.isClosed()) {
0659: conn.close();
0660: }
0661: } catch (SQLException ex) {
0662: logException("Exception while closing connection", ex);
0663: throw new SurveyException("search-failure", ex);
0664: }
0665: }
0666: }
0667:
0668: public ArrayList getQuestions(int surveyId) throws SurveyException {
0669: Connection conn = null;
0670: String sql;
0671: logger.fine("Getting questions for survey " + surveyId);
0672: try {
0673: conn = getConnection();
0674: sql = "select question_id, question_order, question_text, "
0675: + " is_mandatory, has_open_answer, is_multiselect from questions"
0676: + " where survey_id = " + surveyId
0677: + " order by question_order";
0678: Statement s = conn.createStatement();
0679: ResultSet rs = s.executeQuery(sql);
0680: ArrayList l = new ArrayList();
0681: QuestionModel q;
0682: while (rs.next()) {
0683: q = new QuestionModel();
0684: q.setQuestionId(rs.getInt("question_id"));
0685: q.setQuestionOrder(rs.getInt("question_order"));
0686: q.setQuestionText(rs.getString("question_text"));
0687: q.setMandatory(rs.getBoolean("is_mandatory"));
0688: q.setOpen(rs.getBoolean("has_open_answer"));
0689: q.setMultiselect(rs.getBoolean("is_multiselect"));
0690: l.add(q);
0691: }
0692: rs.close();
0693: s.close();
0694: return l;
0695: } catch (SQLException e) {
0696: logException(
0697: "Exception while getting questions for survey "
0698: + surveyId, e);
0699: throw new SurveyException("get-questions-failure", e);
0700: } finally {
0701: try {
0702: if (conn != null && !conn.isClosed()) {
0703: conn.close();
0704: }
0705: } catch (SQLException ex) {
0706: logException("Exception while closing connection", ex);
0707: throw new SurveyException("get-questions-failure", ex);
0708: }
0709: }
0710:
0711: }
0712:
0713: public ArrayList getAnswers(int questionid) throws SurveyException {
0714:
0715: Connection conn = null;
0716: String sql;
0717: logger.fine("Getting answers from db for question "
0718: + questionid);
0719: try {
0720: conn = getConnection();
0721: sql = "select answer_id, answer_order, answer_text from answers "
0722: + "where question_id = "
0723: + questionid
0724: + " order by answer_order";
0725: Statement s = conn.createStatement();
0726: ResultSet rs = s.executeQuery(sql);
0727: ArrayList l = new ArrayList();
0728: AnswerModel a;
0729: while (rs.next()) {
0730: a = new AnswerModel();
0731: a.setAnswerId(rs.getInt("answer_id"));
0732: a.setAnswerOrder(rs.getInt("answer_order"));
0733: a.setAnswerText(rs.getString("answer_text"));
0734: l.add(a);
0735: }
0736: rs.close();
0737: s.close();
0738: return l;
0739: } catch (SQLException e) {
0740: logException(
0741: "Exception while fetching answers for question "
0742: + questionid, e);
0743: throw new SurveyException("get-answers-failure", e);
0744: } finally {
0745: try {
0746: if (conn != null && !conn.isClosed()) {
0747: conn.close();
0748: }
0749: } catch (SQLException ex) {
0750: logException(
0751: "Exception while fetching answers for question "
0752: + questionid, ex);
0753: throw new SurveyException("get-answers-failure", ex);
0754: }
0755: }
0756: }
0757:
0758: public int getSurveyResponseCount(int surveyId)
0759: throws SurveyException {
0760:
0761: String sql;
0762: Connection conn = null;
0763: int count = 0;
0764: logger
0765: .fine("Getting count of responses for survey "
0766: + surveyId);
0767: try {
0768: conn = getConnection();
0769: sql = "select count(distinct user_id) from responses where survey_id = "
0770: + surveyId;
0771: Statement s = conn.createStatement();
0772: ResultSet rs = s.executeQuery(sql);
0773: rs.next();
0774: count = rs.getInt(1);
0775: rs.close();
0776: s.close();
0777: } catch (SQLException e) {
0778: logException(
0779: "Exception while fetching response count for survey "
0780: + surveyId, e);
0781: throw new SurveyException("get-respcount-failure", e);
0782: } finally {
0783: try {
0784: if (conn != null && !conn.isClosed()) {
0785: conn.close();
0786: }
0787: } catch (SQLException ex) {
0788: logException(
0789: "Exception while fetching response count for survey "
0790: + surveyId, ex);
0791: throw new SurveyException("get-respcount-failure", ex);
0792: }
0793: }
0794:
0795: return count;
0796: }
0797:
0798: public int getQuestionRespondantCount(int questionId)
0799: throws SurveyException {
0800: String sql;
0801: Connection conn = null;
0802: int count = 0;
0803: logger.fine("Getting count of respondant for question "
0804: + questionId);
0805: try {
0806: conn = getConnection();
0807: sql = "select count(distinct user_id) from responses where question_id = "
0808: + questionId;
0809: Statement s = conn.createStatement();
0810: ResultSet rs = s.executeQuery(sql);
0811: rs.next();
0812: count = rs.getInt(1);
0813: rs.close();
0814: s.close();
0815: } catch (SQLException e) {
0816: logException(
0817: "Exception while fetching respondant count for question "
0818: + questionId, e);
0819: throw new SurveyException("get-respdantcount-failure", e);
0820: } finally {
0821: try {
0822: if (conn != null && !conn.isClosed()) {
0823: conn.close();
0824: }
0825: } catch (SQLException ex) {
0826: logException(
0827: "Exception while fetching respondant count for question "
0828: + questionId, ex);
0829: throw new SurveyException("get-respdantcount-failure",
0830: ex);
0831: }
0832: }
0833:
0834: return count;
0835: }
0836:
0837: public int getQuestionResponseCount(int questionId)
0838: throws SurveyException {
0839: String sql;
0840: Connection conn = null;
0841: int count = 0;
0842: logger.fine("Getting count of responses for question "
0843: + questionId);
0844: try {
0845: conn = getConnection();
0846: sql = "select count(user_id) from responses where question_id = "
0847: + questionId;
0848: Statement s = conn.createStatement();
0849: ResultSet rs = s.executeQuery(sql);
0850: rs.next();
0851: count = rs.getInt(1);
0852: rs.close();
0853: s.close();
0854: } catch (SQLException e) {
0855: logException(
0856: "Exception while fetching response count for question "
0857: + questionId, e);
0858: throw new SurveyException("get-respcount-failure", e);
0859: } finally {
0860: try {
0861: if (conn != null && !conn.isClosed()) {
0862: conn.close();
0863: }
0864: } catch (SQLException ex) {
0865: logException(
0866: "Exception while fetching response count for question "
0867: + questionId, ex);
0868: throw new SurveyException("get-respcount-failure", ex);
0869: }
0870: }
0871:
0872: return count;
0873: }
0874:
0875: public int getAnswerResponseCount(int answerId)
0876: throws SurveyException {
0877: String sql;
0878: Connection conn = null;
0879: int count = 0;
0880: logger.fine("Fetching count of answers for answer " + answerId);
0881: try {
0882: conn = getConnection();
0883: sql = "select count(answer_id) from responses where answer_id = "
0884: + answerId;
0885: Statement s = conn.createStatement();
0886: ResultSet rs = s.executeQuery(sql);
0887: rs.next();
0888: count = rs.getInt(1);
0889: rs.close();
0890: s.close();
0891: } catch (SQLException e) {
0892: logException(
0893: "Exception while fetching response count for answer "
0894: + answerId, e);
0895: throw new SurveyException("get-respcount-failure", e);
0896: } finally {
0897: try {
0898: if (conn != null && !conn.isClosed()) {
0899: conn.close();
0900: }
0901: } catch (SQLException ex) {
0902: logException(
0903: "Exception while fetching response count for answer "
0904: + answerId, ex);
0905: throw new SurveyException("get-respcount-failure", ex);
0906: }
0907: }
0908:
0909: return count;
0910: }
0911:
0912: public String[] getCustomAnswers(int questionId)
0913: throws SurveyException {
0914: String sql;
0915: Connection conn = null;
0916: ArrayList l = new ArrayList();
0917: try {
0918: conn = getConnection();
0919: sql = "select open_answer from responses where question_id = "
0920: + questionId + " and open_answer is not null";
0921: Statement s = conn.createStatement();
0922: ResultSet rs = s.executeQuery(sql);
0923: while (rs.next()) {
0924: l.add(rs.getString("open_answer"));
0925: }
0926: rs.close();
0927: s.close();
0928: } catch (SQLException e) {
0929: logException("Exception while getting custom answers", e);
0930: throw new SurveyException("get-custanswer-failure", e);
0931: } finally {
0932: try {
0933: if (conn != null && !conn.isClosed()) {
0934: conn.close();
0935: }
0936: } catch (SQLException ex) {
0937: logException("Exception while getting custom answers",
0938: ex);
0939: throw new SurveyException("get-custanswer-failure", ex);
0940: }
0941: }
0942:
0943: String[] arr = (String[]) l.toArray(new String[1]);
0944: if (arr.length == 1 && arr[0] == null) {
0945: return null;
0946: } else {
0947: return arr;
0948: }
0949: }
0950:
0951: public float getQuestionResponsePct(int questionId)
0952: throws SurveyException {
0953: String sql;
0954: Connection conn = null;
0955: logger.fine("Getting question response pct for " + questionId);
0956: try {
0957: conn = getConnection();
0958: sql = "select survey_id from questions where question_id = "
0959: + questionId;
0960: Statement s = conn.createStatement();
0961: ResultSet rs = s.executeQuery(sql);
0962: rs.next();
0963: int surveyId = rs.getInt("survey_id");
0964: rs.close();
0965: s.close();
0966: float sCount = getSurveyResponseCount(surveyId);
0967: float qCount = getQuestionResponseCount(questionId);
0968: return (qCount == 0 ? 0 : ((qCount / sCount) * 100));
0969: } catch (SQLException e) {
0970: logException("Exception while getting response pct", e);
0971: throw new SurveyException("get-responsepct-failure", e);
0972: } finally {
0973: try {
0974: if (conn != null && !conn.isClosed()) {
0975: conn.close();
0976: }
0977: } catch (SQLException ex) {
0978: logException("Exception while getting response pct", ex);
0979: throw new SurveyException("get-responsepct-failure", ex);
0980: }
0981: }
0982: }
0983:
0984: public float getAnswerResponsePct(int answerId)
0985: throws SurveyException {
0986: // First find questionId for this answer
0987: // Then call getQuestionResponseCount() to find responses for this question
0988: // Then call getAnswerResponseCount() to find responses for this answer
0989: // calc & return pct
0990: String sql;
0991: Connection conn = null;
0992: try {
0993: conn = getConnection();
0994: sql = "select question_id from answers where answer_id = "
0995: + answerId;
0996: Statement s = conn.createStatement();
0997: ResultSet rs = s.executeQuery(sql);
0998: rs.next();
0999: int questionId = rs.getInt("question_id");
1000: rs.close();
1001: s.close();
1002: float qCount = getQuestionResponseCount(questionId);
1003: float aCount = getAnswerResponseCount(answerId);
1004: return (qCount == 0 ? 0 : ((aCount / qCount) * 100));
1005: } catch (SQLException e) {
1006: logException("Exception while getting response pct", e);
1007: throw new SurveyException("get-responsepct-failure", e);
1008: } finally {
1009: try {
1010: if (conn != null && !conn.isClosed()) {
1011: conn.close();
1012: }
1013: } catch (SQLException ex) {
1014: logException("Exception while getting response pct", ex);
1015: throw new SurveyException("get-responsepct-failure", ex);
1016: }
1017: }
1018:
1019: }
1020:
1021: public void addResponse(int surveyId, String userId,
1022: int questionId, int answerId) throws SurveyException {
1023: Connection conn = null;
1024: String sql;
1025: logger.fine("Adding response with questionid");
1026: try {
1027: if (responseAllowed(surveyId, questionId, userId)) {
1028: conn = getConnection();
1029: sql = "insert into responses(survey_id, user_id, question_id, answer_id) values(?,?,?,?)";
1030: PreparedStatement ps = conn.prepareStatement(sql);
1031: ps.setInt(1, surveyId);
1032: ps.setString(2, userId);
1033: ps.setInt(3, questionId);
1034: ps.setInt(4, answerId);
1035: ps.executeUpdate();
1036: ps.close();
1037: } else {
1038: logger.fine("User " + userId
1039: + " has already responded to this question");
1040: throw new SurveyException("duplicate-response", null);
1041: }
1042: } catch (SQLException e) {
1043: logException("Exception while adding response", e);
1044: throw new SurveyException("addresponse-failure", e);
1045: } finally {
1046: try {
1047: if (conn != null && !conn.isClosed()) {
1048: conn.close();
1049: }
1050: } catch (SQLException ex) {
1051: logException("Exception while adding response", ex);
1052: throw new SurveyException("addresponse-failure", ex);
1053: }
1054: }
1055: }
1056:
1057: public void addResponse(int surveyId, String userId,
1058: int questionId, String open_answer) throws SurveyException {
1059: Connection conn = null;
1060: String sql;
1061: logger.fine("Adding response with open answer");
1062: try {
1063: if (responseAllowed(surveyId, questionId, userId)) {
1064: conn = getConnection();
1065: sql = "insert into responses(survey_id, user_id, question_id, open_answer) values(?,?,?,?)";
1066: PreparedStatement ps = conn.prepareStatement(sql);
1067: ps.setInt(1, surveyId);
1068: ps.setString(2, userId);
1069: ps.setInt(3, questionId);
1070: ps.setString(4, open_answer);
1071: ps.executeUpdate();
1072: ps.close();
1073: } else {
1074: logger.fine("User " + userId
1075: + " has already responded to this question");
1076: throw new SurveyException("duplicate-response", null);
1077: }
1078: } catch (SQLException e) {
1079: logException("Exception while adding response", e);
1080: throw new SurveyException("addresponse-failure", e);
1081: } finally {
1082: try {
1083: if (conn != null && !conn.isClosed()) {
1084: conn.close();
1085: }
1086: } catch (SQLException ex) {
1087: logException("Exception while adding response", ex);
1088: throw new SurveyException("addresponse-failure", ex);
1089: }
1090: }
1091: }
1092:
1093: public boolean responseAllowed(int surveyId, int questionId,
1094: String userId) throws SurveyException {
1095: Connection conn = null;
1096: String sql;
1097: logger.fine("Checking if response is allowed");
1098: try {
1099: conn = getConnection();
1100: SurveyModel survey = getSurveyModel(surveyId);
1101: QuestionModel questions[] = survey.getQuestions();
1102: QuestionModel q = null;
1103: for (int i = 0; i < questions.length; i++) {
1104: if (questions[i].getQuestionId() == questionId) {
1105: q = questions[i];
1106: break;
1107: }
1108: }
1109:
1110: sql = "select count(*) from responses where user_id = '"
1111: + userId + "'" + " and survey_id = " + surveyId
1112: + " and question_id = " + questionId;
1113: Statement s = conn.createStatement();
1114: ResultSet rs = s.executeQuery(sql);
1115: rs.next();
1116: int count = rs.getInt(1);
1117: rs.close();
1118: s.close();
1119: if (count == 0) {
1120: return true;
1121: } else if (q.isMultiselect()) {
1122: return true;
1123: } else {
1124: return false;
1125: }
1126:
1127: } catch (SQLException e) {
1128: logException("Exception checking if response is allowed", e);
1129: throw new SurveyException("checkresponse-failure", e);
1130: } finally {
1131: try {
1132: if (conn != null && !conn.isClosed()) {
1133: conn.close();
1134: }
1135: } catch (SQLException ex) {
1136: logException(
1137: "Exception checking if response is allowed", ex);
1138: throw new SurveyException("checkresponse-failure", ex);
1139: }
1140: }
1141: }
1142:
1143: public SurveyModel getSurveyModel(int surveyId)
1144: throws SurveyException {
1145: Connection conn = null;
1146: String sql;
1147: logger.fine("Getting survey from survey id");
1148: try {
1149: conn = getConnection();
1150: sql = "select survey_id, survey_name, survey_description,"
1151: + "survey_owner, community_id, start_date, end_date, survey_type "
1152: + "from surveys where survey_id = " + surveyId;
1153: Statement s = conn.createStatement();
1154: ResultSet rs = s.executeQuery(sql);
1155: rs.next();
1156: SurveyModel survey;
1157: survey = new SurveyModel();
1158: survey.setSurveyId(rs.getInt("survey_id"));
1159: survey.setSurveyName(rs.getString("survey_name"));
1160: survey.setSurveyDescription(rs
1161: .getString("survey_description"));
1162: survey.setSurveyOwner(rs.getString("survey_owner"));
1163: survey.setCommunityId(rs.getString("community_id"));
1164: survey.setStartDate(rs.getDate("start_date"));
1165: survey.setEndDate(rs.getDate("end_date"));
1166: survey.setPoll("POLL".equalsIgnoreCase(rs
1167: .getString("survey_type")) ? true : false);
1168: rs.close();
1169: s.close();
1170: return survey;
1171:
1172: } catch (SQLException e) {
1173: logException("Exception getting survey for surveyID="
1174: + surveyId, e);
1175: throw new SurveyException("getsurvey-failure", e);
1176: } finally {
1177: try {
1178: if (conn != null && !conn.isClosed()) {
1179: conn.close();
1180: }
1181: } catch (SQLException ex) {
1182: logException("Exception getting survey for surveyID="
1183: + surveyId, ex);
1184: throw new SurveyException("getsurvey-failure", ex);
1185: }
1186: }
1187:
1188: }
1189:
1190: public boolean hasResponded(int surveyId, String userId)
1191: throws SurveyException {
1192: Connection conn = null;
1193: String sql;
1194: logger.fine("Checking if user " + userId + " has responded to "
1195: + surveyId);
1196: try {
1197: conn = getConnection();
1198: sql = "select count(*) from responses where survey_id = "
1199: + surveyId + " and user_id = '" + userId + "'";
1200: Statement s = conn.createStatement();
1201: ResultSet rs = s.executeQuery(sql);
1202: rs.next();
1203: int count = rs.getInt(1);
1204: rs.close();
1205: s.close();
1206: return count != 0;
1207:
1208: } catch (SQLException e) {
1209: logException("Exception Checking if user " + userId
1210: + " has responded to " + surveyId, e);
1211: throw new SurveyException("hasresponded-failure", e);
1212: } finally {
1213: try {
1214: if (conn != null && !conn.isClosed()) {
1215: conn.close();
1216: }
1217: } catch (SQLException ex) {
1218: logException("Exception Checking if user " + userId
1219: + " has responded to " + surveyId, ex);
1220: throw new SurveyException("hasresponded-failure", ex);
1221: }
1222: }
1223: }
1224:
1225: public static final int LIST_TYPE_POLLS = 1;
1226: public static final int LIST_TYPE_SURVEYS = 2;
1227: public static final int LIST_TYPE_BOTH = 3;
1228:
1229: public static final int LIST_STATUS_OPEN = 1;
1230: public static final int LIST_STATUS_CLOSED = 2;
1231: public static final int LIST_STATUS_ALL = 3;
1232:
1233: protected void logException(String msg, Exception e) {
1234: logger.fine(msg + ": " + e.getMessage());
1235: StackTraceElement[] stack = e.getStackTrace();
1236: for (int i = 0; i < stack.length; i++) {
1237: logger.finer(stack[i].toString());
1238: }
1239: }
1240:
1241: //***************************************
1242: // Testing code
1243: //**************************************
1244:
1245: public static void testCreateSurvey() throws SurveyException {
1246: SurveyModel survey = new SurveyModel();
1247: survey.setSurveyName("Shoe Size");
1248: survey.setSurveyDescription("What size are your shoes?");
1249: survey.setPoll(true);
1250: survey.setCommunityId("10");
1251: survey.setSurveyOwner("donkey");
1252: survey.setStartDate(new java.util.Date());
1253: survey.setEndDate(new java.util.Date());
1254:
1255: QuestionModel q[] = new QuestionModel[1];
1256: q[0] = new QuestionModel();
1257: q[0].setQuestionOrder(1);
1258: q[0].setQuestionText("What size are your shoes?");
1259: q[0].setMandatory(true);
1260: q[0].setMultiselect(false);
1261: q[0].setOpen(true);
1262:
1263: AnswerModel a[] = new AnswerModel[3];
1264: a[0] = new AnswerModel(1, "8");
1265: a[1] = new AnswerModel(2, "23");
1266: a[2] = new AnswerModel(3, "88");
1267: q[0].setAnswers(a);
1268:
1269: survey.setQuestions(q);
1270:
1271: SurveyDB db = new SurveyDB();
1272: db.create(survey);
1273: System.out.println("done creating survey");
1274:
1275: }
1276:
1277: public static void testAddResponse() throws SurveyException {
1278: SurveyDB db = new SurveyDB();
1279: db.addResponse(3, "22", 7, 20);
1280: }
1281:
1282: public static void testPrintSurvey(SurveyModel s)
1283: throws SurveyException {
1284: System.out.println(s.getSurveyName());
1285: System.out.println(s.getSurveyDescription());
1286: System.out.println(s.getSurveyOwner());
1287: System.out.println(s.getResponseCount() + " responses recd");
1288: QuestionModel[] q = s.getQuestions();
1289: for (int j = 0; j < q.length; j++) {
1290: System.out.println("\t" + q[j].getQuestionText() + "[ "
1291: + q[j].getResponseCount() + " responses "
1292: + q[j].getResponsePct() + "%]"
1293: + q[j].getCustomAnswerCount() + " custom answers");
1294: AnswerModel a[] = q[j].getAnswers();
1295: if (a != null) {
1296: for (int k = 0; k < a.length; k++) {
1297: System.out.println("\t\t"
1298: + (a[k] == null ? "NULL" : (a[k]
1299: .getAnswerText()
1300: + "["
1301: + a[k].getResponseCount()
1302: + " responses "
1303: + a[k].getResponsePct() + "%]")));
1304: }
1305: } else {
1306: System.out.println("\t\tNO ANSWERS FOUND");
1307: }
1308: }
1309: }
1310:
1311: public static void testGetSurvey(int surveyId)
1312: throws SurveyException {
1313: SurveyDB db = new SurveyDB();
1314: SurveyModel s = db.getSurveyModel(surveyId);
1315: testPrintSurvey(s);
1316:
1317: }
1318:
1319: public static void testList() throws SurveyException {
1320: SurveyDB d = new SurveyDB();
1321: ArrayList l = d.list("fishing", LIST_TYPE_BOTH,
1322: LIST_STATUS_ALL, 0);
1323: for (int i = 0; i < l.size(); i++) {
1324: SurveyModel s = (SurveyModel) l.get(i);
1325: testPrintSurvey(s);
1326: }
1327: }
1328:
1329: public static void testHasResponded(int surveyId, String userId)
1330: throws SurveyException {
1331: SurveyDB db = new SurveyDB();
1332: SurveyModel survey = db.getSurveyModel(surveyId);
1333: if (survey.hasResponded(userId)) {
1334: System.out.println("user " + userId + " has responded");
1335: } else {
1336: System.out.println("user " + userId + " has not responded");
1337: }
1338: }
1339:
1340: public static void testDelete(int surveyId) throws SurveyException {
1341: SurveyDB db = new SurveyDB();
1342: db.delete(surveyId);
1343: }
1344:
1345: public static void testSetupLogging() throws Exception {
1346: Handler fh = new FileHandler("./log.txt");
1347: fh.setFormatter(new SimpleFormatter());
1348: Logger.getLogger("").addHandler(fh);
1349: Logger.getLogger("").setLevel(Level.FINEST);
1350: }
1351:
1352: public static void main(String[] args) throws Exception {
1353:
1354: testSetupLogging();
1355: // testCreateSurvey();
1356: //testAddResponse();
1357: // testGetSurvey(3);
1358: testHasResponded(1, "9001");
1359: testHasResponded(1, "34534");
1360: testHasResponded(3, "22");
1361: testDelete(3);
1362: testList();
1363:
1364: }
1365: }
|