devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：Li_阴宅 | 2024-07-15 14:14:20

赞

踩

java 获取sql语句中占位符

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

1.引入工具包

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

  • 1
  • 2
  • 3
  • 4
  • 5

2.引入内存镜像

import org.apache.ibatis.session.SqlSessionFactory;

@Autowired
SqlSessionFactory sqlSessionFactory;

  • 1
  • 2
  • 3
  • 4

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());

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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{
                chgData.setBIL_NO("<"+bilNo+">"+UUID.randomUUID().toString().toUpperCase());
            }
            if(preItm == null){
                chgData.setPRE_ITM(1);
            }else{
                chgData.setPRE_ITM(preItm);
            }
            if(itm == null){
                chgData.setITM(1);
            }else{
                chgData.setITM(itm);
            }
            if(ValueUtil.stringOfNullAndEmpty(fldNo)){
                chgData.setFLD_NO("");
            }else{
                chgData.setFLD_NO(fldNo);
            }
            if(ValueUtil.stringOfNullAndEmpty(fldName)){
                chgData.setFLD_NAME("");
            }else{
                chgData.setFLD_NAME(fldName);
            }
            if(ValueUtil.stringOfNullAndEmpty(oldvalue)){
                chgData.setOLDVALUE("");
            }else{
                chgData.setOLDVALUE(oldvalue);
            }
            if(ValueUtil.stringOfNullAndEmpty(newvalue)){
                chgData.setNEWVALUE("");
            }else{
                chgData.setNEWVALUE(newvalue);
            }
            if(ValueUtil.stringOfNullAndEmpty(usr)){
                chgData.setUSR("");
            }else{
                chgData.setUSR(usr);
            }
            if(ValueUtil.stringOfNullAndEmpty(compno)){
                chgData.setCOMPNO("");
            }else{
                chgData.setCOMPNO(compno);
            }

            return dataLogUtil.iChgDataService.addChgData(chgData);
        }catch (Exception e) {
            e.getMessage();
            return false;
        }
    }

    public static String mapToStr(Map<String,Object> map){
        //将MAP转换为字符串
        String str="";
        Collection values = map.values();
        for(Object object:values){
            str+=object+"||";
        }
        return str;
    }

    public static String changeSql(String val){
        String newVal="";
        if(!ValueUtil.stringOfNullAndEmpty(val)){
            //去除换行和空格
//            val= val.replaceAll("\r|\n","").replaceAll("\\s*","");
            val= val.replaceAll("\\r|\n","");

            if(val.contains("{call")){//不适用转换JSON数据的存储过程，传JSON数据请参考 saveSP_ENTERBOX() 方法
                //先转换单引号
                val = val.replaceAll("'","").replaceAll("\"","").replaceAll("/","");
                //第二步，掐头去尾
                String before = val.substring(0,val.indexOf("(")+1);
                val = val.replace(before,"");
                String after = val.substring(val.lastIndexOf(")"),val.length());
                val = val.replace(after,"");
                //第三步
                String str1 = "";
                String[] strArr = val.split(",");
                for (String forStr:strArr) {
                    str1 = str1 + " '" +forStr.trim() + "' ,";
                }
                str1 = str1.substring(0,str1.length()-1);
                newVal = "exec "+ before.replace("{call","").replace("(","").trim()+ " " + str1;
            } else if(val.contains("update") || val.contains("Update") || val.contains("UPDATE")){//修改
                //第一步  清掉单引号
                val = val.replaceAll("'","");
                //第二步 将set和where转为小写
                val = val.replaceAll("SET","set").replaceAll("WHERE","where").replaceAll("AND","and").replaceAll("And","and");
                //第三步  获取set之前的字符串
                String str1 = val.substring(0,val.indexOf("set"));
                newVal = val.replace(str1,"");
                //第四步  获取set之后，where之前的字符串(不含set)
                String str2 = newVal.substring(0,newVal.indexOf("where"));
                newVal = newVal.replace(str2,"");
                str2 = str2.replace("set","");
                //第五步  获取where之后的字符串(不含where)
                String str3 = newVal.replace("where","");

                //处理str2
                String[] strArr = str2.split(",");
                String str21 = "";
                for (String forStr1:strArr) {
                    if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
                        str21 += forStr1.trim()+",";
                    }else{
                        String[] strArr2 = forStr1.split("=");
                        String str22 = "";
                        for (String forStr2:strArr2) {
                            //判断=之后的值如果是时间戳则需要转换时间格式
                            Boolean isTime = false;
                            forStr2 = forStr2.trim();
                            if(forStr2.trim().length() == 13){
                                Pattern pattern = Pattern.compile("\\d{13}");
                                Matcher isNum = pattern.matcher(forStr2);
                                if(isNum.matches()){
                                    if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
                                        isTime = true;
                                    }
                                }
                            }
                            if(isTime){
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
                                forStr2 = sdf.format(millisecondDate);
                            }

                            str22 += forStr2.trim() + "=";
                        }
                        str22 = str22.substring(0,str22.length()-1);
                        str22 = str22.replace("=","='");
                        str21 += str22.trim()+"',";
                    }
                }
                //去掉最后一位
                str21 =  str21.substring(0,str21.length()-1);

                //处理str3
                String[] strArr2 = str3.split("and");
                String str31 = "";
                for (String forStr1:strArr2) {
                    if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
                        str31 += forStr1.trim()+" and ";
                    }else{
                        String[] strArr3 = forStr1.split("=");
                        String str32 = "";
                        for (String forStr2:strArr3) {
                            //判断=之后的值如果是时间戳则需要转换时间格式
                            Boolean isTime = false;
                            forStr2 = forStr2.trim();
                            if(forStr2.trim().length() == 13){
                                Pattern pattern = Pattern.compile("\\d{13}");
                                Matcher isNum = pattern.matcher(forStr2);
                                if(isNum.matches()){
                                    if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
                                        isTime = true;
                                    }
                                }
                            }
                            if(isTime){
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
                                forStr2 = sdf.format(millisecondDate);
                            }

                            str32 += forStr2.trim() + "=";
                        }
                        str32 = str32.substring(0,str32.length()-1);
                        str32 = str32.replace("=","='");
                        str31 += str32.trim()+"' and ";
                    }
                }
                //去掉最后四位
                str31 =  str31.substring(0,str31.length()-4);
                str1 = str1.replace("update","UPDATE ").replace("UPDATE","UPDATE ").replace("Update","UPDATE ");
                newVal = str1 + " set " + str21 + " where " + str31;
            } else if(val.contains("insert") || val.contains("INSERT") || val.contains("Insert")){//新增
                //替换insert语句中的select
                if(val.contains("select") || val.contains("SELECT") || val.contains("Select")){
                    val = val.replaceAll("select","VALUES(").replaceAll("SELECT","VALUES(");
                    val = val + ")";
                }

                String str = val.replaceAll("'","");

                String beforeStr = "";
                if(val.contains("VALUES")){
                    beforeStr = val.substring(0,val.indexOf("VALUES"));
                }
                if(val.contains("values")){
                    beforeStr = val.substring(0,val.indexOf("values"));
                }
                str = val.replace(beforeStr,"");
                str = str.substring(str.indexOf("(")+1,str.length()).trim();
                str = str.substring(0,str.lastIndexOf(")")).trim();
                String[] strArr1 = str.split(",");
                String str1 = "";
                for (String forStr1:strArr1) {
                    Boolean isTime = false;
                    forStr1 = forStr1.trim();
                    if(forStr1.trim().length() == 13){
                        Pattern pattern = Pattern.compile("\\d{13}");
                        Matcher isNum = pattern.matcher(forStr1);
                        if(isNum.matches()){
                            if("16,17,18,19,20".contains(forStr1.trim().substring(0,2))){
                                isTime = true;
                            }
                        }
                    }
                    if(isTime){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date millisecondDate= new Date(Long.valueOf(forStr1.trim()));
                        forStr1 = sdf.format(millisecondDate);
                    }
                    if(forStr1.contains("VALUES") || forStr1.contains("values")){
                        forStr1 = forStr1.replace("VALUES","").replace("values","").trim();
                    }
                    if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
                        str1 += forStr1.trim()+",";
                    }else{
                        str1 += "'"+forStr1.trim()+"',";
                    }
                }
                str1 = str1.substring(0,str1.length()-1);
                newVal =beforeStr + "VALUES("+ str1 +")" ;
            } else if(val.contains("delete") || val.contains("DELETE") || val.contains("Delete")){//删除
                String str = val.replaceAll("'","");
                if(val.contains("delete")){
                    val = val.replace("delete","DELETE ").replace("DELETE","DELETE ").replace("Delete","DELETE ");
                }
                String beforeStr = "";
                if(val.contains("WHERE")){
                    beforeStr = val.substring(0,val.indexOf("WHERE"));
                }
                if(val.contains("where")){
                    beforeStr = val.substring(0,val.indexOf("where"));
                }
                if(val.contains("Where")){
                    beforeStr = val.substring(0,val.indexOf("Where"));
                }
                str = val.replace(beforeStr,"");
                str = str.replace("WHERE","");
                str = str.replace("where","");
                str = str.replace("Where","");
                String[] strArr1 = str.replaceAll("AND","and").replaceAll("And","and").split("and");
                String str1 = "";
                for (String forStr1:strArr1) {
                    if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
                        str1 += forStr1.trim()+",";
                    }else{
                        String[] strArr2 = forStr1.split("=");
                        String str2 = "";
                        for (String forStr2:strArr2) {
                            //判断=之后的值如果是时间戳则需要转换时间格式
                            Boolean isTime = false;
                            forStr2 = forStr2.trim();
                            if(forStr2.trim().length() == 13){
                                Pattern pattern = Pattern.compile("\\d{13}");
                                Matcher isNum = pattern.matcher(forStr2);
                                if(isNum.matches()){
                                    if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
                                        isTime = true;
                                    }
                                }
                            }
                            if(isTime){
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
                                forStr2 = sdf.format(millisecondDate);
                            }

                            str2 += forStr2.trim() + "=";
                        }
                        str2 = str2.substring(0,str2.length()-1);
                        str2 = str2.replace("=","='");
                        str1 += str2.trim()+"' and ";
                    }
                }
                str1 = str1.substring(0,str1.length()-4);
                newVal = beforeStr + " where " + str1;
            }
        }
       return newVal;
    }

    //区位设置转换
    public static String changeSqlByQW(String val){
        String newVal="";
        if(!ValueUtil.stringOfNullAndEmpty(val)){
            //去除换行和空格
            val= val.replaceAll("\\r|\n","");

            newVal = val.replace("{call","exec")
                    .replace("exec","exec  ")
                    .replace(",","',")
                    .replace("=","='")
                    .replace("where","' where")
                    .replace("and","' and")
                    .replace(")}","'");
        }
        return newVal;
    }

    public static String changeSqlByQW2(String val){
        String newVal="";
        if(!ValueUtil.stringOfNullAndEmpty(val)){
            //去除换行和空格
            val= val.replaceAll("\\r|\n","");

            newVal = val.replace("{call","exec")
                    .replace("exec","exec  ")
                    .replace(")}","'");
        }
        return newVal;
    }

    //出场确认转换
    public static String changeSqlByvalidating(String val){
        String newVal="";
        if(!ValueUtil.stringOfNullAndEmpty(val)){
            val= val.replaceAll("\\r|\n","");
            newVal = val.replace("{call","exec  ")
                    .replaceFirst("\\(","'")
                    .replaceFirst("\\'","")
                    .replaceFirst("\\'","")
                    .replace(")}","'");
        }
        return newVal;
    }

    public static String changeJsonSql(String val){
        String newVal="";
        if(!ValueUtil.stringOfNullAndEmpty(val)){
            val= val.replaceAll("\\r|\n","");

            newVal = val.replace("{call","exec")
                    .replace("exec","exec  ")
                    .replace("(","'")
                    .replace("},{","}','{")
                    .replace("},","}','")
                    .replace(")}","'");
        }
        System.out.println("转换"+newVal);
        return newVal;
    }

    //对象转map
    public static Map<String,Object> objectToMap(Object object){
        Map<String,Object> retMap = new HashMap<>();
        retMap = JSON.parseObject(JSON.toJSONString(object), new TypeReference<Map<String,Object>>() {});
        return retMap;
    }

    /**
     *
     * @param bilNo 同步表主键
     * @param bilNo1 同步表主键
     * @param bilTable 操作表名或方法说明
     * @param specialSql 同步语句
     * @param czNo 站点号
     * @return
     */
    public static Boolean addPerformSQLStoredProcedure(String bilNo,Integer bilItm, String bilNo1, String bilTable, String specialSql, String czNo){
        try {
            Map<String,Object> map = new HashMap<>();
            SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMddHHmmssSSS");
            map.put("BIL_NO","BS"+sdf.format(new Date()));
            map.put("BIL_ITM",bilItm==null?1:bilItm);
            String bil = "";
            if(bilNo != null ){
                bil = bilNo;
            }
            if(bilNo1 != null ){
                if(bil.trim().length() > 0){
                    bil =  bil +"/"+bilNo1;
                }else{
                    bil =  bilNo1;
                }
            }
            map.put("BIL_NO1",bil);
            map.put("BIL_TABLE",bilTable);
            map.put("Specail_SQL",specialSql);
            map.put("Specail_SQL1","");
            map.put("DNAME","");
            map.put("CZ_NO",czNo);
            map.put("MOD",0);

            return dataLogUtil.itDataTbNewService.performSQLStoredProcedure(map);
        }catch (Exception e) {
            e.getMessage();
            return false;
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245
  • 246
  • 247
  • 248
  • 249
  • 250
  • 251
  • 252
  • 253
  • 254
  • 255
  • 256
  • 257
  • 258
  • 259
  • 260
  • 261
  • 262
  • 263
  • 264
  • 265
  • 266
  • 267
  • 268
  • 269
  • 270
  • 271
  • 272
  • 273
  • 274
  • 275
  • 276
  • 277
  • 278
  • 279
  • 280
  • 281
  • 282
  • 283
  • 284
  • 285
  • 286
  • 287
  • 288
  • 289
  • 290
  • 291
  • 292
  • 293
  • 294
  • 295
  • 296
  • 297
  • 298
  • 299
  • 300
  • 301
  • 302
  • 303
  • 304
  • 305
  • 306
  • 307
  • 308
  • 309
  • 310
  • 311
  • 312
  • 313
  • 314
  • 315
  • 316
  • 317
  • 318
  • 319
  • 320
  • 321
  • 322
  • 323
  • 324
  • 325
  • 326
  • 327
  • 328
  • 329
  • 330
  • 331
  • 332
  • 333
  • 334
  • 335
  • 336
  • 337
  • 338
  • 339
  • 340
  • 341
  • 342
  • 343
  • 344
  • 345
  • 346
  • 347
  • 348
  • 349
  • 350
  • 351
  • 352
  • 353
  • 354
  • 355
  • 356
  • 357
  • 358
  • 359
  • 360
  • 361
  • 362
  • 363
  • 364
  • 365
  • 366
  • 367
  • 368
  • 369
  • 370
  • 371
  • 372
  • 373
  • 374
  • 375
  • 376
  • 377
  • 378
  • 379
  • 380
  • 381
  • 382
  • 383
  • 384
  • 385
  • 386
  • 387
  • 388
  • 389
  • 390
  • 391
  • 392
  • 393
  • 394
  • 395
  • 396
  • 397
  • 398
  • 399
  • 400
  • 401
  • 402
  • 403
  • 404
  • 405
  • 406
  • 407
  • 408
  • 409
  • 410
  • 411
  • 412
  • 413
  • 414
  • 415
  • 416
  • 417
  • 418
  • 419
  • 420
  • 421
  • 422
  • 423
  • 424
  • 425
  • 426
  • 427
  • 428
  • 429
  • 430
  • 431
  • 432
  • 433
  • 434
  • 435
  • 436
  • 437
  • 438
  • 439
  • 440
  • 441
  • 442
  • 443
  • 444
  • 445
  • 446
  • 447
  • 448
  • 449
  • 450
  • 451
  • 452
  • 453
  • 454
  • 455
  • 456
  • 457
  • 458
  • 459
  • 460
  • 461
  • 462
  • 463
  • 464
  • 465
  • 466
  • 467
  • 468
  • 469
  • 470
  • 471
  • 472
  • 473
  • 474
  • 475
  • 476
  • 477
  • 478
  • 479
  • 480
  • 481
  • 482
  • 483
  • 484
  • 485
  • 486
  • 487
  • 488
  • 489
  • 490
  • 491
  • 492
  • 493
  • 494
  • 495
  • 496
  • 497
  • 498
  • 499
  • 500
  • 501
  • 502
  • 503
  • 504
  • 505
  • 506
  • 507
  • 508
  • 509
  • 510
  • 511
  • 512
  • 513
  • 514

5.MyBatisSqlUtils.java

import java.util.List;
import java.util.Map;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.SqlSessionFactory;

public class MyBatisSqlUtils {
    /**
     * 运行期获取MyBatis执行的SQL及参数
     * @param id             Mapper xml 文件里的select Id
     * @param parameterMap   参数
     * @param sqlSessionFactory
     * @return
     */
    public static MyBatisSql getMyBatisSql(String id, Map<String,Object> parameterMap, SqlSessionFactory sqlSessionFactory) {
        MyBatisSql ibatisSql = new MyBatisSql();
        MappedStatement ms = sqlSessionFactory.getConfiguration().getMappedStatement(id);
        BoundSql boundSql = ms.getBoundSql(parameterMap);
        ibatisSql.setSql(boundSql.getSql());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null)
        {
            Object[] parameterArray = new Object[parameterMappings.size()];
            ParameterMapping parameterMapping = null;
            Object value = null;
            Object parameterObject = null;
            MetaObject metaObject = null;
            PropertyTokenizer prop = null;
            String propertyName = null;
            String[] names = null;
            for (int i = 0; i < parameterMappings.size(); i++)
            {
                parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT)
                {
                    propertyName = parameterMapping.getProperty();
                    names = propertyName.split("\\.");
                    if(propertyName.indexOf(".") != -1 && names.length == 2)
                    {
                        parameterObject = parameterMap.get(names[0]);
                        propertyName = names[1];
                    }
                    else if(propertyName.indexOf(".") != -1 && names.length == 3)
                    {
                        parameterObject = parameterMap.get(names[0]); // map
                        if(parameterObject instanceof Map)
                        {
                            parameterObject = ((Map)parameterObject).get(names[1]);
                        }
                        propertyName = names[2];
                    }
                    else
                    {
                        parameterObject = parameterMap.get(propertyName);
                    }
                    metaObject = parameterMap == null ? null : MetaObject.forObject(parameterObject, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,new DefaultReflectorFactory());
                    prop = new PropertyTokenizer(propertyName);
                    if (parameterObject == null)
                    {
                        value = null;
                    }
                    else if (ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass()))
                    {
                        value = parameterObject;
                    }
                    else if (boundSql.hasAdditionalParameter(propertyName))
                    {
                        value = boundSql.getAdditionalParameter(propertyName);
                    }
                    else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName()))
                    {
                        value = boundSql.getAdditionalParameter(prop.getName());
                        if (value != null)
                        {
                            value = MetaObject.forObject(value,SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,new DefaultReflectorFactory()).getValue(propertyName.substring(prop.getName().length()));
                        }
                    }
                    else
                    {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }
                    parameterArray[i] = value;
                }
            }
            ibatisSql.setParameters(parameterArray);
        }
        return ibatisSql;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97

6.MyBatisSql.java

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class MyBatisSql {
    /**
     * 运行期 sql
     */
    private String sql;

    /**
     * 参数 数组
     */
    private Object[] parameters;

    public void setSql(String sql) {
        this.sql = sql;
    }

    public String getSql() {
        return sql;
    }

    public void setParameters(Object[] parameters) {
        this.parameters = parameters;
    }

    public Object[] getParameters() {
        return parameters;
    }

    @Override
    public String toString() {
        if(parameters == null || sql == null)
        {
            return "";
        }
        List<Object> parametersArray = Arrays.asList(parameters);
        List<Object> list = new ArrayList<Object>(parametersArray);
        while(sql.indexOf("?") != -1 && list.size() > 0 && parameters.length > 0)
        {
            sql = sql.replaceFirst("\\?", list.get(0).toString());
            list.remove(0);
        }
        return sql.replaceAll("(\r?\n(\\s*\r?\n)+)", "\r\n");
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/829780
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号