I'm developing an application that uses a Relational Database and I would like an opinion on a possible framework to use. There are two types of tables in the DB: 1. Catalog tables (fixed structure - eg, Document Type, Attribute, Attribute Type, User ...) 2. Data Tables (variable tables structure, variable column’s number, constructed automatically from the data entered in the catalog tables)
For example I have, among other, the two catalog tables
Document Type (IDDocType, DESCR)
Attribute (IDDocType, ID, DESCR, AttibType)
When I insert a new record in the catalog table "Document Type"
(IDDocType = 1, DESCR = 'INVOICES')
creates a new data table "TAB01_'INVOICES'," and when I insert a new record in the "Attribute"
creates a new column in the table "TAB01_'INVOICES'"
This way I can build an administrative application with which to define all the metadata and structure of "data tables" and then populate the data tables and display them in a graphical component table on which to search, set filters, etc. ...
1) Is there a framework (Java) to facilitate the development of this type of application metadata / catalog vs. data? Examples that may say what the catalog tables, which should be the relationship between them and how to create tables dti from these?
2) For browsing and searching / inserting the data tables I thought to use a component such as the JSF datatable. I wish, however, that this graphical component allows to use a table that is not mapped with an ORM (Hibernate, JPA ...) and then having the following features:
Display in tabular format
Sorting of columns
Settings filters directly on the columns
Automatically create a search/inserts/update form
Is there a component with these characteristics, for which you can specify at runtime on which the DB table you want to work?
Unless I'm mistaken, you are creating tables and columns programmatically using Java. I think this is a really bad idea. The database schema should be created using the database utility that comes with the database, not programmatically. Administrators know nothing about database schema creation and shouldn't be expected to. The database schema should hardly ever change once designed. Your java program should query the tables and populate them with data, not alter the database schema. There isn't even a need to get the metadata programmatically. Simple SQL calls should suffice. I think you should reconsider the entire project from scratch from first principles. Your current project approach appears impractical. I suggest reading up on database design first.
I suggest you use JSP/Servlets for your design if you haven't programmed in JSP/Servlets before. If you already have experience in JSP/Servlets, you can consider moving on to JSF or something else. See my response at: Re: Java questionaire app