automatic column creation and hence query customization

As I understand it, your displayed table contains an unknown number of columns and an unknown number of rows. A person can add more columns and rows to the table and you want the database table to be dynamically increased in size both in the number of fields it has and the number of rows to store all that data.

Database tables should not programmatically be altered. I strongly advice against this (It could be done via some sql statement such as 'alter table' provided your database granted your JDBC permission to alter tables).

Here is a better solution (a database table that contains only two fields):

Table name:USER_TABLE

field1 name:intPERSON_ID

field1 name:intROW_NUMBER

field2 name:String COLUMN_DATA

For the first row of the table on the display, read in all the fields (say, 23 of them), then store thier values in COLUMN_DATA as a delimited string.

Example: if column data is:345456"hello world"12/31/2007

Store it in COLUMN_DATA field as: "345|456|hello world|12/31/2007

where the delimiter here is "|" (you must pick a delimiter that the user is not likely to type into a textfield). The PERSON_ID is the person_id of the person who called up the table (each person logged on can store his own table, retrieved via his person_id).

Then, when you read COLUMN_DATA back from the database , parse it via stringtokenizer using "|" as the delimiter into a String[] to populate the TABLE display

Example: Create the following support java functions:

public String[] getRow(int row_id, int person_id){

//this reads in row=id, parses COLUMN_DATA, puts the data into

//a string array and returns it

}

public void insertRow(String[] columnData, int person_id){

//this converts the array 'columnData into a string separated

//by the delimiter "|" and stores in in the database as a new record.

//(look up the max value of row_id in the database for this person_id,

// add one, and use that value for row_id to insert the new record).

}

public void deleteRow(int row_id, int person_id){

//delete a record for this person_id

}

pubic String[][] getAllRows(int personID){

//get all rows of data for this person as a string array, each item in the

string array is itself an array of the column values

(ie: String[rowNumber][fieldNumbert] )

}

public void updateRow(int row_id, String[] columnData, int person_id){

//replace the specified row with the new data in 'columnData'

}

**********

Note: The first row (row_num=0) in the database table for this personID will not be data, it will be the names of the columns that he wants to display as labels in the displayed table. He therefore can alter the names ( you will need the following function:

public void updateColumnNames(String[] columnNames, int personID){

//update row_num=0 for this personID with the new column names.

}

)

George123a at 2007-7-28 18:30:20 >