make any database have the ability of logging within 10 seconds

[cownew]

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("jdbcdbc: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=jdbcdbc: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=jdbcdbc:MQIS;user=;password=;logtable=T_Log_SQLLog"/>

"dburl=jdbcdbc: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=jdbcdbc: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.