mybaits-动态sql、动态列拼接,boostrap3.v,springboot版

1.业务需求描述

报表前端显示的列不确定根据实际业务变动，所以不能采取传统的boostrap-table.js默认的方法进行配置，包括后端的sql语句也不是固定的，

2.实现的思路，boostraptable默认属性columns为后台动态获取,sql中的列也为动态拼接的sql语句

3.java端mybaits相关代码

xml,业务代码较多，配置文件说明  使用${sql}的方式传入sql语句，支持${}和#{}混用的方式(不要添加statementType="STATEMENT")，sql业务简单说明，各项指标打分，汇总，排名按人员，机构进行分类

<select id="listTableCondition" resultMap="BaseResultMap"
		parameterType="Map" >
		SELECT
        obj_new.*
        FROM
        (SELECT
            obj.*,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := 
            CASE WHEN @rowtotal = obj.sumCnt THEN
            @incrnum
            WHEN @rowtotal := obj.sumCnt THEN
            @rownum
            END AS orderNo
        FROM(
        select sumt.* from (
		select t1.leader_code,t1.leader_name,t2.org_name leadergroup_name,t1.leader_Station leader_Station_value,t4.fd_param_val leader_Station,t5.fd_param_val leader_job_category,sum(t.score) sumCnt,
		${sql}
		from
		eval_leader_report_sum t
		left join eval_leaders t1 on t.leader_id =
		t1.id
		left join eval_leadergroup t2 on t1.leadergroup_id=t2.id
		left join eval_manage_year_detail t3 on t.eval_year_detail_id = t3.uuid
	    left join sys_param t4 on  t4.fd_param_typcde='leader_station_param' and t1.leader_station = t4.fd_param_cde
        left join sys_param t5 on  t5.fd_param_typcde='leader_job_category' and t1.leader_job_category = t5.fd_param_cde
		<where>
			<if test="evalName != null and '' != evalName">
				and t3.eval_year_id = #{evalName}
			</if>
		</where>
		group by t1.leader_code,t1.leader_name,t2.org_name
		) as sumt order by sumt.sumcnt desc
        ) AS obj,
          (
              SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
        ) AS obj_new 
        <where>
           <if test="leadergroupId != null and '' != leadergroupId">
				and leadergroup_name like #{leadergroupId}
			</if>
			<if test="leaderName != null and '' != leaderName">
				and leader_name like #{leaderName}
			</if>
			<if test="leaderStation != null and '' != leaderStation">
				and leader_Station_value = #{leaderStation}
			</if>
            <if test="rankingStart != null and '' != rankingStart">
				<![CDATA[and cast(orderNo as unsigned int) >=  ]]>#{rankingStart,jdbcType=INTEGER}
			</if>
			<if test="rankingEnd != null and '' != rankingEnd">
				<![CDATA[and cast(orderNo as unsigned int) <= ]]> #{rankingEnd,jdbcType=INTEGER}
			</if>
			
        </where>
	</select>

4.java端部分主要代码

controler层，conditionMap.put("sql", sql);为关键代码

	/**
	 * 获取table数据
	 * 
	 * @param pageSize
	 * @param pageNumber
	 * @return
	 */
	@PostMapping("/listTableConditionRanking")
	public @ResponseBody Object listTableCondition(HttpServletRequest request,String infoId, Integer pageSize, Integer pageNumber) {
		// 获取参数信息
		List<ReportQueryPara> listParas = iDynamicQueryService.queryFormConditionList(infoId);
		// 从request获取参数信息 封装到map中
		Map<String, Object> conditionMap = new HashMap<String, Object>();
		for (int i = 0; i < listParas.size(); i++) {
			ReportQueryPara reportQueryPara = listParas.get(i);
			String name = reportQueryPara.getFdName();
			if (0 == reportQueryPara.getFdParaLike()) {
				conditionMap.put(name, request.getParameter(name));
			} else {
				conditionMap.put(name,
					StringUtil.isEmpty(request.getParameter(name))? "" : ("%" + request.getParameter(name) + "%"));
			}
		}
		String sql =iDynamicQueryService.constructDynamicQuotaSql("","leader","1");
		conditionMap.put("sql", sql);
		Object returnList = iLeaderPerformanceRankingService.listTableCondition(pageSize, pageNumber, conditionMap);
		return returnList;
	}

字段拼接，这里可以拼接任意sql语句，根据大家的业务需求

public String constructDynamicQuotaSql(String batch,String type,String level){
		List<ReportDynamicField> dynamicFieldList = dynamicQueryServiceMapper.listDynamicQuota("20180725", type, level);
		StringBuilder sb = new StringBuilder();
		//sb.deleteCharAt(0);
		for(int i=0 ;i<dynamicFieldList.size();i++){
			sb.append(" sum(case when t.quota_id=");
			sb.append(dynamicFieldList.get(i).getQuotaId());
			sb.append(" then t.score else 0 end)as quota"+(i+1));
			if(i!=dynamicFieldList.size()-1){
				sb.append(",");
			}
		}	
		return sb.toString();
	}

impl实现层，@ExtPageHelper为自己扩展的分页插件，详情可以查看https://blog.csdn.net/xiewenfeng520/article/details/80783584

@Override
	@ExtPageHelper
	public Object listTableCondition(Integer pageSize,Integer pageNumber,Map<String, Object> conditionMap) {
		List<LeaderPerformanceRanking> list = leaderQuotaRankingMapper.listTableCondition(conditionMap);
		return list;
	}

sql语句拼接完成效果如下

SELECT
	obj_new.*
FROM
	(
		SELECT
			obj.*, @rownum := @rownum + 1 AS num_tmp,
			@incrnum := CASE
		WHEN @rowtotal = obj.sumCnt THEN
			@incrnum
		WHEN @rowtotal := obj.sumCnt THEN
			@rownum
		END AS orderNo
		FROM
			(
				SELECT
					sumt.*
				FROM
					(
						SELECT
							t1.leader_code,
							t1.leader_name,
							t2.org_name leadergroup_name,
							t4.fd_param_val leader_Station_value,
							t4.fd_param_val leader_Station,
							t5.fd_param_val leader_job_category,
							sum(t.score) sumCnt,
							sum(
								CASE
								WHEN t.quota_id = 10010 THEN
									t.score
								ELSE
									0
								END
							) AS quota1,
							sum(
								CASE
								WHEN t.quota_id = 10012 THEN
									t.score
								ELSE
									0
								END
							) AS quota2,
							sum(
								CASE
								WHEN t.quota_id = 10013 THEN
									t.score
								ELSE
									0
								END
							) AS quota3,
							sum(
								CASE
								WHEN t.quota_id = 10029 THEN
									t.score
								ELSE
									0
								END
							) AS quota4
						FROM
							eval_leader_report_sum t
						LEFT JOIN eval_leaders t1 ON t.leader_id = t1.id
						LEFT JOIN eval_leadergroup t2 ON t1.leadergroup_id = t2.id
						LEFT JOIN eval_manage_year_detail t3 ON t.eval_year_detail_id = t3.uuid
						LEFT JOIN sys_param t4 ON t4.fd_param_typcde = 'leader_station_param'
						AND t1.leader_station = t4.fd_param_cde
						LEFT JOIN sys_param t5 ON t5.fd_param_typcde = 'leader_job_category'
						AND t1.leader_job_category = t5.fd_param_cde
						WHERE
							t3.eval_year_id = '10016'
						GROUP BY
							t1.leader_code,
							t1.leader_name,
							t2.org_name
					) AS sumt
				ORDER BY
					sumt.sumcnt DESC
			) AS obj,
			(
				SELECT
					@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
			) r
	) AS obj_new

mybatis xml配置文件  resultMap属性如下

<resultMap id="BaseResultMap"
		type="com.huajie.entity.report.LeaderPerformanceRanking">
		<result column="leader_code" property="leaderCode" jdbcType="VARCHAR" />
		<result column="leader_name" property="leaderName" jdbcType="VARCHAR" />
		<result column="leader_station" property="leaderStation"
			jdbcType="VARCHAR" />
		<result column="leader_job_category" property="leaderJobCategory"
			jdbcType="VARCHAR" />
		<result column="leadergroup_id" property="leadergroupId"
			jdbcType="VARCHAR" />
		<result column="leadergroup_name" property="leadergroupName"
			jdbcType="VARCHAR" />
		<result column="sumCnt" property="sumCnt" jdbcType="INTEGER" />
		<result column="orderNo" property="orderNo" jdbcType="INTEGER" />
		<result column="quota1" property="quota1" jdbcType="INTEGER" />
		<result column="quota2" property="quota2" jdbcType="INTEGER" />
		<result column="quota3" property="quota3" jdbcType="INTEGER" />
		<result column="quota4" property="quota4" jdbcType="INTEGER" />
		<result column="quota5" property="quota5" jdbcType="INTEGER" />
		<result column="quota6" property="quota6" jdbcType="INTEGER" />
		<result column="quota7" property="quota7" jdbcType="INTEGER" />
		<result column="quota8" property="quota8" jdbcType="INTEGER" />
		<result column="quota9" property="quota9" jdbcType="INTEGER" />
		<result column="quota10" property="quota10" jdbcType="INTEGER" />
		<result column="quota11" property="quota11" jdbcType="INTEGER" />
		<result column="quota12" property="quota12" jdbcType="INTEGER" />
		<result column="quota13" property="quota13" jdbcType="INTEGER" />
		<result column="quota14" property="quota14" jdbcType="INTEGER" />
		<result column="quota15" property="quota15" jdbcType="INTEGER" />
		<result column="quota16" property="quota16" jdbcType="INTEGER" />
		<result column="quota17" property="quota17" jdbcType="INTEGER" />
		<result column="quota18" property="quota18" jdbcType="INTEGER" />
		<result column="quota19" property="quota19" jdbcType="INTEGER" />
		<result column="quota20" property="quota20" jdbcType="INTEGER" />
	</resultMap>

前端对应javaBean

package com.huajie.entity.report;

import lombok.Data;

@Data
public class LeaderPerformanceRanking {
	private String leaderCode;// 员工编号
	private String leaderName;// 员工姓名
	private String leaderStation;// 岗位
	private String leaderJobCategory;// 职位类别
	private String leadergroupId;// 编号
	private String leadergroupName;// 名称
	private Integer sumCnt;//合计
	private Integer orderNo;//排名
	private Integer quota1;
	private Integer quota2;
	private Integer quota3;
	private Integer quota4;
	private Integer quota5;
	private Integer quota6;
	private Integer quota7;
	private Integer quota8;
	private Integer quota9;
	private Integer quota10;
	private Integer quota11;
	private Integer quota12;
	private Integer quota13;
	private Integer quota14;
	private Integer quota15;
	private Integer quota16;
	private Integer quota17;
	private Integer quota18;
	private Integer quota19;
	private Integer quota20;
	
}

4.js前端核心封装代码

js经过几层封装，与bootstrap-table.js有所不同，目前只粘贴核心实现代码

动态列获取，将属性替换为columns : columnArr即可，ajax获取动态列的逻辑和后台java拼接的逻辑一致即可

/**
 * 初始化form表单
 * @param infoId 报表编号
 */
function getTableField() {
	var tableFields;
	$.ajax({
		url : ctlr_tableField_list_url,
		type : 'post',
		data : {
			infoId : infoId
		},
		async : false,
		success : function(data) {
		 tableFields = data.data;
		}
	});
	
	var columnArr = [];
	$.each(tableFields, function(index, value) {
	var val = value.resultFormatter ? eval("(" + value.resultFormatter + ")") : undefined
	var cloumn = {
		    field : value.resultField,
		    title : value.resultTitle,
		    //halign : "center",
		    //valign : "middle",
		    align : value.resultAlign,
		    //rowspan : value.rowSpan,
		    //colspan : value.colSpan,
		    //sortable : true,
		    width : value.resultWidth,
		    //visible : value.resultDisplayYn == 'Y' ? true : false,
		    formatter : val
		};
	columnArr.push(cloumn);
	});
	return columnArr;
}

5.说明

本文只是提出一种实现的方式