com.surveypanel.dao.JDBCFormDAO.java Source code

Introduction

Here is the source code for com.surveypanel.dao.JDBCFormDAO.java

Source

/*
* SurveyPanel
* Copyright (C) 2009 Serge Tan Panza
* All rights reserved.
* License: GNU/GPL License v3 , see LICENSE.txt
* SurveyPanel is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
* 
*/
package com.surveypanel.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import com.surveypanel.form.FormDTO;
import com.surveypanel.form.Questionnaire;

/**
 * @author stanpanza
 *
 */
public class JDBCFormDAO extends JdbcTemplate implements FormDAO {

    public void add(FormDTO form) {
        StringBuilder sql = new StringBuilder("INSERT INTO survey_");
        List<Object> objects = new ArrayList<Object>();
        sql.append(form.getSurveyId());
        sql.append(" (id,surveyId,created,updated,js_data,xml_data) VALUES (?,?,?,?,?,?)");
        objects.add(form.getId());
        objects.add(form.getSurveyId());
        objects.add(form.getCreated());
        objects.add(form.getUpdated());
        objects.add(form.getVariables());
        objects.add(form.getXmlContent());
        Object[] args = objects.toArray();
        update(sql.toString(), args);
    }

    public void delete(String formId, long surveyId) {
        StringBuilder sql = new StringBuilder("DELETE FROM survey_");
        sql.append(surveyId);
        sql.append(" WHERE id = ? and surveyid = ?");
        update(sql.toString(), new Object[] { formId, surveyId });
    }

    public void update(FormDTO form) {
        Date now = new Date();
        StringBuilder sql = new StringBuilder("UPDATE survey_");
        List<Object> objects = new ArrayList<Object>();
        sql.append(form.getSurveyId());
        sql.append(" SET ");
        sql.append("updated = ?, js_data = ?, xml_data = ?");

        objects.add(now);
        objects.add(form.getVariables());
        objects.add(form.getXmlContent());

        if (form.isFinish() && form.getEnded() != null) {
            objects.add(now);
            sql.append(" ,finish = true, ended = ? ");
        }

        sql.append(" ,qualified = ?  ");
        objects.add(form.isQualified());

        objects.add(form.getId());
        sql.append(" WHERE id = ?;");

        Object[] args = objects.toArray();
        update(sql.toString(), args);
    }

    public FormDTO load(String formId, long surveyId) {
        StringBuilder sql = new StringBuilder("SELECT frm.*  ");
        sql.append("FROM  survey_");
        sql.append(surveyId);
        sql.append(" frm ");
        sql.append(" WHERE frm.id = ? AND surveyId = ?  ");
        List<Object> args = new ArrayList<Object>();
        args.add(formId);
        args.add(surveyId);
        FormDTO formDTO = (FormDTO) queryForObject(sql.toString(), args.toArray(), new FormRowHandler());
        return formDTO;
    }

    @Override
    public void init(Questionnaire questionnaire) {
        try {
            queryForInt("SELECT count(id) FROM survey_" + questionnaire.getSurveyId());
        } catch (Exception e) {
            StringBuilder sql = new StringBuilder("CREATE TABLE survey_");
            sql.append(questionnaire.getSurveyId());
            sql.append(" (  id VARCHAR(36), surveyId INT, created DATETIME , updated DATETIME ,");
            sql.append(
                    " ended DATETIME, finish BIT DEFAULT 0, qualified BIT DEFAULT 0, paused BIT DEFAULT 0, xml_data LONGTEXT, js_data LONGBLOB );");
            update(sql.toString());
        }
        try {
            queryForInt("SELECT count(name) FROM survey_values_" + questionnaire.getSurveyId());
        } catch (Exception e) {
            StringBuilder sql = new StringBuilder("CREATE TABLE survey_values_");
            sql.append(questionnaire.getSurveyId());
            sql.append(
                    " (  question VARCHAR(20),name VARCHAR(20), value VARCHAR(500),formId VARCHAR(36) , surveyId INT, created DATETIME )");
            update(sql.toString());
        }
    }

    @Override
    public boolean qualify(String formId, long surveyId, Map<String, Object> defaultValues) {
        FormDTO load = load(formId, surveyId);
        Map<String, Object> values = defaultValues;
        values.putAll(load.getValues());
        String sql = "INSERT INTO survey_values_" + surveyId
                + "  (question,name,value,formId,surveyId,created) VALUES (?,?,?,?,?,?);";
        final Object[][] args = new Object[values.size()][5];
        int count = 0;
        for (Entry<String, Object> value : values.entrySet()) {
            String fieldName = value.getKey();
            String[] key = fieldName.split("_");
            args[count] = new Object[] { key[0], fieldName, (String) value.getValue(), formId, surveyId };
            count++;
        }

        final int counter = values.size();
        batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, (String) args[i][0]);
                ps.setString(2, (String) args[i][1]);
                ps.setString(3, (String) args[i][2]);
                ps.setString(4, (String) args[i][3]);
                ps.setLong(5, (Long) args[i][4]);
                ps.setTimestamp(6, new Timestamp(Calendar.getInstance().getTimeInMillis()));
            }

            public int getBatchSize() {
                return counter;
            }
        });

        update("UPDATE survey_" + surveyId + " SET qualified = true WHERE id = ?", new Object[] { formId });
        return false;
    }

}