Example of how MyBatis3 outputs SQL using log4j on the console, mybatis3log4j

Example of how MyBatis3 outputs SQL using log4j on the console, mybatis3log4j

Why do I need to output SQL statements on the console?

Of course, it is convenient for development and debugging.

If a database-related operation encounters a problem, we can quickly troubleshoot the problem based on the output SQL statement.

Output Information:

[Org. mybatis. spring. SqlSessionUtils]-Creating a new SqlSession
[Org. mybatis. spring. SqlSessionUtils]-SqlSession [org. apache. ibatis. session. defaults. defasqlsqlsession @ 33290f98] was not registered for synchronization because synchronization is not active
[Org. springframework. jdbc. datasource. performanceutils]-Fetching JDBC Connection from DataSource
[Org. mybatis. spring. transaction. SpringManagedTransaction]-JDBC Connection [jdbc: mysql: // rds.aliyuncs.com: 3306/yyyy? UseUnicode = true & characterEncoding = UTF-8 & zeroDateTimeBehavior = convertToNull, UserName = 223323@222.222.xxx.xxx, MySQL Connector Java] will not be managed by Spring
[Datenumber. pageSelect]-==> Preparing: SELECT x. id, DATE_FORMAT (x. 'date', '% Y % m % D') 'datestr', x. befor_num, x. after_num, x. physician_id, y. department_id, y. clinic_id, y. true_name, y. avatar, y. title, y. telephone FROM datenumber x right join physician y on x. physician_id = y. id AND (x. 'date'> =? AND x. 'date' <=? ) Where 1 = 1 AND y. clinic_id =? Order by x. Date ASC
[Datenumber. pageSelect]-=> Parameters: 2017-3-28 (String), 2017-4-4 (String), 1 (Long)
[Datenumber. pageSelect]-<= Total: 19

The output content is terrible. The database connection characters and username and password are all output. Be careful.

However, the SQL statement and parameters are output separately. If you want to copy them to the query tool for debugging, you have to enter the parameters yourself, which is troublesome.

My project environment

Spring 4.0.2 + Spring MVC 4.0.2 + MyBatis 3.2.6

Method 1: use standard log output

This method is relatively simple. You only need to configure related attributes in the Configuration file of MyBatis. You do not need to put another log4j. properties file.

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"     "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>   <settings>     <setting name="logImpl" value="STDOUT_LOGGING"/>   </settings> </configuration> 

Key statement:

<setting name="logImpl" value="STDOUT_LOGGING"/> 

Specify the logging implementation that MyBatis should use. If this setting does not exist, the logging implementation is automatically discovered.

Method 2: Use log4j to output logs

1. spring-mybatis.xml files do not need to be modified;

2. In mybatis. xml, specify log4j as the log implementation, which is not required for my actual test.

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE configuration PUBLIC "-// mybatis.org//DTD Config 3.0/EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name = "logImpl" value = "LOG4J"/> </ settings> </configuration> <! -- Test whether this file can be used. -->

Here, the value can be SLF4J, Apache Commons Logging, Log4J2, Log4J, and JDK logging (not verified except Log4J2 and Log4J) and will be searched in order.

3. You also need to configure web. xml

<listener>    <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>  </listener> 

Or use the following (not tested)

<listener>     <listener-class>org.apache.logging.log4j.web.Log4jServletContextListener</listener-class>   </listener> 

4. Finally, configure log4j. properties.

### Log4j configuration ###### when combined with Spring. specify the location of the file in xml, and add the listener ### define the output level and destination of log4j (the destination can be a custom name, which corresponds to the following) # [level], appenderName1, appenderName2 log4j. rootLogger = DEBUG, console, file # ----------------------------------- #1 Define the log output destination as log4j in the console. appender. console = org. apache. log4j. leleappender log4j. appender. console. target = System. out log4j. appender. console. threshold = DEBUG #### you can flexibly specify the log output format. The following line specifies the specific format #### % c: the category of the output log information, generally, it is the full name of the class # % m: the message specified in the output code, the specific log information # % n: Output A carriage return line break, for Windows, "/r/n" is used, for Unix, "/n" is used to output a new line of log information log4j. appender. console. layout = org. apache. log4j. patternLayout log4j. appender. console. layout. conversionPattern = [% c]-% m % n # ----------------------------------- #2 generate a new file log4j when the file size reaches the specified size. appender. file = org. apache. log4j. rollingFileAppender # log4j, the log file output directory. appender. file. file = log/tibet. log # defines the maximum file size log4j. appender. file. maxFileSize = 10 mb ### output log information ### lowest level log4j. appender. file. threshold = ERROR log4j. appender. file. layout = org. apache. log4j. patternLayout log4j. appender. file. layout. conversionPattern = [% p] [% d {yy-MM-dd}] [% c] % m % n # ----------------------------------- #3 druid log4j. logger. druid. SQL = INFO log4j. logger. druid. SQL. dataSource = info log4j. logger. druid. SQL. connection = info log4j. logger. druid. SQL. statement = info log4j. logger. druid. SQL. resultSet = info #4 mybatis displays part of the SQL statement log4j.logger.org. mybatis = DEBUG # log4j.logger.cn. tibet. cas. dao = DEBUG # log4j.logger.org. mybatis. common. jdbc. simpleDataSource = DEBUG # log4j.logger.org. mybatis. common. jdbc. scriptRunner = DEBUG # log4j.logger.org. mybatis. sqlmap. engine. impl. sqlMapClientDelegate = DEBUG # log4j. logger. java. SQL. connection = DEBUG log4j. logger. java. SQL = DEBUG log4j. logger. java. SQL. statement = DEBUG log4j. logger. java. SQL. resultSet = DEBUG log4j. logger. java. SQL. preparedStatement = DEBUG

Comparison of the two methods

1. The configuration of standard log output is simple, and the configuration of log4j log output is relatively complex;

2. Powerful log4j functions and fine-grained control;

Which log implementation is used when STDOUT_LOGGING is specified in the configuration file? Apache Commons Logging or JDK logging?

None. Actually it is System. out. pringln.

Update

Some friends reported that the SQL statement is not output after the second method is configured. Note the following in the log4j. properties file:

log4j.appender.console.Threshold=DEBUG 

Is it consistent with this example!

Please pay attention to the details. You can use the configuration provided in this example to get the correct output and then customize your personality.
In addition, according to my configuration above, a large amount of information other than SQL statements is output. A friend tries to output only the configuration methods of SQL statements, parameters, and results,

I would like to express my gratitude and share it with you:

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.