Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > make any database have the ability of logging within 10 seconds

Reply
Thread Tools

make any database have the ability of logging within 10 seconds

 
 
cownew
Guest
Posts: n/a
 
      05-14-2006
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.SQLS erverDriver”);
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.DBDriv er”);
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.ConsoleD BListener" arg=""/>

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

<!--the arguments of SocketDBListener is the bound socket port of
the listener server -->
<Listener
class="com.cownew.JDBMonitor.listenerImpl.SocketDB Listener"
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.DBDr iver</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.FileDBLi stener"
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.FileDBLi stener"
arg="c:/log.txt"/>

<!--ConsoleDBListener no arguments-->
<Listener class="com.cownew.JDBMonitor.listenerImpl.ConsoleD BListener"
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.ConsoleD BListener"
arg=""/>

2、FileDBListener

FileDBListener will write SQL Statement to textfile:



Config as below:

<Listener class="com.cownew.JDBMonitor.listenerImpl.FileDBLi stener"
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.SocketDB Listener"
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.sckListenerClie nt.SocketConsoleClient"
to start SocketConsoleClient,and "java -classpath jdbmonitor.jar
com.cownew.JDBMonitor.listenerImpl.sckListenerClie nt.SocketSwingClient"
to start SocketSwingClient.

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

4、DataBaseDBListener

DataBaseDBListener will record SQL statement to Database.



Config as below:

<Listener class="com.cownew.JDBMonitor.listenerImpl.DataBase DBListener"

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.ConsoleD BListener"
arg=""/>

<Listener class="com.cownew.JDBMonitor.listenerImpl.DataBase DBListener"

arg="dburl=jdbcdbc:MQIS;user=;password=;logtable =T_Log_SQLLog"/>
</Listeners>
<JdbcDrivers>
<JdbcDriver class="com.microsoft.jdbc.sqlserver.SQLServerDrive r"/>
<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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert Seconds to X Hours Y Minutes Z Seconds? 00_CP_D12 ASP .Net 3 02-22-2008 10:37 AM
a method to make js have the ability to inherit zzw8206262001@yahoo.com.cn Javascript 19 11-30-2006 06:24 AM
1. Ruby result: 101 seconds , 2. Java result:9.8 seconds, 3. Perl result:62 seconds Michael Tan Ruby 32 07-21-2005 03:23 PM
Convert seconds to minutes and seconds tshad ASP .Net 7 03-11-2005 11:27 PM
Converting seconds to (Days, Hours, Minutes, seconds) Stu C Programming 7 03-07-2005 08:44 AM



Advertisments