一直都在想如何用log4j将message写到数据库中,在以后查看的时候会方便些。(数据库都是使用MSSQL)
在网上搜了下, 有两种方法比较合适。
1.插入数据库中的表的字段有多个(不包括自动增长列),这种情况适合在浏览时要对某个字段进行排序等其他需求。
配置文件主要部分如下:
<
appender
name
="access_Log_JDBC"
class
="org.apache.log4j.jdbc.JDBCAppender"
>
<
param
name
="Driver"
value
="com.microsoft.jdbc.sqlserver.SQLServerDriver"
/>
<
param
name
="URL"
value
="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=LOG"
/>
<
param
name
="user"
value
="sa"
/>
<
param
name
="password"
value
="sa"
/>
<
param
name
="sql"
value
="INSERT INTO t(date,user,b,message) VALUES (getDate(), '%X{user}', '%X{b}', '%m')"
/>
<
param
name
="BufferSize"
value
="1"
/>
</
appender
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
category
name
="accesslog_category"
>
<
priority
value
="debug"
/>
<
appender-ref
ref
="access_Log_JDBC"
/>
<
appender-ref
ref
="CONSOLE"
/>
</
category
>
java 部分代码如下:
//MDC的帮助页面:http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/MDC.html
public
class
AccessLogFactory
...
{
private static final String ACCESSLOG_CATEGORY = "accesslog_category";
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
static...{
DOMConfigurator.configure("./conf/log4j.xml");
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public static Log getLog() ...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Log logger = null;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
logger = LogFactory.getLog(ACCESSLOG_CATEGORY);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (LogConfigurationException e) ...{
e.printStackTrace();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return logger;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public static void main(String[] args)...{
Log logger = AccessLogFactory .getLog();
String msg = "b03";
String user = "u03";
String b = "b03";
MDC.put("user", user); //和配置文件中的%X{user}对应
MDC.put("b", b); //和配置文件中的%X{b}对应
logger.error(msg); //和配置文件中的%m对应
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
msg = "b04";
user = "u04";
b = "b04";
MDC.put("user ", user );
MDC.put("b", b);
logger.error(msg);
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
2.插入数据库中的表的字段有一个(不包括自动增长列),这种情况适合只是要显示在页面上而无其他需求,当然第一种情况也适合(这部分的代码来自http://hi.baidu.com/kekemao1/blog/item/199d3d31ed6d901aebc4afff.html)。
放在WEB_INF/class文件夹下的log.properties文件的内容如下
#
log4j
.
rootLogger
=
DEBUG
,
DATABASE
#
log4j
.
appender
.
DATABASE
=
org
.
apache
.
log4j
.
jdbc
.
JDBCAppender
//
(root日志记录器的存储器)
#
log4j
.
appender
.
DATABASE
.
URL
=
jdbc
:microsoft:sqlserver
:
//
localhost:
1433
;
SelectMethod
=
cursor
#
log4j
.
appender
.
DATABASE
.
driver
=
com
.
microsoft
.
jdbc
.
sqlserver
.
SQLServerDriver
#
log4j
.
appender
.
DATABASE
.
user
=
sd
//
用户名
#
log4j
.
appender
.
DATABASE
.
password
=
#
log4j
.
appender
.
DATABASE
.
sql
=
INSERT INTO LOG4J
(
Message
)
VALUES
(
'%d %c %p %m%n'
)//
写入pubs数据库中的表LOG4J的Message字段中,内容%d(日期)%c: 日志信息所在地(类名)%p: 日志信息级别%m: 产生的日志具体信息 %n: 输出日志信息换行
#
log4j
.
appender
.
DATABASE
.
layout
=
org
.
apache
.
log4j
.
PatternLayout
#
log4j
.
appender
.
DATABASE
.
layout
.
ConversionPattern
=
%d %c %p %m%n
java 代码:
import
org.apache.log4j.Logger;
import
org.apache.log4j.MDC;
import
javax.sql.DataSource;
import
javax.naming.Context;
import
javax.naming.InitialContext;
import
java.sql.Connection;
import
java.sql.SQLException;
import
java.util.Vector;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/** */
/**
* 封装DataSource
*
* @version 1.0, 2005-9-16 11:18:44
*/
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
public
class
DataSourcePackBean
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* log4j
*/
private Logger logger = Logger.getLogger(DataSourcePackBean.class);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//DataSourcePackBean.class日志记录器的名与类名相同;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 引用名
*/
private String ref;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* DataSource对象
*/
private DataSource ds;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 保存当前DataSource对象中所有打开的Connection对象的引用。
*/
private Vector openedConnections = new Vector();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 接收DataSource引用名,查找DataSource对象。
*
* @param ref DataSource引用名
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
protected DataSourcePackBean(String ref) ...{
this.ref = ref;
this.ds = lookup(ref);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public String getRef() ...{
return ref;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public int getActive() ...{
return openedConnections.size();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 打开一个连接
*
* @param username 连接数据库的登录用户名,如果为null,表示用默认用户连接
* @param password 连接数据库的登录密码
* @return
* @throws SQLException
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public Connection getConnection(String username, String password) throws SQLException ...{
Connection conn = (username == null) ? ds.getConnection() : ds.getConnection(username, password);
// add to vector
openedConnections.add(conn);
// log
StringBuffer log = new StringBuffer();
log.append("Open").append(" a connection(").append(conn.hashCode()).append(") of [").append(ref).append("] ");
log.append(openedConnections.size()).append(" connection").append(" in use");
logger.debug(log);
// return
return conn;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* 关闭一个连接
*
* @param conn
* @throws SQLException
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void closeConnection(Connection conn) throws SQLException ...{
conn.close();
// remove from Vector
openedConnections.remove(conn);
// log
StringBuffer log = new StringBuffer();
log.append("Close").append(" a connection(").append(conn.hashCode()).append(") of [").append(ref).append("] ");//添加内容到日志记录中
log.append(openedConnections.size()).append(" connection").append(" in use");
logger.debug(log);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* close all Connection of this DataSource
* @throws SQLException
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void closeAll() throws SQLException ...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
for (int i = 0; i < openedConnections.size(); i++) ...{
((Connection)openedConnections.get(i)).close();
}
// clear Vector
openedConnections.removeAllElements();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* get a DataSource
* @param ref the JNDI name of the DataSource
* @return
*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
private DataSource lookup(String ref) ...{
DataSource dataSource = null;
// 查找ref数据源对象
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
Context env = new InitialContext();
dataSource = (DataSource) env.lookup(ref);
// 测试数据库有效性
dataSource.getConnection().close();
logger.debug(new StringBuffer().append("DataSource[").append(ref).append("] has been initialized successfully."));
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (Exception e) ...{
e.printStackTrace();
}
// return
return dataSource;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public SQLRunnerIfc getSQLRunner(String username, String password) throws SQLException ...{
return new SQLRunner(username, password, this);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public SQLRunnerIfc getSQLRunner() throws SQLException ...{
return getSQLRunner(null, null);
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)