Java notes: Write Java EE Framework by yourself (III)-introduce SQL Monitoring Technology p6spy

I was so busy recently that I wrote last night that I fell asleep. Well, let's write the Java framework.
Logs and certain monitoring in any system are very important. maintenance during the entire life cycle of a software is always a big headache at the same time, log and monitoring provides a good foundation and means for later maintenance. In Java projects, most log4j is used to record system logs. Almost all Java engineers are familiar with this technology, I don't quite understand. You can check Baidu. Here I plan to introduce a framework that can monitor JDBC execution statements to the Java framework I wrote. This framework is very useful, and it is p6spy.

"If you optimize SQL statements and how to optimize the system," I think many programmers have heard that optimization and optimization are difficult technical skills, only with solid technical skills and years of project experience can he do well. I have encountered such a question before. The idea is very mechanical. It is based on SQL syntax, indexes, partitions, and algorithms. In fact, it is also important to optimize and optimize the program environment. For example, currently, many Java enterprise-level projects use the ORM technology and seldom directly use JDBC to operate databases, the encapsulation of JDBC by ORM technology makes the prototype of the final executed SQL statement farther and farther away from the programmer. Therefore, it is very important to control the original SQL statement executed by jbdc.

Here, I will share my experience. I now highly recommend ibatis. It not only implements the ORM idea, but also retains the use of SQL statements, instead of fully object-oriented ing of databases like hibernate, there are a lot of new things, such as hql. This is a simple design. I think the best solution for system design is compatibility between old and new systems. In fact, different programmers are good at different technologies. Java programmers are familiar with Java, and database developers are familiar with databases. If you develop a large background database with a large amount of data, for systems with complicated business operations, I will introduce the ibatis technology as the ORM layer here, because such a large system deals a lot with DBAs at the database level and optimizes SQL statements and databases, ibatis has obvious advantages in writing SQL statements directly. It is much easier for Java programmers to communicate with DBAs and interact with programs. I have recently developed several such systems. After the system is complete, I will extract important SQL statements from the system for DBA optimization, after DBA optimization, I only need to copy it to the program, which brings convenience to the whole work. In addition, if the system goes online, if an error is reported during database operations, extract SQL statements in the log for inspection. After inspection, the maintenance program is very convenient. Finally, embed SQL into the Orm, the changes in program architecture will cause a lot of problems. Therefore, ibatis will be the first choice for developing system ORM technology in the future.

However, ibatis uses preparestatement to execute JDBC, so the final printed SQL statement is in the following format:

2011-10-13 11:17:36  Connection - {conn-100000} Connection
2011-10-13 11:17:36  Connection - {conn-100000} Preparing Statement:    select t.username,t.password,t.enabled from users t   where         t.username = ?          
2011-10-13 11:17:36  PreparedStatement - {pstm-100001} Executing Statement:    select t.username,t.password,t.enabled from users t   where         t.username = ?          
2011-10-13 11:17:36  PreparedStatement - {pstm-100001} Parameters: [sharpxiajun]
2011-10-13 11:17:36  PreparedStatement - {pstm-100001} Types: [java.lang.String]

If we copy the SQL statement and rewrite it, it's really annoying. Then p6spy can solve this problem? Replace it with the parameter to see how it works after I add p6spy.

 

|statement| select t.username,t.password,t.enabled from users t where t.username = 'sharpxiajun' 

P6spy is easy to use. You only need to complete the following steps:
1. Put the p6spy. jar package in the path of the classpath of the application;
2. Modify the connection pool or JDBC driver configured for the connection to the driver provided by p6spy after the guarantee is obtained. com. p6spy. Engine. Spy. p6spydriver
3. Modify spy. properties and put it in the class search directory.

Now we have started development.

1. The new project structure is as follows:

Two new jar packages: log4j-1.2.12.jar and p6spy. Jar

2. set log4j. properties and spy. copy properties to the src directory (temporarily copy to the src directory and put it under the conf directory, because it is currently tested locally and not published to Tomcat, copy the two files to the default path)

The content of log4j. properties is as follows:

log4j.debug=true
log4j.rootLogger=INFO,CONSOLE,STDOUT


#-----CONSOLE-----
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss}  %c{1} - %m%n

#-----SQL LOG-----
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG


log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug

Logs are printed to the console. The configuration in SQL log indicates that jbdc and ibatis logs are printed.

The spy. properties content is as follows:

module.log=com.p6spy.engine.logging.P6LogFactory
realdriver=oracle.jdbc.driver.OracleDriver
deregisterdrivers=true
executionthreshold=
outagedetection=false
outagedetectioninterval=
filter=false
include     = 
exclude     =
sqlexpression = 
autoflush   = true
dateformat=
includecategories=
excludecategories=info,debug,result,batch
stringmatcher=
stacktrace=false
stacktraceclass=
reloadproperties=false
reloadpropertiesinterval=60
useprefix=false
appender=com.p6spy.engine.logging.appender.StdoutLogger
append=true
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.SimpleLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy
log4j.logger.p6spy=DEBUG,STDOUT

Modify realdriver = oracle. JDBC. Driver. oracledriver.

3. Next, we only need to change the database driver. Modify constants. properties as follows:

#db.driverClass = oracle.jdbc.driver.OracleDriver
db.driverClass = com.p6spy.engine.spy.P6SpyDriver
db.user        = sharpxiajun
db.password    = sharpxiajun
db.jdbcUrl     = jdbc:oracle:thin:@127.0.0.1:1521:orcl

#db.driverClass = com.mysql.jdbc.Driver
#db.user        = root
#db.password    = root
#db.jdbcUrl     = jdbc\:mysql\://localhost\:3306/sq_xidi?useUnicode\=true&characterEncoding\=utf-8

4. Finally, modify the users. xml configuration file so that the query method receives the parameters as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="USERS">
<select id="queryUserList" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select t.username,t.password,t.enabled from users t
<dynamic prepend="where">
<isNotEmpty prepend="and" property="username">
                t.username = #username#
</isNotEmpty>    
</dynamic>
</select>
</sqlMap>

The execution result is as follows:

Log4j: parsing for [root] with value = [info, console, stdout].
Log4j: Level Token is [info].
Log4j: Category root set to info
Log4j: parsing appender named "console ".
Log4j: parsing layout options for "console ".
Log4j: setting property [conversionpattern] to [% d {yyyy-mm-dd hh: mm: SS} % c {1}-% m % N].
Log4j: End of parsing for "console ".
Log4j: parsed "console" options.
Log4j: parsing appender named "stdout ".
Log4j: Error cocould not find value for key log4j. appender. stdout
Log4j: Error cocould not instantiate appender named "stdout ".
Log4j: parsing for [COM. ibatis. Common. JDBC. simpledatasource] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category com. ibatis. Common. JDBC. simpledatasource set to debug
Log4j: handling log4j.additivity.com. ibatis. Common. JDBC. simpledatasource = [null]
Log4j: parsing for [java. SQL. Connection] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category java. SQL. Connection set to debug
Log4j: handling log4j. additi.pdf. java. SQL. Connection = [null]
Log4j: parsing for [COM. ibatis] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category com. ibatis set to debug
Log4j: handling log4j.additivity.com. ibatis = [null]
Log4j: parsing for [java. SQL. Statement] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category java. SQL. Statement set to debug
Log4j: handling log4j. additi.pdf. java. SQL. Statement = [null]
Log4j: parsing for [COM. ibatis. sqlmap. Engine. impl. sqlmapclientdelegate] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category com. ibatis. sqlmap. Engine. impl. sqlmapclientdelegate set to debug
Log4j: handling log4j.additivity.com. ibatis. sqlmap. Engine. impl. sqlmapclientdelegate = [null]
Log4j: parsing for [COM. ibatis. Common. JDBC. scriptrunner] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category com. ibatis. Common. JDBC. scriptrunner set to debug
Log4j: handling log4j.additivity.com. ibatis. Common. JDBC. scriptrunner = [null]
Log4j: parsing for [java. SQL. preparedstatement] with value = [debug].
Log4j: Level Token is [debug].
Log4j: Category java. SQL. preparedstatement set to debug
Log4j: handling log4j. additi.pdf. java. SQL. preparedstatement = [null]
Log4j: finished processing ing.
Initialize the test class ....
2011-10-13 11:17:35 xmlbeandefinitionreader-loading XML bean definitions from class path resource [CONF/applicationcontext. xml]
11:17:35 genericapplicationcontext-refreshing org. springframework. Context. Support. genericapplicationcontext @ 2808b3: startup date [Thu Oct 13 11:17:35 CST 2011]; root of context hierarchy
2011-10-13 11:17:35 propertyplaceholderconfigurer-loading properties file from class path resource [CONF/constants. properties]
2011-10-13 11:17:35 defaultlistablebeanfactory-Pre-instantiating singletons in org. springframework. beans. factory. support. defaultlistablebeanfactory @ 1de17f4: defining beans [usersdao, userservice, org. springframework. context. annotation. internalconfigurationannotationprocessor, org. springframework. context. annotation. internalautowiredannotationprocessor, org. springframework. context. annotation. internalrequiredannotationprocessor, org. springframework. context. annotation. internalcommonannotationprocessor, propertyconfigurer, mydatasource, sqlmapclient, sqlmapclienttemplate, transactionmanager, methodserviceadvisor, org. springframework. AOP. config. internalautoproxycreator, baseservicemethods, org. springframework. AOP. support. defaultbeanfactorypointcutadvisor #0]; root of factory hierarchy
2011-10-13 11:17:35 mlog-mlog clients using log4j logging.
2011-10-13 11:17:35 c3p0registry-initializing c3p0-0.9.1.2 [built 21-may-2007 15:04:56; debug? True; trace: 10]
2011-10-13 11:17:36 abstractpoolbackeddatasource-initializing c3p0 pool... com. mchange. v2.c3p0. combopooleddatasource [acquireincrement-> 3, metrics-> 30, acquireretrydelay-> 1000, autocommitonclose-> false, automatictesttable-> null, metrics-> false, checkouttimeout-> 0, connectioncustomizerclassname-> null, connectiontesterclassname-> COM. mchange. v2.c3p0. impl. defaultconnectiontester, datasourcename-> 2wyjv28i1xq0ktewu9ral | 131303f, debugunreturnedconnectionstacktraces-> false, description-> null, driverclass-> COM. p6spy. engine. spy. p6spydriver, factoryclasslocation-> null, Token-> false, identitytoken-> token | 131303f, idleconnectiontestperiod-> 0, initialpoolsize-> 3, jdbcurl-> JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl, maxadministrativetasktime-> 0, maxconnectionage-> 0, maxidletime-> 0, maxidletimeexcessconnections-> 0, maxpoolsize-> 15, maxstatements-> 0, maxstatementsperconnection-> 0, minpoolsize-> 3, numhelperthreads-> 3, numthreadsawaitingcheckoutdefadefauser-> 0, preferredtestquery-> null, properties-> {user = ******, password = ******}, propertycycle-> 0, testconnectiononcheckin-> false, testconnectiononcheckout-> false, unreturnedconnectiontimeout-> 0, usestraditionalreflectiveproxies-> false]
2011-10-13 11:17:36 transactionaltestexecutionlistener-began transaction (1): Transaction Manager [org. springframework. JDBC. datasource. cetcetransactionmanager @ 6db33c]; rollback [false]
Test started ....
The method Interceptor ....
Called service:
Cn.com. sharpxiajun. Service. impl. usersserviceimpl @ 23bdd1
Call method:
Public abstract java. util. List cn.com. sharpxiajun. Service. usersservice. queryuserslist (Java. util. Map) throws java. Lang. Exception
The parameter is:
{Username = sharpxiajun}
11:17:36 connection-{Conn-100000} connection
11:17:36 connection-{Conn-100000} Preparing statement: Select T. username, T. Password, T. enabled from users t where T. Username =?
11:17:36 preparedstatement-{pstm-100001} executing statement: Select T. username, T. Password, T. enabled from users t where T. Username =?
11:17:36 preparedstatement-{pstm-100001} parameters: [sharpxiajun]
11:17:36 preparedstatement-{pstm-100001} types: [java. Lang. String]
| Statement | select T. username, T. Password, T. enabled from users t where T. Username = 'sharpxiajun'
The returned result is:
[]
The Interceptor is finished !!
[]
Test ended !!
| Commit |
2011-10-13 11:17:36 transactionaltestexecutionlistener-committed transaction after test execution for test context [[testcontext @ 1fac852 testclass = usersserviceimpltest, locations = array <string> ['classpath: CONF/applicationcontext. XML '], testinstance = cn.com. sharpxiajun. junittest. service. usersserviceimpltest @ 1758cd1, testmethod = testqueryuserlist @ usersserviceimpltest, testexception = [null]
11:17:36 genericapplicationcontext-closing org. springframework. Context. Support. genericapplicationcontext @ 2808b3: startup date [Thu Oct 13 11:17:35 CST 2011]; root of context hierarchy
2011-10-13 11:17:36 defaultlistablebeanfactory-destroying singletons in org. springframework. beans. factory. support. defaultlistablebeanfactory @ 1de17f4: defining beans [usersdao, userservice, org. springframework. context. annotation. internalconfigurationannotationprocessor, org. springframework. context. annotation. internalautowiredannotationprocessor, org. springframework. context. annotation. internalrequiredannotationprocessor, org. springframework. context. annotation. internalcommonannotationprocessor, propertyconfigurer, mydatasource, sqlmapclient, sqlmapclienttemplate, transactionmanager, methodserviceadvisor, org. springframework. AOP. config. internalautoproxycreator, baseservicemethods, org. springframework. AOP. support. defaultbeanfactorypointcutadvisor #0]; root of factory hierarchy

We can see this sentence. | statement | select T. username, T. Password, T. enabled from users t where T. Username = 'sharpxiajun', OK, it's done.

Summary: The DAO and service of the Java framework have been completed. The next article in this series provides a detailed explanation of the technical points of the first three articles, to improve the performance of a program, you must know what it means. However, in the following blog, I will return to JavaScript to reinforce the basic knowledge that I feel hard to understand in Javascript learning. I will continue to study jquery later. This is my focus.