Files
log4jdbc/doc/index.html
T
2008-11-08 22:01:57 +00:00

596 lines
26 KiB
HTML

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">
<head>
<title>log4jdbc - A JDBC Driver for logging SQL and JDBC calls.</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="reset-fonts-grids.css" />
<link rel="stylesheet" href="log4jdbc.css" />
</head>
<body>
<h1><a name="top"/><img class="logo" src="log4jdbc-logo-gray.png"><br>
<a href="http://sourceforge.net/project/showfiles.php?group_id=194500">download</a> |
<a href="#news">news</a> |
<a href="#features">features</a> |
<a href="#usage">usage</a> |
<a href="#options">options</a> |
<a href="#other">other</a> |
<a href="#similiartools">similiar tools</a> |
<a href="#feedback">feedback</a>
<br>
<a href="http://sourceforge.net/projects/log4jdbc/">sourceforge page</a> |
<a href="apidocs-jdbc3/net/sf/log4jdbc/package-summary.html">JDK1.4/JDBC 3 javadoc</a> |
<a href="apidocs-jdbc4/net/sf/log4jdbc/package-summary.html">JDK1.6/JDBC 4 javadoc</a>
</h1>
<p>
<b>log4jdbc</b> is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information)
for other JDBC drivers using the <a target="slf4j" href="http://www.slf4j.org/">Simple Logging Facade For Java</a> (SLF4J) logging system.
</p>
<h2><a name="news"/>news</h2>
<p class="left"><b>2008-11-08:</b> <a href="http://arthurblake.wordpress.com/2008/11/08/log4jdbc-12-alpha1-released/">log4jdbc 1.2 alpha 1 released.</a> Many new options for controlling SQL output and a new log for viewing connection open/close events (very useful for hunting down connection leak issues.) See <a href="CHANGES">CHANGES</a> for all the release details.</p>
<p class="left"><b>2008-04-11:</b> <a href="http://arthurblake.wordpress.com/2008/04/11/log4jdbc-11-released/">log4jdbc 1.1 final release out!</a> </p>
<p class="left"><b>2007-11-10:</b> <a href="http://sourceforge.net/forum/forum.php?forum_id=753559">log4jdbc 1.1 beta 1 released.</a> New optional timing threshold settings for honing in on slow SQL.</p>
<p class="left"><b>2007-07-25:</b> <a href="http://sourceforge.net/forum/forum.php?forum_id=719640">log4jdbc 1.1 alpha 2 released.</a> JDBC 4 support!</p>
<p class="left"><b>2007-05-29:</b> <a href="http://sourceforge.net/forum/forum.php?forum_id=700185">log4jdbc 1.1 alpha 1 released.</a> Most notable change is that the <a target="slf4j" href="http://slf4j.org">Simple Logging Facade for Java</a> is now used instead of <b>log4j</b> directly.
<p class="left"><b>2007-04-21:</b> <a href="http://sourceforge.net/forum/forum.php?forum_id=688372">log4jdbc 1.0 has been released!</a> Download it and give it a try!</p>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="features"/>features</h2>
<ul class="left">
<li>
Full support for JDBC 3 and JDBC 4!
</li>
<li>
Easy to configure, in most cases all you need to do is change the driver class name to <b>net.sf.log4jdbc.DriverSpy</b>
and prepend <b>"jdbc:log4"</b> to your existing jdbc url, set up your logging categories and
you're ready to go!
</li>
<li>
In the logged output, for prepared statements, the bind arguments are automatically
inserted into the SQL output. This greatly Improves readability and debugging for
many cases.
</li>
<li>
SQL timing information can be generated to help identify how long SQL statements take
to run, helping to identify statements that are running too slowly and this data can
be post processed with an included tool to produce profiling report data for quickly
identifying slow SQL in your application..
</li>
<li>
SQL connection number information is generated to help identify connection pooling or
threading problems.
</li>
<li>
Works with any underlying JDBC driver, with JDK 1.4 and above, and SLF4J 1.x.
</li>
<li>
Open source software, licensed under the business friendly <b>Apache 2.0 license:</b> <a href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
</li>
</ul>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="usage"/>usage</h2>
<ol class="left">
<li><p><b>Decide if you need JDBC 3 or JDBC 4 support.</b>
<ul>
<li>If you are using JDK 1.4 or 1.5, you should use the JDBC 3 version of log4jdbc.</li>
<li>If you are using JDK 1.6 or 1.7, you should use the JDBC 4 version of log4jdbc (even if the actual underlying JDBC driver you are using is a JDBC 3 or older driver).</li>
</ul>
<p>
Currently there are very few actual JDBC 4 drivers on the market. (The only major one that I really know about is Apache Derby aka The Java DB distributed with JDK 1.6.)
JDBC 4 support was added with the JDK 1.6 release and adds many additional features over and above JDBC 3.
However, the log4jdbc JDBC 4 driver can wrap a JDBC 3 or older driver and
it's recommended that if you use JDK 1.6 or above, that you use the log4jdbc JDBC 4 driver that is compiled with JDK 1.6.
</p>
<p>
Note that JDBC 2 is not currently supported by log4jdbc, although if you are
using JDK 1.4 and above, the log4jdbc 3 or 4 driver should be able to wrap
an older JDBC 2 driver as well-- log4jdbc just won't work with Java 1.3 and
earlier.
</p>
<p>
Choose and <a href="http://sourceforge.net/project/showfiles.php?group_id=194500">download</a> one of the driver .jar files:
</p>
<ul>
<li><b>log4jdbc3-1.2alpha1.jar</b> for JDBC 3 support in JDK 1.4 , JDK 1.5</li>
<li><b>log4jdbc4-1.2alpha1.jar</b> for JDBC 4 support in JDK 1.6 , JDK 1.7</li>
</ul>
Place the log4jdbc jar that you choose into your application's classpath.
</li>
<li><p><b>Choose which java logging system you will use.</b></p>
In many cases, you already know this, because it is dictated by your existing application.
log4jdbc uses the Simple Logging Facade for Java (SLF4J) which is a very simple and very flexible little library that lets you pick among many common java logging systems:</p>
<ul>
<li>Log4j</li>
<li>java.util logging in JDK 1.4</li>
<li>logback</li>
<li>Jakarta Commons Logging</li>
</ul>
<p> SLF4J is designed to de-couple your application from the java logging system so you can choose any one you want. This is the same goal of Jakarta Commons Logging. However many people have
had headaches and issues with classloading problems in complex environments using Jakarta Commons Logging. SLF4J solves these problems with it's much simpler design, and you can even integrate
SLF4J to use Jakarta Commons Logging, if you really want to (or are required to) use it.</p>
<p>
<a href="http://www.slf4j.org/download.html">Download</a> the latest official SLF4J release.
</p>
<p>
You will need two jars: <b>slf4j-api-1.5.0.jar</b> (or the latest available version) and
whichever jar you pick depending on the java logging system you choose.</p>
<p>Place these two .jar files into your application's classpath.</p>
<p>
Please read the documentation at the
<a target="slf4j" href="http://slf4j.org">SLF4J website</a>. It's really easy to set up!
</p>
</a>
</li>
<li><p><b>Set your JDBC driver class to <code>net.sf.log4jdbc.DriverSpy</code> in your application's configuration.</b></p>
<p>
The underlying driver that is being spied on in many cases will be loaded automatically
without any additional configuration.
</p>
<p>
The log4jdbc "spy" driver will try and load the following popular jdbc drivers:
</p>
<table class="options">
<tbody>
<tr>
<td>oracle.jdbc.driver.OracleDriver</td>
<td>Oracle</td>
</tr>
<tr>
<td>com.sybase.jdbc2.jdbc.SybDriver</td>
<td>Sybase</td>
</tr>
<tr>
<td>net.sourceforge.jtds.jdbc.Driver</td>
<td>jTDS SQL Server & Sybase driver</td>
</tr>
<tr>
<td>com.microsoft.jdbc.sqlserver.SQLServerDriver</td>
<td>Microsoft SQL Server 2000 driver</td>
</tr>
<tr>
<td>com.microsoft.sqlserver.jdbc.SQLServerDriver</td>
<td>Microsoft SQL Server 2005 driver</td>
</tr>
<tr>
<td>weblogic.jdbc.sqlserver.SQLServerDriver</td>
<td>Weblogic SQL Server driver</td>
</tr>
<tr>
<td>com.informix.jdbc.IfxDriver</td>
<td>Informix</td>
</tr>
<tr>
<td>org.apache.derby.jdbc.ClientDriver</td>
<td>Apache Derby client/server driver, aka the Java DB</td>
</tr>
<tr>
<td>org.apache.derby.jdbc.EmbeddedDriver</td>
<td>Apache Derby embedded driver, aka the Java DB</td>
</tr>
<tr>
<td>com.mysql.jdbc.Driver</td>
<td>MySQL</td>
</tr>
<tr>
<td>org.postgresql.Driver</td>
<td>PostgresSQL</td>
</tr>
<tr>
<td>org.hsqldb.jdbcDriver</td>
<td>HSQLDB pure Java database</td>
</tr>
<tr>
<td>org.h2.Driver</td>
<td>H2 pure Java database</td>
</tr>
</tbody>
</table>
<p>
If you want to use a different underlying jdbc driver that is not already in the above supported list,
set a system property, <b>log4jdbc.drivers</b> to the class name of the additional driver. This
can also be a comma separated list of driver class names if you need more than one.
</p>
<p>
(optional) <code>-Dlog4jdbc.drivers=&lt;driver&gt;[,&lt;driver&gt;...]</code> If your driver is not one of the common preloaded drivers.
</p>
</li>
<li><p><b>Prepend <code>jdbc:log4</code> to the normal jdbc url that you are using.</b></p>
<p>For example, if your normal jdbc url is</p>
<p><code>jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase</code></p>
<p>then You would change it to: </p>
<p><code><b>jdbc:log4</b>jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase</code></p>
<p>to use <b>log4jdbc.</b></p>
</li>
<li><p><b>Set up your loggers.</b> There are 5 loggers that are used
by log4jdbc,
If all 5 are set to a level less than error
(such as the FATAL level), then log4jdbc will not log anything and in fact
the actual (real) connection to the underlying database will be returned by
the log4jdbc driver (thus allowing log4jdbc to be installed and available to
turn on at runtime at a moment's notice without imposing any actual
performance loss when not being used). If any of the 4 logs are set to ERROR
level or above (e.g ERROR,INFO or DEBUG) then log4jdbc will be activated,
wrapping and logging activity in the JDBC connections returned by the
underlying driver.</p>
<p>Each of these logs can be set at either DEBUG, INFO or ERROR level.</p>
<ul>
<li>
<b>DEBUG</b> includes the class name and line number (if available) at which the SQL was executed.<br>
<b>Use DEBUG level with extra care, as this imposes an additional performance penalty when in use.</b>
</li>
<li>
<b>INFO</b> includes the SQL (or other information as applicable.)
</li>
<li>
<b>ERROR</b> will show the stack traces in the log output when SQLExceptions occur.
</li>
</ul>
<table class="options">
<thead>
<tr>
<th>logger</th>
<th>description</th>
<th>since</th>
</tr>
</thead>
<tbody>
<tr>
<td>jdbc.sqlonly</td>
<td>Logs only SQL. SQL executed within a prepared statement is automatically shown with
it's bind arguments replaced with the data bound at that position, for greatly increased readability.</td>
<td>1.0</td>
</tr>
<tr>
<td>jdbc.sqltiming</td>
<td>Logs the SQL, post-execution, including timing statistics on how long the SQL took to execute.</td>
<td>1.0</td>
</tr>
<tr>
<td>jdbc.audit</td>
<td>Logs ALL JDBC calls except for ResultSets. This is a very voluminous
output, and is not normally needed unless tracking down a specific JDBC problem.</td>
<td>1.0</td>
</tr>
<tr>
<td>jdbc.resultset</td>
<td>Even more voluminous, because all calls to ResultSet objects are logged.</td>
<td>1.0</td>
</tr>
<tr>
<td>jdbc.connection</td>
<td>Logs connection open and close events as well as dumping all open
connection numbers. This is very useful for hunting down connection
leak problems.</td>
<td>1.2alpha1</td>
</tr>
</tbody>
</table>
<p>Additionally, there is one logger named <b>log4jdbc.debug</b> which is for use with internal debugging of log4jdbc.
At this time it just prints out information on which underlying drivers were found and not found when the
log4jdbc spy driver loads.</p>
<p>In a typical usage scenario, you might turn on only the jdbc.sqlonly logging at INFO level,
just to view the SQL coming out of your program.</p>
<p>Then if you wanted to view how long each SQL statement is taking to execute, you might use jdbc.sqltiming.</p>
<p>jdbc.audit, jdbc.resultset and jdbc.connection are used for more in depth
diagnosis of what is going on under the hood with JDBC
as potentially almost every single call to JDBC could be logged
(logs can grow very large, very quickly with jdbc.audit and jdbc.resultset!)</p>
<p>Because SLF4J can be used with many popular java logging systems,
the setup for your loggers will vary depending on which underlying
logging system you use. Sample configuration files for log4j are
provided here: <a href="log4j.xml">log4j.xml</a> and <a href="log4j.properties">log4j.properties</a>.</p>
</li>
<li><p><b>Adjust debugging options.</b> When logging at the DEBUG level, the class file and line number (if available) for the
class that invoked JDBC is logged after each log statement. This is enormously useful for finding where in the
code the SQL is generated. Be careful when using this on a production system because there is a small, but
potentially significant penalty performance for generating this data on each logging statement.</p>
<p>In many cases, this call stack data is not very useful because the calling class into log4jdbc is a connection pool,
object-persistance layer or other layer between log4jdbc and your application code-- but the class file and
line number information you really are interested in seeing is where in your application the SQL was generated from.</p>
<p>Set the <code>log4jdbc.debug.stack.prefix</code> System property for log4jdc to help get around this problem:</p>
<p>
(optional) <code>-Dlog4jdbc.debug.stack.prefix=&lt;package.prefix&gt;</code> a String that is the partial (or full) package
prefix for the package name of your application. The call stack will be searched down to the first occurence
of a class that has the matching prefix. If this is not set, the actual class that called into log4jdbc is used
in the debug output (in many cases this will be a connection pool class)</p>
<p>
For example, setting a system property such as this: <code>-Dlog4jdbc.debug.stack.prefix=com.mycompany.myapp</code>
Would cause the call stack to be searched for the first call that came from code in the com.mycompany.myapp package or below,
thus if all of your SQL generating code was in code located in the com.mycompany.myapp package or any subpackages,
this would be printed in the debug information, rather than the package name for a connection pool, object relational system, etc.
</p>
</li>
</ol>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="options"/>options</h2>
<p>
log4jdbc options are controlled via system properties. The simplest way to set
these is with the java <b>-D</b> command line option. For example:
<p><code>
java -D<b>log4jdbc.drivers</b>=my.funky.DriverClass -classpath ./classes my.funky.Program
</code></p>
</p>
<table class="options">
<thead>
<tr>
<th>system property</th>
<th>default</th>
<th>description</th>
<th>since</th>
</tr>
</thead>
<tbody>
<tr>
<td>log4jdbc.drivers</td>
<td></td>
<td>One or more fully qualified class names for JDBC drivers that log4jdbc
should load and wrap. If more than one driver needs to be specified
here, they should be comma separated with no spaces. This option is
not normally needed because most popular JDBC drivers are already
loaded by default-- this should be used if one or more additional
JDBC drivers that (log4jdbc doesn't already wrap) needs to be included.</td>
<td>1.0</td>
</tr>
<tr>
<td>log4jdbc.debug.stack.prefix</td>
<td></td>
<td>The partial (or full) package prefix for the package
name of your application. The call stack will be searched down to the
first occurence of a class that has the matching prefix. If this is not
set, the actual class that called into log4jdbc is used in the debug
output (in many cases this will be a connection pool class)
<br><br>
For example, setting a system property such as this:
<code>-Dlog4jdbc.debug.stack.prefix=com.mycompany.myapp</code>
Would cause the call stack to be searched for the first call that came
from code in the com.mycompany.myapp package or below, thus if all of
your sql generating code was in code located in the com.mycompany.myapp
package or any subpackages, this would be printed in the debug
information, rather than the package name for a connection pool,
object relational system, etc.
</td>
<td>1.0</td>
</tr>
<tr>
<td>log4jdbc.sqltiming.warn.threshold</td>
<td></td>
<td>Millisecond time value. Causes SQL that takes the number of
milliseconds specified or more time to execute to be logged at
the <b>warning</b> level in the sqltiming log.
<br><br>
Note that the sqltiming log must be enabled at the warn log level for
this feature to work. Also the logged output for this setting will log
with debug information that is normally only shown when the sqltiming
log is enabled at the debug level. This can help you to more quickly
find slower running SQL without adding overhead or logging for normal
running SQL that executes below the threshold level (if the logging
level is set appropriately.)
</td>
<td>1.1beta1</td>
</tr>
<tr>
<td>log4jdbc.sqltiming.error.threshold</td>
<td></td>
<td>Millisecond time value. Causes SQL that takes the number of
milliseconds specified or more time to execute to be logged at
the <b>error</b> level in the sqltiming log.
<br><br>
Note that the sqltiming log must be enabled at the error log level for
this feature to work. Also the logged output for this setting will log
with debug information that is normally only shown when the sqltiming
log is enabled at the debug level. This can help you to more quickly
find slower running SQL without adding overhead or logging for normal
running SQL that executes below the threshold level (if the logging
level is set appropriately.)
</td>
<td>1.1beta1</td>
</tr>
<tr>
<td>
log4jdbc.dump.booleanastruefalse
</td>
<td>false</td>
<td>When dumping boolean values in SQL, dump them as 'true' or 'false'.
If this option is not set, they will be dumped as 1 or 0 as many
databases do not have a boolean type, and this allows for more
portable sql dumping.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.maxlinelength
</td>
<td>90</td>
<td>When dumping SQL, if this is greater than 0, than the dumped SQL will
be broken up into lines that are no longer than this value.
Set this value to 0 if you don't want log4jdbc to try and break the SQL
into lines this way. In future versions of log4jdbc, this will
probably default to 0.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.fulldebugstacktrace
</td>
<td>false</td>
<td>If dumping in debug mode, dump the full stack trace.
This will result in <i>EXTREMELY</i> voluminous output, but can be very
useful under some circumstances when trying to track down the call chain
for generated SQL.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.select
</td>
<td>true</td>
<td>Set this to false to suppress SQL select statements in the output.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.insert
</td>
<td>true</td>
<td>Set this to false to suppress SQL insert statements in the output.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.update
</td>
<td>true</td>
<td>Set this to false to suppress SQL update statements in the output.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.delete
</td>
<td>true</td>
<td>Set this to false to suppress SQL delete statements in the output.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.create
</td>
<td>true</td>
<td>Set this to false to suppress SQL create statements in the output.</td>
<td>1.2alpha1</td>
</tr>
<tr>
<td>
log4jdbc.dump.sql.addsemicolon
</td>
<td>false</td>
<td>Set this to true to add an extra semicolon to the end of SQL in the output.
This can be useful when you want to generate SQL from a program with log4jdbc
in order to create a script to feed back into a database to run at a later time.</td>
<td>1.2alpha1</td>
</tr>
</tbody>
</table>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="other"/>other</h2>
<ul class="left">
<li>A simple tool is included which you can use to post-process sql timing logs produced by log4jdbc.
It can output simple profiling reports with statistics and a dump of the sql statements that ran the slowest within the log.
To invoke the tool, use the profsql.sh (for unix/linux) and profsql.cmd (for windows) scripts located in the scripts
folder. These scripts take as one argument, the filename of a sql timing log (generated from the
<code>jdbc.sqltiming</code> log category. They produce a profiling report to stdout.
The tool is currently experimental and I expect it to evolve quite a bit over the next few releases.
Nevertheless, it has already been very useful to me for tracking down SQL performance problems.
</li>
</ul>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="similiartools"/>similiar tools</h2>
<h3>Some other tools and libraries that are similiar to log4jdbc.</h3>
<ul class="left">
<li>
<a target="othertool" href="http://www.p6spy.com/">P6Spy</a>
is probably the most well known JDBC logging driver but it hasn't been updated in about 4 years.
</li>
<li>
<a target="othertool" href="http://www.jahia.net/jahia/page597.html">SQL Profiler</a> an add-on for P6Spy, also about 4 years out of date.
</li>
<li>
<a target="othertool" href="http://zer0.free.fr/craftsman/spy.php">Craftsman Spy</a> appears to overlap quite a bit with the feature set in log4jdbc. This library hasn't been updated in 2 years and depends on Jakarta Commons Logging.
</li>
<li>
<a target="othertool" href="http://jamonapi.sourceforge.net/"> JAMon </a> (Java Application Monitor) is a comprehensive application monitor and monitoring API which includes JDBC/SQL monitoring as part of it's very large feature set.
</li>
<li>
<a target="othertool" href="http://sourceforge.net/projects/jdbcproxy/">JdbcProxy</a> is another sourceforge hosted project. The driver can also emulate another JDBC driver to test the application without a database
</li>
<li>
<a target="othertool" href="http://rkbloom.net/logdriver/index.html">LogDriver</a> appears to be similiar to log4jdbc and the author has written a nice <a target="othertool" href="http://java.sys-con.com/read/204723.htm">article</a> on JDBC logging in general and his motivation and experience of writing LogDriver.
</li>
</ul>
</p>
<p class="right"><a href="#top">[back to top]</a></p>
<h2><a name="feedback"/>feedback</h2>
<p>Feedback and suggestions would be greatly appreciated. Email me at <b>arthur_blake at users.sourceforge.net</b></p>
<p>Also, visit <a target="myblog" href="http://arthurblake.wordpress.com">my blog</a>.</p>
<p class="right"><a href="#top">[back to top]</a></p>
<h1>
<a href="http://sourceforge.net/project/showfiles.php?group_id=194500">download</a> |
<a href="#news">news</a> |
<a href="#features">features</a> |
<a href="#usage">usage</a> |
<a href="#options">options</a> |
<a href="#other">other</a> |
<a href="#similiartools">similiar tools</a> |
<a href="#feedback">feedback</a>
<br>
<a href="http://sourceforge.net/projects/log4jdbc/">sourceforge page</a> |
<a href="apidocs-jdbc3/net/sf/log4jdbc/package-summary.html">JDK1.4/JDBC 3 javadoc</a> |
<a href="apidocs-jdbc4/net/sf/log4jdbc/package-summary.html">JDK1.6/JDBC 4 javadoc</a>
<br>
<img class="logo" src="log4jdbc-logo-gray.png"><br>
<p class="copyright">Copyright &copy; 2007-2008 <a href="http://arthur.blake.name">Arthur Blake</a></p>
</h1>
<a href="http://sourceforge.net/projects/log4jdbc/"><img alt="sourceforge" title="log4jdbc sourceforge page" class="sflogo" src="sourceforge_whitebg.gif"></a>
</body>
</html>