Mysql慢查询日志文件转Excel的方法

@data
public class slowquery {
    private double querytime;
    private double locktime;
    private string sqlquery;
    private string tablename;
    private date executiondate;
}

public class mysqlslowquerylogparser {
    //  正则表达式匹配 慢日志内容格式抓取
    private static final pattern query_time_pattern = pattern.compile("# query_time: (\\d+\\.\\d+)");
    private static final pattern lock_time_pattern = pattern.compile("  lock_time: (\\d+\\.\\d+)");
    private static final pattern timestamp_pattern = pattern.compile("set timestamp=(\\d+);");
    public static void main(string[] args) {
        mysqlslowquerylogparser parser = new mysqlslowquerylogparser();
        //  慢查询日志存放路径
        string filepath = "d:\\日常\\2.oa\\oaserverlandb-slow.log";
        //  导出excel路径
        string excelpath = "d:\\日常\\2.oa\\slow_queries.xlsx";
        //  读取慢查询日志
        list<slowquery> slowqueries = parser.readslowquerylog(filepath);
        //  写入本地excel中
        parser.writequeriestoexcel(slowqueries, excelpath);
    }
    /**
     * 读取慢查询日志 返回list对象
     * @param filepath 慢查询日志文件路径
     * @return list<slowquery> 解析结果
     * */
    public list<slowquery> readslowquerylog(string filepath) {
        list<slowquery> slowqueries = new arraylist<>();
        //  转流
        try (bufferedreader br = new bufferedreader(new filereader(filepath))) {
            string line;
            stringbuilder querybuilder = new stringbuilder();
            //  设定默认值
            double querytime = 0;
            double locktime = 0;
            boolean isslowquery = false;
            long timestamp = 0; // 用于存储时间戳
            while ((line = br.readline()) != null) {
                if (line.startswith("# query_time")) {
                    // 如果前一个查询存在，添加到列表
                    if (isslowquery) {
                        addslowquery(slowqueries, querytime, locktime, querybuilder.tostring().trim(), timestamp);
                    }
                    // 解析查询时间和锁定时间
                    matcher querytimematcher = query_time_pattern.matcher(line);
                    if (querytimematcher.find()) {
                        querytime = double.parsedouble(querytimematcher.group(1));
                    }
                    matcher locktimematcher = lock_time_pattern.matcher(line);
                    if (locktimematcher.find()) {
                        locktime = double.parsedouble(locktimematcher.group(1));
                    }
                    // 开始新的慢查询
                    isslowquery = true;
                    // 清空缓存
                    querybuilder.setlength(0);
                } else if (line.startswith("set timestamp")) {
                    // 提取时间戳
                    matcher timestampmatcher = timestamp_pattern.matcher(line);
                    if (timestampmatcher.find()) {
                        timestamp = long.parselong(timestampmatcher.group(1)); // 获取时间戳
                    }
                } else if (line.startswith("#") || line.trim().isempty()) {
                    // 忽略注释行和空行
                    continue;
                } else {
                    // 记录当前慢查询的内容
                    if (isslowquery) {
                        querybuilder.append(line).append("\n");
                    }
                }
            }
            // 处理最后一个慢查询
            if (querybuilder.length() > 0) {
                addslowquery(slowqueries, querytime, locktime, querybuilder.tostring().trim(), timestamp);
            }
        } catch (ioexception e) {
            system.out.printf(e.tostring());
        }
        return slowqueries;
    }
    /**
     * 添加慢查询对象
     * @param slowqueries list<slowquery> 慢查询对象集合
     * @param querytime 查询时间
     * @param locktime 锁定时间
     * @param sqlquery sql执行时间
     * @param timestamp 时间戳
     * */
    private void addslowquery(list<slowquery> slowqueries, double querytime, double locktime, string sqlquery, long timestamp) {
        slowquery slowquery = new slowquery();
        slowquery.setquerytime(querytime);
        slowquery.setlocktime(locktime);
        slowquery.setsqlquery(sqlquery);
        // 提取表名
        slowquery.settablename(extracttablename(sqlquery));
        // 设置执行日期
        slowquery.setexecutiondate(new date(timestamp * 1000));
        slowqueries.add(slowquery);
    }
    /**
     * 通过sql语句中 提取出表名
     * @param sqlquery 执行的sql语句
     * @return 表名
     * */
    private string extracttablename(string sqlquery) {
        pattern pattern = pattern.compile("from\\s+([\\w.]+)", pattern.case_insensitive);
        matcher matcher = pattern.matcher(sqlquery);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return "";
    }
    /**
     * 通过处理后的集合生成到指定路径
     * @param slowqueries 数据集合
     * @param filepath 导出的excel路径
     * */
    public void writequeriestoexcel(list<slowquery> slowqueries, string filepath) {
        final int max_cell_length = 32767;
        simpledateformat dateformat = new simpledateformat("yyyy-mm-dd hh:mm:ss"); // 日期格式化
        try (workbook workbook = new xssfworkbook()) {
            sheet sheet = workbook.createsheet("slow queries");
            // 创建标题行
            row headerrow = sheet.createrow(0);
            headerrow.createcell(0).setcellvalue("query time (s)");
            headerrow.createcell(1).setcellvalue("lock time (s)");
            headerrow.createcell(2).setcellvalue("sql query");
            headerrow.createcell(3).setcellvalue("table name");
            headerrow.createcell(4).setcellvalue("execution date");
            // 填充数据行
            int rownum = 1;
            for (slowquery slowquery : slowqueries) {
                row row = sheet.createrow(rownum++);
                row.createcell(0).setcellvalue(slowquery.getquerytime());
                row.createcell(1).setcellvalue(slowquery.getlocktime()); // 确保这里写入的是原始 double 值
                string sqlquery = slowquery.getsqlquery();
                if (sqlquery.length() > max_cell_length) {
                    sqlquery = sqlquery.substring(0, max_cell_length);
                }
                row.createcell(2).setcellvalue(sqlquery);
                row.createcell(3).setcellvalue(slowquery.gettablename());
                row.createcell(4).setcellvalue(dateformat.format(slowquery.getexecutiondate()));
            }
            // 写入到文件
            try (fileoutputstream fileout = new fileoutputstream(filepath)) {
                workbook.write(fileout);
            }
        } catch (ioexception e) {
            system.out.printf(e.tostring());
        }
    }