Jlin's Blog

林剑的博客

0%

Mybatis日志打印的SQL转化

发表于 更新于 分类于
本文字数： 535 阅读时长 ≈ 2 分钟

地址： http://47.98.215.13/tool/sql-convert.html

背景

在调试或者分析问题的过程中，需要将带问号的Preparing语句中的问号替换为实际值，问号多了替换很麻烦。网上搜到一个js脚本，原文地址将mybatis打印的Preparing语句与Parameters转化为可执行的sql_Zale_J的博客-CSDN博客

优化

druid的慢SQL输出格式和Mybatis不太一样，在原有js脚本上做了优化。

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
 <!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>SQL转化</title>
    <script type="text/javascript">
        function f(obj){
            var textVa = obj.value;
            // 获取带问号的SQL语句
            var statementStartIndex = textVa.indexOf('Preparing: ');
            var statementEndIndex = textVa.length;
            for(var i = statementStartIndex; i < textVa.length; i++) {
                if(textVa[i] == "\n") {
                   statementEndIndex = i;
                   break;
                }
            }
            var statementStr = textVa.substring(statementStartIndex+"Preparing: ".length, statementEndIndex);
            console.log(statementStr);
            //获取参数
            var parametersStartIndex = textVa.indexOf('Parameters: ');
            var parametersEndIndex = textVa.length;
            for(var i = parametersStartIndex; i < textVa.length; i++) {
                if(textVa[i] == "\n") {
                   parametersEndIndex = i;
                   break;
                } else {
                    console.log(textVa[i]);
                }
            }
            var parametersStr = textVa.substring(parametersStartIndex+"Parameters: ".length, parametersEndIndex);
            parametersStr = parametersStr.split(",");
            console.log(parametersStr);
            for(var i = 0; i < parametersStr.length; i++) {
                // 如果数据中带括号将使用其他逻辑
                tempStr = parametersStr[i].substring(0, parametersStr[i].indexOf("("));
                // 获取括号中内容
                typeStr = parametersStr[i].substring(parametersStr[i].indexOf("(")+1,parametersStr[i].indexOf(")"));
                // 如果为字符类型
                if (typeStr == "String" || typeStr == "Timestamp") {
                    statementStr = statementStr.replace("?", "'"+tempStr.trim()+"'");
                }else{
                    // 数值类型
                    statementStr = statementStr.replace("?", tempStr.trim());
                }
            }
            console.log(statementStr);
            document.getElementById("sql-convert").innerHTML = statementStr;
            return textVa;
        }
        function f1(obj){
            var textVa = obj.value.replaceAll("\n","");
            // 获取带问号的SQL语句
            var statementStartIndex = 0;
            var statementEndIndex = textVa.indexOf('[');
            var statementStr = textVa.substring(statementStartIndex, statementEndIndex);
            console.log(statementStr);
            //获取参数
            var parametersStartIndex = textVa.indexOf('[') + 1;
            var parametersEndIndex = textVa.indexOf(']');
            var parametersStr = textVa.substring(parametersStartIndex, parametersEndIndex);
            parametersStr = parametersStr.split(",");
            console.log(parametersStr);
            for(var i = 0; i < parametersStr.length; i++) {
               console.log(parametersStr[i]);;
               statementStr = statementStr.replace("?", parametersStr[i]);
            }
            console.log(statementStr);
            document.getElementById("slow-sql-convert").innerHTML = statementStr;
            return textVa;
        }
    </script>
</head>
<body>
        <h2>Mybatis日志打印SQL转化</h2>
        <h4>示例</h4>
        <p>2022-12-06 18:31:16.544 DEBUG [scheduler-9] d.c.j.i.s.r.dao.RfPlanPatientMapper.save!selectKey [1600075072922697731] - ==>  Preparing: SELECT id FROM t_research_followup_plan_patient WHERE plan_id = ? AND empi_id = ?</p>
       <p>2022-12-06 18:31:16.544 DEBUG [scheduler-9] d.c.j.i.s.r.dao.RfPlanPatientMapper.save!selectKey [1600075072922697731] - ==> Parameters: 11c81e9f393a4b92b41acc75e9bba9c7(String), 168b442cfb594c54a9020a07497aa015(String)</p>
       <textarea  name="getStr" id="mybatisSQLLog" rows="8" cols="180"></textarea>
       <button type="submit" onclick="f(document.getElementById('mybatisSQLLog'))">转换</button>
       <textarea  name="getStr" id="sql-convert" rows="10" cols="180"></textarea><br/>
       <h2>Druid日志打印慢SQL转化</h2>
       <h4>示例</h4>
       <p>select count(0) from t_warn_filter where hosp_code = ? and deal_date = ? and plan_type = ?</p>
       <p>["48594034-X","2022-12-06",1]</p>
       <textarea  name="getStr" id="druidSlowSQLLog" rows="8" cols="180"></textarea>
       <button type="submit" onclick="f1(document.getElementById('druidSlowSQLLog'))">转换</button>
       <textarea  name="getStr" id="slow-sql-convert" rows="10" cols="180"></textarea>
</body>
</html>

效果