Hibernate: Is there a way to programatically create new tables that resemble an existing one?

Question

I have a web app that have many tables (each represents a POJO). I wrote mapping files for each class and then use Hibernate's SchemaExport to generate the tables in my database. Now I want to create 2 additional tables for each existing table that was created:

• User permission table - stores user permissions on the POJO specific to each field
  each column represents a field in the POJO, each row represents an user, each cell will have a value of "read", "write" etc. representing user's permission on the field
  
  
• Data history table - stores all data history with a version number
  this table will have all the columns that the POJO table has, and with 4 additional fields: data object version, transaction GUID (primary key), a datastamp and user performed this transaction.

I would like to be able to access these tables through Hibernate after they are created. So that I can easily add/remove/update entries in them.

---

My Question

Most of the columns on the additional tables will be the same as the POJO table. So I think it is probably better to somehow reference the POJO table instead of creating brand new tables. This way, if there is a new field added in the POJO table, these tables will have the changes automatically. But I don't seem to know how to do this. I thought maybe there's some way of:

• create hibernate mapping files that references the POJO table somehow
  e.g. in my new POJOPermission.hbm.xml file, somehow to specify, use the same fields as the POJO table, and add these new fields.
• write Java code to create tables in Hibernate
  e.g. I can use the java.lang.Class to return me a list of all the fields in the POJO and then iterate through these, somehow set these as column headers of my new table, and somehow call Hibernate to create these tables at runtime

Could someone please tell me how to do either of the above, or have a work around for this issue? I have a feeling that I might be thinking this in the wrong way....

Thank you!!!

[Edit - solution]

I ended up using XSLT to translate the orginal hbm.xml files into new ones, changing the field types etc. And specify the hibernate.cfg.xml file to include the newly generated ones. In the end just run schemaexport to generate all the java files together...

Answer 1

CREATE TABLE syntax is implementation specific. DB2 has CREATE TABLE LIKE , but you need to parse the catalog tables to retrieve the keys, foreign and domestic, then generate ALTER TABLE... to implement same. Other databases may have similar.

Answer 2

Using "select into" you can create a copy of the basic table structure. It only copies columns and data types, not constraints or indexes. If you only want the structure without data, just specify a where clause that returns no records.

select * into targettable from sourcetable where 1=0

Answer 3

If you want to access the user permissions and data history through Hibernate then I think you need to approach the problem by thinking about how you would represent these as POJOs. You can then produce mappings for storing them in the database.