iBatis, spring, how to log the sql that is executed?

Question

I am using iBatis with spring framework. I want to log the sql that iBatis executes when I say something like

Employee e = (Employee) getSqlMapClientTemplate().queryForObject("emp_sql", emp);

The above line will look for "emp_sql" id in the ibatis sql file that I have. And then run the query corresponding to "emp_sql". I want to log this query.

I have the following log4j xml properties file.

<appender name="sqlLogAppender" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="file" value="/disk1/logs/sql.log"/>
    <param name="datePattern" value="'-'yyyy-MM-dd'.txt'"/>
    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%m %n"/>
    </layout>
   <filter class="org.apache.log4j.varia.LevelRangeFilter">
        <param name="LevelMin" value="DEBUG"/>
    </filter>
</appender>

<logger name="log4j.logger.com.ibatis">
    <level value="DEBUG"/>
    <appender-ref ref="sqlLogAppender"/>
</logger>


<logger name="log4j.logger.java.sql.Connection">
    <level value="DEBUG"/>
    <appender-ref ref="sqlLogAppender"/>
</logger>

<logger name="log4j.logger.java.sql.PreparedStatement">
    <level value="DEBUG"/>
    <appender-ref ref="sqlLogAppender"/>
</logger>

I still cannot get the sql that the ibatis executed. Is there something wrong with the configuration? Should I just say

<appender name="sqlLogAppender" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="file" value="/disk1/logs/sql.log"/>
    <param name="datePattern" value="'-'yyyy-MM-dd'.txt'"/>
    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%m %n"/>
    </layout>
   <filter class="org.apache.log4j.varia.LevelRangeFilter">
        <param name="LevelMin" value="DEBUG"/>
    </filter>
</appender>

<logger name="log4j.logger.java.sql">
    <level value="DEBUG"/>
    <appender-ref ref="sqlLogAppender"/>
</logger>

Do I have to use p6spy or something else? Or is there something that I can do in the log4j configuration to get the iBatis sql logs?

Solution 1

Add the following to your log4j configuration (uncomment what you want to see).

# SqlMap logging configuration.
#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
#log4j.logger.java.sql=DEBUG
#log4j.logger.java.sql.Connection=DEBUG
#log4j.logger.java.sql.Statement=DEBUG
#log4j.logger.java.sql.PreparedStatement=DEBUG
#log4j.logger.java.sql.ResultSet=DEBUG

Solution 2

Add this in your log4j.xml

<logger name="com.ibatis" additivity="false">
    <level value="debug"/>
    <appender-ref ref="APPENDER"/>
</logger>

Solution 3

If you are using Log4j as your logging framework you need to set mybatis to use log4j as its default logging tool. You can do this by setting it in the mybatis-config.xml like this,

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

Or if you are not using mybatis-config.xml and just annotations, then you want to use

org.apache.ibatis.logging.LogFactory.useLog4JLogging();

before invoking any other mybatis methods to set the default logging implementation. Read More...

Use this configuration in your log4j.properties,

# Global logging configuration
log4j.rootLogger=INFO, stdout

# MyBatis mapper interfaces logging configuration...
log4j.logger.com.sample.mappers=DEBUG

# SqlMap logging configuration.
log4j.logger.org.mybatis.spring=DEBUG
log4j.logger.org.apache.ibatis=DEBUG

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%p] %c - %m%n

If you are using log4j.xml configuration try this equivalent of the above,

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>

  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d [%p] %c{1} - %m%n"/>
    </layout>
  </appender>

  <logger name="org.mybatis.spring" additivity="false">
    <level value="debug"/>
    <appender-ref ref="STDOUT"/>
  </logger>

  <logger name="com.sample.mappers">
    <level value="debug"/>
    <appender-ref ref="STDOUT"/>
  </logger>

  <!-- Other custom 3rd party logger configs -->

  <root>
    <priority value ="debug" />
    <appender-ref ref="STDOUT" />
  </root>

</log4j:configuration>

Either use properties file or xml file to configure log4j as above and place it in your classpath for this to work correctly.

Solution 4

Add this in your log4j

<logger name="java.sql" additivity="false">
    <level value="debug" />
    <appender-ref ref="console" /> </logger>

This will print out the sql as well as the output results

View more solutions