完整的可按年份和月份查询数据并显示

效果:

页面的数据准备:

Map<String, String> map = findresult(type, date);List<StatisticsInterrogate> list = statisticsService.StatisticsInterrogatelist(map);request.setAttribute("StatisticsResult", list);

这里的JavaBean:

public class StatisticsInterrogate implements Serializable{private static final long serialVersionUID = 8738708908641883840L;/** 用户ID */private Long id;/** 用户姓名*/private String name;/** 提问数*/private int sumQuestion;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getSumQuestion() {return sumQuestion;}public void setSumQuestion(int sumQuestion) {this.sumQuestion = sumQuestion;}}

sql对应的.xml

<select id="StatisticsInterrogatelist" resultMap="StatisticsInterrogate"parameterType="map"><![CDATA[select u,t.name,count from(select userid u,count(userid) count from T_QUESTION t where]]><if test="type =='month'"><![CDATA[to_char(createtime,'YYYYMM') = #{date} ]]></if><if test="type =='year'"><![CDATA[to_char(createtime,'YYYY') = #{date} ]]></if><![CDATA[group by userid  ) left join(select id,name from T_USER) t on u = t.id where rownum<=20 order by count desc]]></select>

只是个参考,还是javabean有用,findresult:

public Map<String, String> findresult(String type, String date) {//返回的date是YYYY或YYYYMMif (type == null) {type = "month";Calendar c = Calendar.getInstance();int y = c.get(Calendar.YEAR);int m = c.get(Calendar.MONTH) + 1;if (m == 1) {m = 12;y -= 1;}if (m < 10) {date = y + "0" + m;} else {date = y + "" + m;}}request.setAttribute("yearfind", date.subSequence(0, 4));if (type.equals("month")) {CharSequence c = date.subSequence(4, 5);if (c.equals("0")) {request.setAttribute("monthfind", date.subSequence(5, 6) + "月");request.setAttribute("monthfind2", date.subSequence(5, 6));} else {request.setAttribute("monthfind", date.subSequence(4, 6) + "月");request.setAttribute("monthfind2", date.subSequence(4, 6));}}Map<String, String> map = new HashMap<String, String>();map.put("date", date);map.put("type", type);return map;}

然后jsp,用到了Bootstrap和jquery,

<style type="text/css">.center {width: auto;display: table;margin-left: auto;margin-right: auto;}.width40 {width: 40%;}</style>

<table class="table"><tr><td class="width40"><table class="table"><tr><td class="text-center"><h3>查询条件</h3></td></tr><tr><td><div class="radio"><label> <input type="radio" name="datetype" id="dtmonth"value="month" onclick="choosemonth()" checked="checked">按月查询</label></div><div class="radio"><label> <input type="radio" name="datetype" id="dtyear"value="year" onclick="chooseyear()"> 按年查询</label></div></td></tr></table><table class="table"><tr><td><div><select class="form-control" id="year"><c:forEach begin="2014" end="${currentYear }"                 <span style="font-family: Arial, Helvetica, sans-serif;">${currentYear }传来的今年年份</span>varStatus="status" var="year"><option value="${currentYear - (year - 2014)}">${currentYear - (year - 2014)}年</option></c:forEach></select></div></td></tr><tr><td><select class="form-control " id="month"><option value="1">1月</option><option value="2">2月</option><option value="3">3月</option><option value="4">4月</option><option value="5">5月</option><option value="6">6月</option><option value="7">7月</option><option value="8">8月</option><option value="9">9月</option><option value="10">10月</option><option value="11">11月</option><option value="12">12月</option></select></td></tr><tr><td><button onclick="find()" class="btn btn-info">查询</button></td></tr></table></td><td><table class="table table-striped table-bordered"><tr><td colspan="3" class="text-center"><h4><a style="color: blue">${yearfind }年${monthfind }</a>的提出问题排名</h4></td></tr><tr><td class="text-center">排名</td><td class="text-center">姓名</td><td class="text-center">提问数</td></tr><c:forEach items="${StatisticsResult }" var="interrogate"varStatus="stauts"><tr><td class="text-center">${stauts.index+1 }</td><td class="text-center">${interrogate.name}</td><td class="text-center">${interrogate.sumQuestion }</td></tr></c:forEach></table></td></tr></table>

js:

$(function() {//第一次载入是上个月,之后是自己选的$('#year').val('${yearfind }');var m = '${monthfind2 }';if (m != '') {$('#month').val(m);} else {chooseyear();var selects = document.getElementsByName("datetype");selects[1].checked = true;}});function chooseyear() {$('#month').hide();}function choosemonth() {$('#month').show();}function find() {var year = $('#year').val();var month = $('#month').val();if (month.length == 1) {month = "0" + month;}var type = $('input:radio[name=datetype]:checked').val();if (type == "year") {date = year;} else {date = year + month;}window.location.href = '${cp}/statistics/interrogate?date=' + date + '&type=' + type;//下面代码为了说明这个}

说明:最先出现的代码的完整版:

@RequestMapping("interrogate")public String interrogateindex(String type, String date) {Map<String, String> map = findresult(type, date);List<StatisticsInterrogate> list = statisticsService.StatisticsInterrogatelist(map);request.setAttribute("StatisticsResult", list);return "statistics/interrogate";}