Springboot+logback+druid +密码加密 实现业务日志入库

springboot 配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 主库数据源
      master:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/dbName?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&allowMultiQueries=true
        username: uname
        password: ENC(密文)

logback-spring.xml配置

    <!--日志异步到数据库  -->
    <appender name="dbAppender" class="自定义的dbappender.CustomDBAppender">
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>${logLevel}</level>
        </filter>
        <!--自定义的数据源，继承了 ch.qos.logback.core.db.DataSourceConnectionSource    -->
        <connectionSource class="xxx.CustomDruidDataSource">
            <dataSource class="com.alibaba.druid.pool.DruidDataSource">
                <driverClassName>${driver}</driverClassName>
                <url>${url}</url>
                <username>${userName}</username>
            </dataSource>
        </connectionSource>
    </appender>
    <logger level="info" name="具体的业务实现类.impl.DataProductServiceImpl">
        <appender-ref ref="dbAppender"/>
    </logger>
    <logger level="info" name="具体的业务实现类.MessageConsumer">
        <appender-ref ref="dbAppender"/>
    </logger>

如果业务中密码不需要加密，其实可以使用默认的数据库连接 ch.qos.logback.core.db.DataSourceConnectionSource，此时密码是明文

代码

CustomDBAppender

import ch.qos.logback.classic.db.DBHelper;
import ch.qos.logback.classic.db.names.DBNameResolver;
import ch.qos.logback.classic.db.names.DefaultDBNameResolver;
import ch.qos.logback.classic.spi.*;
import ch.qos.logback.core.db.ConnectionSource;
import ch.qos.logback.core.db.DBAppenderBase;
import ch.qos.logback.core.db.dialect.SQLDialectCode;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CustomDBAppender extends DBAppenderBase<ILoggingEvent> {

    protected String insertExceptionSQL;
    protected String insertSQL;
    protected static final Method GET_GENERATED_KEYS_METHOD;
    private DBNameResolver dbNameResolver;

    static final StackTraceElement EMPTY_CALLER_DATA = CallerData.naInstance();

    public CustomDBAppender() {
    }

    public void setDbNameResolver(DBNameResolver dbNameResolver) {
        this.dbNameResolver = dbNameResolver;
    }
    /**
     * @param connectionSource
     *          The connectionSource to set.
     */
    @Override
    public void setConnectionSource(ConnectionSource connectionSource) {
        this.connectionSource = connectionSource;
    }

    @Override
    public void append(ILoggingEvent 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 {
            ch.qos.logback.core.db.DBHelper.closeStatement(insertStatement);
            ch.qos.logback.core.db.DBHelper.closeConnection(connection);
        }
    }


    @Override
    public void start() {
        if (this.dbNameResolver == null) {
            this.dbNameResolver = new DefaultDBNameResolver();
        }

        this.insertExceptionSQL = SQLBuilder.buildInsertExceptionSQL(this.dbNameResolver);

        this.insertSQL = SQLBuilder.buildInsertSQL(this.dbNameResolver);
        super.start();
    }

    @Override
    protected void subAppend(ILoggingEvent event, Connection connection, PreparedStatement insertStatement) throws Throwable {
        this.bindLoggingEventWithInsertStatement(insertStatement, event);
        this.bindLoggingEventArgumentsWithPreparedStatement(insertStatement, event.getArgumentArray());
        StackTraceElement[] stackTraceElements = event.getCallerData();
        if (event.getThrowableProxy() != null) {
            if (event.getThrowableProxy().getCause() != null) {
                StackTraceElementProxy[] stackTraceElementProxies = event.getThrowableProxy().getCause().getStackTraceElementProxyArray();
                if (stackTraceElementProxies != null && stackTraceElementProxies.length > 0) {
                    for (StackTraceElementProxy item : stackTraceElementProxies
                    ) {
                        if (item.getStackTraceElement().getClassName().indexOf("com.xx") >= 0 && item.getStackTraceElement().getLineNumber() > 0) {
                            stackTraceElements[0] = item.getStackTraceElement();
                            break;
                        }
                    }
                }
            }
        }
        this.bindCallerDataWithPreparedStatement(insertStatement, stackTraceElements);
        int updateCount = insertStatement.executeUpdate();
        if (updateCount != 1) {
            this.addWarn("Failed to insert loggingEvent");
        }

    }

    @Override
    protected void secondarySubAppend(ILoggingEvent event, Connection connection, long eventId) throws Throwable {
        /*Map<String, String> mergedMap = this.mergePropertyMaps(event);
        this.insertProperties(mergedMap, connection, eventId);*/
        if (event.getThrowableProxy() != null) {
            this.insertThrowable(event.getThrowableProxy(), connection, eventId);
        }

    }

    void bindLoggingEventWithInsertStatement(PreparedStatement stmt, ILoggingEvent event) throws SQLException {
        stmt.setLong(1, event.getTimeStamp());
        stmt.setString(2, event.getFormattedMessage());
        stmt.setString(3, event.getLoggerName());
        stmt.setString(4, event.getLevel().toString());
        stmt.setString(5, event.getThreadName());
        stmt.setShort(6, DBHelper.computeReferenceMask(event));
    }

    void bindLoggingEventArgumentsWithPreparedStatement(PreparedStatement stmt, Object[] argArray) throws SQLException {
        int arrayLen = argArray != null ? argArray.length : 0;

        int i;
        for (i = 0; i < arrayLen && i < 4; ++i) {
            stmt.setString(7 + i, this.asStringTruncatedTo254(argArray[i]));
        }

        if (arrayLen < 4) {
            for (i = arrayLen; i < 4; ++i) {
                stmt.setString(7 + i, (String) null);
            }
        }

    }

    String asStringTruncatedTo254(Object o) {
        String s = null;
        if (o != null) {
            s = o.toString();
        }

        if (s == null) {
            return null;
        } else {
            return s.length() <= 254 ? s : s.substring(0, 254);
        }
    }

    void bindCallerDataWithPreparedStatement(PreparedStatement stmt, StackTraceElement[] callerDataArray) throws SQLException {
        StackTraceElement caller = this.extractFirstCaller(callerDataArray);
        stmt.setString(11, caller.getFileName());
        stmt.setString(12, caller.getClassName());
        stmt.setString(13, caller.getMethodName());
        stmt.setString(14, Integer.toString(caller.getLineNumber()));
    }

    private StackTraceElement extractFirstCaller(StackTraceElement[] callerDataArray) {
        StackTraceElement caller = EMPTY_CALLER_DATA;
        if (this.hasAtLeastOneNonNullElement(callerDataArray)) {
            caller = callerDataArray[0];
        }

        return caller;
    }

    private boolean hasAtLeastOneNonNullElement(StackTraceElement[] callerDataArray) {
        return callerDataArray != null && callerDataArray.length > 0 && callerDataArray[0] != null;
    }


    @Override
    protected Method getGeneratedKeysMethod() {
        return GET_GENERATED_KEYS_METHOD;
    }

    @Override
    protected String getInsertSQL() {
        return this.insertSQL;
    }


    void updateExceptionStatement(PreparedStatement exceptionStatement, String txt, short i, long eventId) throws SQLException {
        exceptionStatement.setLong(1, eventId);
        exceptionStatement.setShort(2, i);
        exceptionStatement.setString(3, txt);
        if (this.cnxSupportsBatchUpdates) {
            exceptionStatement.addBatch();
        } else {
            exceptionStatement.execute();
        }

    }

    short buildExceptionStatement(IThrowableProxy tp, short baseIndex, PreparedStatement insertExceptionStatement, long eventId) throws SQLException {
        try {
            StringBuilder buf = new StringBuilder();
            ThrowableProxyUtil.subjoinFirstLine(buf, tp);
            this.updateExceptionStatement(insertExceptionStatement, buf.toString(), baseIndex++, eventId);
            int commonFrames = tp.getCommonFrames();
            StackTraceElementProxy[] stepArray = tp.getStackTraceElementProxyArray();

            for (int i = 0; i < stepArray.length; ++i) {
                StringBuilder sb = new StringBuilder();
                sb.append('\t');
                ThrowableProxyUtil.subjoinSTEP(sb, stepArray[i]);
                this.updateExceptionStatement(insertExceptionStatement, sb.toString(), baseIndex++, eventId);
            }


        } catch (Exception e) {
            System.out.println("11");
        }
        return baseIndex;

    }

    protected void insertThrowable(IThrowableProxy tp, Connection connection, long eventId) throws SQLException {
        PreparedStatement exceptionStatement = null;

        try {
            exceptionStatement = connection.prepareStatement(this.insertExceptionSQL);
            short baseIndex = 0;

            while (true) {
                if (tp == null) {
                    if (this.cnxSupportsBatchUpdates) {
                        exceptionStatement.executeBatch();
                    }
                    break;
                }
                if (tp.getCause() != null) {
                    tp = tp.getCause();
                }
                baseIndex = this.buildExceptionStatement(tp, baseIndex, exceptionStatement, eventId);
                tp = tp.getCause();
            }
        } finally {
            ch.qos.logback.core.db.DBHelper.closeStatement(exceptionStatement);
        }

    }

    static {
        Method getGeneratedKeysMethod;
        try {
            getGeneratedKeysMethod = PreparedStatement.class.getMethod("getGeneratedKeys", (Class[]) null);
        } catch (Exception var2) {
            getGeneratedKeysMethod = null;
        }

        GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;
    }
}

CustomDruidDataSource

import ch.qos.logback.core.db.DataSourceConnectionSource;
import ch.qos.logback.core.db.dialect.SQLDialectCode;
import com.alibaba.druid.pool.DruidDataSource;
import xxx.JasyptUtil;

import javax.sql.DataSource;
import java.lang.management.ManagementFactory;
import java.lang.management.RuntimeMXBean;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class CustomDruidDataSource extends DataSourceConnectionSource {

    @Override
    public void start() {
        if (super.getDataSource() == null) {
            addWarn("WARNING: No data source specified");
        } else {
            discoverConnectionProperties();
            if (!supportsGetGeneratedKeys() && getSQLDialectCode() == SQLDialectCode.UNKNOWN_DIALECT) {
                addWarn("Connection does not support GetGeneratedKey method and could not discover the dialect.");
            }
        }
    }

    /**
     * @see ch.qos.logback.core.db.ConnectionSource#getConnection()
     */
    public Connection getConnection() throws SQLException {
        DruidDataSource dataSource = (DruidDataSource) super.getDataSource();
        //对加密的密码进行解密
        String password = dataSource.getPassword();
        //对encryptedPassword 处理
        //判断是否加密处理了
        if (password != null && password.startsWith("ENC(")) {
            String replaceStr = password.replace("ENC(", "");
            String realPdw = replaceStr.substring(0, replaceStr.length() - 1);
            String saltValue = getSaltValue();
            // 解密密码
            String decryptedPassword = JasyptUtil.decyptPwd(saltValue, realPdw);
            dataSource.setPassword(decryptedPassword);
        }
        super.setDataSource(dataSource);
        if (super.getDataSource() == null) {
            addError("WARNING: No data source specified");
            return null;
        }

        if (getUser() == null) {
            return super.getDataSource().getConnection();
        } else {
            return super.getDataSource().getConnection(getUser(), getPassword());
        }
    }

    private String getSaltValue() {
        RuntimeMXBean runtimeMxBean = ManagementFactory.getRuntimeMXBean();
        List<String> inputArguments = runtimeMxBean.getInputArguments();
        String saltKey = "-Djasypt.encryptor.password=";
        String saltValue = "";
        for (String arg : inputArguments) {
            if (arg.startsWith(saltKey)) {
                saltValue = arg.replace(saltKey, "");
                return saltValue;
            }
        }
        return "standard-value-conversion@2022";
    }
}

SQLBuilder

import ch.qos.logback.classic.db.names.ColumnName;
import ch.qos.logback.classic.db.names.DBNameResolver;
import ch.qos.logback.classic.db.names.TableName;

public class SQLBuilder {
    public SQLBuilder() {
    }

    static String buildInsertPropertiesSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOGGING_EVENT_PROPERTY)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.EVENT_ID)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.MAPPED_KEY)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.MAPPED_VALUE)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ?)");
        return sqlBuilder.toString();
    }

    static String buildInsertExceptionSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOGGING_EVENT_EXCEPTION)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.EVENT_ID)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.I)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.TRACE_LINE)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ?)");
        return sqlBuilder.toString();
    }

    static String buildInsertSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOGGING_EVENT)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.TIMESTMP)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.FORMATTED_MESSAGE)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.LOGGER_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.LEVEL_STRING)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.THREAD_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.REFERENCE_FLAG)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.ARG0)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.ARG1)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.ARG2)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.ARG3)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.CALLER_FILENAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.CALLER_CLASS)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.CALLER_METHOD)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnName.CALLER_LINE)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        return sqlBuilder.toString();
    }
}

加解密工具类
pom依赖

        <dependency>
            <groupId>com.github.ulisesbocchio</groupId>
            <artifactId>jasypt-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

import org.jasypt.encryption.pbe.PooledPBEStringEncryptor;
import org.jasypt.encryption.pbe.config.SimpleStringPBEConfig;

/**
 *
 */
public class JasyptUtil {

    /**
     * Jasypt生成加密结果
     *
     * @param password 配置文件中设定的加密密
     * @param value    加密值
     * @return
     */
    public static String encyptPwd(String password, String value) {
        PooledPBEStringEncryptor encryptor = new PooledPBEStringEncryptor();
        encryptor.setConfig(cryptor(password));
        String result = encryptor.encrypt(value);
        return result;
    }

    /**
     * 解密
     *
     * @param password 配置文件中设定的加密密码
     * @param value    解密密文
     * @return
     */
    public static String decyptPwd(String password, String value) {
        PooledPBEStringEncryptor encryptor = new PooledPBEStringEncryptor();
        encryptor.setConfig(cryptor(password));
        String result = encryptor.decrypt(value);
        return result;
    }

    public static SimpleStringPBEConfig cryptor(String password) {
        SimpleStringPBEConfig config = new SimpleStringPBEConfig();
        config.setPassword(password);
        config.setAlgorithm("PBEWithMD5AndDES");
        config.setKeyObtentionIterations("1000");
        config.setPoolSize("1");
        config.setProviderName("SunJCE");
        config.setSaltGeneratorClassName("org.jasypt.salt.RandomSaltGenerator");
        config.setStringOutputType("base64");
        return config;
    }

    public static void main(String[] args) {
        //加密
        //System.out.println(encyptPwd("盐值", "明文密码"));
        //解密
        //System.out.println(decyptPwd("盐值", "密文密码"));
    }
}

密文的盐值，可以采用jvm 的方式进行传递

-Xms3G -Xmx8G -Djasypt.encryptor.password=盐值

日志写入的sql表为

CREATE TABLE `logging_event` (
  `timestmp` bigint(20) NOT NULL,
  `formatted_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `logger_name` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `level_string` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `thread_name` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_flag` smallint(6) DEFAULT NULL,
  `arg0` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `arg1` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `arg2` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `arg3` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caller_filename` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `caller_class` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `caller_method` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `caller_line` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `event_id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`event_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3634 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

效果图