Dynamic Typed Table/Model in J2EE?

Question

Hi,

Usually with Java EE when we create Model, we define the fields and types of fields through XML or annotation before compilation time. Is there a way to change those in runtime? Or better, is it possible to create a new Model based on the user's input during the runtime? Such that the number of columns and types of fields are dynamic (determined at runtime)?

Help is much appreciated. Thank you.

I felt the need to clarify myself.

1. Yes, I meant database modeling, when talking about Model.

2. As for the use cases, I want to provide a means for users to define and create their own tables. Infinite flexibility is not required. However some degree of freedom has to be there: e.g. the users can define what fields are needed to describe their product.

Answer 1

You sound like you want to be able to change both objects and schema according to user input at runtime. This sounds like a chaotic recipe for disaster to me. I've never seen it done.

I have seen general schemas that incorporate foreign key relationships to generic tables of name/value pairs, but these tend to become infinitely flexible abstractions that can neither be easily understood nor get out of their own way when it comes to performance.

I'm betting that your users really don't want infinite flexibility. I'd caution you against taking this direction. Better to get your real use cases straight.

Anything is possible, of course. My direct experience tells me that it's a bad idea that your users will hate if you can pull it off. Best of luck.

Answer 2

This is somehow possible using meta-modeling techniques:

• tables for table / column / types at the database level
• key/value structures at the Java level

But this has obvious limitations (lack of strong typed objects) and can IMHO get quickly very complicated (not even sure how to deal with relations). I wouldn't use this approach to define domain objects entirely, but only to extend existing ones (products, articles, etc).

If I remember well, this is what some e-commerce solutions (e.g. BroadVision) were doing.

Answer 3

I worked on a system where we had such facilities. To stay efficient, we would generate/alter the table dynamically for the customer schema. We also needed to embed a meta-model (the model of the model) to process information in the entities dynamically.

Option 1: With custom tables, you have full flexibility, but it also increases the complexity significantly, notably the update/migration of existing data. Here is a list of things you will need to consider:

• What if the type of a column change?
• What if a column is added? Is there a default value?
• What if a column is removed? Can I discard the existing information?
• How to manage renaming of a column?
• How to make things portable across databases?
• How to make it efficient at database-level (e.g. indexes) ?
• How to manage a human error (e.g. user removes a column then changes its mind)?
• How to manage migration (script, deployment, etc.) when new version of the system is installed at customer site?
• How to have this while using an ORM?

Option 2: A lightweight alternative is to add a few "spare" columns in the business tables of different types (e.g.: "USER_DATE_1", "USER_DATE_2", etc.) I've seen that a few times. It will makes your DBA scream and is not really considered a good practice, but at least can facilitates a few things, e.g. (migration scripts, ORM integration).

Option 3: Another option is to store everything in a table with a structure property/data. But then it's really a disaster for database performance. Anything that is not completely trivial will require many joins. And the DBA will scream even more.

Option 4: It is a mix of options 2 and 3. Core tables are fixed, but a table with property/data can be used to somehow extend them.

In summary: think twice before you go this way. It can be done, but has a significant impact on the design and maintenance of the application.

Answer 4

I think I have found a good answer myself. Those new no-sql (hbase, cassandra) database seems to be exactly what I was looking for. Thanks everyone for your answeres.