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 {    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 extends DBAppenderBase {    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;    }}

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。