在Rank.xml中配置查询语句
<select id="selectSumPrice"
parameterType="com.iscas.manager.model.Rank"
resultType="com.iscas.manager.entity.RankBean">
SELECT
s.code as storeId,
s.name as storeName,
SUM( p.money*p.num ) as totalMoney
FROM
price p
LEFT JOIN store s on s.code = p.store_id
WHERE
1=1
AND TO_DAYS(NOW()) - TO_DAYS(p.`create_time`) = 1
<if test="keywords !=null and keywords !=''">
AND s.name like '%${keywords}%'
</if>
GROUP BY s.code
ORDER BY totalMoney DESC
</select>
建立实体RankBean.java接收查询结果
public class RankBean {
private String storeId;
private String storeName;
private String totalMoney;
public String getStroeId() {
return storeId;
}
public void setStroeId(String stroeId) {
this.storeId = stroeId;
}
public String getStoreName() {
return storeName;
}
public void setStoreName(String storeName) {
this.storeName = storeName;
}
public String getTotalMoney() {
return totalMoney;
}
public void setTotalMoney(String totalMoney) {
this.totalMoney = totalMoney;
}
}
将查询结果封装成json格式传到前台页面显示
@RequestMapping(method = {RequestMethod.GET},value = "selectSumPrice")
public void selectSumPrice(Rank e, HttpServletResponse response, HttpServletRequest request){
ResultDataDto rdd = null;
List<RankBean> edata = new ArrayList<>();
try {
List<RankBean> rankBeanList = rankService.selectSumPrice(e);
edata = rankBeanList;
PageInfo<RankBean> RankPageList = new PageInfo<>(rankBeanList);
if (rankBeanList.size() != 0) {
rdd = new ResultDataDto(ResultDataDto.CODE_SUCCESS, "查询成功", RankPageList);
} else {
rdd = new ResultDataDto(ResultDataDto.CODE_SUCCESS, "查询结果为空", null);
}
} catch (Exception ex){
ex.printStackTrace();
rdd = new ResultDataDto(ResultDataDto.CODE_ERROR_EXCEPTION, "查询异常", null);
}
ReturnJsonResult.result(response, request, rdd);
}
前台页面效果
{
code: "200",
message: "查询成功",
datas: {
pageNum: 1,
pageSize: 5,
size: 5,
startRow: 0,
endRow: 4,
total: 5,
pages: 1,
list: [
{
storeId: "18820",
storeName: "大娘当家(1店)",
totalMoney: "22850"
}
],
prePage: 0,
nextPage: 0,
isFirstPage: true,
isLastPage: true,
hasPreviousPage: false,
hasNextPage: false,
navigatePages: 8,
navigatepageNums: [
1
],
navigateFirstPage: 1,
navigateLastPage: 1
}
}
注意:Bean里面定义的属性名称必须与sql查询字段名称一致才能自动映射,否则前端将不能显示查询结果