views:

376

answers:

3

Where can I find a generic editor (JSP using Oracle's ADF) for create, read, update, and delete on any table?

Example usage:

  1. User selects the name of a table.
  2. User then adds a new row, or updates/deletes an existing row.
  3. User saves the changes.

Foreign keys would appear as drop-down lists, and all others as af:inputText. (The user-friendliness of such an administrative function is not important.) This would allow code table data updates for any number of tables without having to write a new CRUD JSP each time.

Update #1

Oracle ADF 10g (not 11g), which prevents us from using ADF Faces RC.

Update #2

Shay Shmeltzer (and others) directed me to these resources:

A: 

You are using Oracle's ADF stuff. Does the ADF Faces functionality not do you want you want?

APC
The functionality is there to create nice user interfaces for specific tables and table structures. However, I'm looking for an editable generic table dump -- much like a spreadsheet -- for any table (typically for code table data). The component would look at the table's meta information and render the appropriate number of columns (named as the database column names). It would also look at the foreign key relations and provide drop-down lists where required.
Dave Jarvis
A: 

I built a program (in Oracle Forms) on these lines. They can get complicated very quickly (eg do you show dates as dates or with times, with MM or Month formats, let alone BLOBs and CLOBs etc.) Drop down lists sound great, until you try it on a foreign key to a table with 5,000 rows (or 40,000+ zip codes).

Once it is 'live' for 100 tables, you then try to get it working for the 101st, which has a large varchar which you want on multiple lines, or has a surrogate key column which is meaningless to the user without a join to the parent table. So you change it for that, and have to re-test for the 100 screens that worked before.

In short, it is generally a lot easier to churn out a specific screen for a table than make a practical generic one that is widely usable. Then you just have a reference table that lists the table name and the relevant maintenance screen, and an application that calls the screen once the user selects the table.

Gary
The drop-down list would have to use something like Google Suggest, but right now the longest list has ~50 items. CLOBs and BLOBs aren't an issue in our case -- code tables are just that, code tables without CLOBs or BLOBs (although they could be implemented with textareas and file submission input fields). The tables have, for the most part, a very similar structure.
Dave Jarvis
If you can massage the tables so they have the same structure (number of columns, column size, data types, which columns have FKs, column order...) you'd have more luck creating a semi-generic screen that copes with a standard table structure with different table/column names. Any more generic than that and it will be painful.
Gary
Unfortunately the tables cannot be made structurally similar.
Dave Jarvis
A: 

If you intend to create one yourself, then I suggest to take advantage of the MetaData classes in the JDBC API. You can for example make use of Connection#getMetaData() to get hold of an DatabaseMetaData instance which provides information about all catalogs/schemes/tables/procedures of the connected database. Then there's the ResultSet#getMetaData() which returns an ResultSetMetaData instance which provides specific information about the columns. This, in combination with a dynamically populated UIData component (e.g. h:dataTable), must help you a lot further with this.

BalusC
Getting the meta data isn't a problem. I have a backing bean that does this already.
Dave Jarvis