3

I'm using Hibernate 4.3.5-Final for my Java Swing application and I do many UDPATE, INSERT and DELETE with it (in HQL or with Criteria).

Now, what I'm trying to do is to export a SQL script of all modifications done on my database but I don't know how to do that. This script need to contain only the modifications (and not the creation of the tables) and put them on a .sql file (exported file path will be chosen by the user)

Do you have any ideas that can solve my problem ?

Thank you in advance !

[Edit] : Some forums talk about p6spy, can it answer to my problem ?

Florentin Le Moal
  • 177
  • 8

1 Answers1

4

p6spy should help here.

In general, following should do the job for you:

  1. enable p6spy in your app (see official docs: http://p6spy.github.io/p6spy/)

afterwards you have basically 2 options:

  1. use provided: BatchFileLogger, enabling it via: appender=com.p6spy.engine.spy.appender.BatchFileLogger in spy.properties (it's however undocumented yet, see: https://github.com/p6spy/p6spy/issues/119)

OR

  1. implement custom com.p6spy.engine.spy.appender.MessageFormattingStrategy, that would be returning sql only (see: https://stackoverflow.com/a/23521623/1581069 for idea on implementation) and configure it in spy.properties via: logMessageFormat=FooFormat
  2. set sqlexpression to match the queries you need - restricting CREATE/ALTER/... TABLE/SEQUENCE/... (see official docs: http://p6spy.github.io/p6spy/2.0/configandusage.html)

still there are some tricky points, like:

Community
  • 1
  • 1
Peter Butkovic
  • 11,143
  • 10
  • 57
  • 81
  • Thank you for your help, so I'll try `p6spy`. Also, i forget a point, my application is "multi-dialect", that means I let the user choose between `Oracle SQL`, `MS SQL Server 2005` or `MS SQL Server 2005` (on the login screen). So how can I dynamically set the `realdriver` ? – Florentin Le Moal May 14 '14 at 13:07
  • well, it depends, if you're using jdbc4 drivers + would use jdbc url proxing (for Oracle, something like: `jdbc:p6spy:oracle:thin:@localhost:1521:XE`) => no need to configure `realdriver` at all. Still it depends if you use some app server,... feel free to ask for more details on: https://groups.google.com/forum/#!forum/p6spy-users – Peter Butkovic May 14 '14 at 13:11
  • btw. property: `realdriver` has been deprecated in favor of: `driverlist` (still some docs might be outdated) – Peter Butkovic May 14 '14 at 13:13
  • 1
    So I did as you suggested with your second solution (I created a class implementing the `MessageFormattingStrategy`). Now I will try to filter only `UDPATE`, `INSERT` and `DELETE` sqlexpression but I think the most of the work is done now ! Thank you a thousand times ! – Florentin Le Moal May 14 '14 at 14:40
  • 1
    I'm almost done with my problem. I've filtered my SQL `.log` with `filter=true` and `sqlexpression=^(update|insert|delete).*$` in the `spy.properties` but there is a little issue : values are not escaped. So if the user put a field to `This is a test '`, the script will be `update MYTABLE set field='This is a test '' where id=1;` and it won't work because of the unescaped `'`. Is there a solution ? – Florentin Le Moal May 15 '14 at 08:07
  • Seems like a bug in p6spy. Please file a [new one](https://github.com/p6spy/p6spy/issues), as related test has no such scenario considered yet: https://github.com/p6spy/p6spy/blob/master/src/test/java/com/p6spy/engine/spy/LoggedSQLValidTest.java – Peter Butkovic May 15 '14 at 09:01