开源一夏 | 基于若依 springboot 架构实现数据多维统计
作者：六月的雨在infoQ
- 2022 年 8 月 04 日
本文字数：13130 字
阅读完需：约 43 分钟
需求背景
项目中需要一个统计功能，同时可以根据勾选不同维度的统计字段来实现列表动态增减，实现效果如图：https://pan.baidu.com/s/1D9cBOWJ2q6rqL7BpU_wcMQ?pwd=wx6q，由于语雀编辑器暂时不支持视频上传，这里是百度网盘的免提取码链接，可直接观看
代码实现
首先需要处理的是页面动态增减列
页面代码
accountFlowInfoRecordData.html
<!DOCTYPE html><html lang="zh" xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"><head> <th:block th:include="include :: header('流转记录统计列表')" /></head><body class="gray-bg"> <div class="container-div"> <div class="row"> <div class="col-sm-12 search-collapse"> <form id="formId"> <div class="select-list"> <ul> <li> 流转类型： <select name="channelType" th:with="type=${@dict.getType('flow_info_channel_type')}"> <option value="">请选择</option> <option th:each="dict : ${type}" th:text="${dict.dictLabel}" th:value="${dict.dictValue}"></option> </select> </li> <li> 数据来源： <input type="text" name="createDeptName" id="createDeptName" readonly="readonly" onclick="ztreeShowDept();"/> <input type="text" name="params[createDeptIds]" id="createDeptId" style="display: none"/> </li> <li> 流转部门： <input type="text" name="flowDeptName" id="flowDeptName" readonly="readonly" onclick="ztreeShowDept2();"/> <input type="text" name="params[flowDeptIds]" id="flowDeptId" style="display: none"/> </li> <li> 考种： <select name="examTypeId"> <option value="">请选择</option> <option th:each="examType:${examTypes}" th:value="${examType.id}" th:text="${examType.examName}"></option> </select> </li> <li> 班次： <select name="shiftId" th:with="type=${@dict.getType('intendedShift')}"> <option value="">请选择</option> <option th:each="dict : ${type}" th:text="${dict.dictLabel}" th:value="${dict.dictValue}"></option> </select> </li> <li> 员工： <input type="text" name="userName" id="userName" readonly="readonly" onclick="ztreeShowUser();"/> <input type="text" name="params[userIds]" id="userId" style="display: none"/> </li> <li> 登记时间： <input type="text" readonly="readonly" placeholder="时间范围" name="accountTime" id="accountTime"/> <input type="hidden" name="params[accountStartTime]" id="accountStartTime"/> <input type="hidden" name="params[accountEndTime]" id="accountEndTime"/> </li>
<li> <a class="btn btn-primary btn-rounded btn-sm" onclick="mysearch();"><i class="fa fa-search"></i> 搜索</a> <a class="btn btn-warning btn-rounded btn-sm" onclick="reset();"><i class="fa fa-refresh"></i> 重置</a> </li> </ul> </div> </form> </div>
<div class="btn-group-sm" id="toolbar" role="group"> <div class="row"> <div class="select-list"> <ul style="padding-left: 17px;"> <li> 维度: <input type="checkbox" name="selectParam" value="1" style="width: 50px;height: 20px;"/>数据类型 <input type="checkbox" name="selectParam" value="2" style="width: 50px;height: 20px;"/>数据来源 <input type="checkbox" name="selectParam" value="3" style="width: 50px;height: 20px;"/>流转部门 <input type="checkbox" name="selectParam" value="4" style="width: 50px;height: 20px;"/>考种 <input type="checkbox" name="selectParam" value="5" style="width: 50px;height: 20px;"/>班次 <input type="checkbox" name="selectParam" value="6" style="width: 50px;height: 20px;"/>员工 <input type="checkbox" name="selectParam" value="7" style="width: 50px;height: 20px;"/>时间粒度 </li> </ul> </div> </div> </div> <div class="col-sm-12 select-table table-striped"> <table id="bootstrap-table" data-mobile-responsive="true"></table> </div> </div> </div> <div th:include="include :: footer"></div> <script th:inline="javascript"> var prefix = ctx + "project/accountFlowInfoRecordData";
var columus = [ { field : 'allotNums', title : '流转数量' }, { field : 'dailyOrderNums', title : '当日成单人数' }, { field : 'totalOrderNums', title : '累计成单人数' }]; var channeltypes = [[${@dict.getType('flow_info_channel_type')}]]; var shiftids = [[${@dict.getType('intendedShift')}]]; $(function() { // 初始化时间范围插件，传入三个id的名称 initDaterangepickerWithoutInitTime("accountTime","accountStartTime","accountEndTime"); var options = { url: prefix + "/list", modalName: "流转记录统计", showRefresh: false, showSearch: false, showColumns: false, showToggle: false, columns: columus }; $.table.init(options); });
function mysearch() { //维度 搜索 query(); }
function query() { //点击维度加载按钮则需销毁原有表格重新加载表格 $("#bootstrap-table").bootstrapTable('destroy'); var options = { id:"bootstrap-table", url: prefix + "/getAccountDataGroup", modalName: "流转记录统计", showSearch: false, showRefresh: false, showToggle: false, showColumns: false, queryParams: queryParams }; //增加展示列 var columns2 = []; var selectParam = ""; $("input[name='selectParam']:checked").each(function () { selectParam = selectParam + $(this).val() + ","; }); if (selectParam != "") { var one = selectParam.indexOf("1"); var two = selectParam.indexOf("2"); var three = selectParam.indexOf("3"); var four = selectParam.indexOf("4"); var five = selectParam.indexOf("5"); var six = selectParam.indexOf("6"); var seven = selectParam.indexOf("7"); if (one >= 0) { // 1 columns2.push({ field : 'channelType', title : '数据类型', formatter: function(value, row, index) { return $.table.selectDictLabel(channeltypes,value); } }); } if (two >= 0) { // 2 columns2.push({ field : 'createDeptName', title : '数据来源', formatter: function(value, row, index) { return value.substring(0,1)+"***"+value.substring(value.length-1); } }); } if (three >= 0) { // 3 columns2.push({ field : 'flowDeptName', title : '流转部门', formatter: function(value, row, index) { return value.substring(0,1)+"***"+value.substring(value.length-1); } }); } if (four >= 0) { // 4 columns2.push({ field : 'examTypeName', title : '考种', formatter: function(value, row, index) { return value.substring(0,1)+"***"+value.substring(value.length-1); } }); } if (five >= 0) { // 5 columns2.push({ field : 'shiftId', title : '班次', formatter: function(value, row, index) { return $.table.selectDictLabel(shiftids,value); } }); } if (six >= 0) { // 6 columns2.push({ field : 'userName', title : '员工', formatter: function(value, row, index) { return value.substring(0,1)+"***"+value.substring(value.length-1); } }); } if (seven >= 0) { // 7 columns2.push({ field : 'accountTime', title : '时间粒度', formatter: function(value, row, index) { return $.common.dateFormat(value, "yyyy-MM-dd"); } }); } columns2 = columns2.concat(columus); }else { columns2 = columus; } options.columns=columns2; $.table.init(options); }
function queryParams(params) { //获取复选框选中的值 var selectParam = ""; $("input[name='selectParam']:checked").each(function () { selectParam = selectParam + $(this).val() + ","; }); var search = $.table.queryParams(params); search.selectParam = selectParam.toString(); return search; }
$("input:checkbox").change(function () { var selectParam = ""; $("input[name='selectParam']:checked").each(function () { selectParam = selectParam + $(this).val() + ","; }); query(); });
/** * 选择部门树 */ function ztreeShowDept() { // url 可选参数， 其他参数 请自定扩展 // permission 权限 0.无权限 1.按照登录人数据权限 // checkType 选中类型 0.多选 1.单选 // showLevel 展示层级 var url = ctx + "sys/deptTree?checkType=1&showLevel=4"; var options = { title: '选择部门', width: "500", url: url, callBack: function (index,layero) { var body = layer.getChildFrame('body', index); $("#createDeptId").val(body.find('#ids').val()); $("#createDeptName").val(body.find('#names').val()); layer.close(index); } }; $.modal.openOptionsDeptTree(options, $("#createDeptId").val()); }
/** * 选择部门树 */ function ztreeShowDept2() { // url 可选参数， 其他参数 请自定扩展 // permission 权限 0.无权限 1.按照登录人数据权限 // checkType 选中类型 0.多选 1.单选 // showLevel 展示层级 var url = ctx + "sys/deptTree?checkType=1&showLevel=4"; var options = { title: '选择部门', width: "500", url: url, callBack: function (index,layero) { var body = layer.getChildFrame('body', index); $("#flowDeptId").val(body.find('#ids').val()); $("#flowDeptName").val(body.find('#names').val()); layer.close(index); } }; $.modal.openOptionsDeptTree(options, $("#flowDeptId").val()); }
/** * 选择用户树 */ function ztreeShowUser() { // url 可选参数， 其他参数 请自定扩展 // permission 权限 0.无权限 1.按照登录人数据权限 // showType 展示方式 0.人员 1.人员+工号 // checkType 选中类型 0.多选 1.单选 // showLevel 展示层级 var url = ctx + "sys/userDeptTree?showType=1&showLevel=3&checkType=0"; var options = { title: '选择部门', width: "500", url: url, callBack: function (index,layero) { var body = layer.getChildFrame('body', index); $("#userId").val(body.find('#ids').val()); $("#userName").val(body.find('#names').val()); layer.close(index); } }; $.modal.openOptionsUserTree(options, $("#userId").val()); }
//重置 function reset() { $.form.reset(); $("#accountTime").val(''); $("#accountStartTime").val(''); $("#accountEndTime").val(''); } </script></body></html>复制代码
页面代码解析
页面核心函数 query(),页面代码就说这么多，具体的可以查看源码，下面来看一下后端 java 代码
java 代码
controllerk 类 AccountFlowInfoRecordDataController.java 源码
package com.dongao.project.accountflowinforecorddata.controller;
import java.util.List;import java.util.Map;
import com.dongao.project.examtype.domain.ExamType;import com.dongao.project.examtype.service.IExamTypeService;import org.apache.shiro.authz.annotation.RequiresPermissions;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.ModelMap;import org.springframework.web.bind.annotation.*;import com.ruoyi.framework.aspectj.lang.annotation.Log;import com.ruoyi.framework.aspectj.lang.enums.BusinessType;import com.dongao.project.accountflowinforecorddata.domain.AccountFlowInfoRecordData;import com.dongao.project.accountflowinforecorddata.service.IAccountFlowInfoRecordDataService;import com.ruoyi.framework.web.controller.BaseController;import com.ruoyi.framework.web.page.TableDataInfo;import com.ruoyi.framework.web.domain.AjaxResult;import com.ruoyi.common.utils.poi.ExcelUtil;
/** * 流转记录统计信息操作处理 * * @author dongao * @date 2022-07-01 */@Controller@RequestMapping("/project/accountFlowInfoRecordData")public class AccountFlowInfoRecordDataController extends BaseController{ private String prefix = "project/accountFlowInfoRecordData"; @Autowired private IAccountFlowInfoRecordDataService accountFlowInfoRecordDataService; @Autowired private IExamTypeService examTypeService; @RequiresPermissions("project:accountFlowInfoRecordData:view") @GetMapping() public String accountFlowInfoRecordData(ModelMap mmap) { ExamType examType = new ExamType(); List<ExamType> examTypes = examTypeService.selectExamTypeList(examType); mmap.put("examTypes",examTypes); return prefix + "/accountFlowInfoRecordData"; } /** * 查询流转记录统计列表 */ @RequiresPermissions("project:accountFlowInfoRecordData:list") @PostMapping("/list") @ResponseBody public TableDataInfo list(@RequestParam Map<String,Object> params) { startPage(); List<AccountFlowInfoRecordData> list = accountFlowInfoRecordDataService.selectAccountFlowInfoRecordDataListOther(params); return getDataTable(list); }
/** * 分组查询流转记录统计列表 */ @RequiresPermissions("project:accountFlowInfoRecordData:list") @PostMapping("/getAccountDataGroup") @ResponseBody public TableDataInfo getAccountDataGroup(@RequestParam Map<String,Object> params) { startPage(); List<AccountFlowInfoRecordData> list = accountFlowInfoRecordDataService.getAccountDataGroup(params); return getDataTable(list); }
}复制代码
后端 java 代码解析
业务逻辑实现类 AccountFlowInfoRecordDataServiceImpl.java 代码
package com.dongao.project.accountflowinforecorddata.service;
import com.dongao.project.accountflowinforecorddata.domain.AccountFlowInfoRecordData;import com.dongao.project.accountflowinforecorddata.mapper.AccountFlowInfoRecordDataMapper;import com.dongao.project.examtype.domain.ExamType;import com.dongao.project.examtype.mapper.ExamTypeMapper;import com.ruoyi.common.utils.StringUtils;import com.ruoyi.common.utils.text.Convert;import com.ruoyi.project.system.dept.domain.Dept;import com.ruoyi.project.system.dept.mapper.DeptMapper;import com.ruoyi.project.system.user.domain.User;import com.ruoyi.project.system.user.mapper.UserMapper;import org.apache.commons.collections.CollectionUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;
import java.util.List;import java.util.Map;import java.util.Objects;
/** * 流转记录统计 服务层实现 * * @author dongao * @date 2022-07-01 */@Servicepublic class AccountFlowInfoRecordDataServiceImpl implements IAccountFlowInfoRecordDataService { @Autowired private AccountFlowInfoRecordDataMapper accountFlowInfoRecordDataMapper; @Autowired private DeptMapper deptMapper; @Autowired private ExamTypeMapper examTypeMapper; @Autowired private UserMapper userMapper; /** * 查询流转记录统计列表 * * @param accountFlowInfoRecordData 流转记录统计信息 * @return 流转记录统计集合 */ @Override public List<AccountFlowInfoRecordData> selectAccountFlowInfoRecordDataList(AccountFlowInfoRecordData accountFlowInfoRecordData) { return accountFlowInfoRecordDataMapper.selectAccountFlowInfoRecordDataList(accountFlowInfoRecordData); } /** * 查询数据 * @param params * @return */ @Override public List<AccountFlowInfoRecordData> selectAccountFlowInfoRecordDataListOther(Map<String,Object> params) { List<AccountFlowInfoRecordData> list = accountFlowInfoRecordDataMapper.selectAccountFlowInfoRecordDataListOther(params); return list; }
/** * 分组查询流转记录统计列表 * @param params * @return */ @Override public List<AccountFlowInfoRecordData> getAccountDataGroup(Map<String,Object> params) { //分组参数 String selectParam = (String) params.get("selectParam"); if (StringUtils.isNotEmpty(selectParam)) { String groupby = ""; String groupfield = ""; if (selectParam.contains("1")) { groupby = groupby + "channel_type,"; groupfield = groupfield + "channel_type channel_type,"; } if (selectParam.contains("2")) { groupby = groupby + "create_dept_id,"; groupfield = groupfield + "create_dept_id create_dept_id,"; } if (selectParam.contains("3")) { groupby = groupby + "flow_dept_id,"; groupfield = groupfield + "flow_dept_id flow_dept_id,"; } if (selectParam.contains("4")) { groupby = groupby + "exam_type_id,"; groupfield = groupfield + "exam_type_id exam_type_id,"; } if (selectParam.contains("5")) { groupby = groupby + "shift_id,"; groupfield = groupfield + "shift_id shift_id,"; } if (selectParam.contains("6")) { groupby = groupby + "user_id,"; groupfield = groupfield + "user_id user_id,"; } if (selectParam.contains("7")) { groupby = groupby + "account_time"; groupfield = groupfield + "account_time account_time,"; } if (groupby.endsWith(",")) { groupby = groupby.substring(0, groupby.length() - 1); } params.put("groupby",groupby); //字段用 groupfield = groupfield + "SUM(allot_nums) allot_nums,SUM(daily_order_nums) daily_order_nums,SUM(total_order_nums) total_order_nums "; params.put("groupfield",groupfield); } List<AccountFlowInfoRecordData> list = accountFlowInfoRecordDataMapper.selectAccountFlowInfoRecordDataListOther(params); if (CollectionUtils.isNotEmpty(list)) { // 1 数据类型-页面处理 3 流转部门 4 考种 5 班次--页面处理 6 员工 7 时间粒度 for (AccountFlowInfoRecordData account : list) { //2 数据来源 if (Objects.nonNull(account.getCreateDeptId())) { Dept dept = deptMapper.selectDeptById(account.getCreateDeptId()); if (Objects.nonNull(dept)) { account.setCreateDeptName(dept.getDeptName()); } } //3 流转部门 if (Objects.nonNull(account.getFlowDeptId())) { Dept dept = deptMapper.selectDeptById(account.getFlowDeptId()); if (Objects.nonNull(dept)) { account.setFlowDeptName(dept.getDeptName()); } } //4 考种 if (Objects.nonNull(account.getExamTypeId())) { ExamType examType = examTypeMapper.selectExamTypeById(account.getExamTypeId()); if (Objects.nonNull(examType)) { account.setExamTypeName(examType.getExamName()); } } //6 员工 if (Objects.nonNull(account.getUserId())) { User user = userMapper.selectUserById(account.getUserId()); if (Objects.nonNull(user)) { account.setUserName(user.getUserName()); } } } } return list; }
}复制代码
XML 代码 AccountFlowInfoRecordDataMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dongao.project.accountflowinforecorddata.mapper.AccountFlowInfoRecordDataMapper"> <resultMap type="AccountFlowInfoRecordData" id="AccountFlowInfoRecordDataResult"> <result property="id" column="id" /> <result property="accountTime" column="account_time" /> <result property="userId" column="user_id" /> <result property="deptId" column="dept_id" /> <result property="channelType" column="channel_type" /> <result property="createDeptId" column="create_dept_id" /> <result property="flowDeptId" column="flow_dept_id" /> <result property="examTypeId" column="exam_type_id" /> <result property="shiftId" column="shift_id" /> <result property="smallestUnit" column="smallest_unit" /> <result property="allotNums" column="allot_nums" /> <result property="dailyOrderNums" column="daily_order_nums" /> <result property="totalOrderNums" column="total_order_nums" /> <result property="createTime" column="create_time" /> <result property="updateTime" column="update_time" /> </resultMap> <sql id="selectAccountFlowInfoRecordDataVo"> select id, account_time, user_id, dept_id, channel_type, create_dept_id, flow_dept_id, exam_type_id, shift_id, smallest_unit, allot_nums, daily_order_nums, total_order_nums, create_time, update_time from crm_account_flow_info_record_data </sql> <select id="selectAccountFlowInfoRecordDataList" parameterType="AccountFlowInfoRecordData" resultMap="AccountFlowInfoRecordDataResult"> <include refid="selectAccountFlowInfoRecordDataVo"/> <where> <if test="id != null "> and id = #{id}</if> <if test="accountTime != null "> and account_time = #{accountTime}</if> <if test="userId != null "> and user_id = #{userId}</if> <if test="deptId != null "> and dept_id = #{deptId}</if> <if test="channelType != null "> and channel_type = #{channelType}</if> <if test="createDeptId != null "> and create_dept_id = #{createDeptId}</if> <if test="flowDeptId != null "> and flow_dept_id = #{flowDeptId}</if> <if test="examTypeId != null "> and exam_type_id = #{examTypeId}</if> <if test="shiftId != null "> and shift_id = #{shiftId}</if> <if test="smallestUnit != null and smallestUnit != '' "> and smallest_unit = #{smallestUnit}</if> <if test="allotNums != null "> and allot_nums = #{allotNums}</if> <if test="dailyOrderNums != null "> and daily_order_nums = #{dailyOrderNums}</if> <if test="totalOrderNums != null "> and total_order_nums = #{totalOrderNums}</if> <if test="createTime != null "> and create_time = #{createTime}</if> <if test="updateTime != null "> and update_time = #{updateTime}</if> </where> </select> <select id="selectAccountFlowInfoRecordDataListOther" parameterType="Map" resultMap="AccountFlowInfoRecordDataResult"> <choose> <when test="params.groupby != null and params.groupby != ''"> select ${params.groupfield} from crm_account_flow_info_record_data </when> <otherwise> <include refid="selectAccountFlowInfoRecordDataVo"/> </otherwise> </choose> <where> <if test="params != null"> <if test="params.channelType != null and params.channelType != ''"> and channel_type = #{params.channelType}</if> <if test="params.examTypeId != null and params.examTypeId != ''"> and exam_type_id = #{params.examTypeId}</if> <if test="params.shiftId != null and params.shiftId != ''"> and shift_id = #{params.shiftId}</if> <if test="params.userIds != null and params.userIds != '' and params.userIds.size() > 0"> AND user_id IN <foreach collection="params.userIds" item="userId" open="(" close=")" separator=","> #{userId} </foreach> </if> <if test="params.flowDeptIds != null and params.flowDeptIds != '' and params.flowDeptIds.size() > 0"> AND flow_dept_id IN <foreach collection="params.flowDeptIds" item="flowDeptId" open="(" close=")" separator=","> #{flowDeptId} </foreach> </if> <if test="params.createDeptIds != null and params.createDeptIds != '' and params.createDeptIds.size() > 0"> AND create_dept_id IN <foreach collection="params.createDeptIds" item="createDeptId" open="(" close=")" separator=","> #{createDeptId} </foreach> </if> <if test="params.accountStartTime != null and params.accountStartTime != ''" > AND account_time >= #{params.accountStartTime} </if> <if test="params.accountEndTime != null and params.accountEndTime != '' " > AND account_time <= #{params.accountEndTime} </if> </if> </where> <if test="params.groupby != null and params.groupby != ''"> group by ${params.groupby} </if> </select>
</mapper>复制代码
xml 代码解析，主要关注 id 为 selectAccountFlowInfoRecordDataListOther 的 sql 查询，这里用到了 ${},那么也顺便说一下 #{} ${}的区别
#{} ${}的区别
#{}将传入的参数当成一个字符串，会给传入的参数加一个双引号
${}将传入的参数直接显示生成在 sql 中，不会添加引号
#{}能够很大程度上防止 sql 注入，${}无法防止 sql 注入
通过使用 ${}接收业务逻辑层处理动态传入的字段名动态生成分组查询 sql，同时根据页面动态展示查询数据，到这里整个业务逻辑流程也就算完成了，实现的效果就和开始看到的效果一样了。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【六月的雨在infoQ】的原创文章。
原文链接:【http://xie.infoq.cn/article/6e9e31be45fbb089557c0a4a6】。
本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。
六月的雨在infoQ
关注
还未添加个人签名 2022.07.22 加入
还未添加个人简介
评论