Go to "Use LOG4JDBC to record SQL information

Original blog from: http://www.cnblogs.com/xdp-gacl/p/4081848.html thanks!

A brief introduction of LOG4JDBC

　　With log4jdbc , you can collect the executed SQL and JDBC execution without changing the original code .

The ibatis,hibernate,spring JDBC SQL log information that is normally developed for use is a disadvantage where placeholders are printed separately from the parameters, and if you want to copy the SQL to Plsql developer client directly, Need to piece together SQL yourself. And LOG4JDBC is a log framework in the JDBC layer, you can combine the placeholders and parameters to display, easy to copy SQL directly in the Plsql developer and other clients directly execute, speed up debugging.

Second,the use of Log4jdbc

Download the Log4jdbc jar package Log4jdbc-1.2.jar and the dependent jar package Log4j-1.2.17.jar, Slf4j-api-1.6.0.jar, Slf4j-log4j12-1.7.7.jar as shown:

　　

Configure the configuration file for the log4j log4j.properties as follows:

Log4j.logger.jdbc.sqlonly=debug,consolelog4j.appender.console=org.apache.log4j.consoleappender log4j.appender.console.layout=org.apache.log4j.patternlayoutlog4j.appender.console.layout.conversionpattern=%d {Yyyy-mm-dd HH:mm:ss. SSS}%m%n%nlog4j.logger.jdbc.sqltiming=info,console  Log4j.logger.jdbc.connection=info,console

Modify the URL and driverclassname of the dbconfig.properties configuration file

1 URL:jdbc:log4jdbc: Mysql://localhost:3306/xdptest 2 driverclassname:net.sf.log4jdbc.DriverSpy 3 Username:root 4 password:root 5 filters:stat 6 maxactive:200 7 initialsize:20 8 maxwait:60000 9 minIdle:1010 timeBetweenEv ictionrunsmillis:6000011 minevictableidletimemillis:30000012 validationquery:select ' x ' testWhileIdle:true14 Testonborrow:false15 testonreturn:false16 removeabandoned:false17 removeabandonedtimeout:180018 logAbandoned:true

After such a configuration, you can use LOG4JDBC to record the SQL information that the application system executes.

Write a test servlet to test with the following code:

 1/** 2 * 3 */4 package Me.gacl.web.controller; 5 6 Import Java.io.IOException; 7 Import java.sql.Connection; 8 Import java.sql.PreparedStatement; 9 Import java.sql.sqlexception;10 Import java.util.uuid;11 import javax.servlet.servletexception;12 Import JAVAX.SERVLET.HTTP.HTTPSERVLET;13 Import javax.servlet.http.httpservletrequest;14 Import JAVAX.SERVLET.HTTP.HTTPSERVLETRESPONSE;15 Import me.gacl.util.datasourceutil;16/**18 * <p>ClassName: testservlet<p>19 * <p>description: <p>20 * <p>company: Guangzhou li di Network Technology Co., Ltd. <p> * @author Xu     DP22 * @version 1.0 V23 * @createTime 2014-11-5 PM 01:49:4924 */25 public class Testservlet extends HttpServlet {26 27 public void doget (HttpServletRequest request, httpservletresponse response) throws Servletexception, Ioex ception {String sql = "INSERT into Ld_user (Id,username,usercode,password) VALUES (?,?,?,?)";   Connection Connection = Datasourceutil.getconnection (); 31      PreparedStatement pstmt = null;32 try {pstmt = connection.preparestatement (sql); 34 Pstmt.setstring (1, Uuid.randomuuid (). toString ()); Pstmt.setstring (2, "aloof and pale Wolf"); Pstmt.setstrin         G (3, "GaCl"), PNs pstmt.setstring (4, "XDP"); int executeresult = Pstmt.executeupdate (); 39 } catch (SQLException e) {e.printstacktrace ();}finally{42 try {PS Tmt.close (); Connection.close (); catch (SQLException e) {E.printstack Trace ();}48}49}50, public void DoPost (HttpServletRequest request, HttpServletResponse R Esponse) throws Servletexception, IOException {this.doget (request, response); 54}55}

To access Testservlet, execute the Doget method to insert data into the database, LOG4JDBC log the SQL information as shown:

　　

As you can see, LOG4JDBC records the time the SQL was executed, the parameters at execution time, and the timing of the execution. This is very helpful for us to find SQL errors in development.

Go to "Use LOG4JDBC to record SQL information