业务问题是需要统计每个人的不同等级奖励的票数,用了count函数,实体类没有对应字段去直接封装,所以使用ResultMap来封装并展示在页面中,如下
直接上代码:
1、SQL语句中给count(*)取别名为countnum ,ToupiaoinfoDao.xml
<!--封装结果集-->
<resultMap type="java.util.Map" id="ToupiaoinfoMap1">
<result property="pstuname" column="pstuname" jdbcType="VARCHAR"/>
<result property="level" column="level" jdbcType="VARCHAR"/>
<result property="remark" column="remark" jdbcType="VARCHAR"/>
<result property="countnum" column="countnum" jdbcType="VARCHAR"/>
</resultMap>
<!--统计数量-->
<select id="queryAllCount" resultMap="ToupiaoinfoMap1">
SELECT pstuname,remark,level,count(*) as countnum FROM toupiaoinfo
<where>
<if test="pstuname != null and pstuname != ''">
and pstuname = #{pstuname}
</if>
</where>
GROUP BY level,pstuname ORDER BY pstuname;
</select>
2.控制层
/**
* 分页查询
*
* @param page 默认第一页
* @param limit 默认每页显示10条
* @return
*/
@RequestMapping("manage/queryToupiaoinfoListCount")
@ResponseBody
public ResultUtil queryToupiaoinfoListCount(Integer page, Integer limit, String zhuanye,String name,HttpSession session) {
if (null == page) { //默认第一页
page = 1;
}
if (null == limit) { //默认每页10条
limit = 10;
}
Toupiaoinfo toupiaoinfo = new Toupiaoinfo();
Userinfo loginAdmin = (Userinfo)session.getAttribute("loginAdmin");
toupiaoinfo.setRemark(zhuanye);
toupiaoinfo.setPstuname(name);
PageHelper.startPage(page, limit, true);
List<Toupiaoinfo> list = toupiaoinfoDao.queryAllCount(toupiaoinfo);
PageInfo<Toupiaoinfo> pageInfo = new PageInfo<Toupiaoinfo>(list); //使用mybatis分页插件
ResultUtil resultUtil = new ResultUtil();
resultUtil.setCode(0); //设置返回状态0为成功
resultUtil.setCount(pageInfo.getTotal()); //获取总记录数目 类似count(*)
resultUtil.setData(pageInfo.getList()); //获取当前查询出来的集合
return resultUtil;
}
3.实体类,添加一个countnum 属性并添加get,set方法
package com.example.pojo;
import java.io.Serializable;
/**
* (Toupiaoinfo)实体类
*
* @author makejava
* @since 2022-02-19 15:55:54
*/
public class Toupiaoinfo implements Serializable {
private static final long serialVersionUID = -51092151675843990L;
/**
* ID
*/
private Integer id;
/**
* 贫困生姓名
*/
private String pstuname;
/**
* 贫困生id
*/
private Integer pid;
/**
* 投票学生姓名
*/
private String tstuname;
/**
* 投票学生id
*/
private Integer tid;
/**
* 等级
*/
private String level;
/**
* 数量
*/
private String countnum;
/**
* 备注
*/
private String remark;
public static long getSerialVersionUID() {
return serialVersionUID;
}
public String getCountnum() {
return countnum;
}
public void setCountnum(String countnum) {
this.countnum = countnum;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getPstuname() {
return pstuname;
}
public void setPstuname(String pstuname) {
this.pstuname = pstuname;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getTstuname() {
return tstuname;
}
public void setTstuname(String tstuname) {
this.tstuname = tstuname;
}
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getLevel() {
return level;
}
public void setLevel(String level) {
this.level = level;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
4.jsp页面就正常解析:
table.render({
id: 'userinfoList',
elem: '#userinfoList'
, url: ctx + '/manage/queryToupiaoinfoListCount' //数据接口
, cellMinWidth: 80
, limit: 10//每页默认数
, limits: [10, 20, 30, 40]
, cols: [[ //表头
{type: 'checkbox'},
{field:'zizeng', width:80, title: '序号',templet:'#zizeng'},
{field: 'remark', title: '贫困生专业', width: 250, align: 'center'},
{field: 'pstuname', title: '贫困生姓名', width: 250, align: 'center', sort: true},
{field: 'level', title: '等级', width: 140, align: 'center'},
{field: 'countnum', title: '投票数量', width: 250, align: 'center'},
/* {title: '操作', toolbar: '#barEdit'}*/
]]
, page: true //开启分页
});
总结:xml中心构建Resultmap,实体类中加入新的属性,其他不变。