将log4j日志写入MySQL数据库

最近使用log4j 和 mysql 在项目中，搜索了很多相关的文章，感觉都不是很成熟，所以在网上的一些文章的基础上做了一些修改，使实用更加灵活。

以下是我的配置和源码

1、log4j中配置mysql

<appender name="THIRDAPIDEBUGSQL" class="com.****.util.DBAppender">
   <param name="driver" value="com.mysql.jdbc.Driver" />
   <param name="URL" value="jdbc:mysql://localhost:3306/****?characterEncoding=UTF-8" />
   <param name="user" value="****" />
   <param name="password" value="****" />
   <param name="sql" value="%c %p %m %n" />
   <param name="layout" value="org.apache.log4j.PatternLayout" />
   <filter class="org.apache.log4j.varia.LevelRangeFilter">
      <param name="LevelMin" value="INFO" />
      <param name="LevelMax" value="INFO" />
   </filter>
</appender>

上面的配置是有两处需要注意
<appender name="THIRDAPIDEBUGSQL" class="com.****.util.DBAppender">
这个是我重写了一下 org.apache.log4j.jdbc.JDBCAppender 

 <param name="sql" value="%c %p %m %n" /> 
这个是要插入的数据 就是log.info()这里面的数据


<category name="com.****">
   <priority value="INFO" />
   <appender-ref ref="THIRDAPIDEBUGSQL" />
</category>

<category name="com.****">
   <priority value="INFO" />
   <appender-ref ref="THIRDAPIDEBUGSQL" />
</category>

这个是插入指定的包下面的log.info()的数据

2、DBAppender的代码（这个大家可以根据自己的需求去重写这个方法）

public class DBAppender extends org.apache.log4j.AppenderSkeleton
        implements org.apache.log4j.Appender  {

    /**
     * URL of the DB for default connection handling
     */
    protected String databaseURL = "jdbc:odbc:myDB";

    /**
     * User to connect as for default connection handling
     */
    protected String databaseUser = "me";

    /**
     * User to use for default connection handling
     */
    protected String databasePassword = "mypassword";

    /**
     * Connection used by default.  The connection is opened the first time it
     * is needed and then held open until the appender is closed (usually at
     * garbage collection).  This behavior is best modified by creating a
     * sub-class and overriding the <code>getConnection</code> and
     * <code>closeConnection</code> methods.
     */
    protected Connection connection = null;

    /**
     * Stores the string given to the pattern layout for conversion into a SQL
     * statement, eg: insert into LogTable (Thread, Class, Message) values
     * ("%t", "%c", "%m").
     *
     * Be careful of quotes in your messages!
     *
     * Also see PatternLayout.
     */
    protected String sqlStatement = "";
    protected          SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
    /**
     * size of LoggingEvent buffer before writting to the database.
     * Default is 1.
     */
    protected int bufferSize = 1;

    /**
     * ArrayList holding the buffer of Logging Events.
     */
    protected ArrayList buffer;

    /**
     * Helper object for clearing out the buffer
     */
    protected ArrayList removes;

    private boolean locationInfo = false;

    public DBAppender() {
        super();
        buffer = new ArrayList(bufferSize);
        removes = new ArrayList(bufferSize);
    }

    /**
     * Gets whether the location of the logging request call
     * should be captured.
     *
     * @since 1.2.16
     * @return the current value of the <b>LocationInfo</b> option.
     */
    public boolean getLocationInfo() {
        return locationInfo;
    }

    /**
     * The <b>LocationInfo</b> option takes a boolean value. By default, it is
     * set to false which means there will be no effort to extract the location
     * information related to the event. As a result, the event that will be
     * ultimately logged will likely to contain the wrong location information
     * (if present in the log format).
     * <p/>
     * <p/>
     * Location information extraction is comparatively very slow and should be
     * avoided unless performance is not a concern.
     * </p>
     * @since 1.2.16
     * @param flag true if location information should be extracted.
     */
    public void setLocationInfo(final boolean flag) {
        locationInfo = flag;
    }


    /**
     * Adds the event to the buffer.  When full the buffer is flushed.
     */
    public void append(LoggingEvent event) {
        event.getNDC();
        event.getThreadName();
        // Get a copy of this thread's MDC.
        event.getMDCCopy();
        if (locationInfo) {
            event.getLocationInformation();
        }
        event.getRenderedMessage();
        event.getThrowableStrRep();
        buffer.add(event);

        if (buffer.size() >= bufferSize)
            flushBuffer();
    }

    /**
     * By default getLogStatement sends the event to the required Layout object.
     * The layout will format the given pattern into a workable SQL string.
     *
     * Overriding this provides direct access to the LoggingEvent
     * when constructing the logging statement.
     *
     */
    protected String getLogStatement(LoggingEvent event) {
        return getLayout().format(event);
    }

    /**
     *
     * Override this to provide an alertnate method of getting
     * connections (such as caching).  One method to fix this is to open
     * connections at the start of flushBuffer() and close them at the
     * end.  I use a connection pool outside of JDBCAppender which is
     * accessed in an override of this method.
     * */
    private static  String flagDate = "";
    protected void execute(String sql) throws SQLException {
        String dateStr = df.format(new Date());// new Date()为获取当前系统时间
        String[] strArry = sql.split(lmitlog4j);
        String  sqlExecute = "INSERT INTO `log4j_"+dateStr+"`(type,log)  VALUES ('"+strArry[0]+"', '"+strArry[1]+"')";
        System.out.println(sqlExecute);
        Connection con = null;
        Statement stmt = null;

        try {
            con = getConnection();

            stmt = con.createStatement();
            try {
                if (!flagDate.equals(dateStr)) {
                    flagDate = dateStr;
                    String creatTable = "CREATE TABLE `log4j_" + dateStr + "` (" +
                            "  `id` int(20) NOT NULL AUTO_INCREMENT," +
                            "  `type` text CHARACTER SET utf8," +
                            "  `log` text CHARACTER SET utf8," +
                            "  `creat_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n" +
                            "  PRIMARY KEY (`id`)" +
                            ") ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;";
                    stmt.executeUpdate(creatTable);
                }
            }catch (Exception e){

            }

            stmt.executeUpdate(sqlExecute);

        } finally {
            if(stmt != null) {
                stmt.close();
            }
            closeConnection(con);
        }
        System.out.println("Execute: " + sql);
    }

3、使用时

只需要在指定的包下面 

private static Logger log = Logger.getLogger(ThirdAPISuperService.class);

log.info("数据type"+lmitlog4j+new Gson().toJson(repAccount));

4、插入的样式

总结：其实就是在log.info("数据type"+“-------------”+new Gson().toJson(repAccount)); 加上特殊的flag 然后在插入的时候 ，在分出来，这样就可以把不同的数据 插入进去 方便查询