5

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

Improve this question

4 Answers 4

Reset to default
4

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.

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

Comments

2
+200

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.

Improve this answer

3 Comments

Okay, I settled for this being the better answer. I still have reservations to liquibase itself as a dependency or fitting my Spring+Hibernate+Annotations, but it did a great job creating hardcoded SQL queries that I can use for my own, custom solution. Thanks.
Letting Hibernate do its own auto-update works well in simple cases such as adding new nullable columns, but it can't cope with more complex migrations such as renaming or removing columns, changing types, etc. Imagine a situation where you're adding a new column whose value needs to be computed based on values in existing columns (rather than just having a static default that works for all existing rows).
Ah, misunderstood the question, thought it was just about creating a working schema from nothing. Liquibase isn't the only option for ongoing schema migration management, though. Also it's a little funky in that you write your schema migrations in XML, not SQL. There's a good comparison chart on the homepage for another option, flyway: code.google.com/p/flyway
2

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)

Improve this answer

3 Comments

I guess I will take this as the accepted answer. Regretfully I feel odd giving the bounty since it's not really an answer to my question; and is the solution I was planning on falling back on if my question was indeed deemed impossible. But I guess it was the one closest matching with my own ideas of not bringing in additional dependencies and the like. Thanks
Seriously, you should take a good look at Liquibase and in particular its hibernate support...
I did; though I had some reservations against it when reading the documentation. Might just be that I did not read thoroughly enough, and not figuring out how to mix it well with my environment. Might come back with a revised acceptance, if things change.
2

Don't use Hibernate's ddl. It throws away your data if you want to migrate. I suggest you take a look at Liquibase. Liquibase is a database version control. It works using changesets. Each changeset can be created manually or you can let Liquibase read your Hibernate config and generate a changeset.

Liquibase can be started via Spring so it should fit right in with your project ;-)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.