Java-SQL获取执行语句并将实际参数替换占位符

Java-SQL获取执行语句并将实际参数替换占位符

1.引入工具包

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

2.引入内存镜像

import org.apache.ibatis.session.SqlSessionFactory;

@Autowired
SqlSessionFactory sqlSessionFactory;

3.获取并转换

//获取执行语句语句
MyBatisSql myBatisSql = MyBatisSqlUtils.getMyBatisSql("com.ruoyi.business.mapper.IZlxxMapper.updateXW", DataLogUtil.objectToMap(map), sqlSessionFactory);
String specialSql = String.valueOf(myBatisSql);
//转换语句
String changeSql = DataLogUtil.changeSql(specialSql);
//添加日志记录
DataLogUtil.addDataLog(tZlxxEntity.getZlNo(), 1, tZlxxEntity.getItm().toString(), "指令管理-箱位修改-zlxxEdit", changeSql, tZlxxEntity.getCzNo());
DataLogUtil.addChgData("ZE", tZlxxEntity.getTdNo(), tZlxxEntity.getBoxNo(), tZlxxEntity.getZlNo()+":"+UUID.randomUUID().toString().toUpperCase(), 1, 1, null, "指令管理-箱位修改-zlxxEdit", "xw:" + tZlxxEntity.getXw(), map.toString(), SecurityUtils.getUsr(), SecurityUtils.getCompno());
DataLogUtil.addPerformSQLStoredProcedure(tZlxxEntity.getZlNo(), 1, tZlxxEntity.getItm().toString(), "指令管理-箱位修改-zlxxEdit", changeSql, tZlxxEntity.getCzNo());

4.DataLogUtil.java

import cn.hutool.core.util.ReUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.ruoyi.business.domain.entity.TDataTbNewWithBLOBsEntity;
import com.ruoyi.business.domain.vo.ChgData;
import com.ruoyi.business.service.dataLog.IChgDataService;
import com.ruoyi.business.service.dataLog.ITDataTbNewService;
import com.ruoyi.common.utils.ValueUtil;
import com.ruoyi.common.utils.uuid.UUID;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

//同步sql记录
@Component
public class DataLogUtil {

    @Autowired
    private ITDataTbNewService itDataTbNewService;
    @Autowired
    private IChgDataService iChgDataService;

    public static DataLogUtil dataLogUtil;

    @PostConstruct
    public void init(){
        dataLogUtil = this;
        dataLogUtil.itDataTbNewService = this.itDataTbNewService;
        dataLogUtil.iChgDataService = this.iChgDataService;
    }

    /**
     *
     * @param bilNo 同步表主键
     * @param bilNo1 同步表主键
     * @param bilTable 操作表名或方法说明
     * @param specialSql 同步语句
     * @param czNo 站点号
     * @return
     */
    public static Boolean addDataLog(String bilNo,Integer bilItm, String bilNo1, String bilTable, String specialSql, String czNo){
        try {
            TDataTbNewWithBLOBsEntity tDataTbNewWithBLOBsEntity = new TDataTbNewWithBLOBsEntity();
            SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMddHHmmssSSS");
            tDataTbNewWithBLOBsEntity.setBilNo("BS"+sdf.format(new Date()));
            tDataTbNewWithBLOBsEntity.setBilItm(bilItm==null?1:bilItm);
            String bil = "";
            if(bilNo != null ){
                bil = bilNo;
            }
            if(bilNo1 != null && !"null".equals(bilNo1) && !"NULL".equals(bilNo1)){
                if(bil.trim().length() > 0){
                    bil =  bil +"/"+bilNo1;
                }else{
                    bil =  bilNo1;
                }
            }
            tDataTbNewWithBLOBsEntity.setBilNo1(bil);
            if(ValueUtil.stringOfNullAndEmpty(bilTable)){
                tDataTbNewWithBLOBsEntity.setBilTable("");
            }else {
                tDataTbNewWithBLOBsEntity.setBilTable(bilTable);
            }
            if(ValueUtil.stringOfNullAndEmpty(specialSql)){
                tDataTbNewWithBLOBsEntity.setSpecialSql("");
            }else {
                tDataTbNewWithBLOBsEntity.setSpecialSql(specialSql);
            }
            if(ValueUtil.stringOfNullAndEmpty(czNo)){
                tDataTbNewWithBLOBsEntity.setCzNo("");
            }else {
                tDataTbNewWithBLOBsEntity.setCzNo(czNo);
            }

            return dataLogUtil.itDataTbNewService.add1(tDataTbNewWithBLOBsEntity);
        }catch (Exception e) {
            e.getMessage();
            return false;
        }
    }
    
    /**
     * @param bilId
     * @param tdNo
     * @param boxNo
     * @param bilNo
     * @param preItm
     * @param itm
     * @param fldNo
     * @param fldName
     * @param oldvalue
     * @param newvalue
     * @param usr
     * @param compno
     * @return
     */
    public static Boolean addChgData(String bilId,String tdNo, String boxNo, String bilNo, Integer preItm, Integer itm, String fldNo,String fldName,String oldvalue,String newvalue,String usr,String compno){
        try {
            ChgData chgData = new ChgData();
            chgData.setBIL_ID(bilId);
            if(ValueUtil.stringOfNullAndEmpty(tdNo)){
                chgData.setTD_NO("");
            }else{
                chgData.setTD_NO(tdNo);
            }
            if(ValueUtil.stringOfNullAndEmpty(boxNo)){
                chgData.setBOX_NO("");
            }else{
                chgData.setBOX_NO(boxNo);
            }
            if(ValueUtil.stringOfNullAndEmpty(bilNo)){
                chgData.setBIL_NO(UUID.randomUUID().toString().toUpperCase());
            }else{