log4j2记录日志到数据库（完美支持mysql）

1、首先创建存储日志的数据表ibs_log

• 创建语句如下：

DROP TABLE IF EXISTS `ibs_log`;
CREATE TABLE `ibs_log` (
 `log_id` int(10) NOT NULL AUTO_INCREMENT,
 `log_date` datetime NOT NULL COMMENT '日期',
 `log_file` varchar(255) DEFAULT NULL COMMENT '文件名',
 `log_line` varchar(255) DEFAULT NULL COMMENT '行号',
  `log_thread`varchar(255) DEFAULT NULL COMMENT '线程',
 `log_level` varchar(255) DEFAULT NULL COMMENT 'Log级别 info warndebug error等',
 `log_message` varchar(2048) DEFAULT NULL COMMENT 'Log信息',
 PRIMARY KEY (`log_id`,`log_date`),
  KEY`INDEX_LOG_DATE` (`log_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建Log4j2.xml配置文件

<?xml version="1.0"encoding="UTF-8"?>
<!-- 不打印log4j内部日志，配置文件刷新频率为30秒 --> 
<Configuration status="off"monitorInterval="30">
 <Appenders>
         <JDBCname="log_database" tableName="ibs_log">
     <ConnectionFactory class="com.*.*.ConnectionFactory"method="getDatabaseConnection" />
     <Column name="log_date" isEventTimestamp="true"/>
     <Column name="log_file" pattern="%file" />
     <Column name="log_line" pattern="%line" />
     <Column name="log_thread" pattern="%thread" />
     <Column name="log_level" pattern="%level" />
     <Column name="log_message" pattern="%message"/>
         </JDBC>
 </Appenders> 
 <Loggers>
   <!-- 打印等级：off、fatal、error、warn、info、debug、trace、all --> 
   <Logger name="项目包名（com.*.*）" level="debug">
     <appender-ref ref="databaseAppender"/>
   </Logger> 
   <Root level="off"></Root>
 </Loggers>
</Configuration>

3、创建数据库连接类ConnectionFactory

3.1 普通写法：

• 加载mysql驱动，自己配置连接池及数据源，获取数据库连接

package com.centerm.util;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
importorg.apache.commons.dbcp.DriverManagerConnectionFactory;
importorg.apache.commons.dbcp.PoolableConnection;
import org.apache.commons.dbcp.PoolableConnectionFactory;
importorg.apache.commons.dbcp.PoolingDataSource;
importorg.apache.commons.pool.impl.GenericObjectPool;
 
public class ConnectionFactory 
{
	private static interface Singleton {
	final ConnectionFactory INSTANCE = new ConnectionFactory();
}
 
    private final DataSource dataSource;
 
    private ConnectionFactory() 
    {
    	try 
    	{
           Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException e) 
        {
           e.printStackTrace();
           System.exit(0);
        }
            
        Properties properties = new Properties();
        properties.setProperty("user", "用户名");
        properties.setProperty("password", "密码"); //or get properties from some configuration file
 
        GenericObjectPool<PoolableConnection>pool = new GenericObjectPool<PoolableConnection>();
        DriverManagerConnectionFactory connectionFactory = newDriverManagerConnectionFactory(
                "jdbc:mysql://localhost:3306/数据库名称",properties
        );
        new PoolableConnectionFactory(connectionFactory, pool, null,"SELECT 1", 3, false, false, Connection.TRANSACTION_READ_COMMITTED
        );
 
        this.dataSource = new PoolingDataSource(pool);
     }
 
     public static Connection getDatabaseConnection() throws SQLException 
     {
         return Singleton.INSTANCE.dataSource.getConnection();
     }
}

3.2 调用配置好的连接池获取数据库连接（关键）

• 以druid连接池为例（其他连接池也可以），配置数据源，交由spring管理：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
	<property name="driverClassName" value="${driver}"></property>
	<property name="url" value="${url}"></property>
	<property name="username" value="${username}"></property>
	<property name="password" value="${password}"></property>
</bean>

• 创建数据库连接类（利用druid数据源获取数据库连接）

package com.jokul.truckbroker.instrument.log4j2;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.stereotype.Component;
import com.jokul.truckbroker.instrument.util.SpringUtil;

@Component
public class Log4j2ConnectionFactory
{
    
    public static Connection getDruidConnection() throws SQLException
    {
        DataSource dataSource = (DataSource)SpringUtil.getBean("dataSource");
        return dataSource.getConnection();
    }
}

记得在log4j2.xml中的JDBC标签处配置上Log4j2ConnectionFactory连接类和getDruidConnection获取连接的方法
例：

<Appenders>
     <JDBC name="log_database" tableName="ibs_log">
	     <ConnectionFactory class="com.jokul.truckbroker.instrument.log4j2.Log4j2ConnectionFactory"method="getDruidConnection" />
	     <Column name="log_date" isEventTimestamp="true"/>
	     <Column name="log_file" pattern="%file" />
	     <Column name="log_line" pattern="%line" />
	     <Column name="log_thread" pattern="%thread" />
	     <Column name="log_level" pattern="%level" />
	     <Column name="log_message" pattern="%message"/>
     </JDBC>
 </Appenders> 
 <Loggers>

最后记得在web.xml中增加

<listener>
    <listener-class>org.apache.logging.log4j.web.Log4jServletContextListener</listener-class>
</listener>

没研究出来这个配置的作用，但是没有这个配置，日志写入数据库就失败，到这里基本就配置完毕了，后面就可以利用MDC来自定义参数，写入数据库了，祝你成功！！！