How to Enable Logging for SQL Statements When Using Jdbc

How to enable logging for SQL statements when using JDBC

If you are using Spring framework, then the datasource-proxy framework is very convenient. You can basically wrap around any DataSource and just add the logging behavior.

If you're using Java EE, then P6spy is a good alternative:

Behind the scenes, p6spy provides the statement interceptor at the Driver level, which is much more convenient for Java EE applications because the DataSource is provided by the application server.

Enabling MySQL general query log with JDBC

I ended up finding a workaround. I enable MySQL general query logging through Java by modifying MySQL global system variables at runtime with the following SQL queries.

SET GLOBAL log_output="FILE"
SET GLOBAL general_log_file="Path/File"
SET GLOBAL general_log='ON'

I recommend using forward slashes in the general_log_file path. I could not get backslashes to work, even in a Windows environment.

I disable general query logging at runtime with the following SQL query.

SET GLOBAL general_log='OFF'

How to log SQL statements and parameters with Spring Data JDBC

Spring Data JDBC uses a JdbcTemplate to execute all SQL statements.
So you really need to configure logging for that.

The statements itself are logged by the template itself with DEBUG level.
To see these you have to set the log level of org.springframework.jdbc.core.JdbcTemplate to DEBUG.

The arguments for the bind variables are logged by org.springframework.jdbc.core.StatementCreatorUtils on TRACE level.

How you configure those depends on your project setup.
You can use the configuration options of Log4J, Logback or whatever logging implementation you are using.
Or if you happen to use Spring Boot the following rows in the application.properties should do the trick.

logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE
logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

Or simply

logging.level.org.springframework.jdbc.core = TRACE

If you don't mind possibly getting some extra output.

How can I log SQL statements in Spring Boot?

Try using this in your properties file:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Spring Boot JDBC Template SQL Log

Try

log4j.category.org.springframework.jdbc.core = TRACE

The above statement will print SQL queries with inbound parameters as well.

Incase you need to log only the query use the following

log4j.category.org.springframework.jdbc.core = DEBUG

You can enable in your logback file with the following

<logger name="org.springframework.jdbc.core.JdbcTemplate">
  <level value="debug" />
</logger>

<logger name="org.springframework.jdbc.core.StatementCreatorUtils">
  <level value="debug" />
</logger>

Update : For Springboot 2.x , it would be

logging.level.org.springframework.jdbc.core=TRACE

Thanks zhuguowei!

How to enable sql queries console logging in spring JDBC

You should add this in the property file

logging.level.org.springframework.jdbc.core = TRACE

This will enable Spring Jdbc logs

How to enable oracle jdbc logging?

I can reproduce the problem using your properties file

.level=SEVERE oracle.jdbc.level=ALL
oracle.jdbc.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

I receive only

 Bad level value for property: .level

You must use this properies file (note the new line after SEVERE)

.level=SEVERE 
oracle.jdbc.level=ALL
oracle.jdbc.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

How can I log JDBC database queries using java.util.logging?

If you are using Hibernate following configurations can be done to get the sql queries printed.

How to print a query string with parameter values when using Hibernate

You can use http://sourceforge.net/projects/p6spy/ also and intercept all queries and log them to a file

JDBC logging to file

After much reading, this is how I got things working :

---

NOTE : Fore more information, read the Oracle Diagnosability in JDBC document

---

Properties prop = new Properties();
prop.put ("user", USER);
prop.put ("password", PASS);
// prop.put(propname, propValue);

Class.forName("oracle.jdbc.driver.OracleDriver");

enableLogging(false);

conn = DriverManager.getConnection("jdbc:oracle:thin:@"+HOST+":"+PORT+":"+SID, prop);

And here's the magic :

static private void enableLogging(boolean logDriver) 
throws MalformedObjectNameException, NullPointerException, 
       AttributeNotFoundException, InstanceNotFoundException, 
       MBeanException, ReflectionException, InvalidAttributeValueException, 
       SecurityException, FileNotFoundException, IOException 
{
    oracle.jdbc.driver.OracleLog.setTrace(true);

    // compute the ObjectName
    String loader = Thread.currentThread().getContextClassLoader().toString().replaceAll("[,=:\"]+", "");
    javax.management.ObjectName name = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosability,name="+loader);

    // get the MBean server
    javax.management.MBeanServer mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer();

    // find out if logging is enabled or not
    System.out.println("LoggingEnabled = " + mbs.getAttribute(name, "LoggingEnabled"));

    // enable logging
    mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", true));

    File propFile = new File("path/to/properties");
    LogManager logManager = LogManager.getLogManager();
    logManager.readConfiguration(new FileInputStream(propFile));

    if (logDriver) {
        DriverManager.setLogWriter(new PrintWriter(System.err));
    }
}

The properties file (from Oracle's documentation) :

.level=SEVERE
oracle.jdbc.level=INFO
oracle.jdbc.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=INFO
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

Basically, this is where the handlers are declared

oracle.jdbc.handlers=java.util.logging.ConsoleHandler

Declares the ConsoleHandler to be used by Oracle's JDBC driver. Any and any number of handlers can be declared here, one per line, with the class' full qualified name :

oracle.jdbc.handlers=java.util.logging.ConsoleHandler
oracle.jdbc.handlers=java.util.logging.FileHandler
...

One can provide their own custom made handlers with the same rule. The following lines are to setup the handler

java.util.logging.ConsoleHandler.level=INFO

will call the methode setLevel(Level.INFO) of the ConsoleHandler handler instance.

com.my.own.project.logging.handler.MyHandler.foo=Bar

will call the method setFoo("Bar") of the MyHandler handler instance. And that's it.

Happy logging!

---

Related Topics