Dear folks,
lets say I have an EJB application wich defines some entities and relations among them. I figured the DB schema can easily be created from the entities. So now the app works but after a while a need to change some relations, e.g. a one-to-one needs to become a one-to-many, can I upgrade my DB to do so WITHOUT losing the old data?
Of course you can! What you have to do exactly depends on many different factors: Which ORM do you use? Which EJB container? Which RDBMS?
Usually the following steps apply:
To get a more detailed (and helpful) answer, you'll have to give us a lot more details of the current situation, of what you want to achieve and what you're going to change.
In case of using Hibernate as JPA implementation you can pass property hibernate.hbm2ddl.auto. If you change schema manually you should think about setting it to "validate". Also you can try "update" which should take care of simple stuff without manual changes.
It sounds like you're letting the container autogenerate the schema from your entities so you don't have to do get down and dirty creating your own ORM (why not?) If so, there's a nifty tool at the Apache project called DdlUtils that you might find useful.
First backup the old database, then redeploy your updated app to generate an empty database with the new schema. Using DdlUtils, you can then connect to the two databases and have it generate the SQL necessary to migrate your old database to the new schema, preserving your data.
It works like a charm if you're just adding or deleting tables or columns. I don't know how well it works when you do something like change column names. You may need to hand edit the sql in that case.