Dump Hibernate activity to sql script file

Question

Hi,

I'm trying to log hibernate activity (only dml operations) to an sql script file. My goal is to have a way to reconstruct the database from a given starting point to the current state by executing the generated script.

I can get the sql queries from log4j logs but they have more information than the raw sql queries and i would need to parse them and extract only the helpful statements.

So i'm looking for a programatic way, maybe by listening the persist/merge/delete operations and accessing the hibernate-generated sql statements.

I don't like to reinvent the wheel so, if anybody know a way for doing this i would appreciate it very much.

Thanks in advance

Answer 1

Generally the best way to do this is to just turn on logging on your SQL server. All the major RDBMSes support logging all the SQL statements that they run. This has the added advantage of catching things that happened outside of Hibernate.

Answer 2

You could also try to use NHProf which will intercept/record hibernate traffic to the database and dump it into an XML file. You might have to parse the file by hand, but all the information will be there.

Answer 3

You could also hook at the JDBC level directly and record the JDBC statements that are performed.

P6Spy is a great tool to inspect what's going on. It can log the queries, though I don't know if you can replay them as is.

I'm sure there are other such tool (or at worse you could try subclass the DataSource, Connection and PreparedStatement implementation of your choice to do that yourself).