Getting SQL queries is particularly important to identify potential bottlenecks and understand what is doing an application.
We often use external tools like jamon, slf4j-jdbc and so on to get this information.
There are two main drawbacks:
- it often breaks a bit the configuration since you replace your driver by the library one and your jdbc url by another one (or at least your original one with some additional information)
- You need another library (find it, learn it etc…)
That’s why in OpenEJB and TomEE we added a property LogSql which simply logs all queries, no need of hard configuration, that’s just a boolean.
You simply have to set this property to true to get the SQL. The surprise? you even get bound parameter values (all libraries don’t it)!
Moreover it gives you the execution duration of the method corresponding to the query (execute* methods of javax.sql API).
Here is a sample configuration of a datasource for SQL logging:
<Resource type="DataSource"> JdbcDriver = com.mysql.Driver JdbcUrl = jdbc:mysql://localhost:3306/demo Username = demo Password = demo LogSql = true </Resource>
And here is a sample output (was done on an embedded database so it is pretty fast):
INFO - INSERT INTO User (id, email, fullname, password) VALUES (1, firstname.lastname@example.org, tomee, tomee) --> 0ms INFO - INSERT INTO User (id, email, fullname, password) VALUES (2, email@example.com, openejb, openejb) --> 0ms INFO - INSERT INTO Post (id, created, content, title, USER_ID) VALUES (51, 2012-08-15 22:52:23.785, java.io.StringReader@dc420b, TomEE, 1) --> 5ms INFO - INSERT INTO Post (id, created, content, title, USER_ID) VALUES (52, 2012-08-15 22:52:23.795, java.io.StringReader@16f8789, OpenEJB, 2) --> 2ms INFO - INSERT INTO Comment (id, author, content, post_id) VALUES (101, visitor, java.io.StringReader@b9e9a3, 51) --> 1ms