java110-bean/src/main/java/com/java110/dto/reportCustomComponent/ReportCustomComponentDto.java
@@ -15,6 +15,9 @@ **/ public class ReportCustomComponentDto extends PageDto implements Serializable { public static final String QUERY_MODEL_SQL = "1"; public static final String QUERY_MODEL_JAVA = "2"; private String componentType; private String componentId; private String javaScript; java110-db/src/main/java/com/java110/db/dao/IQueryServiceDAO.java
@@ -19,6 +19,15 @@ */ public List<Map<String,Object>> executeSql(String sql,Object []params); /** * 防止sql注入 改造成直接用prepareStatement 预处理sql * * @param sql * @param params * @return */ public List<Map<String, Object>> executeSql(String sql, Object[] params, List<String> columns); public int updateSql(String sql,Object[] params); /** java110-db/src/main/java/com/java110/db/dao/impl/QueryServiceDAOImpl.java
@@ -1,20 +1,16 @@ package com.java110.db.dao.impl; import com.java110.utils.util.StringUtil; import com.java110.core.base.dao.BaseServiceDao; import com.java110.db.dao.IQueryServiceDAO; import com.java110.entity.order.ServiceBusiness; import com.java110.entity.service.ServiceSql; import com.java110.db.dao.IQueryServiceDAO; import com.java110.utils.util.StringUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; @@ -39,6 +35,18 @@ */ @Override public List<Map<String, Object>> executeSql(String sql, Object[] params) { return executeSql(sql, params, null); } /** * 防止sql注入 改造成直接用prepareStatement 预处理sql * * @param sql * @param params * @return */ @Override public List<Map<String, Object>> executeSql(String sql, Object[] params, List<String> columns) { logger.debug("----【queryServiceDAOImpl.executeSql】入参 : " + sql + " params= " + params); Connection conn = null; ResultSet rs = null; @@ -55,6 +63,11 @@ rs = ps.executeQuery(); //精髓的地方就在这里，类ResultSet有getMetaData()会返回数据的列和对应的值的信息，然后我们将列名和对应的值作为map的键值存入map对象之中... ResultSetMetaData rsmd = rs.getMetaData(); if (columns != null) { for (int i = 1; i < rsmd.getColumnCount(); ++i) { columns.add(rsmd.getColumnLabel(i)); } } while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 0; i < rsmd.getColumnCount(); i++) { java110-db/src/main/resources/mapper/report/ReportCustomComponentRelV1ServiceDaoImplMapper.xml
@@ -19,13 +19,16 @@ <select id="getReportCustomComponentRelInfo" parameterType="Map" resultType="Map"> select t.rel_id,t.rel_id relId,t.component_id,t.component_id componentId,t.status_cd,t.status_cd statusCd,t.custom_id,t.custom_id customId,t.seq,cc.`name` componentName,t.create_time createTime, cc.component_type componentType,cc.java_script javaScript,cc.component_group componentGroup,cc.query_model queryModel,cc.component_sql componentSql cc.component_type componentType,cc.component_group componentGroup,cc.query_model queryModel from report_custom_component_rel t left join report_custom_component cc on t.component_id = cc.component_id and cc.status_cd = '0' where 1 =1 <if test="relId !=null and relId != ''"> and t.rel_id= #{relId} </if> <if test="componentType !=null and componentType != ''"> and t.component_type= #{componentType} </if> <if test="componentId !=null and componentId != ''"> and t.component_id= #{componentId} @@ -77,6 +80,9 @@ <if test="relId !=null and relId != ''"> and t.rel_id= #{relId} </if> <if test="componentType !=null and componentType != ''"> and t.component_type= #{componentType} </if> <if test="componentId !=null and componentId != ''"> and t.component_id= #{componentId} </if> java110-service/src/main/java/com/java110/service/smo/IQueryServiceSMO.java
@@ -1,5 +1,7 @@ package com.java110.service.smo; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.java110.utils.exception.BusinessException; import com.java110.service.context.DataQuery; import org.springframework.http.ResponseEntity; @@ -35,4 +37,15 @@ * @throws BusinessException */ public ResponseEntity<String> fallBack(String fallBackInfo) throws BusinessException; /** * 执行查询sql * @param param * @param sql * @return { * th:[], * td:[{}] * } */ JSONObject execQuerySql(JSONObject param, String sql) throws BusinessException; } java110-service/src/main/java/com/java110/service/smo/impl/QueryServiceSMOImpl.java
@@ -136,6 +136,7 @@ } /** * {"PARAM:"{ * "param1": "$.a.#A#Object", @@ -340,6 +341,59 @@ } } @Override public JSONObject execQuerySql(JSONObject params, String currentSql) throws BusinessException { List<Map<String, Object>> results = null; List<String> columns = new ArrayList<>(); try { List<Object> currentParams = new ArrayList<Object>(); //处理 if 判断 logger.debug("dealSqlIf开始处理sql中的<if>节点 " + currentSql + " 入参:" + params.toJSONString()); currentSql = dealSqlIf(currentSql, params); logger.debug("dealSqlIf处理完成sql中的<if>节点 " + currentSql + " 入参:" + params.toJSONString()); String[] sqls = currentSql.split("#"); String currentSqlNew = ""; for (int sqlIndex = 0; sqlIndex < sqls.length; sqlIndex++) { if (sqlIndex % 2 == 0) { currentSqlNew += sqls[sqlIndex]; continue; } currentSqlNew += "?"; Object param = params.getString(sqls[sqlIndex]); if (params.get(sqls[sqlIndex]) instanceof Integer) { param = params.getInteger(sqls[sqlIndex]); } //这里对 page 和 rows 特殊处理 ，目前没有想到其他的办法 if (StringUtils.isNumeric(param.toString()) && "page,rows,row".contains(sqls[sqlIndex])) { param = Integer.parseInt(param.toString()); } currentParams.add(param); } results = queryServiceDAOImpl.executeSql(currentSqlNew, currentParams.toArray(), columns); } catch (Exception e) { logger.error("解析sql 异常", e); throw new BusinessException("1999", e.getLocalizedMessage()); } JSONArray data = null; if (results == null || results.size() < 1) { data = new JSONArray(); } else { data = JSONArray.parseArray(JSONArray.toJSONString(results)); } JSONArray th = null; if (columns.size() < 1) { th = new JSONArray(); } else { th = JSONArray.parseArray(JSONArray.toJSONString(columns)); } JSONObject paramOut = new JSONObject(); paramOut.put("th", th); paramOut.put("td", data); return paramOut; } /** * 处理SQL语句 * @@ -357,7 +411,6 @@ if (!oldSql.contains("<if")) { return oldSql; } String[] oSqls = oldSql.split("</if>"); for (String oSql : oSqls) { logger.debug("处理if 节点，当前处理的oSql=" + oSql + "总的oSqls = " + oSqls); service-report/src/main/java/com/java110/report/cmd/reportCustomComponent/ListReportCustomComponentDataCmd.java
New file @@ -0,0 +1,116 @@ /* * Copyright 2017-2020 吴学文 and java110 team. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.java110.report.cmd.reportCustomComponent; import com.alibaba.fastjson.JSONObject; import com.java110.core.annotation.Java110Cmd; import com.java110.core.context.ICmdDataFlowContext; import com.java110.core.event.cmd.AbstractServiceCmdListener; import com.java110.core.event.cmd.CmdEvent; import com.java110.dto.PageDto; import com.java110.dto.reportCustomComponent.ReportCustomComponentDto; import com.java110.intf.report.IReportCustomComponentV1InnerServiceSMO; import com.java110.service.smo.IQueryServiceSMO; import com.java110.utils.exception.CmdException; import com.java110.utils.util.Assert; import com.java110.vo.ResultVo; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import java.util.List; /** * 类表述：查询 * 服务编码：reportCustomComponent.listReportCustomComponentData * 请求路劲：/app/reportCustomComponent.listReportCustomComponentData * add by 吴学文 at 2021-11-09 13:18:41 mail: 928255095@qq.com * open source address: https://gitee.com/wuxw7/MicroCommunity * 官网：http://www.homecommunity.cn * 温馨提示：如果您对此文件进行修改 请不要删除原有作者及注释信息，请补充您的 修改的原因以及联系邮箱如下 * // modify by 张三 at 2021-09-12 第10行在某种场景下存在某种bug 需要修复，注释10至20行 加入 20行至30行 */ @Java110Cmd(serviceCode = "reportCustomComponent.listReportCustomComponentData") public class ListReportCustomComponentDataCmd extends AbstractServiceCmdListener { private static Logger logger = LoggerFactory.getLogger(ListReportCustomComponentDataCmd.class); @Autowired private IReportCustomComponentV1InnerServiceSMO reportCustomComponentV1InnerServiceSMOImpl; @Autowired private IQueryServiceSMO queryServiceSMOImpl; @Override public void validate(CmdEvent event, ICmdDataFlowContext cmdDataFlowContext, JSONObject reqJson) { super.validatePageInfo(reqJson); Assert.hasKeyAndValue(reqJson, "componentId", "未包含组件ID"); } @Override public void doCmd(CmdEvent event, ICmdDataFlowContext cmdDataFlowContext, JSONObject reqJson) throws CmdException { //查询组件是否存在 ReportCustomComponentDto reportCustomComponentDto = new ReportCustomComponentDto(); reportCustomComponentDto.setComponentId(reqJson.getString("componentId")); List<ReportCustomComponentDto> reportCustomComponentDtos = reportCustomComponentV1InnerServiceSMOImpl.queryReportCustomComponents(reportCustomComponentDto); Assert.listOnlyOne(reportCustomComponentDtos, "组件不存在，请联系开发人员"); reportCustomComponentDto = reportCustomComponentDtos.get(0); if (ReportCustomComponentDto.QUERY_MODEL_SQL.equals(reportCustomComponentDto.getQueryModel())) { doDealSql(reqJson, reportCustomComponentDto, cmdDataFlowContext); } else if (ReportCustomComponentDto.QUERY_MODEL_JAVA.equals(reportCustomComponentDto.getQueryModel())) { doDealJava(reqJson, reportCustomComponentDto, cmdDataFlowContext); } else { throw new CmdException("组件实现方式不支持，请联系开发人员"); } } private void doDealJava(JSONObject reqJson, ReportCustomComponentDto reportCustomComponentDto, ICmdDataFlowContext cmdDataFlowContext) { } private void doDealSql(JSONObject reqJson, ReportCustomComponentDto reportCustomComponentDto, ICmdDataFlowContext cmdDataFlowContext) { //校验是否传了 分页信息 String sql = reportCustomComponentDto.getComponentSql(); long total = reqJson.getIntValue("row"); if (sql.trim().contains("test=\"count")) { // 如果包含 count(1) 求总数 JSONObject reqJsonCount = new JSONObject(); for (String key : reqJson.keySet()) { if ("row".equals(key) || "page".equals(key)) { continue; } reqJsonCount.put(key, reqJson.get(key)); } reqJsonCount.put("count", "1"); JSONObject data = queryServiceSMOImpl.execQuerySql(reqJsonCount, sql); total = data.getJSONArray("data").getJSONObject(0).getIntValue("total"); } reqJson.put("count", "0"); int page = reqJson.getInteger("page"); if (page != PageDto.DEFAULT_PAGE) { reqJson.put("page", (page - 1) * reqJson.getIntValue("row")); } JSONObject data = queryServiceSMOImpl.execQuerySql(reqJson, sql); ResultVo resultVo = new ResultVo((int) Math.ceil((double) total / (double) reqJson.getInteger("row")), total, data); ResponseEntity<String> responseEntity = new ResponseEntity<String>(resultVo.toString(), HttpStatus.OK); cmdDataFlowContext.setResponseEntity(responseEntity); } }
