飞讯管理员端的问卷方面功能

数据库方面设计

问卷主体表：

varchar questionnaireId 自增主键，问卷的唯一标识

varchar userid 发布问卷的用户

varchar name 问卷名称

varchar createTime 问卷创建时间

varchar startTime 问卷开始时间

varchar endTime 问卷结束时间

varchar state 问卷状态，分为暂存，发布，删除三种状态

int count 问卷被完成的次数

问题表

varchar questionId 自增主键，问题的唯一标识

varchar questionnaireId 问题所属问卷

varchar type 问题类型，分为单选（Radio）多选（Checkbox）主观（Input）

varchar flag 问题选择类型，分为必选和可选

varchar text 问题文本

选项表

varchar optionId 自增Id，选项唯一标识

varchar questionId 选项所属问题

varchar questionnaireId 选项所属问卷

varchar text 选项文本

回答表

varchar answerInfoId 自增主键，回答唯一标识

varchar questionId 回答所属问题

varchar questionnaireId 回答所属问卷

varchar createTime 回答时间

varchar content 回答内容

int count 第几个回答问卷的人

分别为问卷，问题，选项设置dao

QuestionnaireDao

package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import entity.Questionnaire;

public class QuestionnaireDao {
    public List<Questionnaire> getQuestionnaireList(){
        Connection con=null;
        try{
            con=DBConnection.getConnection();
            String sql="select * from questionaire\n" +
                    "";
            PreparedStatement pst=con.prepareStatement(sql);
            List<Questionnaire> questionnaires=new ArrayList<Questionnaire>();
            ResultSet rs=pst.executeQuery();

            while(rs.next())
            {
                Questionnaire questionnaire=new Questionnaire();

                questionnaire.setQuestionnaireId(rs.getString("questionnaireId"));

                questionnaire.setOperator_Account(rs.getString("userid"));
                questionnaire.setName(rs.getString("name"));
                questionnaire.setCreateTime(rs.getString("createTime"));
                questionnaire.setStartTime(rs.getString("startTime"));
                questionnaire.setEndTime(rs.getString("endTime"));
                questionnaire.setState(rs.getString("state"));

                questionnaires.add(questionnaire);

            }
            return questionnaires;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }finally{
            DBConnection.closeConnection(con);
        }

    }
    public Questionnaire getQuestionnaireById(String QuestionnaireId){
        Connection con=null;
        try{
            con=DBConnection.getConnection();
            String sql="select * from questionaire\n" +
                    "where  questionnaireId=\'"+QuestionnaireId+"\'";
            PreparedStatement pst=con.prepareStatement(sql);

            ResultSet rs=pst.executeQuery();
            Questionnaire questionnaire=new Questionnaire();
            while(rs.next())
            {
                QuestionDao questionDao=new QuestionDao();

                questionnaire.setQuestionnaireId(rs.getString("questionnaireId"));

                questionnaire.setOperator_Account(rs.getString("userid"));
                questionnaire.setName(rs.getString("name"));
                questionnaire.setCreateTime(rs.getString("createTime"));
                questionnaire.setStartTime(rs.getString("startTime"));
                questionnaire.setEndTime(rs.getString("endTime"));
                questionnaire.setState(rs.getString("state"));
                questionnaire.setQuestions(questionDao.QuestionByQuestionnaireId(QuestionnaireId));

            }
            return questionnaire;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }finally{
            DBConnection.closeConnection(con);
        }

    }
    public List<Map<String, Integer>> getAnswerNumberByQuestionnaireId(String QuestionnaireId){
        Connection con=null;
        con=DBConnection.getConnection();

        try {
            String sql="select *,count(*) nums from answerinfo where questionnaireId=\'"+QuestionnaireId+"\'\n" +
                    "and questionId not in (select question.questionId from question where question.type='Input' and question.questionnaireId=\'"+QuestionnaireId+"\')\n" +
                    "                group by questionId,content\n" +
                    "                order by questionId";
            PreparedStatement pst= null;
            pst = con.prepareStatement(sql);
            Questionnaire questionnaire=this.getQuestionnaireById(QuestionnaireId);
            List<Map<String,Integer>> maps=new ArrayList<Map<String,Integer>>();
            for(int i=1;i<=questionnaire.getQuestions().size();i++){
                maps.add(new HashMap<String,Integer>());
            }
            ResultSet rs=pst.executeQuery();

            while(rs.next()){

                for(int i=1;i<=questionnaire.getQuestions().size();i++){
                    Map<String,Integer> map = new HashMap<String,Integer>();
                    String s=rs.getString("questionId");
                    while(rs.getString("questionId").equals(s)){
                        maps.get(i-1).put(rs.getString("content"),rs.getInt("nums"));
                        if(!rs.isLast()) {
                            rs.next();
                        }else {break;}

                    }

                }
            }
            return  maps;
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return null;
    }
    public int[] getQuestionnaireNUms(){
        Connection c=null;
        try {
            c=DBConnection.getConnection();
            PreparedStatement ps = null;
            String sql = "select * ,count(*) num from questionaire\n" +
                    "    " +
                    "group by state\n" +
                    "" +
                    "" +
                    "\n";
            ps = c.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            int[] nums= new int[2];//0-published,1-saved,2-destroyed

            for(int i=0;rs.next();i++) {
            nums[i]=rs.getInt("num");
            }


            return nums;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(c);
        }
        return null;
    }


}

QuestionDao

package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.sql.Timestamp;

import entity.Question;
import entity.Option;
public class QuestionDao {
    public List<Question> QuestionByQuestionnaireId(String QuestionnaireId){
        Connection con=null;
        try{
            con=DBConnection.getConnection();
            String sql="select * from question\n" +
                    "where questionnaireId=\'"+QuestionnaireId+"\' ";
            PreparedStatement pst=con.prepareStatement(sql);
            List<Question> questions=new ArrayList<Question>();
            ResultSet rs=pst.executeQuery();

            while(rs.next())
            {
               Question question=new Question();
               OptionDao optionDao=new OptionDao();

               question.setQuestionId(rs.getInt("questionId"));
               question.setQuestionnaireId(rs.getString("questionnaireId"));
               question.setType(rs.getString("type"));
               question.setFlag(rs.getString("flag"));
               question.setText(rs.getString("text"));
               question.setOptions(optionDao.OptionByQuestionnaireIdAndQuestionId(QuestionnaireId,rs.getString("questionId")));
               questions.add(question);

            }
            return questions;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }finally{
            DBConnection.closeConnection(con);
        }

    }
    public int[] getQuestionNums(){
        Connection c=null;
        try {
            c=DBConnection.getConnection();
            PreparedStatement ps = null;
            String sql = "select * ,count(*) num from question\n" +
                    "    " +
                    "group by type";
            ps = c.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            int[] nums= {0,0,0};//0-Radio,1-Checkbox,2-Input

            for(int i=0;rs.next();i++) {
                nums[i]=rs.getInt("num");
            }


            return nums;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(c);
        }
        return null;
    }
    public int getAnswerNums(){
        Connection c=null;
        try {
            c=DBConnection.getConnection();
            PreparedStatement ps = null;
            String sql = "select * from questionaire";
            ps = c.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            int nums=0;

            while (rs.next()){

                nums+=rs.getInt("count");
            }


            return nums;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(c);
        }
        return 0;
    }
}

OptionDao

package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.Option;
public class OptionDao {
    public List<Option> OptionByQuestionnaireIdAndQuestionId(String QuestionnaireId,String QuestionId){

        Connection con=null;
        try{
            con=DBConnection.getConnection();
            String sql="select * from optionselect\n" +
                    "where questionnaireId=\'"+QuestionnaireId+"\' and questionId=\'"+QuestionId+"\'";
            PreparedStatement pst=con.prepareStatement(sql);
            List<Option> options=new ArrayList<Option>();
            ResultSet rs=pst.executeQuery();
           while(rs.next()) {
               Option option = new Option();

               option.setQuestionnaireId(rs.getString("questionnaireId"));
               option.setQuestionId(rs.getInt("questionId"));
               option.setOptionId(rs.getString("optionId"));
               option.setText(rs.getString("text"));
               options.add(option);
           }

            return options;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }finally{
            DBConnection.closeConnection(con);
        }
    }
}

servlet部分设计

首先需要向前端发送所有的问卷信息，将问卷封装成对象，再将每个对象放到一个LIstanbul中

List<Questionnaire> questionnaireList=dao.getQuestionnaireList();

前端使用表格承载

<fieldset class="table-search-fieldset" style="color: #009688">
                <legend>搜索信息</legend>
                <div style="margin: 10px 10px 10px 10px" id="btn">
                    <form class="layui-form layui-form-pane" action="">
                        <div class="layui-form-item">
                            <div class="layui-inline">
                                <label class="layui-form-label">搜索内容</label>
                                <div class="layui-input-inline">
                                    <!--注意此处input标签里的id-->
                                    <input class="layui-input" name="keyword" id="demoReload" autocomplete="off">
                                </div>
                            </div>

                            <div class="layui-inline">
                                <!--注意此处button标签里的type属性-->
                                <button type="button" onclick="find()" class="layui-btn layui-btn-primary"  lay-submit data-type="reload" lay-filter="data-search-btn" style="color:#428bca"><i class="layui-icon"></i> 搜 索</button>
                                <input type="radio" name="type" value="questionnaireId" title="问卷Id" checked>
                                <input type="radio" name="type" value="operator_Account" title="操作者" >
                                <input type="radio" name="type" value="name" title="问卷名" >

                            </div>
                        </div>
                    </form>
                </div>
            </fieldset>
            <table class="result-tab" width="100%" lay-filter="demo">
                <thead>
                <tr align="left" >
                    <!-- <th class="tc" width="5%"><input class="allChoose" name="" type="checkbox"></th>
                    <th>排序</th> -->

                    <th lay-data="{field:'questionnaireId', width:100}">问卷Id</th>

                    <th lay-data="{field:'operator_Account', width:100}">操作者ID</th>
                    <th lay-data="{field:'name', width:150}">问卷名称</th>
                    <th lay-data="{field:'createTime', width:200}">创建时间</th>
                    <th lay-data="{field:'startTime', width:200}">开始时间</th>
                    <th lay-data="{field:'endTime', width:200}">结束时间</th>
                    <th lay-data="{field:'state', width:100}">状态</th>
                    <th lay-data="{field:'check', width:100}">查看</th>
                </tr>
                </thead>

                <c:forEach items="${questionnairelist}" var="questionnaire">
                    <tr>


                        <td>${questionnaire.questionnaireId}</td>

                        <td>${questionnaire.operator_Account}</td>
                        <td>${questionnaire.name}</td>
                        <td>${questionnaire.createTime}</td>
                        <td>${questionnaire.startTime}</td>
                        <td>${questionnaire.endTime}</td>
                        <td>${questionnaire.state}</td>
                        <td id="${questionnaire.questionnaireId}"></td>
                        <script type="text/javascript">

                            var url="${questionnaire.questionnaireId}";
                            var res=encodeURIComponent(url);
                            console.log(res);
                            document.getElementById("${questionnaire.questionnaireId}").innerHTML="<a href=/QuestionnaireDetail?questionnaireId="+res+">查看</a>"

                        </script>
                    </tr>


                    </tr>
                </c:forEach>
            </table>
            <script>
                var table = layui.table;

                //转换静态表格
                table.init('demo', {
                    height: 450 //设置高度
                    ,limit: 10 //注意：请务必确保 limit 参数（默认：10）是与你服务端限定的数据条数一致
                    ,page:true
                });
            </script>

查看问卷详情的时候，需要对问卷的每一个问题实体化，封装成一个个问题传送到前端

questions=questionDao.QuestionByQuestionnaireId(QuestionnaireId);

将客观题存储到map中，并且将map变为前端可以识别的字符串

String datas[]=new String[maps.size()];
        for(int i=0;i<maps.size();i++){
            datas[i]="";
            for(String key: maps.get(i).keySet()){
                datas[i]+="[\""+key+"\","+maps.get(i).get(key)+"],";
            }
            System.out.println(datas[i]);
        }

前端实现

<div class="result-wrap">
            <nobr style="margin-left: 50px ;font-size: 20px">共有${selectNum+inputNum}道题，其中选择${selectNum}道，主观${inputNum}道</nobr>
            <nobr style="margin-left: 50px;font-size: 10px;color: #808080">${mandatoryNum}道必选题</nobr>
            <div class="layui-col-md8">
                <c:forEach items="${questions}" var="question" varStatus="id" >
                <div id="${id.count}" style="display:none; height: 200px">
                    <div class="layui-panel">
                        <div style="padding: 30px;">
                            <c:if test="${question.getFlag()=='Mandatory'}"><nobr style="color: #ff0000">*</nobr></c:if>
                            <nobr style="color: #009688;font-size: 20px">${question.getText()}</nobr>
                            <nobr style="color: #3F3F3F;">
                                <c:if test="${question.getType()=='Radio'}">(单选)</c:if>
                                <c:if test="${question.getType()=='Checkbox'}">(多选)</c:if>
                                <c:if test="${question.getType()=='Input'}">(主观)

                                </c:if>
                                <c:if test="${question.getType()=='Input'}">
                                    <button type="button" class="layui-btn layui-btn-sm layui-btn-normal" style="float: right" onclick="detail()">
                                    查看详情<i class="layui-icon layui-icon-right"></i>
                                </button>
                                    <script>
                                        function deatil(){
                                            window.location.href ="/QuestionDetail?questionId="+"${question.getQuestionId()}";
                                        }
                                    </script>

                                </c:if>


                            </nobr>
                            <br/>
                            <c:forEach items="${question.getOptions()}" var="options" varStatus="id2">
                                <i class="layui-icon ">&#xe63f;</i><nobr>${options.getText()}</nobr>
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            </c:forEach>
                        </div>

                   </div>
                </div>
                </c:forEach>


                </div>
            <div class="layui-col-md4"  style="height: 200px;width: 300px">
                <c:forEach items="${questionmaps}" var="data" varStatus="id" >
                    <div style="height: 200px; display: none;margin: auto" id="${id.count+10}"></div>
                    <c:if test="${data!=''}">
                        <script type="text/javascript">


                            var chart = Highcharts.chart('${id.count+10}', {
                                credits: {
                                    enabled:false,
                                },chart: {
                                    type: 'column'
                                },
                                title: {
                                    text: ''
                                },
                                subtitle: {
                                    text: ''
                                },
                                xAxis: {
                                    type: 'category',
                                    labels: {
                                        rotation: -45  // 设置轴标签旋转角度
                                    }
                                },
                                yAxis: {
                                    min: 0,
                                    title: {
                                        text: '选择人次'
                                    }
                                },
                                legend: {
                                    enabled: false
                                },
                                tooltip: {
                                    pointFormat: '选择人数: <b>{point.y:.0f} 次</b>'
                                },
                                series: [{
                                    name: '',
                                    data: [${data}
                                    ],

                                }]
                            });
                        </script>
                    </c:if>

                </c:forEach>
            </div>


                <script type="text/javascript">

                    var nums = ${inputNum+selectNum};
                    if(nums>3){
                        nums=3;
                    }
                    for(var i=0;i<nums;i++){
                        document.getElementById(nums-i).setAttribute("style", "display:block;height: 200px");
                        document.getElementById(nums+10-i).setAttribute("style", "display:block;height: 200px");
                    }

                </script>
            <div style="clear: both">
                <button type="button" class="layui-btn layui-btn-disabled" onclick="fpre()" id="buttonpre">上一页</button>
                <button type="button" class="layui-btn" onclick="fnext()" id="buttonnext">下一页</button>
                <script type="text/javascript">
                    if(1 == parseInt((total + nums-1) / nums)){document.getElementById("buttonnext").setAttribute("class", "layui-btn layui-btn-disabled");}

                </script>
            </div>



        </div>

采用每一页展示三个题目，可以进行翻页

翻页的js部分

fnext为下一页

fpre为上一页

function fnext() {
            console.log(parseInt((total + nums-1) / nums))
            if (page < parseInt((total + nums-1) / nums)) {
                page++;
                for (var num = (page - 2) * nums + 1; num <= (page - 1) * nums; num++) {


                    document.getElementById((num) + "").setAttribute("style", "display:none;");
                    document.getElementById((num+10) + "").setAttribute("style", "display:none;");

                }
                for (var num = (page - 1) * nums + 1; num <= page * nums; num++) {
                    if (num <= total) {

                        document.getElementById((num) + "").setAttribute("style", "display:block;height: 200px");
                        document.getElementById((num+10) + "").setAttribute("style", "display:block;height: 200px");
                    }

                }

                if (page >= parseInt(total + 1) / nums) {
                    document.getElementById("buttonnext").setAttribute("class", "layui-btn layui-btn-disabled");

                }
                document.getElementById("buttonpre").setAttribute("class", "layui-btn");
            }

        }

        function fpre() {
            if (page > 1) {
                page--;
                for (var num = (page) * nums + 1; num <= (page + 1) * nums; num++) {
                    if (num <= total) {

                        document.getElementById((num) + "").setAttribute("style", "display:none;");
                        document.getElementById((num+10) + "").setAttribute("style", "display:none;");
                    }

                }
                for (var num = (page - 1) * nums + 1; num <= page * nums; num++) {


                    document.getElementById((num) + "").setAttribute("style", "display:block;height: 200px");
                    document.getElementById((num+10) + "").setAttribute("style", "display:block;height: 200px");

                }

                if (page == 1) {
                    document.getElementById("buttonpre").setAttribute("class", "layui-btn layui-btn-disabled");

                }
                document.getElementById("buttonnext").setAttribute("class", "layui-btn");
            }

        }

原文链接：https://blog.csdn.net/qq_46025739/article/details/124925153

你可能也喜欢