log4j2: Is there any way to display sql values in log4j2?

Question

Right now my info logs only show sql queries with ? for values. Can you suggest me changes to log4j2.xml file to display values?

log4j2.xml:

   <RollingRandomAccessFile name="SQLLOGFILE">
    <PatternLayout>
      <Pattern>%-5p:%d{dd-MMM-yyyy HH:mm:ss,SSS}: %-25t %c : %m%n</Pattern>
    </PatternLayout>
   </RollingRandomAccessFile>

     <AsyncLogger name="jdbc.sqlonly" level="INFO">
       <AppenderRef ref="LOGFILE" level="INFO"/>
     </AsyncLogger>

     <AsyncLogger name="jdbc.audit" level="off"></AsyncLogger>

     <AsyncLogger name="jdbc.resultset" level="off"></AsyncLogger>

     <AsyncLogger name="jdbc.connection" level="DEBUG">
       <AppenderRef ref="CONNECTION"/>
     </AsyncLogger> 

Answer 1

If your ORM vendor is Hiberante, which is by default in Spring framework you can use the following properties inside log4j2.xml

  <AsyncLogger name="org.hibernate.SQL" level="DEBUG">
      <AppenderRef ref="LOGFILE"/>
  </AsyncLogger>

  <AsyncLogger name="org.hibernate.type" level="TRACE">
      <AppenderRef ref="LOGFILE"/>
  </AsyncLogger>

Those 2 above properties include all necessary classes for the SQLs to appear correctly in your logs, including the binded values inside ? placeholders.

More specifically in hibernate the responsible class for placing values inside ? is the org.hibernate.type.descriptor.sql.BasicBinder. Those 2 above properties include this class as well to the Trace level, so the binded values for ? are correctly appended.

Answer 2

Consider using the datasource proxy TTDDYY that logs the quires already at more convenient way