Logging SQL Statements in Clojure (for JDBC)

#clojure

#mysql

#rails

#log

#jdbc

#sql-statement

#slf4j

No matter if you use a connection pool (like c3p0) or raw JDBC, you can hook in a DriverSpy in, to support logging of SQL Statements (similar to Ruby on Rails).

Add to your project.clj:

:dependencies [ ...
[com.googlecode.log4jdbc/log4jdbc "1.2"]
... ]

Make sure to update/create a log4j.properties file in your resource path (depends on the settings of your project.clj)

# the appender used for the JDBC API layer call logging above, sql only
log4j.appender.sql=org.apache.log4j.ConsoleAppender
log4j.appender.sql.Target=System.out
log4j.appender.sql.layout=org.apache.log4j.PatternLayout
log4j.appender.sql.layout.ConversionPattern= \u001b[0;31m (SQL)\u001b[m %d{yyyy-MM-dd HH:mm:ss.SSS} \u001b[0;32m %m \u001b[m %n

# ==============================================================================
# JDBC API layer call logging :
# INFO shows logging, DEBUG also shows where in code the jdbc calls were made,
# setting DEBUG to true might cause minor slow-down in some environments.
# If you experience too much slowness, use INFO instead.

log4jdbc.drivers=com.mysql.jdbc.Driver

# Log all JDBC calls except for ResultSet calls
log4j.logger.jdbc.audit=FATAL,sql
log4j.additivity.jdbc.audit=false

# Log only JDBC calls to ResultSet objects
log4j.logger.jdbc.resultset=FATAL,sql
log4j.additivity.jdbc.resultset=false

# Log only the SQL that is executed.
log4j.logger.jdbc.sqlonly=FATAL,sql
log4j.additivity.jdbc.sqlonly=false

# Log timing information about the SQL that is executed.
log4j.logger.jdbc.sqltiming=INFO,sql
log4j.additivity.jdbc.sqltiming=false

# Log connection open/close events and connection number dump
log4j.logger.jdbc.connection=FATAL,sql
log4j.additivity.jdbc.connection=false

Modify your connection specification to match something like this:

(def connection-spec {:classname "net.sf.log4jdbc.DriverSpy"
 :connection-uri "jdbc:log4jdbc:mysql://localhost:3306/my-database"
...})

Be aware that this example specifies the database Driver in the log4j.properties file. Therefor, when you change mysql to something else, make sure to change the log4j.properties file as well, or simply remove this line in the log-settings (the DriverSpy will then try to autoload the driver.)