How to creata database Schema using Hibernate

Question

After reading Hibernate: hbm2ddl.auto=update in production? some questions arose. First of all, the reason for I'm using Hibernate is to be database vendor independent (no need to write 10 versions of the "same" sql query, eg. tsql vs. sql).

My problem appears when it's time to create the database schemas (production environment). As far as I can see I have two alternatives.

1. hbm2dll = update
2. pure sql (ddl) scripts.

The first alternative is widely discussed in the thread above. The second alternative is bad because that means I'm back to my first problem: "Don't want to create sql statements that are database vendor dependent". (This statement could be false if "all" (atlast the databases Hibernate support) are implementing the DDL (The subset of SQL used for defining and examining the structure of a database.) equal).

Answer 1

http://stackoverflow.com/questions/737989/how-to-update-a-database-schema-without-losing-your-data-with-hibernate

Answer 2

Do all changes in development/staging mode and transfer and execute scripts in production manually (or automatically, but don't let hibernate run them). The scripts may need some tunning since hbm2ddl update does not cover all cases.

In fact I never let hibernate run ddl against any database. I use hbm2ddl to generate text:

org.hibernate.tool.hbm2ddl.SchemaExport --config=hibernate.cfg.xml --text --format --delimiter=;

org.hibernate.tool.hbm2ddl.SchemaUpdate --config=hibernate.cfg.xml --text --format --delimiter=;

Answer 3

I like Hibernate (a LOT), and I think it makes some incredibly good quality code, but I would no sooner turn it loose on a production database than I'd let a very well-mannered grizzly bear babysit. Everything can go great for a while, but the one incident where it goes bad is REALLY bad.

My suggestion would be in a test environment, have hibernate generate database schemas. Test those in a test environment. Then take those scripts to the production environment and run them. Note the specificity there; even if the tests succeed fantastically, I STILL wouldn't just allow Hibernate to go wild on the production server. Take the output of Hibernate schemagen, test it, and once it's validated, then deploy that to the production server.