How to record Java SQL logs efficiently (GO)

In the development of regular projects, the most likely problem is the processing of the database, in most environments, we operate on the database using popular frameworks, such as Hibernate, MyBatis and so on. For a variety of reasons, we sometimes wonder what SQL actually executes under these frameworks.

Although Hibernate can open the function of SHOW SQL in the configuration file, MyBatis can configure the output of the SQL statement in the log4j configuration file, but these outputs are similar to the INSERT ...? ? ? Statement, not a complete SQL to run, to know that the full SQL needs to manually complete the parameters, if you want to debug such SQL is undoubtedly very painful.

LOG4JDBC is an open source SQL log framework that uses proxy mode for common JDBC Driver (Oracle, Derby, MySQL, PostgreSQL, H2, HSQLDB, ... The interception of operations, the recording of real SQL, you can combine the placeholders and parameters to display, easy to copy SQL directly in MySQL and other clients directly execute, speed up debugging. This article mainly describes how to use LOG4JDBC this can directly display the full SQL log framework, I hope you can be helpful.

Features of LOG4JDBC:

LOG4JDBC's official homepage is HTTPS://GITHUB.COM/ARTHURBLAKE/LOG4JDBC, which has the following features:

• Full support for JDBC3 and JDBC4

• Configuration is simple, in most cases, you only need to change the JDBC driver class to Net.sf.log4jdbc.DriverSpy, and then add JDBC:LOG4JDBC to the existing JDBC URL, and finally configure the type of logging

• The binding parameters in the Prepared statements are automatically inserted into the corresponding location. Greatly improved readability and commissioning in most cases

• The time-consuming information of SQL can be obtained to help determine which statements are performing too slowly, and this information can be recognized by the tool for a report on slow SQL

• SQL connection information can also be obtained to help diagnose problems with connection pooling or threading

• Compatible with any JDBC driver, requires JDK1.4 and above with slf4j1.x

• Open source software, using Apache 2.0 License

Steps to use LOG4JDBC:

Interested friends can go to the LOG4JDBC Project homepage to see how it is used, if simply using LOG4JDBC, will not be presented to ResultSet in tabular form, where we use the Extended Log framework log4jdbc-log4j2 from LOG4JDBC, which adds to the R Esultset the processing that is rendered in tabular form, the project home page is: https://code.google.com/p/log4jdbc-log4j2/. It is used in the following steps:

1. Decide which version of the JAR package to use:

If you use JDK1.5, you should use the JDBC3 version of the JAR package, which is Log4jdbc-log4j2-jdbc3.jar.

If you use JDK1.6, you should use the JDBC4 version of the JAR package as the Log4jdbc-log4j2-jdbc4.jar (even if the actual JDBC driver used is JDBC3 or even older).

If you use JDK1.7, you should use the JDBC4.1 version of the JAR package as the Log4jdbc-log4j2-jdbc4.1.jar (even if the actual JDBC driver used is JDBC3 or even older).

2. Add the JAR package into the project:

There are two ways to add a jar package to a project, the first is to add the log4jdbc-log4j2 and slf4j jar packages directly into the CLASSPATH, and the second is to use MAVEN to introduce the jar package, which we mainly describe in the second way. In the Pom.xml file, replace log4jdbc-log4j2-jdbcxx (such as log4jdbc-log4j2-jdbc4.1, or LOG4JDBC-LOG4J2-JDBC4, or log4j, depending on the JDBC-driven version used) DBC-LOG4J2-JDBC3). The framework needs to be used in conjunction with SLF4J, with MAVEN configured as follows:

<Dependency><Groupid>org.bgee.log4jdbc-log4j2</Groupid><Artifactid>log4jdbc-log4j2-jdbcxx</Artifactid><version>1.16</Version></Dependency><Dependency><Groupid>org.slf4j</Groupid><Artifactid>jcl-over-slf4j</Artifactid><version>1.7.2</Version></Dependency><Dependency><Groupid>org.slf4j</Groupid><Artifactid>slf4j-api</artifactid>< version>1.7.2</version></dependency>< dependency><groupId> Org.slf4j</groupid><artifactid>slf4j-log4j12</artifactId>< Span class= "Hljs-tag" ><version>1.7.2</ version></DEPENDENCY>   

3. Change the JDBC driver class in the project configuration file to Net.sf.log4jdbc.sql.jdbcapi.DriverSpy.

4. Before adding JDBC:LOG4 to the existing JDBC URL:

For example, the original JDBC URL is

, it should be changed to:

5, the type of configuration log record:

LOG4JDBC can be configured with the following types of logs:

• Jdbc.sqlonly: Logging only SQL

• Jdbc.sqltiming: Logging SQL and time-consuming information

• Jdbc.audit: Records all JDBC Call information except ResultSet, generates a lot of records and facilitates debugging to track specific JDBC problems

• Jdbc.resultset: More record information is generated because resultset information is recorded

• Jdbc.connection: Record connection open, close and other information, facilitate debugging database connection related issues

The above log types can be set to DEBUG, INFO or ERROR level. When set to FATAL or off, this means that the record is closed.

The following is a typical configuration using log4j as a specific logging system, which is configured in Log4j.properties:

log4j.logger.jdbc.sqlonly=OFF log4j.logger.jdbc.sqltiming=INFO   log4j.logger.jdbc.audit=OFF log4j.logger.jdbc.resultset=OFF log4j.logger.jdbc.connection=OFF

6. Add log4jdbc.log4j2.properties file:

This is the final step, create a log4jdbc.log4j2.properties file under the project's CLASSPATH path, and tell Log4jdbc-log4j2 to use SLF4J to record and print the log, adding in the configuration file:

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

Finally, run the project and see the effect.

Effect:

As you can see, the execution time of the two SELECT statements is 117 and 552 milliseconds respectively, which is helpful for debugging. Now many projects under the pressure measurement and on-line, the basic use of OneAPM, its database monitoring analysis function more powerful, not only can record SQL log, but also can be located to the Java line of operation SQL, directly on the page can see the effect, the use of experience is good.

Http://www.cnblogs.com/oneapm/p/4828243.html

How to record Java SQL logs efficiently (GO)