logback日志如何寫入mysql自定義表

logback日志如何寫入mysql自定義表，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

1，先看 DBAppender

package com.liudandan.logbackmysql.controller.config;
import ch.qos.logback.classic.db.names.DBNameResolver;
import ch.qos.logback.classic.db.names.DefaultDBNameResolver;
import ch.qos.logback.classic.spi.*;
import com.liudandan.logbackmysql.controller.MyDBAppenderBase;
import com.liudandan.logbackmysql.controller.buildInsertSQL;

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


public class DBAppender extends MyDBAppenderBase<ILoggingEvent> {

    private String insertSQL;
    private static final Method GET_GENERATED_KEYS_METHOD;

    private DBNameResolver dbNameResolver;

    protected String insertDebugSQL;
    protected String insertErrorSQL;
    protected String insertInfoSQL;

    private static final int TIME_INDEX = 1;
    private static final int MESSAGE_INDEX = 2;
    private static final int LEVEL_STRING_INDEX = 3;
    private static final int LOGGER_NAME_INDEX = 4;
    private static final int THREAD_NAME_INDEX = 5;
    private static final int CALLER_FILENAME_INDEX = 6;
    private static final int CALLER_CLASS_INDEX = 7;
    private static final int CALLER_METHOD_INDEX = 8;
    private static final int CALLER_LINE_INDEX = 9;
    private static final int ORG_ID_INDEX = 10;

    private 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;
    }

//    @Override
//    public void start() {
//        insertSQL = buildInsertSQL();
//        super.start();
//    }

    @Override
    public void start() {
        if (dbNameResolver == null)
            dbNameResolver = new DefaultDBNameResolver();
        insertDebugSQL = buildInsertSQL.buildInsertDebugSQL(dbNameResolver);
        insertErrorSQL = buildInsertSQL.buildInsertErrorSQL(dbNameResolver);
        insertInfoSQL = buildInsertSQL.buildInsertInfoSQL(dbNameResolver);
//        insertSQL = buildInsertSQL.buildInsertSQL();
        super.start();
    }


    private static String buildInsertSQL() {
        return "INSERT INTO log_record " +
                "(time, message, logger_name, level_string, thread_name," +
                "caller_filename, caller_class, caller_method, caller_line, org_id)"+
                "VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?)";
    }

    private void bindLoggingEventWithInsertStatement(PreparedStatement stmt, ILoggingEvent event) throws SQLException {
        stmt.setTimestamp(TIME_INDEX, new Timestamp(event.getTimeStamp()));
        stmt.setString(MESSAGE_INDEX, event.getFormattedMessage());
        stmt.setString(LEVEL_STRING_INDEX, event.getLevel().toString());
        stmt.setString(LOGGER_NAME_INDEX, event.getLoggerName());
        stmt.setString(THREAD_NAME_INDEX, event.getThreadName());
        stmt.setString(ORG_ID_INDEX, String.valueOf(12));
    }

    private void bindCallerDataWithPreparedStatement(PreparedStatement stmt, StackTraceElement[] callerDataArray) throws SQLException {
        StackTraceElement caller = extractFirstCaller(callerDataArray);
        stmt.setString(CALLER_FILENAME_INDEX, caller.getFileName());
        stmt.setString(CALLER_CLASS_INDEX, caller.getClassName());
        stmt.setString(CALLER_METHOD_INDEX, caller.getMethodName());
        stmt.setString(CALLER_LINE_INDEX, Integer.toString(caller.getLineNumber()));
//        stmt.setString(ORG_ID_INDEX, String.valueOf(12));
    }
//2
    @Override
    protected void subAppend(ILoggingEvent event, Connection connection, PreparedStatement insertStatement) throws Throwable {
            bindLoggingEventWithInsertStatement(insertStatement, event);
            // This is expensive... should we do it every time?
            bindCallerDataWithPreparedStatement(insertStatement, event.getCallerData());
            int updateCount = insertStatement.executeUpdate();
            if (updateCount != 1) {
                addWarn("Failed to insert loggingEvent");
            }

    }

    @Override
    protected void secondarySubAppend(ILoggingEvent eventObject, Connection connection, long eventId) throws Throwable {

    }
//3
    private StackTraceElement extractFirstCaller(StackTraceElement[] callerDataArray) {
        StackTraceElement caller = EMPTY_CALLER_DATA;
        if (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 insertDebugSQL;
//    }


    protected String getInsertErrorSQL() {
        return insertErrorSQL;
    }


    protected String getInsertInfoSQL() {
        return insertInfoSQL;
    }


    protected String getInsertDebugSQL() {
        return insertDebugSQL;
    }

}

2，

buildInsertSQL 重寫

package com.liudandan.logbackmysql.controller;

import ch.qos.logback.classic.db.names.DBNameResolver;
import com.liudandan.logbackmysql.controller.config.ColumnNames;
import com.liudandan.logbackmysql.controller.config.TableName;

public class buildInsertSQL {
//    public static String buildInsertSQL() {
//        return "INSERT INTO log_record " +
//                "(time, message, level_string, logger_name, thread_name," +
//                "caller_filename, caller_class, caller_method, caller_line,org_id)" +
//                "VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?)";
//    }


    public static String buildInsertDebugSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOG_DEBUG)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.TIME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.MESSAGE)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LEVEL_STRING)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LOGGER_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.THREAD_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_FILENAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_CLASS)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_METHOD)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_LINE)).append(",");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.ORG_ID)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?)");
        return sqlBuilder.toString();
    }


    public static String buildInsertErrorSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOG_ERROR)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.TIME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.MESSAGE)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LEVEL_STRING)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LOGGER_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.THREAD_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_FILENAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_CLASS)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_METHOD)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_LINE)).append(",");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.ORG_ID)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?)");
        return sqlBuilder.toString();
    }

    public static String buildInsertInfoSQL(DBNameResolver dbNameResolver) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(dbNameResolver.getTableName(TableName.LOG_INFO)).append(" (");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.TIME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.MESSAGE)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LEVEL_STRING)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.LOGGER_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.THREAD_NAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_FILENAME)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_CLASS)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_METHOD)).append(", ");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.CALLER_LINE)).append(",");
        sqlBuilder.append(dbNameResolver.getColumnName(ColumnNames.ORG_ID)).append(") ");
        sqlBuilder.append("VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?)");
        return sqlBuilder.toString();
    }

}

3，最主要的  再這個里邊級別分類不同存儲

DBAppenderBase

package com.liudandan.logbackmysql.controller;

import ch.qos.logback.classic.spi.LoggingEvent;
import ch.qos.logback.core.db.DBAppenderBase;
import ch.qos.logback.core.db.DBHelper;
import ch.qos.logback.core.db.dialect.SQLDialectCode;

import java.lang.reflect.Method;
import java.sql.*;

public abstract class MyDBAppenderBase<E> extends DBAppenderBase<E> {


    protected abstract String getInsertErrorSQL();

    protected abstract String getInsertInfoSQL();

    protected abstract String getInsertDebugSQL();


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

    @Override
    protected String getInsertSQL() {
        return null;
    }

    @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();
                }
                String s = ((LoggingEvent) eventObject).getLevel().toString();
                if (s.equals("DEBUG")) {
                    insertStatement = connection.prepareStatement(getInsertDebugSQL(), new String[]{EVENT_ID_COL_NAME});
                } else if (s.equals("INFO")) {
                    insertStatement = connection.prepareStatement(getInsertInfoSQL(), new String[]{EVENT_ID_COL_NAME});
                } else  if (s.equals("ERROR")){
                    insertStatement = connection.prepareStatement(getInsertErrorSQL(), new String[]{EVENT_ID_COL_NAME});
                }

            } else {
                insertStatement = connection.prepareStatement(getInsertErrorSQL());
            }

            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);
        }
    }

    @Override
    protected void subAppend(E eventObject, Connection connection, PreparedStatement statement) throws Throwable {

    }

    @Override
    protected void secondarySubAppend(E eventObject, Connection connection, long eventId) throws Throwable {

    }

}

4 TableName

public enum TableName {
        LOG_INFO,LOG_ERROR,LOG_DEBUG
}

5， ColumnNames

public enum ColumnNames {

   TIME,MESSAGE,LEVEL_STRING,LOGGER_NAME,THREAD_NAME,CALLER_FILENAME,CALLER_CLASS,CALLER_METHOD,CALLER_LINE,ORG_ID;
}

6， ConnectionSourceBase

package com.liudandan.logbackmysql.controller.config;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

import ch.qos.logback.core.db.ConnectionSource;
import ch.qos.logback.core.db.DBHelper;
import ch.qos.logback.core.db.dialect.DBUtil;
import ch.qos.logback.core.db.dialect.SQLDialectCode;
import ch.qos.logback.core.spi.ContextAwareBase;

/**
 * @author Ceki Gülcü
 */
public abstract class ConnectionSourceBase extends ContextAwareBase implements ConnectionSource {

    private boolean started;

    private String user = null;
    private String password = null;

    // initially we have an unknown dialect
    private SQLDialectCode dialectCode = SQLDialectCode.UNKNOWN_DIALECT;
    private boolean supportsGetGeneratedKeys = false;
    private boolean supportsBatchUpdates = false;

    /**
     * Learn relevant information about this connection source.
     *
     */
    public void discoverConnectionProperties() {
        Connection connection = null;
        try {
            connection = getConnection();
            if (connection == null) {
                addWarn("Could not get a connection");
                return;
            }
            DatabaseMetaData meta = connection.getMetaData();
            DBUtil util = new DBUtil();
            util.setContext(getContext());
            supportsGetGeneratedKeys = util.supportsGetGeneratedKeys(meta);
            supportsBatchUpdates = util.supportsBatchUpdates(meta);
            dialectCode = DBUtil.discoverSQLDialect(meta);
            addInfo("Driver name=" + meta.getDriverName());
            addInfo("Driver version=" + meta.getDriverVersion());
            addInfo("supportsGetGeneratedKeys=" + supportsGetGeneratedKeys);

        } catch (SQLException se) {
            addWarn("Could not discover the dialect to use.", se);
        } finally {
            DBHelper.closeConnection(connection);
        }
    }

    /**
     * Does this connection support the JDBC Connection.getGeneratedKeys method?
     */
    public final boolean supportsGetGeneratedKeys() {
        return supportsGetGeneratedKeys;
    }

    public final SQLDialectCode getSQLDialectCode() {
        return dialectCode;
    }

    /**
     * Get the password for this connection source.
     */
    public final String getPassword() {
        return password;
    }

    /**
     * Sets the password.
     * @param password The password to set
     */
    public final void setPassword(final String password) {
        this.password = password;
    }

    /**
     * Get the user for this connection source.
     */
    public final String getUser() {
        return user;
    }

    /**
     * Sets the username.
     * @param username The username to set
     */
    public final void setUser(final String username) {
        this.user = username;
    }

    /**
     * Does this connection support batch updates?
     */
    public final boolean supportsBatchUpdates() {
        return supportsBatchUpdates;
    }

    public boolean isStarted() {
        return started;
    }

    public void start() {
        started = true;
    }

    public void stop() {
        started = false;
    }

}

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。