Would it be possible to have hibernate log the actual SQL statement and any bind variables used when it catches a SQL Exception?

For example, I encountered the following error while testing the my code. In this case, it would be very help if I could see the bind variables together with the SQL statement without changing the log level to something other than "error".

java.sql.SQLException: ORA-01400: cannot insert NULL into ()
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at net.sf.hibernate.persister.NormalizedEntityPersister.insert(NormalizedEntityPersister.java:496)
at net.sf.hibernate.persister.NormalizedEntityPersister.insert(NormalizedEntityPersister.java:465)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:23)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2278)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2234)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2178)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:56)

You can use P6spy.

enable sql logging to see exactly what sql is being attempted by putting

in hibernate.properties

or

in hibernate.cfg.xml

to show the bind params add

into your log4j properties

Thanks for the answers. However, they are not exactly what I ws looking for.

Using "show_sql" will print all the sql statements that have been executed. The number can be huge in a busy production system. I'm only interested in the ones that actually caused SQLExceptions. And I'd like to see the bind variables as well.

You will have to speak to your jdbc driver provider for that one! I belive Hibernate utilizes batched sql calls so the specific failure would have to be reported by your db/jdbc driver.

log4j.logger.net.sf.hibernate.type=debug


which is exactly what:
log4j.logger.net.sf.hibernate.type=debug
does!