How to intercept the Hibernate generated SQL?

Question

For a security system which works as a big brother (like a watched mandatory access control), we have to intercept and handle all select statements hibernate is generating. We store the user, the timestamp and the sql select in a database to enable some profiling with other tools. The informations allow to determine what a user tried to look at. For the select statements the prepared properties are valuable. We need the complete SQL statement including all parameters.

Is there any listener or interceptor where we can join and handle all these things? The biggest outstanding problem so far is collecting of the statement parameters.

Thank you

Answer 1

You can use Interceptor.prepareSQL() (3.1+) to intercept the prepared statements.

I don't think you can get the actual parameters without going down in the abstraction layer. A possible solution would be to use a JDBC proxy driver (see P6Spy).

Hope that helps.

Answer 2

The actual parameter values happen to be made available (atleast to my knowledge), when the logging level of the org.hibernate package is set to DEBUG, and with the hibernate.show_sql property set. Use a JDBCAppender, if you want the logger output in the database itself.

Alternatively, you could take a look at the log4jdbc project, which claims the following:

In the logged output, for prepared statements, the bind arguments are automatically inserted into the SQL output. This greatly Improves readability and debugging for many cases.

If that is not suitable, you could investigate whether P6Spy can be used in your situation. On WebLogic Server, the equivalent functionality is achieved via the WebLogic JDBC Spy, which comes out of the box with the WebLogic JDBC drivers for certain databases. Both of these write to System.out and not to a database (unless I'm mistaken), so it might not be that useful.

Answer 3

mkyong claims that the p6spy.jar will display the hibernate queries with parameter values. Probably you can try this

http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-solution/