JDBC Web Application advice

[bschmitt78]

Hello,

I am writing a web app with an interface to query a MySQL database. The interface has a drop down list of 8 tables to choose from.

The first action the user will take is to choose a table and click a "display table" button. After the user sees the table, they will be able to insert, modify or delete existing records in any of the eight tables.

I have already written a Queries class containing methods createConnection(), closeConnection(), public ResultSet insert(), selectAll(), ...etc. The issue I am facing is that since I have eight tables I need to figure out a way to dynamically present the user with an insert or update interface with the correct number and types of fields. I am able to output the records via selectAll() by looping through the ResultSet using getColumnName(i), etc, and presenting this to the user.

My specific questions are:

1) Since I won't know what table is chosen by the user, should I use Generics somehow in my methods and pass an arbitrary number of field parameters? For example, if I call the update() method, I will need to grab the selected index of the row and pass those values to a smaller popup window for updating.

2) Would xml work better in a situation like this?

I realize my question might be a little vague, and I can post my code later tonight when I have access to it, I was just looking at least for a hint or some advice on how to implement a dynamic interface rather than writing 8 overridden methods for update() for each table's parameters. The Select() and Delete() queries I know how to handle; I am simply passing in the table name and ID of the record, the when I call that method, it would look something like this:

ResultSet result = queries.update(request.getParameter("tableName").toString(), request.getParameter("ID")).

Again, I am not looking for someone to write this app for me, I just need some hints or advice from someone or people who have faced this situation before :)

Thanks in advance,

Ben

[bschmitt78]

I PROMISE, I will post my code tonight so you can see what I am referring to below! Been busy with work, school and other comittments. I am looking forward to building this app and getting it to work properly, but need some assistance it making it more dynamic and be able to act on multiple tables, selected by the user.

Thanks!
Ben

Hello,

I am writing a web app with an interface to query a MySQL database. The interface has a drop down list of 8 tables to choose from.

The first action the user will take is to choose a table and click a "display table" button. After the user sees the table, they will be able to insert, modify or delete existing records in any of the eight tables.

I have already written a Queries class containing methods createConnection(), closeConnection(), public ResultSet insert(), selectAll(), ...etc. The issue I am facing is that since I have eight tables I need to figure out a way to dynamically present the user with an insert or update interface with the correct number and types of fields. I am able to output the records via selectAll() by looping through the ResultSet using getColumnName(i), etc, and presenting this to the user.

My specific questions are:

1) Since I won't know what table is chosen by the user, should I use Generics somehow in my methods and pass an arbitrary number of field parameters? For example, if I call the update() method, I will need to grab the selected index of the row and pass those values to a smaller popup window for updating.

2) Would xml work better in a situation like this?

I realize my question might be a little vague, and I can post my code later tonight when I have access to it, I was just looking at least for a hint or some advice on how to implement a dynamic interface rather than writing 8 overridden methods for update() for each table's parameters. The Select() and Delete() queries I know how to handle; I am simply passing in the table name and ID of the record, the when I call that method, it would look something like this:

ResultSet result = queries.update(request.getParameter("tableName").toString(), request.getParameter("ID")).

Again, I am not looking for someone to write this app for me, I just need some hints or advice from someone or people who have faced this situation before :)

Thanks in advance,

Ben

[~s.o.s~]

Look into the DatabaseMetaData class of JDBC which can be retrieved using your existing Connection object. This object in turn can be used to retrieve all the tables of the database, their columns, constraints etc.

It might take a bit of digging around to use the API right but once done, you can write code which can manipulate any table irrespective of the number of columns it has.

[bschmitt78]

I will do that! I was going to post my code but since none of it has this class incorporated into it, I will try that first then post my results and questions if they arise (they probably will:)) Thanks SOS!!!

Ben