EKP其它/EKPSQL工具

置顶 Liquid-Li

已于 2023-04-29 21:42:39 修改

阅读量934

点赞数

分类专栏：蓝凌Landray-EKP开发(后端) 文章标签： java 前端开发语言

于 2022-10-21 13:24:40 首次发布

本文链接：https://blog.csdn.net/qq_41141902/article/details/127443096

版权

蓝凌Landray-EKP开发(后端) 专栏收录该内容

32 篇文章 74 订阅

订阅专栏

1、说明

有时候想在前端快速查询一些数据，ekp自带的第三方集成jdbc也可以用，但怎么说呢，总之用得很不爽！所以就有了以下的扩展。

功能大概是这样的：前端写好纯sql，前端根据关键字传入参数进行调用，获取到查询结果，并支持SQL开窗

2、效果图

低代码：

开窗效果：

开窗应用，具体可看本人文章：自定义表单控件 - 扩展出一个选择框_Liquid-Li的博客-CSDN博客

3、具体实现 - 后端service

package com.landray.kmss.xpcommon.service.spring;

import cn.hutool.core.lang.func.VoidFunc1;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.transaction.TransactionLevel;
import com.alibaba.fastjson.JSONObject;
import com.landray.kmss.common.service.BaseServiceImp;
import com.landray.kmss.component.dbop.model.CompDbcp;
import com.landray.kmss.component.dbop.service.ICompDbcpService;
import com.landray.kmss.util.SpringBeanUtil;
import com.landray.kmss.util.StringUtil;
import com.landray.kmss.xp.util.XpStringUtil;

import javax.sql.DataSource;
import java.util.*;

public abstract class XpSqlBaseServiceImp extends BaseServiceImp{
    private ICompDbcpService compDbcpService;
    private Db db;

    public abstract String getDbSourceId();

    public ICompDbcpService getCompDbcpService() {
        if (compDbcpService == null) {
            compDbcpService = (ICompDbcpService) SpringBeanUtil.getBean("compDbcpService");
        }
        return compDbcpService;
    }

    public Db getDb() throws Exception {
        if (db == null || db.getConnection().isClosed()) {
            ICompDbcpService compDbcpService = getCompDbcpService();
            CompDbcp compDbcp = (CompDbcp) compDbcpService.findByPrimaryKey(this.getDbSourceId());// 数据源对象
            DataSource ds = new SimpleDataSource(compDbcp.getFdUrl(), compDbcp.getFdUsername(), compDbcp.getFdPassword());
            db = Db.use(ds);
        }
        return db;
    }

    /**
     * 执行sql获取查询结果(一维List,元素为Entity对象)
     * @author liquid
     * @date 2022年10月11日
     * @param sql SQL
     * @param params 参数集合
     *               如需分页操作，请在子类重新该方法，根据params._fixed{firstResult,maxResults}进行特殊处理
     * @return 查询结果数据
     */
    public List<Entity> executeQueryToEntity(String sql, JSONObject params) {
        List<Entity> entityList;
        try {
            entityList = getDb().query(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return entityList;
    }

    /**
     * 执行sql获取查询结果(二维List,元素为具体数据对象)
     * @author liquid
     * @date 2022年10月11日
     * @param sql SQL
     * @param params 参数集合
     *               可传入固定参数 _fixed{
     *                  firstResult: 0,
     *                  maxResults: 10
     *               } 从第0条记录开始取10条数据
     * @return 查询结果数据
     */
    public List executeQuery(String sql, JSONObject params) {
        List<List<Object>> rtnList = new ArrayList<>();
        List<Entity> queryList = this.executeQueryToEntity(sql, params);
        if (queryList.size() == 0) {
            return rtnList;
        }
        // Entity 转 List 后返回
        Set<String> fieldNames = queryList.get(0).getFieldNames();
        for (Entity entity : queryList) {
            List<Object> row = new ArrayList<>();
            for (String fieldName : fieldNames) {
                row.add(entity.get(fieldName));
            }
            rtnList.add(row);
        }
        return rtnList;
    }

    /**
     * 执行sql获取查询结果(一维List,元素为Map对象)
     * @author liquid
     * @date 2022年10月11日
     * @param sql SQL
     * @param params 参数集合
     *               可传入固定参数 _fixed{
     *                  firstResult: 0,
     *                  maxResults: 10
     *               } 从第0条记录开始取10条数据
     * @return 查询结果数据
     */
    public List executeQueryToMap(String sql, JSONObject params) {
        List<Map<String, Object>> rtnList = new ArrayList<>();
        List<Entity> queryList = this.executeQueryToEntity(sql, params);
        if (queryList.size() == 0) {
            return rtnList;
        }
        // Entity 转 Map 后返回
        Set<String> fieldNames = queryList.get(0).getFieldNames();
        for (Entity entity : queryList) {
            Map<String, Object> row = new HashMap<>();
            for (String fieldName : fieldNames) {
                row.put(fieldName, entity.get(fieldName));
            }
            rtnList.add(row);
        }
        return rtnList;
    }

    /**
     * 执行sql写入操作
     * ekp数据库，执行写操作sql(多条sql语句,加入事务管理)
     * @author liquid
     * @date 2023年2月2日
     * @param sqlList sql语句列表
     * @param paramsList sql语句参数列表
     *                   (注意该参数和sqlList的元素个数需一致，若对应sql无需传参则new一个空的JOSNObject进去)
     * @return 是否执行成功(1成功，0失败)
     */
    public String executeUpdate(List<String> sqlList, List<JSONObject> paramsList) {
        String status;
        try {
            Db db = getDb();
            db.tx(TransactionLevel.READ_COMMITTED, (VoidFunc1<Db>) thisDb -> {
                for (int i = 0; i < sqlList.size(); i++) {
                    thisDb.execute(sqlList.get(i), paramsList.get(i));
                }
            });
            status = XpStringUtil.CODE_SUCCESS;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return status;
    }

    /**
     * 执行sql写入操作
     * @author liquid
     * @date 2023年3月2日
     * @param sql sql语句
     * @param param json参数
     * @return 是否执行成功(1成功，0失败)
     */
    public String executeUpdate(String sql, JSONObject param) {
        if (StringUtil.isNull(sql)) {
            return XpStringUtil.CODE_ERROR;
        }
        List<String> sqlList = new ArrayList<>();
        List<JSONObject> paramsList = new ArrayList<>();
        sqlList.add(sql);
        paramsList.add(param);
        return executeUpdate(sqlList, paramsList);
    }

    /**
     * 获取sql查询结果个数
     * @author liquid
     * @date 2022年11月2日
     * @param sql SQL
     * @param params 参数集合
     * @return 查询结果个数
     */
    public long getQueryMaxCount(String sql, JSONObject params) {
        Db db;
        long maxCount;
        try {
            db = getDb();
            maxCount = db.count(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return maxCount;
    }
}

package com.landray.kmss.xpcommon.service.spring;

import cn.hutool.db.Entity;
import com.alibaba.fastjson.JSONObject;
import com.landray.kmss.xp.modeling.app.it.util.XpSqlUtil;
import com.landray.kmss.xp.util.enums.XpDbType;

import java.util.List;

public abstract class XpSqlBaseOracleServiceImp  extends XpSqlBaseServiceImp {
    /**
     * 执行sql获取查询结果(一维List,元素为Entity对象)
     * @author liquid
     * @date 2022年10月11日
     * @param sql SQL(分页查询：SELECT ROWNUM rowno, V_GROUP_USER_02.* FROM V_GROUP_USER_02 WHERE ORGNAME = :ORGNAME)
     * @param params 参数集合
     *               可传入固定参数 _fixed{
     *                  firstResult: 0,
     *                  maxResults: 10
     *               } 从第0条记录开始取10条数据
     * @return 查询结果数据
     */
    @Override
    public List<Entity> executeQueryToEntity(String sql, JSONObject params) {
        List<Entity> entityList;
        // 固定参数
        if (params.containsKey("_fixed")) {
            JSONObject fixed = params.getJSONObject("_fixed");
            Integer firstResult = fixed.getInteger("firstResult");
            Integer maxResults = fixed.getInteger("maxResults");
            // 构造分页sql
            if (firstResult != null && maxResults != null) {
                sql = XpSqlUtil.makePagingSql(XpDbType.DB_TYPE_ORACLE, sql, firstResult, maxResults);
            }
        }
        try {
            entityList = getDb().query(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        entityList.forEach(entity -> {
            entity.remove("rowno");
            entity.getFieldNames().remove("rowno");
        });

        return entityList;
    }
}

package com.landray.kmss.xp.modeling.app.it.service.spring;

import com.landray.kmss.xp.modeling.app.it.constant.XpCompDbcpConstant;
import com.landray.kmss.xpcommon.service.spring.XpSqlBaseOracleServiceImp;

public class XpEkpSqlBpmServiceImp extends XpSqlBaseOracleServiceImp {
    @Override
    public String getDbSourceId() {
        return XpCompDbcpConstant.DB_SOURCE_ID_BPM;
    }
}

package com.landray.kmss.xp.modeling.app.it.service.spring;

import com.landray.kmss.xp.modeling.app.it.constant.XpCompDbcpConstant;
import com.landray.kmss.xpcommon.service.spring.XpSqlBaseOracleServiceImp;

/**
 * @author liquid
 */
public class XpEkpSqlErpServiceImp extends XpSqlBaseOracleServiceImp {
    @Override
    public String getDbSourceId() {
        return XpCompDbcpConstant.DB_SOURCE_ID_ERP;
    }
}

package com.landray.kmss.xp.modeling.app.it.service.spring;

import com.alibaba.fastjson.JSONObject;
import com.landray.kmss.util.IDGenerator;
import com.landray.kmss.util.StringUtil;
import com.landray.kmss.xp.util.XpStringUtil;
import com.landray.kmss.xpcommon.service.spring.XpSqlBaseServiceImp;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author liquid
 * @date 2022年10月10日
 */
public class XpEkpSqlEkpServiceImp extends XpSqlBaseServiceImp {
    @Override
    public String getDbSourceId() {
        return null;// ekp数据库不使用数据源中的链接信息
    }

    @Override
    public List executeQuery(String sql, JSONObject params) {
        NativeQuery query = super.getBaseDao().getHibernateSession().createNativeQuery(sql);
        params.keySet().parallelStream().forEach(key -> {
            // 如果是固定参数
            if ("_fixed".equals(key)) {
                JSONObject fixed = params.getJSONObject(key);
                Integer firstResult = fixed.getInteger("firstResult");
                Integer maxResults = fixed.getInteger("maxResults");
                if (firstResult != null) {
                    query.setFirstResult(fixed.getInteger("firstResult"));
                }
                if (maxResults != null) {
                    query.setMaxResults(fixed.getInteger("maxResults"));
                }
            }
            else {
                query.setParameter(key, params.get(key));
            }
        });
        return query.list();
    }

    @Override
    public List executeQueryToMap(String sql, JSONObject params) {
        NativeQuery query = super.getBaseDao().getHibernateSession().createNativeQuery(sql);
        params.keySet().parallelStream().forEach(key -> {
            // 如果是固定参数
            if ("_fixed".equals(key)) {
                JSONObject fixed = params.getJSONObject(key);
                query.setFirstResult(fixed.getInteger("firstResult"));
                query.setMaxResults(fixed.getInteger("maxResults"));
            }
            else {
                query.setParameter(key, params.get(key));
            }
        });
        return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

    @Override
    public String executeUpdate(List<String> sqlList, List<JSONObject> paramsList) {
        String rtnStatus;
        Transaction tx = null;
        try {
            Session session = super.getBaseDao().getHibernateSession();
            boolean needCommit = true;
            tx = session.getTransaction();// 获取事务对象(调用者未启动事务时返回一个新的事务)
            if (tx.isActive()) {// 机器人节点或定时任务中，事务已启动，且结束后会自动提交事务
                needCommit = false;
            } else {// 其它情况下，事务未启动，需begin
                tx.begin();
            }
            for (int i = 0; i < sqlList.size(); i++) {
                NativeQuery query = session.createSQLQuery(sqlList.get(i));
                JSONObject params = paramsList.get(i);
                for (String paramKey : params.keySet()) {// 设置sql参数值
                    query.setParameter(paramKey, params.get(paramKey));
                }
                query.executeUpdate();
            }
            if (needCommit) {
                tx.commit();// 事务提交
            }
            rtnStatus = XpStringUtil.CODE_SUCCESS;
        } catch (Exception e) {
            assert tx != null;
            tx.rollback();// 出错回滚
            throw new RuntimeException(e);
        }
        return rtnStatus;
    }

    @Override
    public String executeUpdate(String sql, JSONObject param) {
        if (StringUtil.isNull(sql)) {
            return XpStringUtil.CODE_ERROR;
        }
        List<String> sqlList = new ArrayList<>();
        List<JSONObject> paramList = new ArrayList<>();
        sqlList.add(sql);
        paramList.add(param);
        return executeUpdate(sqlList, paramList);
    }

    @Override
    public long getQueryMaxCount(String sql, JSONObject params) {
        // query.uniqueResult(); 用不了
        sql = "SELECT COUNT(*) FROM (" + sql + ") AS count_" + IDGenerator.generateID();
        NativeQuery query = super.getBaseDao().getHibernateSession().createNativeQuery(sql);
        params.keySet().parallelStream().forEach(key -> {
            // 如果不是固定参数
            if (!"_fixed".equals(key)) {
                query.setParameter(key, params.get(key));
            }
        });
        BigInteger count = (BigInteger) query.list().get(0);
        return count.intValue();
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <bean id="xpXformBaseDao"
          class="com.landray.kmss.xpcommon.dao.XpXformBaseDaoImp"
          parent="KmssBaseDao" abstract="true">
    </bean>

    <bean id="xpXformModelingModelMainBaseDao"
          class="com.landray.kmss.xpcommon.dao.XpXformModelingModelMainBaseDaoImp"
          parent="xpXformBaseDao">
    </bean>

    <bean id="xpXformModelingSimpleMainBaseDao"
          class="com.landray.kmss.xpcommon.dao.XpXformModelingSimpleMainBaseDaoImp"
          parent="xpXformBaseDao">
    </bean>

    <bean id="xpXformReviewBaseDao"
          class="com.landray.kmss.xpcommon.dao.XpXformReviewBaseDaoImp"
          parent="xpXformBaseDao">
    </bean>

    <bean id="xpSqlBaseTarget"
          class="com.landray.kmss.xpcommon.service.spring.XpSqlBaseServiceImp"
          parent="KmssBaseTarget" abstract="true">
    </bean>

    <bean id="xpSqlBaseOracleTarget"
          class="com.landray.kmss.xpcommon.service.spring.XpSqlBaseOracleServiceImp"
          parent="KmssBaseTarget" abstract="true">
    </bean>
</beans>

<?xml version="1.0" encoding="UTF-8"?>
<beans
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <bean
            id="xpEkpSqlEkpTarget"
            class="com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlEkpServiceImp"
            parent="xpSqlBaseTarget">
    </bean>
    <bean
            id="xpEkpSqlErpTarget"
            class="com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlErpServiceImp"
            parent="xpSqlBaseOracleTarget">
    </bean>
    <bean
            id="xpEkpSqlBpmTarget"
            class="com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlBpmServiceImp"
            parent="xpSqlBaseOracleTarget">
    </bean>

    <bean id="xpEkpSqlDefinitionDao"
          class="com.landray.kmss.xp.modeling.app.it.dao.XpEkpSqlDefinitionDaoImp"
          parent="xpXformModelingSimpleMainBaseDao">
        <property name="tableName" value="ekp_sql_definition"/>
    </bean>
    <bean id="xpEkpSqlDefinitionTarget"
          class="com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlDefinitionServiceImp">
    </bean>

    <bean id="xpEkpSqlMainWsTarget"
          class="com.landray.kmss.xp.modeling.app.it.webservice.XpEkpSqlMainWsImp">
    </bean>
</beans>

3、具体实现 - action

package com.landray.kmss.xp.modeling.app.it.actions;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.landray.kmss.common.actions.ExtendAction;
import com.landray.kmss.common.service.IBaseService;
import com.landray.kmss.sys.organization.model.SysOrgElement;
import com.landray.kmss.util.DateUtil;
import com.landray.kmss.util.SpringBeanUtil;
import com.landray.kmss.util.StringUtil;
import com.landray.kmss.util.UserUtil;
import com.landray.kmss.web.action.ActionForm;
import com.landray.kmss.web.action.ActionForward;
import com.landray.kmss.web.action.ActionMapping;
import com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlDefinitionServiceImp;
import com.landray.kmss.xp.modeling.app.it.util.XpSqlUtil;
import com.landray.kmss.xp.util.XpRequestUtil;
import com.landray.kmss.xp.util.XpStringUtil;
import com.landray.kmss.xpcommon.service.spring.XpSqlBaseServiceImp;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StopWatch;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.stream.Collectors;

import static com.landray.kmss.tic.core.common.actions.TicCoreInvokeFuncAction.ReadAsChars;

/**
 * @author liquid
 * @date 2022年10月10日
 */
public class XpEkpSqlAction extends ExtendAction {
    private static final Logger logger = LoggerFactory.getLogger(XpEkpSqlAction.class);
    private XpSqlBaseServiceImp xpEkpSqlService;
    private XpSqlBaseServiceImp xpErpSqlService;
    private XpSqlBaseServiceImp xpBpmSqlService;
    private XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService;

    public XpSqlBaseServiceImp getXpSqlBaseService(String dbSourceName) {
        if (Objects.equals(dbSourceName, "EKP")) {
            if(xpEkpSqlService == null) {
                xpEkpSqlService = (XpSqlBaseServiceImp) SpringBeanUtil.getBean("xpEkpSqlEkpTarget");
            }
            return xpEkpSqlService;
        } else if (Objects.equals(dbSourceName, "ERP")) {
            if(xpErpSqlService == null) {
                xpErpSqlService = (XpSqlBaseServiceImp) SpringBeanUtil.getBean("xpEkpSqlErpTarget");
            }
            return xpErpSqlService;
        } else if (Objects.equals(dbSourceName, "BPM")) {
            if(xpBpmSqlService == null) {
                xpBpmSqlService = (XpSqlBaseServiceImp) SpringBeanUtil.getBean("xpEkpSqlBpmTarget");
            }
            return xpBpmSqlService;
        }
        return null;
    }

    public XpEkpSqlDefinitionServiceImp getXpEkpSqlDefinitionService() {
        if (xpEkpSqlDefinitionService == null) {
            xpEkpSqlDefinitionService = (XpEkpSqlDefinitionServiceImp) SpringBeanUtil.getBean("xpEkpSqlDefinitionTarget");
        }
        return xpEkpSqlDefinitionService;
    }

    @Override
    protected IBaseService getServiceImp(HttpServletRequest request) {
        return null;
    }

    /**
     * @apiNote 执行ekpSql获取查询结果
     * @author liquid
     * @date 2022年10月11日
     * 传入参数-
     * key ekpSql关键字
     * params 传入参数(json字符串)
     * returnType (可选:不传入则默认返回array)回传数据类型(array、json)
     * 返回参数-
     * queryData 查询结果数据
     */
    public ActionForward executeQuery(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        JSONObject result = new JSONObject();
        response.setHeader("Content-Type", "application/json;charset=utf-8");
        String bodyStr = ReadAsChars(request);
        JSONObject o = JSONObject.parseObject(bodyStr);
        String invokeDatetime = DateUtil.convertDateToString(new Date(), "yyyy-MM-dd HH:mm:ss.SSS");
        StopWatch sw = new StopWatch();

        /* 获取body参数 */
        sw.start("task_1");
        String key = o.getString("key");
        JSONObject params = JSONObject.parseObject(o.getString("params"));
        String returnType = o.getString("returnType");
        // 不传入params参数，另其为空JSONObject，避免后续空引用错误
        params = params == null ? new JSONObject() : params;
        // 参数校验
        if (StringUtil.isNull(key)) {
            XpRequestUtil.setFailure(response, "调用失败：请检查参数[名]和[值]");
            return null;
        }

        /* 业务逻辑 */
        XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService = getXpEkpSqlDefinitionService();
        Map definitionData = xpEkpSqlDefinitionService.getSelectedColumnsByKey(key);
        if (definitionData == null || definitionData.size() == 0) {
            XpRequestUtil.setFailure(response, "调用失败：关键字错误、不存在");
            return null;
        }
        // 调用权限检查(调用XpEkpSqlServiceImp方法)
        boolean isAuthorized = xpEkpSqlDefinitionService.isAuthorized((List<SysOrgElement>) definitionData.get("fd_authorizing"), true);
        if (!isAuthorized) {
            XpRequestUtil.setFailure(response, "调用失败：该用户无权限调用");
            return null;
        }
        String sql = (String) definitionData.get("fd_sql");
        // sql参数校验
        if (!XpSqlUtil.checkSqlParamsNotCheckFixed(sql, params)) {
            XpRequestUtil.setFailure(response, "调用失败：sql与传入的参数不匹配");
            return null;
        }
        // 设置动态sql片段
        sql = XpSqlUtil.setDynamicSql(sql, params);
        // 执行sql
        String dbSource = (String) definitionData.get("fd_db_source");
        XpSqlBaseServiceImp xpSqlService = getXpSqlBaseService(dbSource);
        // erp、bpm的sql参数，JSONArray转数组
        if ("ERP".equals(dbSource) || "BPM".equals(dbSource)) {
            JSONObject finalParams = params;
            JSONObject newParams = new JSONObject();
            finalParams.keySet().forEach(paramKey -> {
                Object obj = finalParams.get(paramKey);
                if (obj instanceof JSONArray) {
                    newParams.put(paramKey, ((JSONArray) obj).toArray());
                } else {
                    newParams.put(paramKey, obj);
                }
            });
            params = newParams;
        }
        // 开始执行sql
        Object queryData;
        if (StringUtil.isNotNull(returnType) && "json".equals(returnType)) {// 返回json格式
            queryData = xpSqlService.executeQueryToMap(sql, params);
        } else {// 返回数组格式
            queryData = xpSqlService.executeQuery(sql, params);
        }
        result.put("queryData",queryData);
        result.put("queryMaxCount", xpSqlService.getQueryMaxCount(sql, params));

        /* 响应数据 */
        result.put("status", XpRequestUtil.STATUS_SUCCESS);
        result.put("code", XpRequestUtil.CODE_SUCCESS);
        result.put("msg", "调用成功");
        XpRequestUtil.setResponseResult(response, result);
        sw.stop();

        /* 后续业务逻辑 */
        // 打印输出真正执行的sql语句
        String actualSql = XpSqlUtil.getActualSql(sql, params);
        String sqlKey = (String) definitionData.get("fd_key");
        if ("Y".equals(definitionData.get("fd_print_sql"))) {
            System.out.println(sqlKey + "：" + actualSql);
        }
        // 输出调用记录日志到文件
        if ("Y".equals(definitionData.get("fd_call_record"))) {
            Map<String,Object> callRecord = new HashMap<>();
            callRecord.put("fd_key", sqlKey);
            callRecord.put("fd_invoker", UserUtil.getUser().getFdId());
            callRecord.put("fd_invoke_datetime", invokeDatetime);
            callRecord.put("fd_consuming_time", String.valueOf(sw.getTotalTimeMillis()));
            callRecord.put("fd_executed_sql", actualSql);
            logger.info("ekpSql调用记录：" + callRecord);
        }

        return null;
    }

    /**
     * @apiNote 执行ekpSql写入操作(仅支持对同一数据源进行操作)
     * @author liquid
     * @date 2023年2月6日
     * 传入参数-
     * keyOrder: sqlKey列表(以分号分隔)，用以表示执行顺序
     * sqlParams: sqlParams.md
     * 返回参数-
     * updateStatus: 1 sql执行成功  0 sql执行失败
     */
    public ActionForward executeUpdate(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        JSONObject result = new JSONObject();
        response.setHeader("Content-Type", "application/json;charset=utf-8");
        String bodyStr = ReadAsChars(request);
        JSONObject o = JSONObject.parseObject(bodyStr);
        String invokeDatetime = DateUtil.convertDateToString(new Date(), "yyyy-MM-dd HH:mm:ss.SSS");
        StopWatch sw = new StopWatch();

        /* 获取body参数 */
        sw.start("task_1");
        String keyOrder = o.getString("keyOrder");
        String sqlParamsStr = o.getString("sqlParams");
        JSONObject sqlParam = JSONObject.parseObject(sqlParamsStr);
        // 不传入params参数，另其为空JSONObject，避免后续空引用错误
        sqlParam = sqlParam == null ? new JSONObject() : sqlParam;
        // 参数校验
        if (StringUtil.isNull(keyOrder) || StringUtil.isNull(sqlParamsStr)) {
            XpRequestUtil.setFailure(response, "调用失败：请检查参数[名]和[值]");
            return null;
        }

        /* 业务逻辑 */
        XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService = getXpEkpSqlDefinitionService();
        Map<String, Map<String, Object>> definitionDataMap;
        // 关键字列表
        List<String> sqlKeyList = Arrays.stream(keyOrder.split(XpStringUtil.SEMICOLON)).distinct().collect(Collectors.toList());
        // 将关键字对应的数据拿出来
        definitionDataMap = xpEkpSqlDefinitionService.getSelectedColumnsByKeyList(sqlKeyList);
        if (definitionDataMap == null || definitionDataMap.size() == 0
                || definitionDataMap.size() != sqlKeyList.size()) {
            // 根据传入的sqlKey拿到空的sql定义，或sqlKey数量和sql定义数量不匹配
            XpRequestUtil.setFailure(response, "调用失败：关键字错误、不存在");
            return null;
        }
        // 调用权限检查(调用XpEkpSqlServiceImp方法)
        for (String sqlKey : sqlKeyList) {
            Map<String, Object> definitionData = definitionDataMap.get(sqlKey);
            if (definitionData == null) {
                continue;
            }
            boolean isAuthorized = xpEkpSqlDefinitionService.isAuthorized((List<SysOrgElement>) definitionData.get("fd_authorizing"), true);
            if (!isAuthorized) {
                XpRequestUtil.setFailure(response, "调用失败：该用户无权限调用");
                return null;
            }
        }
        // sql参数校验 & 设置动态sql片段
        for (String sqlKey : sqlKeyList) {
            String sql = (String) definitionDataMap.get(sqlKey).get("fd_sql");
            JSONArray params = sqlParam.getJSONArray(sqlKey);
            for (Object param : params) {
                // sql参数校验
                boolean isValidParam = XpSqlUtil.checkSqlParamsNotCheckFixed(sql, (JSONObject) param);
                if (!isValidParam) {
                    XpRequestUtil.setFailure(response, "调用失败：sql与传入的参数不匹配");
                    return null;
                }
                // 设置动态sql片段
                definitionDataMap.get(sqlKey).put("fd_sql", XpSqlUtil.setDynamicSql(sql, (JSONObject) param));
            }
        }
        // sql和参数
        String dbSourceName = null;
        List<String> sqlList = new ArrayList<>();
        List<JSONObject> paramsList = new ArrayList<>();
        for (String sqlKey : sqlKeyList) {
            String sql = (String) definitionDataMap.get(sqlKey).get("fd_sql");
            dbSourceName = (String) definitionDataMap.get(sqlKey).get("fd_db_source");
            JSONArray paramArr = sqlParam.getJSONArray(sqlKey);
            for (Object param : paramArr) {
                sqlList.add(sql);
                // erp、bpm的sql参数，JSONArray转数组
                if ("ERP".equals(dbSourceName) || "BPM".equals(dbSourceName)) {
                    JSONObject newParam = new JSONObject();
                    JSONObject finalParams = (JSONObject) param;
                    finalParams.keySet().forEach(paramKey -> {
                        Object obj = finalParams.get(paramKey);
                        if (obj instanceof JSONArray) {
                            newParam.put(paramKey, ((JSONArray) obj).toArray());
                        } else {
                            newParam.put(paramKey, obj);
                        }
                    });
                    paramsList.add(newParam);
                } else {
                    paramsList.add((JSONObject) param);
                }
            }
        }
        // 执行sql
        XpSqlBaseServiceImp xpSqlService = getXpSqlBaseService(dbSourceName);
        result.put("updateStatus", xpSqlService.executeUpdate(sqlList, paramsList));

        /* 响应数据 */
        result.put("status", XpRequestUtil.STATUS_SUCCESS);
        result.put("code", XpRequestUtil.CODE_SUCCESS);
        result.put("msg", "调用成功");
        XpRequestUtil.setResponseResult(response, result);
        sw.stop();

        /* 后续业务逻辑 */
        // 打印输出真正执行的sql语句
        for (String sqlKey : sqlKeyList) {
            Map<String, Object> definitionData = definitionDataMap.get(sqlKey);
            if ("Y".equals(definitionData.get("fd_print_sql"))) {
                String sql = (String) definitionData.get("fd_sql");
                JSONArray paramArr = (JSONArray) sqlParam.get(sqlKey);
                for (Object param : paramArr) {
                    System.out.println(sqlKey + "：" + XpSqlUtil.getActualSql(sql, (JSONObject) param));
                }
            }
        }
        // 输出调用记录日志到文件
        for (String sqlKey : sqlKeyList) {
            Map<String, Object> definitionData = definitionDataMap.get(sqlKey);
            if ("Y".equals(definitionData.get("fd_call_record"))) {
                String sql = (String) definitionData.get("fd_sql");
                JSONArray paramArr = (JSONArray) sqlParam.get(sqlKey);
                for (Object param : paramArr) {
                    String actualSql = XpSqlUtil.getActualSql(sql, (JSONObject) param);
                    Map<String,Object> callRecord = new HashMap<>();
                    callRecord.put("fd_key", sqlKey);
                    callRecord.put("fd_invoker", UserUtil.getUser().getFdId());
                    callRecord.put("fd_invoke_datetime", invokeDatetime);
                    callRecord.put("fd_consuming_time", String.valueOf(sw.getTotalTimeMillis()));
                    callRecord.put("fd_executed_sql", actualSql);
                    logger.info("ekpSql调用记录：" + callRecord);
                }
            }
        }

        return null;
    }
}

package com.landray.kmss.xp.modeling.app.it.actions;

import com.alibaba.fastjson.JSONObject;
import com.landray.kmss.common.actions.ExtendAction;
import com.landray.kmss.common.service.IBaseService;
import com.landray.kmss.util.SpringBeanUtil;
import com.landray.kmss.util.StringUtil;
import com.landray.kmss.web.action.ActionForm;
import com.landray.kmss.web.action.ActionForward;
import com.landray.kmss.web.action.ActionMapping;
import com.landray.kmss.xp.modeling.app.it.dao.XpEkpSqlDefinitionDaoImp;
import com.landray.kmss.xp.modeling.app.it.service.spring.XpEkpSqlDefinitionServiceImp;
import com.landray.kmss.xp.util.XpRequestUtil;
import com.landray.kmss.xp.xform.service.spring.XpXformTemplateServiceImp;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.util.List;
import java.util.Map;

import static com.landray.kmss.tic.core.common.actions.TicCoreInvokeFuncAction.ReadAsChars;

/**
 * @author liquid
 */
public class XpEkpSqlDefinitionAction extends ExtendAction {
    private static XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService;
    private static XpXformTemplateServiceImp xpXformTemplateServiceImp;

    public static XpEkpSqlDefinitionServiceImp getXpEkpSqlDefinitionService() {
        if (xpEkpSqlDefinitionService == null) {
            xpEkpSqlDefinitionService = (XpEkpSqlDefinitionServiceImp) SpringBeanUtil.getBean("xpEkpSqlDefinitionTarget");
        }
        return xpEkpSqlDefinitionService;
    }

    public static XpXformTemplateServiceImp getXpXformTemplateServiceImp() {
        if (xpXformTemplateServiceImp == null) {
            xpXformTemplateServiceImp = (XpXformTemplateServiceImp) SpringBeanUtil.getBean("xpXformTemplateTarget");
        }
        return xpXformTemplateServiceImp;
    }

    @Override
    protected IBaseService getServiceImp(HttpServletRequest request) {
        return null;
    }

    /**
     * @apiNote 检查关键字唯一性
     * @author liquid
     * @date 2022年10月11日
     * 传入参数-
     * id 表单id(更新数据时不为空)
     * key 关键字
     * 返回参数-
     * unique 是否唯一
     */
    public ActionForward checkKeyUnique(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        JSONObject result = new JSONObject();
        response.setHeader("Content-Type", "application/json;charset=utf-8");
        String bodyStr = ReadAsChars(request);
        JSONObject o = JSONObject.parseObject(bodyStr);

        /* 获取body参数 */
        String id = o.getString("id");
        String key = o.getString("key");
        // 参数校验
        if (StringUtil.isNull(key)) {
            XpRequestUtil.setFailure(response, "调用失败：请检查参数[名]和[值]");
            return null;
        }

        /* 业务逻辑 */
        XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService = getXpEkpSqlDefinitionService();
        boolean unique = xpEkpSqlDefinitionService.checkKeyUnique(id, key);
        result.put("unique", unique);

        /* 响应数据 */
        result.put("status", XpRequestUtil.STATUS_SUCCESS);
        result.put("code", XpRequestUtil.CODE_SUCCESS);
        result.put("msg", "调用成功");
        XpRequestUtil.setResponseResult(response, result);

        return null;
    }

    /**
     * @apiNote 获取开窗配置JSON字符串
     * @author liquid
     * @date 2022年11月17日
     * 传入参数-
     * key 关键字
     * 返回参数-
     * openWinConfigJson 开窗配置JSON字符串
     */
    public ActionForward getOpenWinConfigJson(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        JSONObject result = new JSONObject();
        response.setHeader("Content-Type", "application/json;charset=utf-8");
        String bodyStr = ReadAsChars(request);
        JSONObject o = JSONObject.parseObject(bodyStr);

        /* 获取body参数 */
        String key = o.getString("key");
        // 参数校验
        if (StringUtil.isNull(key)) {
            XpRequestUtil.setFailure(response, "调用失败：请检查参数[名]和[值]");
            return null;
        }

        /* 业务逻辑 */
        XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService = getXpEkpSqlDefinitionService();
        Object[] data = (Object[]) xpEkpSqlDefinitionService.getColumnByKey(key, "fdId,fd_openwin_config_json");
        result.put("fdId", data[0]);
        result.put("openWinConfigJson", data[1]);

        /* 响应数据 */
        result.put("status", XpRequestUtil.STATUS_SUCCESS);
        result.put("code", XpRequestUtil.CODE_SUCCESS);
        result.put("msg", "调用成功");
        XpRequestUtil.setResponseResult(response, result);

        return null;
    }

    /**
     * @apiNote 获取在系统用已引用某sql的位置(最新版的xform模板信息)
     * @author liquid
     * @date 2023年2月21日
     * 传入参数-
     * key 关键字
     * 返回参数-
     * xformTemplateInfo 已引用的xform模板信息
     */
    public ActionForward getWhereCall(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        JSONObject result = new JSONObject();
        response.setHeader("Content-Type", "application/json;charset=utf-8");
        String bodyStr = ReadAsChars(request);
        JSONObject o = JSONObject.parseObject(bodyStr);

        /* 获取body参数 */
        String key = o.getString("key");
        // 参数校验
        if (StringUtil.isNull(key)) {
            XpRequestUtil.setFailure(response, "调用失败：请检查参数[名]和[值]");
            return null;
        }
        // 判断key是否存在
        XpEkpSqlDefinitionServiceImp xpEkpSqlDefinitionService = getXpEkpSqlDefinitionService();
        String definition = (String) xpEkpSqlDefinitionService.getColumnByKey(key, "fdId");
        if (definition == null) {
            XpRequestUtil.setFailure(response, "调用失败：关键字不存在");
            return null;
        }

        /* 业务逻辑 */
        XpXformTemplateServiceImp xpXformTemplateServiceImp = getXpXformTemplateServiceImp();
        List<Map<String, Object>> xformTemplateInfo1 = xpXformTemplateServiceImp.getXfromTemplateInfoByDesignerHtml("'" + key + "'");
        List<Map<String, Object>> xformTemplateInfo2 = xpXformTemplateServiceImp.getXfromTemplateInfoByDesignerHtml("&quot;" + key + "&quot;");
        xformTemplateInfo1.addAll(xformTemplateInfo2);
        result.put("xformTemplateInfo", xformTemplateInfo1);

        /* 响应数据 */
        result.put("status", XpRequestUtil.STATUS_SUCCESS);
        result.put("code", XpRequestUtil.CODE_SUCCESS);
        result.put("msg", "调用成功");
        XpRequestUtil.setResponseResult(response, result);

        return null;
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean
            class="com.landray.kmss.xp.modeling.app.it.actions.XpModelingItMainAction"
            name="/xp/modeling/app/it/actions/XpModelingItMainAction.do"
            lazy-init="true"
            parent="KmssBaseAction">
    </bean>
    <bean
            class="com.landray.kmss.xp.modeling.app.it.actions.XpEkpSqlAction"
            name="/xp/modeling/app/it/actions/XpEkpSqlAction.do"
            lazy-init="true"
            parent="KmssBaseAction">
    </bean>
    <bean
            class="com.landray.kmss.xp.modeling.app.it.actions.XpEkpSqlDefinitionAction"
            name="/xp/modeling/app/it/actions/XpEkpSqlDefinitionAction.do"
            lazy-init="true"
            parent="KmssBaseAction">
    </bean>
</beans>

4、前端调用

// ekpSql调用 com.landray.kmss.xp.modeling.app.it.actions.XpEkpSqlAction.executeEkpSql
ajaxActionPms("xp/modeling/app/it/actions/XpEkpSqlAction.do?method=executeQuery", {
    key: "ekpsql_testSql001",
    params: {
        name:"黎令魁"
    }
}).then((res)=>{
    if (res.status !== "success") { errorHandler(res); return; }
    // 成功回调
    console.log(res);
}).catch((error)=>{errorHandler(error);})



// 以下函数可作为公用函数
/**
 * 调用ekp后台java方法 - promise回调方式(需返回基本数据类型时，请使用非promise方式)
 * @author liquid
 * @date 2022年6月8日
 * @param {string} path pring-mvc.xml中定义的bean.name+方法名
 * 例如：xp/org/organizational/actions/XpOrgMainAction.do?method=getFdHierarchyId
 * @param {object} data ajax.data，无需序列化
 * @return {Promise<unknown>}
 */
function ajaxActionPms(path, data) {
    return new Promise((resolve, reject) => {
        $.ajax({
            type: "POST",
            contentType:"application/json;charset=utf-8",
            url: Com_Parameter.ContextPath + path,
            data: JSON.stringify(data),
            dataType:"json",
            async: true,
            success: (res)=>{resolve(res)},
            error: (error)=>{reject(error)}
        })
    });
}

/**
 * 错误通用处理(建议在随机性错误处调用，以便开发人员排错)
 * @author liquid
 * @date 2022年5月31日
 * @param {object} obj 错误信息对象
 * @param {boolean} [isAlert=true] 是否alert相关错误信息
 * @return {void}
 */
function errorHandler(obj, isAlert) {
    isAlert = typeof isAlert === "undefined";
    const fileRow = getFileRow();
    if (isAlert) {
        alert("file: " + fileRow.file + "\nrow: " + fileRow.row + "\nmsg: " + JSON.stringify(obj));
    }
    console.log("--------------ERROR------------")
    console.log("msg", obj);
    console.log("file", fileRow.file);
    console.log("row", fileRow.row);
    console.log("--------------ERROR------------")
}

Liquid-Li

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
2
评论
EKP其它/EKPSQL工具

有时候想在前端快速查询一些数据，ekp自动的第三方集成jdbc也可以用，但怎么说呢，总之用得很不爽！所以就有了以下的扩展。功能大概是这样的：前端写好纯sql，前端根据关键字传入参数进行调用，获取到查询结果
复制链接

扫一扫