[大数据]Java替换mybatis框架SQL日志中的预编译问号“?“

当我们去排查问题的时候，经常需要查看SQL来判断执行逻辑，但是打开日志后，往往会发现是这样的：

2022-03-07 11:45:25,959 [http-nio2-8080-exec-10] DEBUG [16466247258377172025468] c.xylink.crm.product.mapper.ProductMapper.pageList:137 - ==>  Preparing: SELECT p.id, p.product_name, p.product_code, p.shelf_status, p.config, p.has_u8, p.sale_scope, p.gmt_modified, p.price_group FROM basic_product AS p WHERE p.deleted = 0 AND p.product_category_id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND (p.product_code LIKE CONCAT('%', ?, '%') OR p.product_name LIKE CONCAT('%', ?, '%')) ORDER BY p.product_code LIMIT ?
2022-03-07 11:45:25,961 [http-nio2-8080-exec-10] DEBUG [16466247258377172025468] c.xylink.crm.product.mapper.ProductMapper.pageList:137 - ==> Parameters: 0(Long), 18865823302549504(Long), 18865823308840960(Long), 18865823315132416(Long), 18865823321948160(Long), 18865823328239616(Long), 18865823334531072(Long), 18865823340822528(Long), 18865823346589696(Long), 18865823352881152(Long), 18865823358124032(Long), 18865823364415488(Long), 18865823369658368(Long), 18865823375425536(Long), 18865823381192704(Long), 存储(String), 存储(String), 10(Long)
2022-03-07 11:45:25,965 [http-nio2-8080-exec-10] DEBUG [16466247258377172025468] c.xylink.crm.product.mapper.ProductMapper.pageList:137 - <==      Total: 5

因为预编译里面有Preparing、Parameters，固有很多问号，每次手动去变动问号，比较麻烦，所以写了个程序用于处理：

• 一个方法填入预编译（带问号）的字符串
• 一个方法填入参数字符串
• 运行程序，便会打印替换后的

public class Main {
    public static void main(String[] args) {
        String originSql = getOriSql();
        String paramStr = getParamStr();

        originSql = originSql.trim();
        paramStr = paramStr.trim();
        originSql = originSql.replace("Preparing:", "");
        paramStr = paramStr.replace("Parameters:", "");


        String[] params = paramStr.split(",");
        for (String param : params) {
            param = adjustParam(param);
            originSql = originSql.replaceFirst("\\?", param);
        }
        System.out.println(originSql);

    }

    private static String adjustParam(String param) {
        //去掉第一个空格
        param = param.substring(1);
        int i1 = param.indexOf("(");
        int i2 = param.indexOf(")");
        String subStr = param.substring(i1, i2 + 1);
        //移除类型
        param = param.replace(subStr, "");
        switch (subStr) {
            case "(Long)":
            case "(Integer)":
            case "(Boolean)":
            case "(BigDecimal)":
                return param;

            case "(String)":
            case "(Timestamp)":
                return "'" + param + "'";
            default:
        }
        throw new RuntimeException("Not Match Param Type");
    }

    //参数填充
    private static String getParamStr() {
        return "Parameters: 0(Long), 18865823302549504(Long), 18865823308840960(Long), 18865823315132416(Long), 18865823321948160(Long), 18865823328239616(Long), 18865823334531072(Long), 18865823340822528(Long), 18865823346589696(Long), 18865823352881152(Long), 18865823358124032(Long), 18865823364415488(Long), 18865823369658368(Long), 18865823375425536(Long), 18865823381192704(Long), 存储(String), 存储(String), 10(Long)";
    }

    //原始预编译SQL
    private static String getOriSql() {
        return "Preparing: SELECT p.id, p.product_name, p.product_code, p.shelf_status, p.config, p.has_u8, p.sale_scope, p.gmt_modified, p.price_group FROM basic_product AS p WHERE p.deleted = 0 AND p.product_category_id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND (p.product_code LIKE CONCAT('%', ?, '%') OR p.product_name LIKE CONCAT('%', ?, '%')) ORDER BY p.product_code LIMIT ?";
    }

}