JAVA-实现类似于idea的SQL Params Setter插件功能

含义

        SQL Params Setter（给日志输出中的sql转换成可执行SQL）

---

场景

        现在进行java编程时，基本都是利用idea进行的，因为idea提供了很多省事的插件，但往往因为很多原因，还是在用eclipse进行编程，但eclipse的插件库又没有idea的全，所以参考idea的SQL Params Setter插件，整理出一份实现类似功能的工具类，实现的效果和SQL Params Setter是一致的，代码如下。

---

package com.alex.examples;

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

/**
 * 格式化控制台输出的sql语句，功能：将参数匹配到对应的占位符中
 * 功能类型于idea的插件：SQL Params Setter
 */
public class SqlFormatting {
    private static String SQL = "Preparing: SELECT a.id, a.number, DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%s') as createTime, c.realName as salesmanName, a.picture, a.create_id, a.status, a.line_number, DATE_FORMAT(d.createTime,'%Y-%m-%d %H:%i:%s') as offerTime, DATE_FORMAT(d.offer_time,'%Y-%m-%d %H:%i:%s') as submitOfferTime, DATE_FORMAT(a.lost_order_time,'%Y-%m-%d %H:%i:%s') as lostOrderTime, DATE_FORMAT(d.confirm_time,'%Y-%m-%d %H:%i:%s') as confirmTime, DATE_FORMAT(d.to_order_time,'%Y-%m-%d %H:%i:%s') as toOrderTime, DATE_FORMAT(a.receiving_time,'%Y-%m-%d %H:%i:%s') as receivingTime, e.orderNumber, if(d.createTime is not null,TIMESTAMPDIFF(SECOND,a.create_time,d.createTime),0) as quotationDuration, if(d.offer_time is not null,TIMESTAMPDIFF(SECOND,d.createTime,d.offer_time),0) as offerDuration, if(d.talk_receiving_time is not null,TIMESTAMPDIFF(SECOND,d.offer_time,d.talk_receiving_time),0) as talkDuration, a.is_talk as isTalk, a.talk_state as talkState, a.talk_user_name as talkUserName, a.talk_remark as talkRemark, a.goods_class_name as goodsClassName, d.reason_type as reasonType, a.lost_order_reason as lostOrderReason FROM quotation_apply a LEFT JOIN sys_user c ON a.create_id = c.user_id LEFT JOIN Quotation_Orders d ON a.id = d.quotation_apply_id LEFT JOIN orders e ON d.ordersId = e.id LEFT JOIN sys_dept f ON c.dept_id = f.dept_id WHERE a.status = ? AND a.create_time >= CONCAT(?,' 00:00:00') AND a.create_time <= CONCAT(?,' 23:59:59') ORDER BY a.create_time ASC LIMIT ?,? \n" +
            "2022-11-11 11:12:32.956 DEBUG 50576 --- [io-8280-exec-18] c.y.m.m.dao.QuotationApplyDao.getList    : ==> Parameters: 1(Integer), 2022-11-09(String), 2022-11-11(String), 0(Long), 10(Long)\n";
    private static final String SQL_INDEX = "Preparing: ";
    private static final String PARAMETER_INDEX = "Parameters: ";
    private static final String SEPARATOR = ", ";

    public static void main(String[] args) {
        Integer cn = getCount(SQL_INDEX, SQL);
        if (cn != 1) {
            System.out.println("格式不对！！！");
            return;
        }
        cn = getCount(PARAMETER_INDEX, SQL);
        if (cn != 1) {
            System.out.println("格式不对！！！");
            return;
        }

        //取出sql
        String sql = SQL.substring(SQL.indexOf(SQL_INDEX) + SQL_INDEX.length(), SQL.indexOf("\n")) + ";";
        sql = sql.replaceAll("\\?", "#s");

        //取出参数
        List<Object> o = new ArrayList<>();
        String parameter = SQL.substring(SQL.lastIndexOf(PARAMETER_INDEX) + PARAMETER_INDEX.length());
        String[] parameterArray = parameter.split(SEPARATOR);
        for (String p : parameterArray) {
            String key = p.substring(p.indexOf("(") + 1, p.lastIndexOf(")"));
            String value = p.substring(0, p.indexOf("("));
            o.add(getValue(key, value));
        }

        //组装数据
        for (int i = 0; i < o.size(); i++) {
            sql = sql.replaceFirst("#s", o.get(i).toString()); //replaceFirst：只替换第一个
        }
        System.out.println("整合后的sql：" + sql);
    }

    /**
     * 根据8种数据类型对应的包装类替换数据，String不是基本数据类型
     *
     * @param key
     * @param value
     * @return
     */
    public static Object getValue(String key, String value) {
        // TODO: 2022/11/10 其他类型待补充

        if (key.equals("String")) {
            return "'" + value + "'";
        }

        if (key.equals("Integer")) {
            return value;
        }

        if (key.equals("Long")) {
            return value;
        }

        if (key.equals("Float")) {
            return value;
        }

        return null;
    }

    /**
     * 获取一个字符串在另一个字符串中出现的次数
     *
     * @param str
     * @param allStr
     * @return
     */
    private static Integer getCount(String str, String allStr) {
        //获取两个字符串的长度
        int OneLength = allStr.length();
        int ToLength = str.length();
        //定义两个整数，count记录出现的次数、index记录每次找到一个以后位置
        int count = 0;
        int index = 0;
        //if判断查找的字符串与总字符串的长度，如果查找的字符串较长，则直接输出0
        //while循环查找指定字符串在总字符串中出现的次数
        if (OneLength >= ToLength) {
            while ((index = allStr.indexOf(str, index)) != -1) {
                count++;
                index += ToLength;
            }
        }
        return count;
    }
}