Licensed to the Apache Software Foundation (ASF) under one or more
 contributor license agreements.  See the NOTICE file distributed with
 this work for additional information regarding copyright ownership.
 The ASF licenses this file to You under the Apache License, Version 2.0
 (the "License"); you may not use this file except in compliance with
 the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing, software
 distributed under the License is distributed on an "AS IS" BASIS,
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 See the License for the specific language governing permissions and
 limitations under the License.



Delivered-To: urba-cgu@urbanet.ch 
Mailing-List: contact log4j-user-help@jakarta.apache.org; run by ezmlm 
List-Post: <mailto:log4j-user@jakarta.apache.org> 
List-Help: <mailto:log4j-user-help@jakarta.apache.org> 
List-Unsubscribe: <mailto:log4j-user-unsubscribe@jakarta.apache.org> 
List-Subscribe: <mailto:log4j-user-subscribe@jakarta.apache.org> 
Reply-To: "LOG4J Users Mailing List" <log4j-user@jakarta.apache.org> 
Delivered-To: mailing list log4j-user@jakarta.apache.org 
Date: Thu, 01 Feb 2001 14:26:34 -0800 
From: Kevin Steppe <ksteppe@pacbell.net> 
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U) 
X-Accept-Language: en 
To: LOG4J Users Mailing List <log4j-user@jakarta.apache.org> 
Subject: JDBC Appender 
X-Spam-Rating: h31.sny.collab.net 1.6.2 0/1000/N 


Ok, here it is.  Since there will be differences in database schemas and
connection/execution methods, I wrote this with the intention that those
parts would be overriden by subclasses (that's what I'm doing for my
company), however it will work as is if you have a stored procedure
spLog @msg.  I'm sure there are optimizations which could be done.


The code for org.apache.log4j.varia.JDBCAppender and
org.apache.log4j.varia.test.JDBCTest follow and files attached.  At the
bottem is the SQL I used to test this on M$ SQL-Server.


I help this proves useful,
Kevin




package org.apache.log4j.varia;


import org.apache.log4j.*;
import org.apache.log4j.spi.*;


import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;


/**
 * Contribution from MD Data Direct.
 *
 * Implements an ArrayList buffer before storing messages to the DB.
 * Override getSQL to fit your database schema (or implement spLog msg
on your DB)
 * Override executeSQL to modify how DB connection and SQL execution is
made.
 *
 * @author: Kevin Steppe
 */
public class JDBCAppender extends org.apache.log4j.AppenderSkeleton
    implements org.apache.log4j.Appender
{
    protected String databaseURL = "jdbc:odbc:myDB";
    protected String databaseUser = "me";
    protected String databasePassword = "mypassword";


    public static final String URL_OPTION = "URL";
    public static final String USER_OPTION = "User";
    public static final String PASSWORD_OPTION = "Password";
    public static final String BUFFER_OPTION = "Buffer";
    protected int bufferSize = 1;
    protected List buffer;


    public JDBCAppender()
    {
        super();
        buffer = new ArrayList();
    }


    public void append(LoggingEvent event)
    {
        buffer.add(event);


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


    public void close()
    {
        flushBuffer();
        this.closed = true;
    }


    public void setOption(String key, String value)
    {
        super.setOption(key, value);


        if (key.equalsIgnoreCase(URL_OPTION))
            databaseURL = value;
        else if (key.equalsIgnoreCase(USER_OPTION))
            databaseUser = value;
        else if (key.equalsIgnoreCase(PASSWORD_OPTION))
            databasePassword = value;
        else if (key.equalsIgnoreCase(BUFFER_OPTION))
            bufferSize = Integer.parseInt(value);
    }


    /**
     * Override this to create the SQL needed for your DB schema
     */
    protected String getSQL(LoggingEvent event)
    {
        String msg = this.layout.format(event);
        String sql = "spLog '" + msg + "'";
        return sql;
    }


    /**
     * Override this to provide  an alertnate method of getting
connections (such as caching)
     * This implementation creates a new connection and statement for
every execution which
     * is very wastefull.  One method to fix this is to open connections
at the start of
     * flushBuffer() and close them at the end.  MD Data uses a
connection pool outside
     * of JDBCAppender which is accessed in the override of this method.


     */
    protected void executeSQL(String sql) throws SQLException
    {
        Connection con = null;
        Statement stmt = null;


        try {
            con = DriverManager.getConnection(databaseURL, databaseUser,
databasePassword);
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        }
        catch (SQLException e)
        {
            if (con != null)
                con.close();
            if (stmt != null)
                stmt.close();


            throw e;
        }
        stmt.close();
        con.close();
    }


    public void flushBuffer()
    {
        //Do the actual logging
        for (Iterator i = buffer.iterator(); i.hasNext();)
        {
            try {
                String sql = getSQL((LoggingEvent)i.next());
                executeSQL(sql);
            }
            catch (SQLException e)
            {
                errorHandler.error("Failed to excute sql", e,
ErrorCode.FLUSH_FAILURE);
            }
        }
        buffer.clear();
    }


    public void finalize()
    {
        close();
    }


    public boolean requiresLayout()
    {
        return true;
    }


}



package org.apache.log4j.varia.test;


import org.apache.log4j.varia.JDBCAppender;
import org.apache.log4j.*;


public class JDBCTest
{
    public static void main (String argv[])
    {
        try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        }
        catch (Exception e)
        {
            e.printStackTrace();
            System.out.println(e.toString());
        }


     Category rootLog = Category.getRoot();
        Layout layout = new PatternLayout("%p [%t] %c - %m%n");
        JDBCAppender appender = new JDBCAppender();
        appender.setLayout(layout);
        appender.setOption(JDBCAppender.URL_OPTION, "jdbc:odbc:someDB");


        appender.setOption(JDBCAppender.USER_OPTION, "auser");
        appender.setOption(JDBCAppender.PASSWORD_OPTION, "thepassword");



        rootLog.addAppender(appender);


        try {
            Category log = Category.getInstance("main");
            log.debug("Debug 1");
            Thread.sleep(500);
            log.info("info 1");
            Thread.sleep(500);
            log.warn("warn 1");
            Thread.sleep(500);
            log.error("error 1");
            Thread.sleep(500);
            log.fatal("fatal 1");
            Thread.sleep(500);


            appender.setOption(JDBCAppender.BUFFER_OPTION, "5");
            log.debug("Debug 2");
            Thread.sleep(500);
            log.info("info 2");
            Thread.sleep(500);
            log.warn("warn 2");
            Thread.sleep(500);
            log.error("error 2");
            Thread.sleep(500);
            log.fatal("fatal 2");
            Thread.sleep(500);


            appender.setOption(JDBCAppender.BUFFER_OPTION, "2");
            appender.setThreshold(Priority.WARN);
            log.debug("Debug 3");
            Thread.sleep(500);
            log.info("info 3");
            Thread.sleep(500);
            log.warn("warn 3");
            Thread.sleep(500);
            log.error("error 3");
            Thread.sleep(500);
            log.fatal("fatal 3");
        }
        catch (InterruptedException e)
        {
            System.out.println("Interrupted");
        }
    }
}



drop table JDBCAppenderTest
go
create table JDBCAppenderTest (EventID int identity, entrytime datetime,
message varchar(255))


drop procedure spLog
go
create procedure spLog (@msg varchar(255)) as
 insert into JDBCAppenderTest (message, entrytime) values (@msg,
getdate())


select * from JDBCAppenderTest



drop table JDBCAppenderTest 
go
create table JDBCAppenderTest (EventID int identity, entrytime datetime, message varchar(255))


drop procedure spLog
go
create procedure spLog (@msg varchar(255)) as
        insert into JDBCAppenderTest (message, entrytime) values (@msg, getdate())


select * from JDBCAppenderTest
package org.apache.log4j.varia;


import org.apache.log4j.*;
import org.apache.log4j.spi.*;


import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;


/**
 * Contribution from MD Data Direct.
 *
 * Implements an ArrayList buffer before storing messages to the DB.
 * Override getSQL to fit your database schema (or implement spLog msg on your DB)
 * Override executeSQL to modify how DB connection and SQL execution is made.
 *
 * @author: Kevin Steppe
 */
public class JDBCAppender extends org.apache.log4j.AppenderSkeleton 
    implements org.apache.log4j.Appender
{
    protected String databaseURL = "jdbc:odbc:myDB";
    protected String databaseUser = "me";
    protected String databasePassword = "mypassword";
    
    public static final String URL_OPTION = "URL";
    public static final String USER_OPTION = "User";
    public static final String PASSWORD_OPTION = "Password";
    public static final String BUFFER_OPTION = "Buffer";
    protected int bufferSize = 1;
    protected List buffer;
    
    public JDBCAppender()
    {
        super();
        buffer = new ArrayList();            
    }
    
    public void append(LoggingEvent event)
    { 
        buffer.add(event);
        
        if (buffer.size() >= bufferSize)
            flushBuffer();
    }


    public void close()
    {
        flushBuffer(); 
        this.closed = true;
    }


    public void setOption(String key, String value) 
    {
        super.setOption(key, value);
        
        if (key.equalsIgnoreCase(URL_OPTION))
            databaseURL = value;
        else if (key.equalsIgnoreCase(USER_OPTION))
            databaseUser = value;
        else if (key.equalsIgnoreCase(PASSWORD_OPTION))
            databasePassword = value;
        else if (key.equalsIgnoreCase(BUFFER_OPTION))
            bufferSize = Integer.parseInt(value);
    }


    /**
     * Override this to create the SQL needed for your DB schema
     */
    protected String getSQL(LoggingEvent event)
    {
        String msg = this.layout.format(event); 
        String sql = "spLog '" + msg + "'"; 
        System.out.println(sql); //DEBUG         
        return sql;
    }
    
    /**
     * Override this to provide  an alertnate method of getting connections (such as caching)
     * This implementation creates a new connection and statement for every execution which
     * is very wastefull.  One method to fix this is to open connections at the start of
     * flushBuffer() and close them at the end.  MD Data uses a connection pool outside 
     * of JDBCAppender which is accessed in the override of this method.
     */
    protected void executeSQL(String sql) throws SQLException
    {
        Connection con = null;
        Statement stmt = null;


        try {
            con = DriverManager.getConnection(databaseURL, databaseUser, databasePassword);
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        }
        catch (SQLException e)
        {
            if (con != null)
                con.close();
            if (stmt != null)
                stmt.close();
                
            throw e;
        }
        stmt.close();
        con.close();
    }
    
    public void flushBuffer()
    {
        //Do the actual logging
        for (Iterator i = buffer.iterator(); i.hasNext();)
        {
            try {
                String sql = getSQL((LoggingEvent)i.next());
                executeSQL(sql);
            }
            catch (SQLException e)
            {
                errorHandler.error("Failed to excute sql", e, ErrorCode.FLUSH_FAILURE);
            }
        }
        buffer.clear();
    }
    
    public void finalize()
    {
        close();
    }
    
    public boolean requiresLayout()
    {
        return true;
    }


}package org.apache.log4j.varia.test;


import org.apache.log4j.varia.JDBCAppender;
import org.apache.log4j.*;


public class JDBCTest
{
    public static void main (String argv[])
    {
        try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");        
        }
        catch (Exception e)
        {
            e.printStackTrace();
            System.out.println(e.toString());    
        }
        
            Category rootLog = Category.getRoot();
        Layout layout = new PatternLayout("%p [%t] %c - %m%n");
        JDBCAppender appender = new JDBCAppender();        
        appender.setLayout(layout);        
        appender.setOption(JDBCAppender.URL_OPTION, "jdbc:odbc:someDB");
        appender.setOption(JDBCAppender.USER_OPTION, "auser");
        appender.setOption(JDBCAppender.PASSWORD_OPTION, "thepassword");
        
        rootLog.addAppender(appender);  


        try {
            Category log = Category.getInstance("main");
            log.debug("Debug 1");
            Thread.sleep(500);
            log.info("info 1");
            Thread.sleep(500);
            log.warn("warn 1");
            Thread.sleep(500);
            log.error("error 1");
            Thread.sleep(500);
            log.fatal("fatal 1");
            Thread.sleep(500);
            
            appender.setOption(JDBCAppender.BUFFER_OPTION, "5");
            log.debug("Debug 2");
            Thread.sleep(500);
            log.info("info 2");
            Thread.sleep(500);
            log.warn("warn 2");
            Thread.sleep(500);
            log.error("error 2");
            Thread.sleep(500);
            log.fatal("fatal 2");
            Thread.sleep(500);


            appender.setOption(JDBCAppender.BUFFER_OPTION, "2");
            appender.setThreshold(Priority.WARN);
            log.debug("Debug 3");
            Thread.sleep(500);
            log.info("info 3");
            Thread.sleep(500);
            log.warn("warn 3");
            Thread.sleep(500);
            log.error("error 3");
            Thread.sleep(500);
            log.fatal("fatal 3");
        }
        catch (InterruptedException e)
        {
            System.out.println("Interrupted");
        }
    }
}