/* * * Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved. * * This file is part of Entando software. * Entando is a free software; * You can redistribute it and/or modify it * under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation; version 2. * * See the file License for the specific language governing permissions * and limitations under the License * * * * Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved. * */ package com.agiletec.plugins.jpsurvey.aps.system.services; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.HashSet; import java.util.List; import java.util.Set; import com.agiletec.aps.system.ApsSystemUtils; import com.agiletec.aps.system.common.AbstractSearcherDAO; import com.agiletec.aps.system.common.FieldSearchFilter; import com.agiletec.aps.util.ApsProperties; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Choice; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Question; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Survey; public class AbstractSurveyDAO extends AbstractSearcherDAO { @Override protected String getTableFieldName(String metadataFieldKey) { return metadataFieldKey; } @Override protected String getMasterTableName() { return "jpsurvey_voters"; } @Override protected String getMasterTableIdFieldName() { return "id"; } @Override protected boolean isForceCaseInsensitiveLikeSearch() { return true; } /** * This inspect the given table and return the id to be used as primary key for further operations * @param query the query used to inspect the datasource * @param conn the connection to the datasource * @return The first free id to use as primary key */ protected int getUniqueId(String query, Connection conn) { int id = 0; Statement stat = null; ResultSet res = null; try { stat = conn.createStatement(); res = stat.executeQuery(query); res.next(); id = res.getInt(1) + 1; } catch (Throwable t) { processDaoException(t, "Error while getting last used ID - '" + query + "'", "getUniqueId"); } finally { closeDaoResources(res, stat); } return id; } /** * Create a 'question' object from the result set. Note that this method expects a row containing all * the columns describing the question. Invoked from several DAOs. * @param res the result set containing the record of the question * @param shift The number of the first column containing the data of the questions within the result set. * @return the 'question' object requested */ protected Question buildQuestionRecordFromResultSet(ResultSet res, int shift) { Question question = null; if (null == res) return null; if (shift > 0) --shift; try { int id = res.getInt(shift + 1); if (id > 0) { question = new Question(); question.setChoices(new ArrayList<Choice>()); question.setId(id); question.setSurveyId(res.getInt(shift + 2)); ApsProperties prop = new ApsProperties(); prop.loadFromXml(res.getString(shift + 3)); question.setQuestions(prop); question.setPos(res.getInt(shift + 4)); question.setSingleChoice(res.getBoolean(shift + 5)); question.setMinResponseNumber(res.getInt(shift + 6)); question.setMaxResponseNumber(res.getInt(shift + 7)); } } catch (Throwable t) { ApsSystemUtils.logThrowable(t, this, "buildQuestionRecordFromResultSet", "Error while building a 'question' object from the result set"); } return question; } /** * Create a 'choice' record object from the result set. Note that this method expects a row containing all * the columns describing the choice. * @param res The result set as returned from the database * @param shift The shift to the 'choice' object data in the result set * @return the 'choice' record requested */ protected Choice buildChoiceRecordFromResultSet(ResultSet res, int shift) { Choice choice = null; if (null == res) return null; if (shift > 0) --shift; try { int id = res.getInt(shift + 1); if (id > 0) { choice = new Choice(); choice.setId(id); choice.setQuestionId(res.getInt(shift + 2)); ApsProperties prop = new ApsProperties(); prop.loadFromXml(res.getString(shift + 3)); choice.setChoices(prop); choice.setPos(res.getInt(shift + 4)); choice.setFreeText(res.getBoolean(shift + 5)); } } catch (Throwable t) { ApsSystemUtils.logThrowable(t, this, "buildChoiceRecordFromResultSet", "Error while building a 'choice' object from the result set"); } return choice; } /** * This saves a choice in the database tables. Since this method is invoked in several DAOs it's been * inserted in the common action. * @param conn An opened connection to the database * @param choice The choice object to store * @param sql the SQL statement used to record the choice */ protected void saveChoice(Connection conn, Choice choice) { PreparedStatement stat = null; try { int choiceId = this.getUniqueId("SELECT MAX(id) FROM jpsurvey_choices ", conn); stat = conn.prepareStatement(SAVE_CHOICE); choice.setId(choiceId); stat.setInt(1, choice.getId()); stat.setInt(2, choice.getQuestionId()); stat.setString(3, choice.getChoices().toXml()); stat.setInt(4, choice.getPos()); if (choice.isFreeText()) { stat.setInt(5, 1); } else { stat.setInt(5, 0); } stat.executeUpdate(); } catch (Throwable t) { processDaoException(t, "Error while saving 'choice' ", "saveChoice"); } finally { closeDaoResources(null, stat); } } protected void deleteChoice(Connection conn, int id) { PreparedStatement stat = null; try { stat = conn.prepareStatement(DELETE_CHOICE_BY_ID); stat.setInt(1, id); stat.execute(); } catch (Throwable t) { processDaoException(t, "Error while deleting the 'choice'", "deleteChoice"); } finally { closeDaoResources(null, stat); } } /** * This saves a question in the database tables. Since this method is invoked in several DAOs it's been * inserted in the common action. * @param question The question object to save * @param conn An opened connection to the database * @param sql the SQL statement used to record the question */ protected void saveQuestion(Connection conn, Question question) { PreparedStatement stat = null; try { int questionId = this.getUniqueId("SELECT MAX(id) FROM jpsurvey_questions ", conn); // SAVE QUESTION ITSELF stat = conn.prepareStatement(SAVE_QUESTION); question.setId(questionId); stat.setInt(1, question.getId()); stat.setInt(2, question.getSurveyId()); stat.setString(3, question.getQuestions().toXml()); stat.setInt(4, question.getPos()); if (question.isSingleChoice()) { stat.setInt(5, 1); } else { stat.setInt(5, 0); } stat.setInt(6, question.getMinResponseNumber()); stat.setInt(7, question.getMaxResponseNumber()); stat.executeUpdate(); // SAVE CHOICES if (null != question.getChoices() && question.getChoices().size() > 0) { for (Choice currentchoice: question.getChoices()) { currentchoice.setExtraInfo(question.getSurveyId(), null, null, question.getQuestions()); currentchoice.setQuestionId(question.getId()); this.saveChoice(conn, currentchoice); } } } catch (Throwable t) { processDaoException(t, "Error while saving the question", "saveQuestion"); } finally { closeDaoResources(null, stat); } } /** * Delete a question and the related choices, if any * @param conn the connection to the database * @param id The id of the question to delete */ protected void deleteQuestion(Connection conn, int id) { PreparedStatement stat = null; try { stat = conn.prepareStatement(DELETE_CHOICE_BY_QUESTIONID); stat.setInt(1, id); stat.execute(); // FIXME delete response here or let the action drive the deletion process? stat = conn.prepareStatement(DELETE_QUESTION_BY_ID); stat.setInt(1, id); stat.execute(); } catch (Throwable t) { processDaoException(t, "Error while deleting the question", "deleteQuestion"); } finally { closeDaoResources(null, stat); } } /** * Updates the given choice * @param conn The connection to the database * @param choice The object to save */ protected void updateChoice(Connection conn, Choice choice) { PreparedStatement stat = null; try { stat = conn.prepareStatement(UPDATE_CHOICE); stat.setInt(1, choice.getQuestionId()); stat.setString(2, choice.getChoices().toXml()); stat.setInt(3, choice.getPos()); if (choice.isFreeText()) { stat.setInt(4, 1); } else { stat.setInt(4, 0); } stat.setInt(5, choice.getId()); stat.executeUpdate(); } catch (Throwable t) { processDaoException(t, "Error while updating a 'choice' record", "updateChoice"); } finally { closeDaoResources(null, stat); } } /** * This updates the question and saves the related choice as NEW elements in the database * @param conn The connection to the database * @param question The question object to save */ protected void updateQuestion(Connection conn, Question question) { PreparedStatement stat=null; try { stat = conn.prepareStatement(UPDATE_QUESTION); stat.setInt(1, question.getSurveyId()); stat.setString(2, question.getQuestions().toXml()); stat.setInt(3, question.getPos()); if (question.isSingleChoice()) { stat.setInt(4, 1); } else { stat.setInt(4, 0); } stat.setInt(5, question.getMinResponseNumber()); stat.setInt(6, question.getMaxResponseNumber()); stat.setInt(7, question.getId()); stat.executeUpdate(); // delete the choices that don't exist anymore Set<Integer> kept = this.deleteChoicesInExcess(conn, question); // save the new choices or update the new ones for (Choice currentChoice: question.getChoices()) { if (kept.contains(currentChoice.getId())) { this.updateChoice(conn, currentChoice); } else { currentChoice.setQuestionId(question.getId()); // for sake of safety this.saveChoice(conn, currentChoice); } } } catch (Throwable t) { processDaoException(t, "Error while updating a question in the database", "updateQuestion"); } finally { closeDaoResources(null, stat); } } /** * Delete all the choices belonging to the given question * @param conn An opened connection to the database * @param id The ID of the questions whose choices -if any- are to be deleted */ protected void deleteChoiceByQuestionId(Connection conn, int id) { PreparedStatement stat = null; try { stat = conn.prepareStatement(DELETE_CHOICE_BY_QUESTIONID); stat.setInt(1, id); stat.execute(); } catch (Throwable t) { processDaoException(t, "Error while updating a question in the database", "deleteChoiceByQuestionId"); } finally { closeDaoResources(null, stat); } } /** * Delete questions of the given survey * @param conn An opened connection to the database * @param id The id of the survey whose questions are to be deleted */ protected void deleteQuestionBySurveyId(Connection conn, int id) { PreparedStatement stat = null; ResultSet res = null; try { stat = conn.prepareStatement(GET_QUESTION_BY_SURVEYID); stat.setInt(1, id); res = stat.executeQuery(); while (res.next()) { int questionId = res.getInt(1); this.deleteQuestion(conn, questionId); } } catch (Throwable t) { processDaoException(t, "Error while updating a question in the database", "deleteQuestionsBySurveyId"); } finally { closeDaoResources(res, stat); } } /** * Determine whether the survey is a questionnaire or not. Do not use to check the existence of * a survey! * @param conn The connection to the database * @param surveyId The id of the survey * @return true if the ID belongs to a questionnaire, false otherwise. */ protected boolean isSurveyQuestionnaire(Connection conn, int surveyId) { PreparedStatement stat = null; ResultSet res = null; boolean isQquestionnaire = false; try { stat = conn.prepareStatement(DETERMINE_SURVEY_TYPE); stat.setInt(1, surveyId); res = stat.executeQuery(); while (res.next()) { int type = res.getInt(1); isQquestionnaire = (type == 1 ? true:false); } } catch (Throwable t) { processDaoException(t, "Errore nell'ottenere il tipo di survey", "loadSurveyType"); } finally { closeDaoResources(res, stat); } return isQquestionnaire; } /** * This will delete from the database all the questions whose ID is not within the questions * of the given survey. This is invoked ONLY by the update routines and MUST not be used for * purposes other than updating! * @param survey The survey to analyze for questions in excess * @return The set of the ID of the questions which are kept, an empty list otherwise */ protected Set<Integer> deleteQuestionsInExcess(Connection conn, Survey survey) { ResultSet res = null; PreparedStatement stat = null; Set<Integer> list = new HashSet<Integer>(); try { stat = conn.prepareStatement(GET_QUESTION_BY_SURVEYID); stat.setInt(1, survey.getId()); res = stat.executeQuery(); while (res.next()) { int id = res.getInt(1); if (null == survey.getQuestion(id)) { this.deleteQuestion(conn, id); } else { list.add(id); } } } catch (Throwable t) { this.processDaoException(t, "error while deleting question in excess from a survey", "deleteQuestionInExcess"); } finally { closeDaoResources(res, stat); } return list; } /** * This will delete from the database all the choices whose ID is not within the choices * of the given question. This is invoked ONLY by the update routines and MUST not be used for * purposes other than updating! * @param question The question which is going to be updated * @return The set of the ID of the choices kept, an empty list otherwise */ private Set<Integer> deleteChoicesInExcess(Connection conn, Question question) { ResultSet res = null; PreparedStatement stat = null; Set<Integer> list = new HashSet<Integer>(); try { stat = conn.prepareStatement(GET_CHOICES_BY_QUESTIONID); stat.setInt(1, question.getId()); res = stat.executeQuery(); while (res.next()) { int id = res.getInt(1); if (null == question.getChoice(id)) { this.deleteChoice(conn, id); } else { list.add(id); } } } catch (Throwable t) { this.processDaoException(t, "error while deleting question in excess from a survey", "deleteQuestionInExcess"); } finally { closeDaoResources(res, stat); } return list; } /** * Upon a save or update operation we have to update the extra info for safety reasons. * That means that this method is invoked only by the proper DAO, so don't use it (unless, of course, * you know what are you doing!) */ protected void refreshExtraInfo(Question question) { if (null != question && question.getChoices() != null && question.getChoices().size() > 0) { for (Choice choice: question.getChoices()) { choice.setExtraInfo(question.getSurveyId(), null, null, question.getQuestions()); } } } /** * Upon a save or update operation we have to update the extra info for safety reasons. * That means that this method is invoked only by the proper DAO, so don't use it (unless, of course, * you know what are you doing!) */ protected void refreshExtraInfo(Survey survey) { if (null != survey && survey.getQuestions() != null && survey.getQuestions().size() > 0) { for (Question question: survey.getQuestions()) { question.setExtraInfo(survey.isQuestionnaire(), survey.getTitles()); if (null != question && question.getChoices() != null && question.getChoices().size() > 0) { for (Choice choice: question.getChoices()) { choice.setExtraInfo(question.getSurveyId(), survey.isQuestionnaire(), survey.getTitles(), question.getQuestions()); } } } } } private static final String SAVE_CHOICE = "INSERT INTO jpsurvey_choices (id,questionid,choice,pos,freetext) VALUES (?,?,?,?,?)"; private static final String SAVE_QUESTION = "INSERT INTO jpsurvey_questions (id,surveyid,question,pos,singlechoice,minresponsenumber,maxresponsenumber) VALUES (?,?,?,?,?,?,?)"; private static final String DELETE_CHOICE_BY_ID = "DELETE FROM jpsurvey_choices WHERE id = ?"; private static final String DELETE_QUESTION_BY_ID = "DELETE FROM jpsurvey_questions WHERE id = ? "; private static final String DELETE_CHOICE_BY_QUESTIONID = "DELETE FROM jpsurvey_choices WHERE questionid = ?"; private static final String UPDATE_CHOICE = "UPDATE jpsurvey_choices SET questionid = ?, choice = ?, pos = ?, freetext = ? WHERE id = ?"; private static final String UPDATE_QUESTION = "UPDATE jpsurvey_questions SET surveyid = ?, question = ?, pos = ?, singlechoice = ?, minresponsenumber = ?, maxresponsenumber = ? WHERE id = ? "; private static final String GET_QUESTION_BY_SURVEYID = "SELECT id FROM jpsurvey_questions WHERE surveyid = ? "; private static final String GET_CHOICES_BY_QUESTIONID = "SELECT id FROM jpsurvey_choices WHERE questionid = ? "; private static final String DETERMINE_SURVEY_TYPE = "SELECT questionnaire FROM jpsurvey WHERE id = ?"; }