make any database have the ability of logging within 10 seconds

Discussion in 'Java' started by cownew, May 14, 2006.

  1. cownew

    cownew Guest

    JDBMonitor is an open source project. It allows the developer to add
    the function of database execution logging to the application. It's so
    easy to use that the only thing you should do is appending
    "listenerconfig=/config.xml:url=" to the JDBC connection string of your
    application,without writing any code.

    With JDBMonitor,you can log the database execution in many ways,for
    example,to console,to file or to remote client through
    socket.JDBMonitor is extendible,so you can extend it to log the
    execution in other ways.The only thing you should do is writing a class
    implements the interface IDBListener.

    JDBMonitor is licensed under the terms of the GNU Lesser General Public
    Licence (LGPL). A copy of the licence is included in the distribution.

    Introduction
    Almost every large database application includes its own SQL execution
    logging function,which not only can help the developers to debug,but
    also can provide information for the DBA(DataBase Administrator).
    At the same time,a lot off code likeâ€logger.logSQL(sql)†filling in
    the code.It’s drawbacks as follow:
    (1) It’s difficult to separate this codes from the business code.
    (2) It reduces the readability of the code.
    (3) It slows down the application.When the logger write the SQL to file
    or output to console,the programme will wait util the process to
    finish,I/O operation is time-consuming.
    (4) It’s not easy to record the execution time span,statement
    parameters or other information.
    (5) It’s hard to add log function to an application that cann’t be
    modify(for example,an application that without sourcecode),or hard to
    add log code(for example,an application that uses ORMapping ).

    JDBMonitor is different:
    (1) At most,only one line code modifying will be enough.The single code
    is: Class.forName("com.cownew.JDBMonitor.jdbc.DBDriver") and a single
    change in JDBC connection String,that is modify it from
    “jdbc:db2://10.74.198.247:50000/appâ€toâ€
    listenerconfig=config.xml:url= jdbc:db2://10.74.198.247:50000/appâ€.
    In some case this single code also have no need .for instance,if you
    use the DataSource of WebLogic ,Tomcat or other Server.
    (2) It uses another thread to log the SQL,so it almost doesn’t effect
    on the running speed.
    (3) It’s highly extendible,so you can extend it to log the
    execution in other ways.For example,you can write a class to send the
    SQL statement through Email.

    Getting JDBMonitor
    The latest stable version of JDBMonitor is available from the
    JDBMonitor web page:
    http://www.cownew.com/JDBMonitor

    Using JDBMonitor
    1 Drop jdbmonitor.jar to the classpath of your application
    2 Make the application load the JDBMonitor JDBC Driver.
    This step depends on the way you load the JDBC Driver
    (1) if you write code to load the JDBC Driver,for example:

    Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriverâ€);
    Connection cn = DriverManager.getConnection(……);

    In this case,you must modify the “Class.forName†to load
    JDBMonitor JDBC Driver(“com.cownew.JDBMonitor.jdbc.DBDriverâ€)
    instead of the original database JDBC driver.
    For example:
    Class.forName(“com.cownew.JDBMonitor.jdbc.DBDriverâ€);
    Connection cn = DriverManager.getConnection(……);
    (2) if you specify the JDBC Driver class in config file,for
    example,datasource configfile or other file.
    Please modify the original database JDBC driver name to
    “com.cownew.JDBMonitor.jdbc.DBDriverâ€
    3 Make the JDBMonitor load the original database JDBC driver.
    The principle of JDBMonitor is to intercept and capture the SQL
    statement sending to database JDBC driver,it logs the SQL
    statement,then redirects the SQL statement to the original JDBC
    Driver,so JDBMonitor must register the original JDBC Driver to
    DriverManager first.
    The original JDBC Driver is defined at the “JdbcDrivers†segment of
    the config file.
    <JdbcDrivers>
    <JdbcDriver class=" com.mysql.jdbc.Driver"/>
    </JdbcDrivers>
    4 Appending the JDBMonitor information to the original JDBC connection
    string.
    The only change you should do it to appendâ€
    listenerconfig=<configfilepath>:url=†before the original JDBC
    connection string.
    “<configfilepath>â€is path of the confile file, file path below all
    support:
    /com/jdbmonitor/config.xml
    com/jdbmonitor/config.xml
    c:/ jdbmonitor /config.xml
    JDBMoinitor uses getClass().getResourceAsStream to load
    “/com/jdbmonitor/config.xml†and “com/jdbmonitor/config.xml†,
    and FileInputStream to load “c:/ jdbmonitor /config.xmlâ€.
    5 Specify the DBListener to use:
    You can log the SQL execution in different ways,eg, to console,to file
    ,to remote client or others.
    We have developed such DBListeners in common use: FileDBListener,
    ConsoleDBListener, SocketDBListenerã€SocketDBListener.You can also
    develop DBListeners on your demond.

    The DBListener is defined at the “Listeners†segment of theconfig
    file:
    <Listeners>
    <!--ConsoleDBListener no arguments-->
    <Listener
    class="com.cownew.JDBMonitor.listenerImpl.ConsoleDBListener" arg=""/>

    <!--the arguments of FileDBListener is the file to log the SQL
    statement -->
    <Listener class="com.cownew.JDBMonitor.listenerImpl.FileDBListener"
    arg="c:/aaa.txt"/>

    <!--the arguments of SocketDBListener is the bound socket port of
    the listener server -->
    <Listener
    class="com.cownew.JDBMonitor.listenerImpl.SocketDBListener"
    arg="9527"/>
    </Listeners>

    That’s all!Start your application.Yeah,SQL statements are logged,we
    can see them in console,in file,even in remote client monitor.

    Examples
    mvnforum Example:
    You can get mvnforum from http://www.mvnForum.com. The version I use to
    demo is 1.0.
    (1) Open webapp\WEB-INF\classes\ mvncore.xml,re-config it:
    Before re-config:
    <driver_class_name>com.mysql.jdbc.Driver</driver_class_name>
    <database_url>listenerconfig=c:/log/jdbmonitor/config.xml:url=
    jdbc:mysql://localhost/mvnforum?useUnicode=true&amp;characterEncoding=utf-8</database_url>
    After re-config:
    <driver_class_name>com.cownew.JDBMonitor.jdbc.DBDriver</driver_class_name>

    <database_url>jdbc:mysql://localhost/mvnforum?useUnicode=true&amp;characterEncoding=utf-8</database_url>
    (2) create c:/log/jdbmonitor/config.xml.I only wanna log the SQL
    statement to text file,so I config it as below:
    <config>
    <Listeners>
    <!--the arguments of FileDBListener is the file to log the SQL
    statement -->
    <Listener class="com.cownew.JDBMonitor.listenerImpl.FileDBListener"
    arg="c:/log.txt"/>
    </Listeners>
    <JdbcDrivers>
    <JdbcDriver class="com.mysql.jdbc.Driver"/>
    </JdbcDrivers>
    </config>
    (3) Drop jdbmonitor.jar to webapp\WEB-INF\lib
    (4) Done!

    Jive example:
    You can get Jive from http://www.jivesoftware.com. The version I use to
    demo is Jive 2.0 beta.
    (1) Open http://localhost:8080/jive/admin/
    Fill “jdbc†with:com.cownew.JDBMonitor.jdbc.DBDriver
    Fill “server†with :
    c:/log/jdbmonitor/config.xml:url=jdbc:mysql://locahost/jive
    (3) Drop jdbmonitor.jar to WEB-INF\lib
    (4) create c:/log/jdbmonitor/config.xml as mvnforum Example.
    (4) Done!

    Code exmple:
    Though it’s not recommended,some application write the JDBC driver
    class name and JDBC connection string in code.
    For example:

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection conn = null;
    PreparedStatement ps = null;
    try
    {
    conn = DriverManager
    .getConnection("jdbc:eek:dbc:MQIS");
    for (int i = 0; i < 1000; i++)
    {
    ps = conn.prepareStatement("update
    T_Material set fid=fid");
    ps.execute();
    ps.close();
    }
    } finally
    {
    ....
    }
    (1) Let’s recode a little:

    Class.forName("com.cownew.JDBMonitor.jdbc.DBDriver");
    Connection conn = null;
    PreparedStatement ps = null;
    try
    {
    conn =
    DriverManager.getConnection("listenerconfig=
    c:/log/jdbmonitor/config.xml:url=jdbc:eek:dbc:MQIS");
    for (int i = 0; i < 1000; i++)
    {
    ps = conn.prepareStatement("update
    T_Material set fid=fid");
    ps.execute();
    ps.close();
    }
    } finally
    {
    ....
    }
    (2) create c:/log/jdbmonitor/config.xml.I wanna log the SQL statement
    to text file and log to them to console so that it can help me to
    debug,so I config it as below:
    <config>
    <Listeners>
    <!--the arguments of FileDBListener is the file to log the SQL
    statement -->
    <Listener class="com.cownew.JDBMonitor.listenerImpl.FileDBListener"
    arg="c:/log.txt"/>

    <!--ConsoleDBListener no arguments-->
    <Listener class="com.cownew.JDBMonitor.listenerImpl.ConsoleDBListener"
    arg=""/>
    </Listeners>
    <JdbcDrivers>
    <JdbcDriver class="com.mysql.jdbc.Driver"/>
    </JdbcDrivers>
    </config>
    (3) Drop j dbmonitor.jar to classpath
    (4) Done!



    Listener
    We have developed such DBListeners in common use: ConsoleDBListener,
    FileDBListener,SocketDBListener,DataBaseDBListener.

    1ã€ConsoleDBListener

    ConsoleDBListener will write SQL Statement to Console.

    ..

    This Listener is easy to config:

    <Listener class="com.cownew.JDBMonitor.listenerImpl.ConsoleDBListener"
    arg=""/>

    2ã€FileDBListener

    FileDBListener will write SQL Statement to textfile:



    Config as below:

    <Listener class="com.cownew.JDBMonitor.listenerImpl.FileDBListener"
    arg="c:/aaa.txt"/>

    arg="c:/aaa.txt" means the SQL statement will be writen into
    c:/aaa.txt.

    3ã€SocketDBListener

    SocketDBListener works as a socket server,client can receive the SQL
    statement after connected to the socket server.

    Config as below:

    <Listener class="com.cownew.JDBMonitor.listenerImpl.SocketDBListener"
    arg="9527"/>

    arg="9527" means the SocketDBListener will listen at port 9527.

    Now,we have developed two kinds of socket client:SocketConsoleClient
    and SocketSwingClient.

    SocketConsoleClient works in console:



    SocketSwingClient works in Swing GUI:



    You can execute "java -classpath jdbmonitor.jar
    com.cownew.JDBMonitor.listenerImpl.sckListenerClient.SocketConsoleClient"
    to start SocketConsoleClient,and "java -classpath jdbmonitor.jar
    com.cownew.JDBMonitor.listenerImpl.sckListenerClient.SocketSwingClient"
    to start SocketSwingClient.

    If you wanna write client listener on your demand,please reference
    com.cownew.JDBMonitor.listenerImpl.sckListenerClient.ListenerClient and
    com.cownew.JDBMonitor.listenerImpl.sckListenerClient.IDBSocketClientListener.

    4ã€DataBaseDBListener

    DataBaseDBListener will record SQL statement to Database.



    Config as below:

    <Listener class="com.cownew.JDBMonitor.listenerImpl.DataBaseDBListener"

    arg="dburl=jdbc:eek:dbc:MQIS;user=;password=;logtable=T_Log_SQLLog"/>

    "dburl=jdbc:eek:dbc:MQIS;user=;password=;" declares the JDBC connection
    string of the target database;"logtable=T_Log_SQLLog" declares which
    table to record the SQL statement,default tablename is T_Log_SQLLog.

    If the JDBC driver class is different from the database to be
    monitored,please add the JDBC driver class in the "JdbcDrivers" tags of
    the config file.

    for example:

    <config>
    <Active>true</Active>
    <Listeners>

    <Listener class="com.cownew.JDBMonitor.listenerImpl.ConsoleDBListener"
    arg=""/>

    <Listener class="com.cownew.JDBMonitor.listenerImpl.DataBaseDBListener"

    arg="dburl=jdbc:eek:dbc:MQIS;user=;password=;logtable=T_Log_SQLLog"/>
    </Listeners>
    <JdbcDrivers>
    <JdbcDriver class="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
    <JdbcDriver class="sun.jdbc.odbc.JdbcOdbcDriver"/>
    </JdbcDrivers>
    </config>


    The schema of "T_Log_SQLLog" is:



    The "create table" SQL statement can be found
    at:com/cownew/JDBMonitor/listenerImpl/dataBaseListener,(db2.sql,mssqlserver..sql,oracle.sql)

    DataBaseDBListener is database independent, You can record the SQL
    statement to any relation database.
     
    cownew, May 14, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. tshad
    Replies:
    7
    Views:
    16,999
    tshad
    Mar 11, 2005
  2. 00_CP_D12
    Replies:
    3
    Views:
    8,932
    dilipv
    Feb 22, 2008
  3. Stu
    Replies:
    7
    Views:
    1,769
    Dave Thompson
    Mar 7, 2005
  4. Michael Tan
    Replies:
    32
    Views:
    998
    Ara.T.Howard
    Jul 21, 2005
  5. Replies:
    19
    Views:
    212
    Randy Webb
    Nov 30, 2006
Loading...

Share This Page