1 Reply Latest reply on Nov 9, 2012 7:00 PM by 939520

    [Java] Framework app with catalog/data tables

      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"

      (IDDocType = 1, ID = 1, DESCR = "Customer", AttibType= "STRING")

      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. ...

      3 questions/dubt:

      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?

      3) I have seen many graphical components in different languages and libraries (JSF, GWT, ZF, Javascript) that provide all the functionality listed above for a table mapped with JPA / Hibernate. Is it possibe to make such a mapping table of the DB created during the execution of the application (in my case with the trigger sgli entries in the catalog tables) on a JPA runtime to be able to use those features? I seem to have read that such a thing could be possible with libraries like CGLIB, JAVASSIST, ASM. Can you confirm this and possibly link me some example, if you know him?

      Thank you , Daniele
        • 1. Re: [Java] Framework app with catalog/data tables
          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