log4j1.x、log4j2.x日志输出到数据库（clickhouse、mysql）、文件

log4j 1.x

在pom中加入log4j1.x的依赖和数据库jdbc依赖

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.2-patch1</version>
        </dependency>

....log4j version 1.2

在数据库中建表

CREATE TABLE logging.ods_log_info_f
(
    `level` String COMMENT '日志级别,%p',
    `thread` String COMMENT '线程名,%t',
    `class` String COMMENT '所在类的类名,%c',
    `message` String COMMENT '日志内容,%m',
    `line` String COMMENT '代码中的行数,%L ',
    `code` String COMMENT '分组编码',
    `runtime` String COMMENT '输出自应用启动到输出该log信息耗费的毫秒数,%r',
    `describe` String COMMENT '自定义的描述信息',
    `insert_time` String COMMENT '输出日志时间的日期或时间,%d{yyyy-MM-dd HH:mm:ss,SSS}'
)
ENGINE = MergeTree
PARTITION BY substring(insert_time, 1, 10)
ORDER BY insert_time
SETTINGS index_granularity = 8192;

创建并编辑log4j.properties

#Definetherootloggerwithappenderfile
log4j.rootLogger=INFO,stdout,DB
#log4j.rootLogger=INFO,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-ddHH:mm:ss,SSS}%X{code}%-5p%c{1}:%L%t%r-%m%n

#DefinetheDBappender
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
#SetJDBCURL
log4j.appender.DB.URL=jdbc:clickhouse://ip:8123?socket_timeout=300000
#SetDatabaseDriver
log4j.appender.DB.driver=com.clickhouse.jdbc.ClickHouseDriver
#Setdatabaseusernameandpassword
log4j.appender.DB.user=default
log4j.appender.DB.password=aaa
#SettheSQLstatementtobeexecuted.
log4j.appender.DB.sql=INSERT INTO dev.ods_log_info_f select '%p' as level,'%t' as thread,'%c' as class,'%m' as message,'%L' as line,'%X{code}' as code,'%r' as runtime,'%X{describe}' as describe,'%d{yyyy-MM-ddHH:mm:ss,SSS}' as insert_time
#Definethelayoutforfileappender
log4j.appender.DB.layout=org.apache.log4j.PatternLayout

在程序中使用

import org.apache.log4j.Logger

val logger=Logger.getLogger(XXX.getClass)

参考资料

Log4j（一）：Log4j将日志信息写入数据库

使用log4j让日志写入数据库

用log4j将日志写入数据库

使用log4j把日志写到mysql数据库

log4j 2.x

输出到数据库

在1.x中能自定义执行的sql，在2.x中还没找到入口，只能在xml中配置

官网中JDBCAppender的使用说明

在pom中引入数据库jdbc依赖、log4j2依赖

<log4j2.version>2.11.2</log4j2.version>

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.2-patch1</version>
        </dependency>

<dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${log4j2.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${log4j2.version}</version>
        </dependency>

在数据库中建表，建表语句同上

log4j2.x写数据库要麻烦一些，需要自定义数据库连接的工厂类，代码如下：
ConnectionFactory.class
支持从db.properties中获取数据库连接属性
需要设置以下几个属性
username=default
password=aaa
url=jdbc:clickhouse://ip:8123?socket_timeout=300000
driver=com.clickhouse.jdbc.ClickHouseDriver

import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnection;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionFactory {
	private static String driver = "oracle.jdbc.driver.OracleDriver";
	private static String url = "jdbc:oracle:thin:@localhost:1521:XE";
	private static String Name = "root";
	private static String Password = "123456";

	private static interface Singleton {
		final ConnectionFactory INSTANCE = new ConnectionFactory();
	}

	private final DataSource dataSource;

	private ConnectionFactory() {

		Properties properties = new Properties();
		try {
			properties.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("db.properties"));
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			Name = properties.getProperty("username");
			Password = properties.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
		GenericObjectPool<PoolableConnection> pool = new GenericObjectPool<PoolableConnection>();
		DriverManagerConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
				url, 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();
	}
}

创建并编辑log4j2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<Configuration status="INFO">
    <Filters>
        <RegexFilter regex=".*ClickHouseConnectionImpl.*" onMatch="DENY" onMismatch="NEUTRAL"/>
        <RegexFilter regex=".*JdbcDatabaseManager.*" onMatch="DENY" onMismatch="NEUTRAL"/>
    </Filters>
    <Appenders>
        <Console name="console" target="SYSTEM_OUT">
            <PatternLayout pattern="[%level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} - %msg%n"/>
        </Console>
        <!-- JDBCAppender -->
        <JDBC name="jdbc" tableName="logging.ods_uas_log_info_f">
            <ConnectionFactory class="myspark.warehouse.common.ConnectionFactory" method="getDatabaseConnection"/>
            <Column name="level" pattern="%level"/>
            <Column name="thread" pattern="%t"/>
            <Column name="class" pattern="%class"/>
            <Column name="message" pattern="%message"/>
            <Column name="line" pattern="%L"/>
            <Column name="code" pattern="%X{code}"/>
            <Column name="runtime" pattern="%r"/>
            <Column name="describe" pattern="%X{describe}"/>
            <Column name="insert_time" pattern="%d{yyyy-MM-dd HH:mm:ss.SSS}"/>
        </JDBC>
    </Appenders>
    <Loggers>
        <Logger name="myspark.warehouse" level="info">
            <AppenderRef ref="console"/>
            <AppenderRef ref="jdbc"/>
        </Logger>
        <Logger name="org.apache.logging" level="error">
            <AppenderRef ref="console"/>
            <AppenderRef ref="jdbc"/>
        </Logger>
        <Logger name="org.slf4j" level="error">
            <AppenderRef ref="console"/>
            <AppenderRef ref="jdbc"/>
        </Logger>
        <Root level="error" additivity="false">
            <AppenderRef ref="console"/>
            <AppenderRef ref="jdbc"/>
        </Root>
    </Loggers>
</Configuration>

使用

import org.apache.logging.log4j.{LogManager, Logger}

val logger: Logger = LogManager.getLogger(gscUASOTHCDLogToDBUnion.getClass)

参考资料

官方文档

日志写入数据库：Log4j2-JDBCAppender

log4j2 jdbc的使用

Log4j2记录日志到数据库(MySQL&MongoDB)

Log4j2记录日志到数据库

使用Log4j2实现日志输出

输出到文件，自动按大小、按天拆分

<Appenders>
<RollingFile name="RollingFile" fileName="logs/app.log"
                     filePattern="logs/app-%d{yyyy-MM-dd HH}.log">
            <PatternLayout>
                <Pattern>[%level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} - %msg%n</Pattern>
            </PatternLayout>
            <Policies>
                <TimeBasedTriggeringPolicy interval="1"/>
                <SizeBasedTriggeringPolicy size="10MB"/>
            </Policies>
        </RollingFile>
  </Appenders>
<Loggers>
        <Root level="info">
            <AppenderRef ref="console"/>
<!--            <AppenderRef ref="jdbc"/>-->
            <AppenderRef ref="RollingFile"/>
        </Root>
</Loggers>