此前，已改造完基礎監控工廠。此篇，將基於此前的功能，實現SQL語句的監控日志記錄。

開發思路

為實現對系統執行SQL進行監控，主要記錄“執行時長”、“轉換類”、“是否執行成功”、“執行器”、“執行Sql腳本”、“執行參數”、“返回結果”等數據。在後期系統上線運行過程中，進行實時監控，便於優化產品性能。

設計數據庫

yml配置

   # sql監控配置
    sql:
      # 是否監控
      is-monitor: true
      # 是否輸出到控制臺
      is-output-console: true
      # 是否保存用戶代理信息
      is-save-user-agent: true
      # 是否保存異常信息
      is-save-throwable: true
      # 是否保存請求客戶端信息
      is-save-request-client: true
      # 是否保存sql語句
      is-save-sql: true
      # 是否保存返回結果集
      is-save-return-results: true
      # 是否保存執行參數
      is-save-execute-params: true

此上配置參數，用於動態配置是否開啟指定記錄項。

PO定義

package com.flycoding.monitor.entity;
import com.flycoding.dblibrary.annotation.create.Column;
import com.flycoding.dblibrary.annotation.create.PrimaryAuto;
import com.flycoding.dblibrary.annotation.create.Table;
import com.flycoding.dblibrary.enums.ColumnType;
import com.flycoding.dblibrary.enums.OrderByType;
import com.flycoding.drivenlibrary.engine.annotation.function.FunctionConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.config.popup.PopupConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.config.table.TableConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.form.FormConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.form.FormFieldConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.form.field.DictConfig;
import com.flycoding.drivenlibrary.engine.annotation.function.form.field.FieldConfig;
import com.flycoding.drivenlibrary.engine.config.constants.dictionary.ConfigDictionaryConstants.EnableCodeDictionary;
import com.flycoding.drivenlibrary.engine.constants.DefaultFieldConstants;
import com.flycoding.drivenlibrary.engine.constants.FieldConfigConstants;
import com.flycoding.drivenlibrary.engine.constants.SqlConstants;
import com.flycoding.drivenlibrary.engine.constants.config.DrivenElementConstants;
import com.flycoding.monitor.entity.base.BaseMonitorPO;
import com.flycoding.monitor.entity.base.BaseRequestMonitorPO;
/**
 * 監控SQL語句
 *
 * @author 趙屈犇
 * @version 1.0
 * @date 創建時間: 2020/10/8 20:35
 */
@Table(tableName = MonitorSqlPO.TABLE_NAME)
@FunctionConfig(funcName = "SQL執行監控", funcCode = "Sy_Monitor_Sql", tableConfig = @TableConfig(tableName = MonitorSqlPO.TABLE_NAME,
                                                                                              columns = {
                                                                                                  @Column(columnName = DefaultFieldConstants.CREATE_TIME, isInsertUse = false, isSelectUse = false, orderBy = OrderByType.DESC)
                                                                                              }), formConfig = @FormConfig(keyCode = "id", isCreateTableAddBtn = false, popupConfig = @PopupConfig(popupWidth = 850, popupHeight = 550)))
public class MonitorSqlPO extends BaseRequestMonitorPO {
    public static final String TABLE_NAME = "Sys_Monitor_Sql";
    @PrimaryAuto(columnName = "id", comment = "主鍵ID")
    @FormFieldConfig(fieldCode = "id", fieldName = "SqlID", isPageVisibility = false, isTableEnable = false, fieldParentName = MONITOR_BASIC_MESSAGE_NAME)
    private Integer id;
    /**
     * 轉換類別名
     */
    @FormFieldConfig(fieldCode = "convert_class_name", fieldName = "轉換類別名", fieldParentName = MONITOR_BASIC_MESSAGE_NAME)
    @Column(columnName = "convert_class_name", columnType = ColumnType.VARCHAR, length = SqlConstants.DB_NAME_SIZE)
    private String convertClassName;
    /**
     * 執行時長
     */
    @FormFieldConfig(fieldCode = "executor_duration", fieldName = "執行時長(毫秒)", fieldParentName = MONITOR_BASIC_MESSAGE_NAME)
    @Column(columnName = "executor_duration", columnType = ColumnType.INTEGER, length = 20)
    private Long executorDuration;
    /**
     * 執行是否成功
     */
    @FormFieldConfig(fieldCode = "is_executor_success", fieldName = "是否執行成功", elementCode = DrivenElementConstants.RADIO_ELEMENT,
            pageDefaultValue = EnableCodeDictionary.YES, fieldParentName = MONITOR_BASIC_MESSAGE_NAME,
            dict = @DictConfig(dictCode = EnableCodeDictionary.DICTIONARY_CODE))
    @Column(columnName = "is_executor_success", columnType = ColumnType.VARCHAR, length = SqlConstants.DICTIONARY_VALUE_SIZE)
    private String isExecutorSuccess;
    /**
     * 數據庫執行器包名
     */
    @FormFieldConfig(fieldCode = "executor_class_name", fieldName = "執行器名稱", fieldParentName = MONITOR_BASIC_MESSAGE_NAME)
    @Column(columnName = "executor_class_name", columnType = ColumnType.VARCHAR, length = SqlConstants.DB_NAME_SIZE)
    private String executorClassName;
    /**
     * 執行Sql語句
     */
    @Column(columnName = "execute_sql", columnType = ColumnType.LONG_TEXT, comment = "執行SQL")
    @FormFieldConfig(fieldCode = "execute_sql", fieldName = "執行SQL", config = @FieldConfig(appendCss = FieldConfigConstants.CSS_HALF_SCREEN_COL),
            elementCode = DrivenElementConstants.BIG_INPUT_ELEMENT, isTableEnable = false, fieldParentName = BaseMonitorPO.DETAIL_BASIC_MESSAGE_NAME)
    private String executeSql;
    /**
     * 執行參數
     */
    @Column(columnName = "execute_params", columnType = ColumnType.LONG_TEXT, comment = "執行參數")
    @FormFieldConfig(fieldCode = "execute_params", fieldName = "執行參數", config = @FieldConfig(appendCss = FieldConfigConstants.CSS_HALF_SCREEN_COL),
            elementCode = DrivenElementConstants.BIG_INPUT_ELEMENT, isTableEnable = false, fieldParentName = BaseMonitorPO.DETAIL_BASIC_MESSAGE_NAME)
    private String executeParams;
    /**
     * 返回結果
     */
    @Column(columnName = "return_results", columnType = ColumnType.LONG_TEXT, comment = "返回結果")
    @FormFieldConfig(fieldCode = "return_results", fieldName = "返回結果", config = @FieldConfig(appendCss = FieldConfigConstants.CSS_HALF_SCREEN_COL),
            elementCode = DrivenElementConstants.BIG_INPUT_ELEMENT, isTableEnable = false, fieldParentName = BaseMonitorPO.DETAIL_BASIC_MESSAGE_NAME)
    private String returnResults;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getConvertClassName() {
        return convertClassName;
    }
    public void setConvertClassName(String convertClassName) {
        this.convertClassName = convertClassName;
    }
    public Long getExecutorDuration() {
        return executorDuration;
    }
    public void setExecutorDuration(Long executorDuration) {
        this.executorDuration = executorDuration;
    }
    public String getIsExecutorSuccess() {
        return isExecutorSuccess;
    }
    public void setIsExecutorSuccess(String isExecutorSuccess) {
        this.isExecutorSuccess = isExecutorSuccess;
    }
    public String getExecutorClassName() {
        return executorClassName;
    }
    public void setExecutorClassName(String executorClassName) {
        this.executorClassName = executorClassName;
    }
    public String getExecuteSql() {
        return executeSql;
    }
    public void setExecuteSql(String executeSql) {
        this.executeSql = executeSql;
    }
    public String getExecuteParams() {
        return executeParams;
    }
    public void setExecuteParams(String executeParams) {
        this.executeParams = executeParams;
    }
    public String getReturnResults() {
        return returnResults;
    }
    public void setReturnResults(String returnResults) {
        this.returnResults = returnResults;
    }
}

監控工廠實現

package com.flycoding.monitor.factory;
import com.flycoding.biz.manage.constants.ManageDictionaryConstants;
import com.flycoding.monitor.entity.MonitorSqlPO;
import com.flycoding.monitor.entity.base.BaseMonitorPO;
import com.flycoding.monitor.factory.base.BaseMonitorFactory;
import com.flycoding.drivenlibrary.engine.config.DrivenEngineConfig;
import com.flycoding.drivenlibrary.engine.config.constants.DrivenConstants;
import com.flycoding.drivenlibrary.engine.config.factory.ClassAliasFactory;
import com.flycoding.utillibrary.BasicUtils;
import com.flycoding.utillibrary.date.TimeUtils;
import com.flycoding.utillibrary.date.enums.DateStyle;
import com.flycoding.utillibrary.java.JSONUtils;
import com.flycoding.utillibrary.java.ThrowableUtil;
import com.flycoding.utillibrary.strings.StringUtils;
/**
 * 監控SQL的工廠類
 *
 * @author 趙屈犇
 * @version 1.0
 * @date 創建時間: 2020/10/8 20:50
 */
public class MonitorSqlFactory extends BaseMonitorFactory<MonitorSqlFactory> {
    /**
     * 查詢結果
     */
    private Object results;
    /**
     * 請求參數
     */
    private Object[] params;
    /**
     * 是否執行成功
     */
    private boolean isSuccess;
    /**
     * 轉換的class
     */
    private Class convertClass;
    /**
     * 執行sql  執行器擴展類
     */
    private String sql, executorClassName;
    /**
     * 執行結果集  sql   執行參數
     */
    private boolean isReturnResults, isSaveSql, isExecuteParams;
    public static MonitorSqlFactory builder() {
        return new MonitorSqlFactory();
    }
    private MonitorSqlFactory() {
        super();
        isSaveSql = DrivenEngineConfig.getConfigValue(prefix   DrivenConstants.MonitorConstants.IS_SAVE_SQL, true);
        isReturnResults = DrivenEngineConfig.getConfigValue(prefix   DrivenConstants.MonitorConstants.IS_SAVE_RETURN_RESULTS, true);
        isExecuteParams = DrivenEngineConfig.getConfigValue(prefix   DrivenConstants.MonitorConstants.IS_SAVE_EXECUTE_PARAMS, true);
    }
    /**
     * sql語句
     *
     * @param sql
     * @return
     */
    public MonitorSqlFactory sql(String sql) {
        if (isMonitor && isSaveSql) {
            this.sql = sql;
        }
        return this;
    }
    /**
     * 執行結果
     *
     * @param results
     * @return
     */
    public MonitorSqlFactory results(Object results) {
        if (isMonitor && isReturnResults) {
            this.results = results;
        }
        return this;
    }
    /**
     * 請求參數
     *
     * @param params
     * @return
     */
    public MonitorSqlFactory params(Object[] params) {
        if (isMonitor && isExecuteParams) {
            this.params = params;
        }
        return this;
    }
    /**
     * 是否執行成功
     *
     * @param isSuccess
     * @return
     */
    public MonitorSqlFactory success(boolean isSuccess) {
        this.isSuccess = isSuccess;
        return this;
    }
    /**
     * 轉換class類
     *
     * @param convertClass
     * @return
     */
    public MonitorSqlFactory convertClass(Class convertClass) {
        this.convertClass = convertClass;
        return this;
    }
    /**
     * 執行器class
     *
     * @param executorClassName
     * @return
     */
    public MonitorSqlFactory executorClassName(String executorClassName) {
        this.executorClassName = executorClassName;
        return this;
    }
    @Override
    protected BaseMonitorPO getMonitorInfo(long duration, MonitorFileFactory monitorFileFactory) throws Exception {
        MonitorSqlPO monitorInfo = new MonitorSqlPO();
        // 執行器類全名
        if (StringUtils.isNotEmpty(executorClassName)) {
            monitorInfo.setExecutorClassName(executorClassName);
        }
        // 是否執行成功
        monitorInfo.setIsExecutorSuccess(BasicUtils.getEnableCode(isSuccess));
        // 轉換類
        monitorInfo.setConvertClassName(ClassAliasFactory.getClassName(convertClass));
        // 存儲sql語句
        if (isSaveSql) {
            monitorInfo.setExecuteSql(sql);
        }
        // 存儲請求參數
        if (isExecuteParams) {
            monitorInfo.setExecuteParams(JSONUtils.toJSON(params));
        }
        // 存儲返回結果
        if (isReturnResults) {
            monitorInfo.setReturnResults(JSONUtils.toJSON(results));
        }
        if (isOutputConsole) {
            StringBuffer content = new StringBuffer("====================").append(TimeUtils.getNowTimeString(DateStyle.YYYY_MM_DD_HH_MM_SS_SSS.getValue()))
                    .append("===============").append("\nSQL語句:\n")
                    .append(sql).append("\n執行參數:\n").append(JSONUtils.toJSON(params))
                    .append("\n執行結果:\n").append(JSONUtils.toJSON(results));
            if (throwable != null) {
                content.append("\n錯誤信息:\n").append(ThrowableUtil.throwableConvertString(throwable));
            }
            content.append("\n==========================執行時長:").append(duration).append("==========================");
            if (isSuccess) {
                System.out.println(content);
            } else {
                System.err.println(content);
            }
        }
        // 設置執行時長
        monitorInfo.setExecutorDuration(duration);
        return monitorInfo;
    }
    @Override
    protected String getMonitorConfigKey() {
        return "sql";
    }
    @Override
    protected String getMonitorChannelCode() {
        return ManageDictionaryConstants.ChannelDictionary.MONITOR_SQL;
    }
}

此工廠，實現了sql記錄功能。