How to convert the Preparing and Parameters in Mybatis log into executable SQL gracefully

During the epidemic, did you feel sick when you were staying at home~~

The company has opened VPN, mobile computers can connect, mobile phones can work with APP test package, so walking has been at home for 11 days since entering Beijing on February 1, 2020. These two days at home telecommuting, forecast next week will also work at home..

During the recent isolation at home, I learned less and played more. No, today I found a good Mybatis plug-in and a conversion tool when I was looking at the source code of Mybatis, so I rushed to write it and share it with you today.

We all have used Mybatis in our work. Sometimes when we debug locally, we will open the SQL log of Mybatis for printing. Then the printed SQL is as follows:

You can see placeholders for precompiled SQL conditions select * from User where id =?, not the real SQL select * from User where id = 1.

If we want to get real SQL, we can manually spell the parameter values if there are few parameters like the above figure, but what if there are more parameters? It's troublesome to fill it out by hand.

Don't bother with MAME. Today I'll show you how to convert the Preparing and Parameters of mybatis logs into executable sql.

There are two ways to share. One is mybatis log plugin, a plug-in of IDEA. The other is a static page way when there is no condition to install the plug-in or IDEA.

The first mybatis log plugin plug-in

Search mybatis log plugin in the setting - plugins of idea, as shown in the figure below, click install, and then restart idea as prompted. (the following has been installed)

Then we can right-click the SQL log to select: Restore Sql from Selection

Then you can see the real SQL in the Mybatis Log window

Then it can be copied and executed elsewhere

The second static page tool

In addition, smart students have created a static page to use when the plugin cannot be installed.

Original author's address:
https://blog.csdn.net/Zale_J/article/details/89402668

Just make an html file, paste the source code of the author, save it, open it with a browser, paste the mybatis log in and click "convert" to get the real SQL. I also posted the source code below.

Design sketch:

Source code:

 <!DOCTYPE html>
 <html>
 <head>
   <meta charset="utf-8">
   <title></title>
   <script type="text/javascript">
     function f(obj){
       var textVa = obj.value;
       // Get SQL statement with question mark
       var statementStartIndex = textVa.indexOf('Preparing: ');
       var statementEndIndex = textVa.length-1;
       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);
       //Get parameters
       var parametersStartIndex = textVa.indexOf('Parameters: ');
       var parametersEndIndex = textVa.length-1;
       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++) {
         // Other logic will be used if the data is bracketed
         tempStr = parametersStr[i].substring(0, parametersStr[i].indexOf("("));
         // Get the contents in brackets
         typeStr = parametersStr[i].substring(parametersStr[i].indexOf("(")+1,parametersStr[i].indexOf(")"));
         // If character type
         if (typeStr == "String" || typeStr == "Timestamp") {
           statementStr = statementStr.replace("?", "'"+tempStr.trim()+"'");
         }else{
           // value type
           statementStr = statementStr.replace("?", tempStr.trim());
         }
       }
       console.log(statementStr);
       document.getElementById("d1").innerHTML = statementStr;
       return textVa;
     }
</script>
 </head>
 <body>
    <textarea   name="getStr" id="1" rows="4" cols="100"></textarea>
    <button type="submit" onclick="f(document.getElementById('1'))">Transformation</button>
  <div id="d1"></div>
 </body>
 </html>

Another netizen said that the original author had a little bug, and didn't say any specific bug. He should have done some optimization. I haven't verified anything.

Optimized Original: https://www.cnblogs.com/n031/p/11176346.html

Here is the rendering:

Optimized code:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>mybatis Transformation</title>
    <script type="text/javascript">
        function f(obj) {
            var textVa = obj.value;
            // Get SQL statement with question mark
            var statementStartIndex = textVa.indexOf('Preparing: ');
            var statementEndIndex = textVa.length - 1;
            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);
            //Get parameters
            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);
            console.log(parametersStr);
            // parameter list
            var parametersStrArr = parametersStr.split(",");
            console.log(parametersStrArr);
            for (var i = 0; i < parametersStrArr.length; i++) {
                tempStr = parametersStrArr[i].substring(0, parametersStrArr[i].indexOf("("));
                // Without "(" is null
                if(tempStr == ''){
                    tempStr = "null";
                }
                // If the bracketed data needs to determine the parameter type
                typeStr = parametersStrArr[i].substring(parametersStrArr[i].indexOf("(") + 1, parametersStrArr[i].indexOf(")"));
                if (typeStr == "String" || typeStr == "Timestamp") {
                    statementStr = statementStr.replace("?", "'" + tempStr.trim() + "'");
                } else {
                    statementStr = statementStr.replace("?", tempStr.trim());
                }
            }
            console.log(statementStr);
            document.getElementById("d1").innerHTML = statementStr;
            return true;
        }
</script>
</head>
<body>
    <button type="submit" onclick="f(document.getElementById('1'))">Transformation</button><br><br>
    <textarea style="border:blue solid 2px;" name="getStr" id="1" rows="25" cols="150"></textarea><br>
    <p style="color:red;font:30px bold;">Your SQL</p>
    <div style="border:red solid 2px;" id="d1"></div>
</body>
</html>