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;
}
}看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：https://my.oschina.net/liudandan/blog/3095477
