Runtime Creating Tables

[Sanket L Shah]

Hello All,

In my application i need to create Table Dynamically. I mean user will create new table from user interface.

I am having two classes named : Table and Column

    
  public class Table{
    
   private String tableName;
   private List<Column> columns;
}

  public class Column{
    
   private String columnName;
   private Table table;
}  

I have created the proper mapping file for both and tested. And it works fine.

Now suppose, my Table class is having 2 columns object in columns (List<Column>). And each column is having Table object.

 // in some method
 Table table = new Table();
 table.setTableName("Student");
 
 Column column1 = new Column();
 column1.setColumnName("Column 1");
 column1.setTable(table);

 Column column2 = new Column();
 column2.setColumnName("Column 2");
 column2.setTable(table);
 
 table.getColumns().add(column1);
 table.getColumns().add(column2);
 /* Suppose I am having this table object at runtime. 
     Now I want to create Table (at runtime) into database as per the table object.
     My table object is having 2 Columns object too.
 */
 

I'm using MySQL.

How can i solve the above problem?

Please help me out!!!

[Paul Sturrock]

What ORM tool (if any) are you using?

[Sanket L Shah]

Paul Sturrock wrote:What ORM tool (if any) are you using?

Thanks Paul, I am using Hibernate.
I am using MySQL.

[Paul Sturrock]

Hibernate doesn't really support dynamic table creation. You can use hbm2ddl to generate DDL from mappings (or vice versa), but this is at SessionFactory creation time, not normal runtime. You can run native SQL to do your table creation but using that table at runtime is going to be fairly difficult to do. I think you could do it by performing your DDL via native SQL, running hmb2ddl to generate mapping files, compiling these dynamically then reconfiguring your SessionFactory with them. Sounds like a difficult (and dangerous - any user generated DDL is a security hole) thing to do.

[Sanket L Shah]

Hi Paul,

I think you understood it in the wrong way! What I mean is, when i create a table object with the name student, it should create new Database Table with the name Student and the Database Columns as defined in the columns List.

Thanks Paul!

[Paul Sturrock]

So you are not trying to create a table in a database? When you say "it should create a new database table with the name student..." etc. do you mean you want to add a new row to an existing table called Table and two Rows in a table called Columns?

[Sanket L Shah]

Hi Paul,

I want to create new Table with the name student and it will have two columns named column 1 and column 2. It will be a new physical table in the database.



Thanking you!

[Paul Sturrock]

Sanket L Shah wrote: Hi Paul,

I want to create new Table with the name student and it will have two columns named column 1 and column 2. It will be a new physical table in the database.



Thanking you!

OK. Then please refer to my earlier answer.

[Sanket L Shah]

Hi Paul,
Yes, I have read again your previous post, and I have come to know that it is very defficult create table dynamically.
But if I wirte native SQL code then if in future If client want to change the DB form MySQL to some other Database then it will create problems. Thanks for all your replies.

Thanks!

[Paul Sturrock]

So instead dynamically create your mapping file for the new entity and run hbm2ddl to generate the ddl (since this will give you database agnostic SQL). You'll still need to ditch your existing SessionFactory and reconfigure a new one to use the new table. Still seems like an awkward requirement though - how is anyone going to use this dynamically created table? Why does it need to be a seperate entity?