/*
*
* 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 = ?";
}