开发中，mybatis执行SQL打印了这么一段日志，我们想复制出来，验证一下，找找SQL哪里出错了，如果将日志解析为SQL呢。
2022-12-30 10:38:29.823 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList : ==> Preparing: SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND reim.system_batch_id IN (?, ?, ?) AND reim.tenant_id = '1' 2022-12-30 10:38:29.824 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList : ==> Parameters: 24(Long), 567(Long), 568(Long), 569(Long), 570(Long), 571(Long), 572(Long), 573(Long), 574(Long), 575(Long), 576(Long), 577(Long), 578(Long), 579(Long), 580(Long), 581(Long), 582(Long), 583(Long), 584(Long), 585(Long), 586(Long), 587(Long), 588(Long), 589(Long), 590(Long), 591(Long), 592(Long), 593(Long), 594(Long), 595(Long), 596(Long), 597(Long), 598(Long), 599(Long), 600(Long), 601(Long), 602(Long), 603(Long), 604(Long), 605(Long), 606(Long), 607(Long), 608(Long), 609(Long), 610(Long), 611(Long), 612(Long), 613(Long), 614(Long), 615(Long), 616(Long), 617(Long), 618(Long), 619(Long), 620(Long), 621(Long), 622(Long), 623(Long), 626(Long), 627(Long), 628(Long), 629(Long), 630(Long), 631(Long), 632(Long), 633(Long), 634(Long), 635(Long), 636(Long), 637(Long), 638(Long), 639(Long), 640(Long), 641(Long), 642(Long), 643(Long), 644(Long), 645(Long), 646(Long), 647(Long), 648(Long), 649(Long), 650(Long), 651(Long), 652(Long), 653(Long), 654(Long), 655(Long), 656(Long), 657(Long), 658(Long), 659(Long), 660(Long), 661(Long), 662(Long), 663(Long), 664(Long), 665(Long), 666(Long), 667(Long), 668(Long), 669(Long), 670(Long), 671(Long), 672(Long), 673(Long), 674(Long), 675(Long), 676(Long), 677(Long), 678(Long), 679(Long), 680(Long), 681(Long), 682(Long), 683(Long), 684(Long), 693(Long), 694(Long), 740(Long), 741(Long), 742(Long), 743(Long), 744(Long), 745(Long), 746(Long), 305(Long), 306(Long), 307(Long), 308(Long), 309(Long), 310(Long), 311(Long), 312(Long), 313(Long), 314(Long), 315(Long), 316(Long), 317(Long), 318(Long), 319(Long), 320(Long), 321(Long), 322(Long), 323(Long), 324(Long), 839(Long), 840(Long), 841(Long), 842(Long), 843(Long), 844(Long), 845(Long), 846(Long), 847(Long), 848(Long), 849(Long), 854(Long), 421(Long), 422(Long), 423(Long), 424(Long), 425(Long), 426(Long), 427(Long), 428(Long), 429(Long), 430(Long), 431(Long), 432(Long), 433(Long), 460(Long), 461(Long), 462(Long), 463(Long), 464(Long), 465(Long), 466(Long), 467(Long), 468(Long), 469(Long), 470(Long), 471(Long), 472(Long), 473(Long), 474(Long), 475(Long), 476(Long), 477(Long), 478(Long), 479(Long), 480(Long), 481(Long), 482(Long), 483(Long), 484(Long), 485(Long), 486(Long), 487(Long), 488(Long), 489(Long), 490(Long), 491(Long), 492(Long), 493(Long), 494(Long), 495(Long), 496(Long), 497(Long), 498(Long), 499(Long), 500(Long), 32(Long), 35(Long), 40(Long)
一、解析代码
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title></title> </head> <body> <h2><font color="#00bfff"> 输入Mybatis SQL日志：</font></h2> <textarea id="sqlLog" rows="13" cols="140" style="font-size:20px"></textarea> <div style="border:0px deepskyblue solid;width:1425px;height:50px;text-align:right"> <button style="color:mediumblue;width:100px;height:60px" type="button" onclick="clearLog(document.getElementById('sqlLog'))"> 清空 </button> <button style="color:mediumblue;width:100px;height:60px" type="submit" onclick="f(document.getElementById('sqlLog'))"> 解析SQL </button> </div> <h2><font color="#32cd32">解析为可执行SQL：</font></h2> <textarea id="d1" rows="13" cols="140" style="font-size:20px"></textarea> <div style="border:0px deepskyblue solid;width:1425px;height:50px;text-align:right"> <button style="color:mediumblue;width:100px;height:60px" type="button" onclick="copySQL()">复制SQL</button> </div> <div id="msg" style="color:cornflowerblue;border:0px black solid;width:800px;height:20px;text-align:right;font-style: initial;font-size: large"> </div> <script type="text/javascript"> function f(obj) { var textVa = obj.value; // 获取带问号的SQL语句 var statementStartIndex = textVa.indexOf('Preparing: '); var statementEndIndex = textVa.length - 1; for (var i = statementStartIndex; i < textVa.length; i++) { if (textVa[i] == "") { statementEndIndex = i; break; } } var statementStr = textVa.substring(statementStartIndex + "Preparing: ".length, statementEndIndex); console.log(statementStr); //获取参数 var parametersStartIndex = textVa.indexOf('Parameters: '); var parametersEndIndex = textVa.length - 1; for (var i = parametersStartIndex; i < textVa.length; i++) { if (textVa[i] == "") { 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("d1").innerHTML = statementStr.split('\n')[0]; return textVa; } function copySQL() { var SQL = document.getElementById("d1"); SQL.select(); // 选择对象 document.execCommand("Copy"); // 执行浏览器复制命令 var msg = document.getElementById("msg"); msg.innerHTML = "已复制到剪切板"; setTimeout(function () { msg.innerHTML = ""; }, 3000); } function clearLog(obj) { obj.select(); obj.value = ""; } </script> </body> </html>
二、页面解析效果
Preparing: SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND reim.system_batch_id IN (?, ?, ?) AND reim.tenant_id = '1' 2022-12-30 10:38:29.824 DEBUG 7568 --- [http-nio-48080-exec-2] [TID: N/A] c.i.y.s.m.S.selectDataStatisticsList : ==> Parameters: 24(Long), 567(Long), 568(Long), 569(Long), 570(Long), 571(Long), 572(Long), 573(Long), 574(Long), 575(Long), 576(Long), 577(Long), 578(Long), 579(Long), 580(Long), 581(Long), 582(Long), 583(Long), 584(Long), 585(Long), 586(Long), 587(Long), 588(Long), 589(Long), 590(Long), 591(Long), 592(Long), 593(Long), 594(Long), 595(Long), 596(Long), 597(Long), 598(Long), 599(Long), 600(Long), 601(Long), 602(Long), 603(Long), 604(Long), 605(Long), 606(Long), 607(Long), 608(Long), 609(Long), 610(Long), 611(Long), 612(Long), 613(Long), 614(Long), 615(Long), 616(Long), 617(Long), 618(Long), 619(Long), 620(Long), 621(Long), 622(Long), 623(Long), 626(Long), 627(Long), 628(Long), 629(Long), 630(Long), 631(Long), 632(Long), 633(Long), 634(Long), 635(Long), 636(Long), 637(Long), 638(Long), 639(Long), 640(Long), 641(Long), 642(Long), 643(Long), 644(Long), 645(Long), 646(Long), 647(Long), 648(Long), 649(Long), 650(Long), 651(Long), 652(Long), 653(Long), 654(Long), 655(Long), 656(Long), 657(Long), 658(Long), 659(Long), 660(Long), 661(Long), 662(Long), 663(Long), 664(Long), 665(Long), 666(Long), 667(Long), 668(Long), 669(Long), 670(Long), 671(Long), 672(Long), 673(Long), 674(Long), 675(Long), 676(Long), 677(Long), 678(Long), 679(Long), 680(Long), 681(Long), 682(Long), 683(Long), 684(Long), 693(Long), 694(Long), 740(Long), 741(Long), 742(Long), 743(Long), 744(Long), 745(Long), 746(Long), 305(Long), 306(Long), 307(Long), 308(Long), 309(Long), 310(Long), 311(Long), 312(Long), 313(Long), 314(Long), 315(Long), 316(Long), 317(Long), 318(Long), 319(Long), 320(Long), 321(Long), 322(Long), 323(Long), 324(Long), 839(Long), 840(Long), 841(Long), 842(Long), 843(Long), 844(Long), 845(Long), 846(Long), 847(Long), 848(Long), 849(Long), 854(Long), 421(Long), 422(Long), 423(Long), 424(Long), 425(Long), 426(Long), 427(Long), 428(Long), 429(Long), 430(Long), 431(Long), 432(Long), 433(Long), 460(Long), 461(Long), 462(Long), 463(Long), 464(Long), 465(Long), 466(Long), 467(Long), 468(Long), 469(Long), 470(Long), 471(Long), 472(Long), 473(Long), 474(Long), 475(Long), 476(Long), 477(Long), 478(Long), 479(Long), 480(Long), 481(Long), 482(Long), 483(Long), 484(Long), 485(Long), 486(Long), 487(Long), 488(Long), 489(Long), 490(Long), 491(Long), 492(Long), 493(Long), 494(Long), 495(Long), 496(Long), 497(Long), 498(Long), 499(Long), 500(Long), 32(Long), 35(Long), 40(Long)
SELECT * FROM system_reimbursement AS reim WHERE reim.deleted = 0 AND reim.state = 11 AND reim.type = 2 AND reim.system_classification_id IN (24, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 649, 650, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682, 683, 684, 693, 694, 740, 741, 742, 743, 744, 745, 746, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 839, 840, 841, 842, 843, 844, 845, 846, 847, 848, 849, 854, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 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) AND reim.system_batch_id IN (32, 35, 40) AND reim.tenant_id = '1'
