CODEWITHSUNDEEP
javaspringhibernatehsqldb
Stmated
Stmated

Reputation: 495

Creating HSQL create table query from class

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:

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

Upvotes: 5

Views: 1211

Answers (4)

siebz0r
siebz0r

Reputation: 20329

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 ;-)

Upvotes: 2

Dipesh
Dipesh

Reputation: 305

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.

Upvotes: 4

Ian Roberts
Ian Roberts

Reputation: 122364

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.

Upvotes: 2

Janoz
Janoz

Reputation: 953

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)

Upvotes: 2

Related Questions