net.mingsoft.mdiy.biz.impl.ModelBizImpl Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of ms-mdiy Show documentation
The newest version!
/**
 * Copyright (c) 2012-present 铭软科技(mingsoft.net)
 * 本软件及相关文档文件（以下简称“软件”）的版权归 铭软科技 所有
 * 遵循 铭软科技《服务协议》中的《保密条款》
 */




package net.mingsoft.mdiy.biz.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import net.mingsoft.base.biz.impl.BaseBizImpl;
import net.mingsoft.base.dao.IBaseDao;
import net.mingsoft.base.exception.BusinessException;
import net.mingsoft.base.util.BundleUtil;
import net.mingsoft.base.util.SqlInjectionUtil;
import net.mingsoft.basic.util.BasicUtil;
import net.mingsoft.mdiy.bean.ModelJsonBean;
import net.mingsoft.mdiy.biz.IConfigBiz;
import net.mingsoft.mdiy.biz.IModelBiz;
import net.mingsoft.mdiy.constant.e.ModelCustomTypeEnum;
import net.mingsoft.mdiy.dao.IModelDao;
import net.mingsoft.mdiy.entity.ConfigEntity;
import net.mingsoft.mdiy.entity.ModelEntity;
import net.mingsoft.mdiy.util.ConfigUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 自定义表单接口实现类
 *
 * @author 铭软
 * @version 版本号：100-000-000

 * 创建日期：2012-03-15

 * 历史修订：2022-1-21 modelDao.excuteSql(sql); 只有创建表和更新表可以执行
 * 历史修订：2023-12-12 重写base的一些sql执行方法; 方便对自定义相关操作做一些切面处理
 */
@Service("mdiyModelBizImpl")
@Transactional(rollbackFor = Exception.class)
public class ModelBizImpl extends BaseBizImpl implements IModelBiz {

    /**
     * 注入自定义表单持久化层
     */
    @Autowired
    private IModelDao modelDao;

    @Autowired
    private IConfigBiz configBiz;


    @Override
    protected IBaseDao getDao() {
        
        return modelDao;
    }

    @Override
    public boolean importConfig(String customType, ModelJsonBean modelJsonBean) {
        if (StringUtils.isEmpty(customType) || modelJsonBean == null) {
            return false;
        }
        return this.importModel(customType, modelJsonBean, "");
    }


    @Override
    public boolean importModel(String customType, ModelJsonBean modelJsonBean, String modelType) {
        if (StringUtils.isEmpty(customType) || modelJsonBean == null) {
            return false;
        }
        if (StringUtils.isBlank(modelJsonBean.getTitle())){
            return false;
        }
        // 判断导入的模型业务类型一致的情况下，判断模型名 或 表名是否存在
        LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(StringUtils.isNotBlank(customType),ModelEntity::getModelCustomType,customType)
                .and(wrapper -> wrapper.eq(ModelEntity::getModelName,modelJsonBean.getTitle())
                        .or().eq(StrUtil.isNotBlank(modelJsonBean.getTableName()),ModelEntity::getModelTableName,"mdiy_"+customType+"_"+modelJsonBean.getTableName()));

        List modelEntities = this.list(queryWrapper);
        //判断表名是否存在
        if (CollectionUtil.isNotEmpty(modelEntities)) {
            return false;
        }
        ModelEntity model = new ModelEntity();
        model.setModelName(modelJsonBean.getTitle());
        model.setModelTableName(modelJsonBean.getTableName());
        model.setModelCustomType(customType);
        model.setModelIdType(modelJsonBean.getId());

        // 兼容oracle 放行触发器和序列sql
        String tmpSql = modelJsonBean.getSql();
        Pattern compile = Pattern.compile(";[\\s\\S]*?(CREATE OR REPLACE[\\s\\S]*;)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = compile.matcher(tmpSql);
        if (matcher.find()) {
            tmpSql = matcher.group(1);
            // 将被匹配的sql从bean中移除
            modelJsonBean.setSql(modelJsonBean.getSql().replace(tmpSql,""));
        }else {
            tmpSql = "";
        }
        if (customType.equalsIgnoreCase(ModelCustomTypeEnum.MODEL.getLabel())) {
            //在表名前面拼接前缀
            model.setModelTableName("MDIY_MODEL_" + modelJsonBean.getTableName());

            tmpSql = tmpSql.replace("{model}", "MDIY_MODEL_");

            //创建表
            if (StringUtils.isNotBlank(modelJsonBean.getSql())){
                String[] modelSqls = modelJsonBean.getSql().replace("{model}", "MDIY_MODEL_").trim().split(";");
                for (String sql : modelSqls) {

                    // 只允许创建、修改当前导入模型名称的表
                    if (StringUtils.containsAnyIgnoreCase(sql,"CREATE TABLE","ALTER TABLE")){
                        String createRegex = "CREATE[\\s]*TABLE[\\s\\S]*MDIY_MODEL_"+ modelJsonBean.getTableName().toUpperCase();
                        String alterRegex = "ALTER[\\s]*TABLE[\\s\\S]*MDIY_MODEL_"+ modelJsonBean.getTableName().toUpperCase();
                        Pattern createPattern = Pattern.compile(createRegex);
                        Pattern alterPattern = Pattern.compile(alterRegex);
                        if (createPattern.matcher(sql.toUpperCase()).find() || alterPattern.matcher(sql.toUpperCase()).find()){
                            modelDao.excuteSql(sql);
                            continue;
                        }
                    }

                    //insert、delete和DROP TABLE 语句不能执行
                    if (StringUtils.isBlank(sql) || StrUtil.containsAnyIgnoreCase(sql,"INSERT ","SELECT ","UPDATE ","DELETE ", "DROP ", "ALTER ", "TRUNCATE ","RENAME ")) {
                        continue;
                    }

                    modelDao.excuteSql(sql);
                }
            }
        }
        if (customType.equalsIgnoreCase(ModelCustomTypeEnum.FORM.getLabel())) {
            //在表名前面拼接前缀
            model.setModelTableName("MDIY_FORM_" + modelJsonBean.getTableName());
            // 自定义业务移除link_id
            // 批注：这里可能还会进行优化——使用正则来进行替换,这样的好处是代码生成器字段有变化的时候不会影响这里
            //TODO 考虑自定义业务上复制json导入模型会失去link_id字段，这里不做link_id移除处理
//            modelJsonBean.setSql(modelJsonBean.getSql().replaceAll("\\W?LINK_ID\\W? \\w+\\(\\d+\\) DEFAULT NULL,",""));
            modelJsonBean.setSql(modelJsonBean.getSql().replaceAll("ALTER TABLE[\\s\\S]*?UNIQUE[\\s\\S]*?;",""));

            tmpSql = tmpSql.replace("{model}", "MDIY_FORM_");

            //创建表
            String[] formSqls = modelJsonBean.getSql().replace("{model}", "MDIY_FORM_").trim().split(";");
            for (String sql : formSqls) {

                // 只允许创建、修改当前导入模型名称的表
                if (StrUtil.containsAnyIgnoreCase(sql,"CREATE TABLE","ALTER TABLE")){
                    String createRegex = "CREATE[\\s]*TABLE[\\s\\S]*MDIY_FORM_"+ modelJsonBean.getTableName().toUpperCase();
                    String alterRegex = "ALTER[\\s]*TABLE[\\s\\S]*MDIY_FORM_"+ modelJsonBean.getTableName().toUpperCase();
                    Pattern createPattern = Pattern.compile(createRegex);
                    Pattern alterPattern = Pattern.compile(alterRegex);
                    if (createPattern.matcher(sql.toUpperCase()).find() || alterPattern.matcher(sql.toUpperCase()).find()){
                        modelDao.excuteSql(sql);
                        continue;
                    }
                }

                //insert、delete和DROP TABLE 语句不能执行 // 跳过link_id
                if (StringUtils.isBlank(sql) || StrUtil.containsAnyIgnoreCase(sql,"INSERT ","SELECT ","UPDATE ","DELETE ", "DROP ", "ALTER ", "TRUNCATE ","RENAME ", "UNIQUE_LINK_ID`(`LINK_ID`)", "UNIQUE(\"LINK_ID\")", "DROP TABLE ")) {
                    //oracle会存在create里面有select，具体看代码生成器
                    if(sql.indexOf("CREATE") < 0) {
                        continue;
                    }
                }


                //oracle需要分号结束
                sql = sql.replace("FROM dual","FROM dual;").replace(" END"," END;");
                try {
                    modelDao.excuteSql(sql);
                } catch (Exception e){
                    LOG.debug("恶意执行 sql: {}",sql);
                    e.printStackTrace();

                }

                LOG.debug(sql);
            }
        }
        // 执行触发器和序列sql
        if (StringUtils.isNotBlank(tmpSql)){
            modelDao.excuteSql(tmpSql);
        }
        Map json = new HashMap();
        json.put("html", modelJsonBean.getHtml());
        json.put("searchJson", modelJsonBean.getSearchJson());
        json.put("script", modelJsonBean.getScript());
        json.put("isWebSubmit", modelJsonBean.isWebSubmit());
        json.put("isWebCode", modelJsonBean.isWebCode());
        json.put("id", modelJsonBean.getId());

        //因为ModelAop会进行站群插件id拼接，保存模型的时候需要还原最原始的表名称，方便复制JSON模型使用
        if(BasicUtil.getWebsiteApp() != null) {
            json.put("sql", modelJsonBean.getSql().replace("_"+BasicUtil.getWebsiteApp().getId(),""));
            json.put("tableName", modelJsonBean.getTableName().replace("_"+BasicUtil.getWebsiteApp().getId(),""));
        } else {
            json.put("tableName", modelJsonBean.getTableName());
            json.put("sql", modelJsonBean.getSql());
        }

        json.put("form", modelJsonBean.getForm());
        model.setModelField(modelJsonBean.getField());
        model.setModelType(modelType);
        model.setModelJson(JSONUtil.toJsonStr(json));
        model.setCreateDate(new Date());
        //保存自定义模型实体
        super.save(model);
        return true;
    }

    @Override
    public boolean updateConfig(String modelId, ModelJsonBean modelJsonBean) {
        if (StringUtils.isEmpty(modelId) || modelJsonBean == null) {
            return false;
        }
        return this.updateConfig(modelId, modelJsonBean, "");
    }

    @Override
    public boolean updateConfig(String modelId, ModelJsonBean modelJsonBean, String modelType) {
        if (StringUtils.isEmpty(modelId) || modelJsonBean == null) {
            return false;
        }
        ModelEntity modelEntity = super.getById(modelId);
        if (ObjectUtil.isNull(modelEntity)) {
            return false;
        }

        //模型名称必须唯一，需要进行查询判断
        ModelEntity model = new ModelEntity();
        model.setModelName(modelJsonBean.getTitle());
        model.setModelCustomType(modelEntity.getModelCustomType());
        ModelEntity oldModel = super.getOne(new QueryWrapper<>(model));
        //判断表名是否存在
        if (ObjectUtil.isNotNull(oldModel) && !modelEntity.getId().equals(oldModel.getId())) {
            return false;
        }

        // 原始表名
        String oldTableName = modelEntity.getModelTableName();
        // 表名重命名
        String rename = "ALTER  TABLE {} RENAME TO {};";
        if (modelEntity.getModelCustomType().equalsIgnoreCase(ModelCustomTypeEnum.MODEL.getLabel())) {
            //在表名前面拼接前缀
            modelEntity.setModelTableName(("MDIY_MODEL_" + modelJsonBean.getTableName()).toUpperCase());
            updateTable(modelEntity.getModelField(), modelJsonBean.getField(), modelEntity.getModelTableName());

            if (!oldTableName.equals(modelEntity.getModelTableName())) {
                rename = StrUtil.format(rename, oldTableName, modelEntity.getModelTableName());
                //insert、delete和DROP TABLE 语句不能执行
                if (StringUtils.isNotBlank(rename) && !StrUtil.containsAnyIgnoreCase(rename,"INSERT ","SELECT ","UPDATE ","DELETE ", "DROP ", "TRUNCATE ","RENAME ")) {
                    modelDao.excuteSql(rename);
                }
            }
        }
        if (modelEntity.getModelCustomType().equalsIgnoreCase(ModelCustomTypeEnum.FORM.getLabel())) {
            //在表名前面拼接前缀
            modelEntity.setModelTableName(("MDIY_FORM_" + modelJsonBean.getTableName()).toUpperCase());
            if (StringUtils.isNotBlank( modelJsonBean.getTableName()) && !oldTableName.equals(modelEntity.getModelTableName())) {
                // TODO: 2023/10/25 更新模型 不允许表名修改 
                throw new BusinessException(BundleUtil.getBaseString("err.error",
                        BundleUtil.getString(net.mingsoft.mdiy.constant.Const.RESOURCES,"table.name")));
            }
            updateTable(modelEntity.getModelField(), modelJsonBean.getField(), oldTableName);

        }
        // 更新业务信息
        Map json = new HashMap();
        json.put("html", modelJsonBean.getHtml());
        json.put("searchJson", modelJsonBean.getSearchJson());
        json.put("script", modelJsonBean.getScript());
        json.put("isWebSubmit", modelJsonBean.isWebSubmit());
        json.put("isWebCode", modelJsonBean.isWebCode());
        json.put("form", modelJsonBean.getForm());
        json.put("id", modelJsonBean.getId());

        //因为ModelAop会进行站群插件id拼接，保存模型的时候需要还原最原始的表名称，方便复制JSON模型使用
        if(BasicUtil.getWebsiteApp() != null) {
            if(StringUtils.isNotEmpty( modelJsonBean.getSql())) {
                json.put("sql", modelJsonBean.getSql().replace("_"+BasicUtil.getWebsiteApp().getId(),""));
            }

            json.put("tableName", modelJsonBean.getTableName().replace("_"+BasicUtil.getWebsiteApp().getId(),""));
        } else {
            if(StringUtils.isNotEmpty( modelJsonBean.getSql())) {
                json.put("sql", modelJsonBean.getSql());
            }
            json.put("tableName", modelJsonBean.getTableName());
        }
        if(modelJsonBean.getField()!=null) {
            modelEntity.setModelField(modelJsonBean.getField());
        } else {
            modelEntity.setModelField("[]");
        }

        modelEntity.setModelName(modelJsonBean.getTitle());
        modelEntity.setModelType(modelType);
        modelEntity.setModelJson(JSONUtil.toJsonStr(json));
        modelEntity.setUpdateDate(new Date());
        //保存自定义模型实体
        super.updateById(modelEntity);
        List mapList = JSONUtil.toList(JSONUtil.parseArray(modelJsonBean.getField()), Map.class);
        List fieldList = mapList.stream().map(map -> StrUtil.toCamelCase(map.get("field").toString().toLowerCase())).collect(Collectors.toList());
        fieldList.add("linkId");
        fieldList.add("modelId");
        ConfigEntity configEntity =  configBiz.getOne(new QueryWrapper().eq("config_name",modelEntity.getModelName()));
        Map map = ConfigUtil.getMap(modelEntity.getModelName());

        if (CollUtil.isNotEmpty(map)) {
            Object[] keys = map.keySet().toArray();
            for (Object key : keys) {
                if (!fieldList.contains(key)){
                    map.remove(key);
                }
            }
            configEntity.setConfigData(JSONUtil.toJsonStr(map));
            configBiz.updateById(configEntity);
        }
        return true;
    }

    /**
     * 批量删除，并且删除表
     * @param ids
     * @return
     */
    @Override
    public boolean delete(List ids) {
        for (String id : ids) {
            ModelEntity modelEntity = super.getById(id);
            boolean flag = super.removeById(id);
            if (!flag) {
                LOG.debug("{}删除失败", modelEntity.getModelTableName());
                break;
            } else {
                try {
                    modelDao.dropTable(modelEntity.getModelTableName());
                } catch (Exception e){
                    LOG.debug("{}表不存在", modelEntity.getModelTableName());
                    e.printStackTrace();
                    continue;
                }
            }
        }
        return true;
    }


    private void updateTable(String oldStr, String newStr, String tableName) {
        List oldList = JSONUtil.toList(oldStr, Dict.class);
        List newList = JSONUtil.toList(newStr, Dict.class);
        StringBuffer stringBuffer = new StringBuffer();
        // 获取两个集合的差集
        Collection disMap = CollUtil.disjunction(oldList, newList);
        if (CollUtil.isNotEmpty(disMap)) {
            // 旧字符串中删除和修改的集合
            Collection oldIntersection = CollUtil.intersection(oldList, disMap);
            String alertTable = "";
            stringBuffer.append(alertTable);
            if (CollUtil.isNotEmpty(oldIntersection)) {
                // 预执行SQL
                String dropSql = StrUtil.format("ALTER TABLE {} ", tableName.toUpperCase()).concat("DROP COLUMN {};");
                // 先通过循环找到已经删除的字段，删除字段需要drop 操作
                List dropList = oldIntersection.stream()
                        .map(dict -> {
                            // 旧字段在新字段里找，如果没有找到，说明该字段已经被删除，直接drop掉
                            List collect = newList.stream()
                                    .filter(_dict -> _dict.getStr("field").equals(dict.getStr("field")))
                                    .collect(Collectors.toList());
                            // 找不到说明没找到，已经没有该字段啦
                            if (CollUtil.isEmpty(collect)) {
                                return StrUtil.format(dropSql, dict.getStr("field"));
                            }
                            return null;
                        }).collect(Collectors.toList());
                // 移除里面所有的null
                dropList.removeAll(Collections.singleton(null));
                if (CollUtil.isNotEmpty(dropList)) {
                    stringBuffer.append(CollUtil.join(dropList, ";"));
                }
            }
            // 新字符串中新增和修改的集合
            Collection newIntersection = CollUtil.intersection(newList, disMap);
            if (CollUtil.isNotEmpty(newIntersection)) {
                // 预执行SQL
                String addSql = StrUtil.format("ALTER TABLE {} ", tableName.toUpperCase()).concat("ADD ( {} {}({}) NULL);");
                String modifySql = StrUtil.format("ALTER TABLE {} ", tableName.toUpperCase()).concat("MODIFY COLUMN `{}` {}({}) ;");
                List addList = newIntersection.stream().map(dict -> {
                    List collect = oldList.stream()
                            .filter(_dict -> _dict.getStr("field").equals(dict.getStr("field")))
                            .collect(Collectors.toList());
                    // 不为空，说明它只更改了类型和名字
                    if (CollUtil.isEmpty(collect)) {
                        return StrUtil.format(addSql, dict.getStr("field"), dict.getStr("jdbcType"), dict.getStr("length")).replaceAll("\\(0\\)", "");
                    }
                    return StrUtil.format(modifySql, dict.getStr("field"), dict.getStr("jdbcType"), dict.getStr("length")).replaceAll("\\(0\\)", "");
                }).collect(Collectors.toList());
                if (CollUtil.isNotEmpty(addList)) {
                    stringBuffer.append(CollUtil.join(addList, ";"));
                }
            }
            LOG.debug("执行的SQL{}", stringBuffer);

            String[] formSqls = stringBuffer.toString().split(";");
            for (String sql : formSqls) {
                //insert和delete语句不能执行
                if (StringUtils.isBlank(sql) || StrUtil.containsAnyIgnoreCase(sql,"SELECT ","INSERT ","DELETE ","DELETE ")) {
                    continue;
                }
                try {
                    modelDao.excuteSql(sql);
                } catch (Exception e) {
                    // TODO: 2024/9/25 这里防止原业务字段不存在或者字段被修改（直接修改数据库之类的）但是filed字段没有修改导致报错，统一捕获处理。
                    LOG.error("执行SQL {} 失败", sql);
                    e.printStackTrace();
                }
            }

        }


    }


    @Override
    public List queryBySQL(String table, List fields, Map wheres, ListsqlWhereList,String orderBy,String order) {
        SqlInjectionUtil.filterContent(table);
        //对高级搜索进行了注入过滤
        if(sqlWhereList!=null) {
            sqlWhereList.forEach(map->{
                SqlInjectionUtil.filterContent(map.get("field")+"");
            });
        }
        
        return getDao().queryBySQL(table, fields, wheres,sqlWhereList,  null, null, orderBy,order);
    }

    @Override
    public void updateBySQL(String table, Map fields, Map wheres) {
        SqlInjectionUtil.filterContent(table);
        
        getDao().updateBySQL(table, fields, wheres);
    }

    @Override
    public void deleteBySQL(String table, Map wheres) {
        SqlInjectionUtil.filterContent(table);
        
        getDao().deleteBySQL(table, wheres);
    }

    @Override
    public void insertBySQL(String table, Map fields) {
        SqlInjectionUtil.filterContent(table);
        
        getDao().insertBySQL(table, fields);
    }

}