make any database have the ability of logging within 10 seconds
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=EF=BC=8Cwithout 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=EF=BC=8Cso 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=E2=80=9Dlogger.logSQL(sql)=E2=80=9D fi=
lling in
the code.It=E2=80=99s drawbacks as follow:
(1) It=E2=80=99s 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=E2=80=99s not easy to record the execution time span,statement
parameters or other information.
(5) It=E2=80=99s hard to add log function to an application that cann=E2=80=
=99t 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
=E2=80=9Cjdbc:db2://10.74.198.247:50000/app=E2=80=9Dto=E2=80=9D
listenerconfig=config.xml:url= jdbc:db2://10.74.198.247:50000/app=E2=80=
=9D.
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=E2=80=99t effe=
ct
on the running speed.
(3) It=E2=80=99s highly extendible=EF=BC=8Cso 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(=E2=80=9Ccom.microsoft.jdbc.sqlserver.SQLServerDriver=E2=
=80=9D);
Connection cn = DriverManager.getConnection(=E2=80=A6=E2=80=A6);
In this case,you must modify the =E2=80=9CClass.forName=E2=80=9D to load
JDBMonitor JDBC Driver(=E2=80=9Ccom.cownew.JDBMonitor.jdbc.DBDriver=E2=80=
=9D)
instead of the original database JDBC driver.
For example:
Class.forName(=E2=80=9Ccom.cownew.JDBMonitor.jdbc.DBDriver=E2=80=9D);
Connection cn = DriverManager.getConnection(=E2=80=A6=E2=80=A6);
(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
=E2=80=9Ccom.cownew.JDBMonitor.jdbc.DBDriver=E2=80=9D
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 =E2=80=9CJdbcDrivers=E2=80=9D se=
gment 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=E2=80=9D
listenerconfig=<configfilepath>:url==E2=80=9D before the original JDBC
connection string.
=E2=80=9C<configfilepath>=E2=80=9Dis path of the confile file, file path be=
low all
support:
/com/jdbmonitor/config.xml
com/jdbmonitor/config.xml
c:/ jdbmonitor /config.xml
JDBMoinitor uses getClass().getResourceAsStream to load
=E2=80=9C/com/jdbmonitor/config.xml=E2=80=9D and =E2=80=9Ccom/jdbmonitor/co=
nfig.xml=E2=80=9D ,
and FileInputStream to load =E2=80=9Cc:/ jdbmonitor /config.xml=E2=80=9D.
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=E3=80=81SocketDBListener.You can also
develop DBListeners on your demond.
The DBListener is defined at the =E2=80=9CListeners=E2=80=9D segment of the=
config
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=E2=80=99s 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&characterEncoding=u=
tf-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&charact=
erEncoding=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 =E2=80=9Cjdbc=E2=80=9D with:com.cownew.JDBMonitor.jdbc.DBDriver
Fill =E2=80=9Cserver=E2=80=9D 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=E2=80=99s 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:odbc:MQIS");
for (int i = 0; i < 1000; i++)
{
ps = conn.prepareStatement("update
T_Material set fid=fid");
ps.execute();
ps.close();
}
} finally
{
....
}
(1) Let=E2=80=99s 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:odbc: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=E3=80=81ConsoleDBListener
ConsoleDBListener will write SQL Statement to Console.
..
This Listener is easy to config:
<Listener class="com.cownew.JDBMonitor.listenerImpl.ConsoleDBListener"
arg=""/>
2=E3=80=81FileDBListener
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=E3=80=81SocketDBListener
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.IDBSocketClientListene=
r.
4=E3=80=81DataBaseDBListener
DataBaseDBListener will record SQL statement to Database.
Config as below:
<Listener class="com.cownew.JDBMonitor.listenerImpl.DataBaseDBListener"
arg="dburl=jdbc:odbc:MQIS;user=;password=;logtable=T_Log_SQLLog"/>
"dburl=jdbc:odbc: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:odbc: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.