自定义数据库字段日志存储 log4jdbc-log4j2+logback

吱~~吱~…

于 2022-10-27 10:11:27 发布

阅读量402

点赞数

文章标签：数据库 log4j logback

本文链接：https://blog.csdn.net/qq_44630461/article/details/127545627

版权

自定义日志存储 log4jdbc-log4j2+logback（包括sql存储）

自定义MyDBAppender
前提配置好log4jdbc.log4j2.properties和logback.xml文件
创建好表：
表略
"INSERT INTO " +
" log(px_name,info,log_status,s_sql,sql_count,start_time,end_time) "+
“VALUES (?,?,?,?,?,?,?)”
详细内容请看 注释：

///**
// * Logback: the reliable, generic, fast and flexible logging framework.
// * Copyright (C) 1999-2015, QOS.ch. All rights reserved.
// *
// * This program and the accompanying materials are dual-licensed under
// * either the terms of the Eclipse Public License v1.0 as published by
// * the Eclipse Foundation
// *
// *   or (per the licensee's choosing)
// *
// * under the terms of the GNU Lesser General Public License version 2.1
// * as published by the Free Software Foundation.
// */
package com.weipu.dx45gdata.common.config;
//
//import static ch.qos.logback.core.db.DBHelper.closeStatement;
//
//import java.lang.reflect.Method;
//import java.sql.Connection;
//import java.sql.PreparedStatement;
//import java.sql.SQLException;
//import java.sql.Timestamp;
//import java.util.HashMap;
//import java.util.Map;
//import java.util.Set;
//
//import ch.qos.logback.classic.db.DBHelper;
//import ch.qos.logback.classic.db.names.ColumnName;
//import ch.qos.logback.classic.db.names.DBNameResolver;
//import ch.qos.logback.classic.db.names.DefaultDBNameResolver;
//import ch.qos.logback.classic.db.names.TableName;
//import ch.qos.logback.classic.spi.*;
//import ch.qos.logback.core.CoreConstants;

import ch.qos.logback.classic.db.names.ColumnName;
import ch.qos.logback.classic.db.names.DBNameResolver;
import ch.qos.logback.classic.db.names.DefaultDBNameResolver;
import ch.qos.logback.classic.db.names.TableName;
import ch.qos.logback.classic.spi.*;
import ch.qos.logback.core.CoreConstants;
import ch.qos.logback.core.db.DBAppenderBase;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

/**
 * The DBAppender inserts logging events into three database tables in a format
 * independent of the Java programming language.
 *
 * For more information about this appender, please refer to the online manual
 * at http://logback.qos.ch/manual/appenders.html#DBAppender
 *
 * @author Ceki G&uuml;lc&uuml;
 * @author Ray DeCampo
 * @author S&eacute;bastien Pennec
 */
@Slf4j
public class MyDBAppender extends DBAppenderBase<ILoggingEvent> {
//    protected String insertPropertiesSQL; 自带的表插入语句
//    protected String insertExceptionSQL自带的表插入语句
    protected String insertSQL; //自定义表插入语句
    protected static final Method GET_GENERATED_KEYS_METHOD;

    private DBNameResolver dbNameResolver;
	//该数字顺序是作用在PrepareStatment上
    static final int PX_NAME_INDEX=1;
    static final int  INFO_INDEX=2;
    static final int  STATUS_INDEX=3;
    static final int SQL_INDEX=4;
    static final int SQL_COUNT_INDEX=5;
    static final int START_TIME_INDEX=6;
    static final int END_TIME_INDEX=7;
    static final int ARG0_INDEX = 5;
    static final int CALLER_FILENAME_INDEX = 6;
    static final int CALLER_CLASS_INDEX = 7;
    static final int CALLER_METHOD_INDEX = 8;
    static final int CALLER_LINE_INDEX = 9;
    static final int EVENT_ID_INDEX = 10;

    static final StackTraceElement EMPTY_CALLER_DATA = CallerData.naInstance();

    static {
        // PreparedStatement.getGeneratedKeys() method was added in JDK 1.4
        Method getGeneratedKeysMethod;
        try {
            // the
            getGeneratedKeysMethod = PreparedStatement.class.getMethod("getGeneratedKeys", (Class[]) null);
        } catch (Exception ex) {
            getGeneratedKeysMethod = null;
        }
        GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;
    }

    public void setDbNameResolver(DBNameResolver dbNameResolver) {
        this.dbNameResolver = dbNameResolver;
    }

    @Override
    //启动的地方，相当于初始化的地方
    public void start() {
        if (dbNameResolver == null)
            dbNameResolver = new DefaultDBNameResolver();
//        insertExceptionSQL = buildInsertExceptionSQL(dbNameResolver);
//        insertPropertiesSQL = buildInsertPropertiesSQL(dbNameResolver);
		//获取到插入语句
        insertSQL = buildInsertSQL(dbNameResolver);
        super.start();
    }

    @Override
    //该类工作的地方，表示初始化完成，执行的第一个工作方法
    //原理是在父类的public void append(E eventObject) 方法里
    protected void subAppend(ILoggingEvent event, Connection connection, PreparedStatement insertStatement) throws Throwable {

		
        String info = bindLoggingEventWithInsertStatement(insertStatement, event);
        if(info.isEmpty()){
            return;
        }
        int updateCount = insertStatement.executeUpdate();
        if (updateCount != 1) {
            addWarn("Failed to insert loggingEvent");
        }
    }
 //该类工作的地方，表示初始化完成，执行的第二个工作方法
     protected void secondarySubAppend(ILoggingEvent event, Connection connection, long eventId) throws Throwable {
     
// 若需要把错误sql语句记录在日志中，并把正确sql剔除，请看这里。
//原理：sql信息和sql错误信息是连续的，也就是eventId连续，当插入sql信息后，如果下一次是Error信息并且为"jdbc.sqlonly"，就会删除之前插入sql的信息。
if(event.getLevel().levelStr.equals("ERROR")&&event.getLoggerName().equals("jdbc.sqlonly")){
//            PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM log WHERE event_id = ?");
//            preparedStatement.setLong(1,eventId-1);
//            int i = preparedStatement.executeUpdate( );
//            if(i!=1){
//                addWarn("Failed to Delete loggingEvent");
//            };
//
//        }

//        Map<String, String> mergedMap = mergePropertyMaps(event);
//        insertProperties(mergedMap, connection, eventId);
//
//        if (event.getThrowableProxy() != null) {
//            insertThrowable(event.getThrowableProxy(), connection, eventId);
//
//        }
    }

    String bindLoggingEventWithInsertStatement(PreparedStatement stmt, ILoggingEvent event) throws SQLException {
        String px_name="";
        String info="";
        String status="";
        String sql="";
        Integer sql_count=0;
        Timestamp start_time = null;
        Timestamp end_time=null;

        //px_name
        px_name=event.getMDCPropertyMap().get("px_name");
        //status
        status=event.getLevel( ).levelStr.equals("ERROR")?"failed":"success";
        //info sql
        if(event.getLoggerName().equals("jdbc.sqlonly")){
        	
            //记录jdbc.sqlonly ERROR级别信息  event.getThrowableProxy()不为空
            //jdbc.sqlonly错误信息存在代理异常。
            if(event.getThrowableProxy()!=null){
                IThrowableProxy throwableProxy = event.getThrowableProxy( );
                info=throwableProxy.getClassName()+": "+throwableProxy.getMessage();
                sql = event.getMessage( ).substring(event.getMessage( ).indexOf("I"));
            }
        }
        else{
            // 主动抛出异常不是代理异常
            if(event.getThrowableProxy()!=null){
            	//基本不会触发
                info=ThrowableProxyUtil.asString(event.getThrowableProxy( )).substring(0,255);
            }else{
                //拦截了
                info=event.getMessage();
            }
        }
        //sql_count
        if (event.getMDCPropertyMap().get("sql_count")!=null){
            sql_count= Integer.valueOf(event.getMDCPropertyMap().get("sql_count"));
        }
        //start_time
        if(event.getMDCPropertyMap().get("start_time")!=null){
            start_time=new Timestamp(Long.valueOf(event.getMDCPropertyMap().get("start_time")));
        }

        //end_time
        if(event.getMDCPropertyMap().get("end_time")!=null){
            end_time=new Timestamp(Long.valueOf(event.getMDCPropertyMap().get("end_time")));
        }

        stmt.setString(PX_NAME_INDEX,px_name);
        stmt.setString(INFO_INDEX,info);
        stmt.setString(STATUS_INDEX,status);
        stmt.setString(SQL_INDEX,sql);
        stmt.setInt(SQL_COUNT_INDEX,sql_count);
        stmt.setTimestamp(START_TIME_INDEX,start_time);
        stmt.setTimestamp(END_TIME_INDEX,end_time);
        return info;

    }
    //自带表后尾字段处理的 不需要
    
    @Override
    protected Method getGeneratedKeysMethod() {
        return GET_GENERATED_KEYS_METHOD;
    }

    @Override
    protected String getInsertSQL() {
        return insertSQL;
    }
	//自定义表插入sql
    static String buildInsertSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder(
                "INSERT INTO " +
                        " log(px_name,info,log_status,s_sql,sql_count,start_time,end_time) "+
                        "VALUES (?,?,?,?,?,?,?)"
        );
        return sqlBuilder.toString();
    }
}

以下是自定义类的父类：
核心在这个方法里了

 @Override
    public void append(E eventObject) {
        Connection connection = null;
        PreparedStatement insertStatement = null;
        try {
            connection = connectionSource.getConnection();
            connection.setAutoCommit(false);

            if (cnxSupportsGetGeneratedKeys) {
                String EVENT_ID_COL_NAME = "EVENT_ID";
                // see
                if (connectionSource.getSQLDialectCode() == SQLDialectCode.POSTGRES_DIALECT) {
                    EVENT_ID_COL_NAME = EVENT_ID_COL_NAME.toLowerCase();
                }
                insertStatement = connection.prepareStatement(getInsertSQL(), new String[] { EVENT_ID_COL_NAME });
            } else {
                insertStatement = connection.prepareStatement(getInsertSQL());
            }

            long eventId;
            // inserting an event and getting the result must be exclusive
            synchronized (this) {
                subAppend(eventObject, connection, insertStatement);
                eventId = selectEventId(insertStatement, connection);
            }
            secondarySubAppend(eventObject, connection, eventId);

            connection.commit();
        } catch (Throwable sqle) {
            addError("problem appending event", sqle);
        } finally {
            DBHelper.closeStatement(insertStatement);
            DBHelper.closeConnection(connection);
        }
    }

总结：
该类的主要内容包括相关字段（列顺序，插入语句）、方法（启动方法、工作方法1、工作方法2、获取插入语句方法、为PrepareStatment放入参数方法）。
注意：
无法异步，由于父类的synchronized 作用域没有包含connection.commit()，在提交之前Connection的一些内容可能会被覆盖和清空，可能会出现莫名其妙的情况。
留言：
有写不到位的地方还请大佬指正，顺便能给出异步问题的解决思路就更好了。
(^_)