Generate a SQL script from Hibernate

Question

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 ?

Community · Accepted Answer · 2017-05-23 12:22:23Z

4

p6spy should help here.

In general, following should do the job for you:

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

afterwards you have basically 2 options:

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

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
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:

databaseDialectDateFormat property (to be able to kind of replay the output without modifications). For inspiration for some of the common databases, see the unit tests of p6spy itself: https://github.com/p6spy/p6spy/tree/master/src/test/resources/com/p6spy/engine/spy

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered May 14, 2014 at 12:58

Peter Butkovic

12.3k10 gold badges62 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Florentin Le Moal Over a year ago

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 ?

Peter Butkovic Over a year ago

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: groups.google.com/forum/#!forum/p6spy-users

Peter Butkovic Over a year ago

btw. property: realdriver has been deprecated in favor of: driverlist (still some docs might be outdated)

Florentin Le Moal Over a year ago

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 Over a year ago

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 ?

|

Collectives™ on Stack Overflow

Generate a SQL script from Hibernate

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related