Creating HSQL create table query from class

Question

I have a much used project that I am working on currently updating. There are several places where this project can be installed, and in the future it is not certain what version is used where and to what version one might be updated to in the future. Right now they are all the same, though.

My problem stems from the fact that there might be many changes to the hibernate entity classes, and it must be easy to update to a newer version without any hassle, and no loss of database content. Just replace WAR and start and it should migrate itself.

To my knowledge Hibernate does no altering of tables unless hibernate.hbm2ddl.auto=create, but which actually throws away all the data?

So right now when the Spring context has fully loaded, it executes a bean that will migrate the database to the current version by going through all the changes from versionX to versionY (what version it previously was is saved in the database), and manually alter the table.

It's not much hassle doing a few hard-coded ALTER TABLE to add some columns, but when it comes to adding complete new tables, it feels silly to have to write all that...

So my question(s) is this:

• Is there any way to send an entity class and a dialect to Hibernate code somewhere, and get back a valid SQL query for creating a table?

• And even better, somehow create an SQL string for adding a column to a table, dialect-safe?

I hope this is not a silly question, and I have not missed something obvious when it comes to Hibernate...

Answer 1

have you tried

hibernate.hbm2ddl.auto=update

it retains all the database with the data and append only columns and tables you have changed in entity.

Answer 2

I don't think you'll be able to fully automate this. Hibernate has the hbm2ddl tool (available as an ant task or a maven plugin) to generate the required DDL statements from your hibernate configuration to create an empty database but I'm not aware of any tools that can do an automatic "diff" between two versions. In any case you're probably better off doing the diff carefully by hand, as only you know your object model well enough to be able to pick the right defaults for new properties of existing entities etc.

Once you have worked out your diffs you can use a tool like liquibase to manage them and handle actually applying the updates to a database at application start time.

Answer 3

Maybe you should try a different approach. In stead of generating an schema at runtime update, make one 'by hand' (could be based on a hibernate generated script though).

Store a version number in the database and create an update script for every next version. The only thing you have to do now is determine in which version the database currently is and sequentially run the necessary update scripts to get it to the current version.

To make it extra robust you can make a unit/integration test which runs every possible database update and checks the integrity of the resulting database.

I used this method for an application I build and it works flawlessly. An other example of an implementation of this pattern is Android. They have an upgrade method in their API

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)