3 messages in com.mysql.lists.javaRe: Dependent SQL calls using the JDBC| From | Sent On | Attachments |
|---|---|---|
| Stuart G. Poss | 06 Jun 2000 16:22 | |
| Cris Perdue | 06 Jun 2000 21:40 | |
| LEBLANC, CLAUDE | 07 Jun 2000 05:49 |
| Subject: | Re: Dependent SQL calls using the JDBC |
|---|---|
| From: | LEBLANC, CLAUDE (clau...@bell.ca) |
| Date: | 06/07/2000 05:49:54 AM |
| List: | com.mysql.lists.java |
"Stuart G. Poss" wrote:
I have a simple servlet (using the org.gjt.mm.mysql driver) that creates a flat html table displaying many the many fields a mySQL table. However, some users tell me that the size of the table creates browser viewing problems. Consequently, I am interested in displaying an abbreviated table that permits the values of a key field to display the remaining data fields for records with that value only, essentially "drilling down" into the table.
In effect, I want to create a second HTML table (separate page) that effectively generates series of potentially "dependent" servlets that use the value of the hyperlinked field to populate the value for a WHERE clause. That is, the first call would be SELECT FIELD1, FIELD2 FROM TABLEX; and the second call would then be generated from hyperlinks within the second displayed table of the form SELECT FIELD3, FIELD4, ... FIELD50 FROM TABLEX WHERE FIELD1="value for that row of initial html table".
How can I create the second HTML table without actually creating multiple copies of the servlet code for each and every unique value of FIELD1?
You would need only 2 servlets to perform this. One for your first page and the second, called from your first page, that will receive the value of Field1 as a parameter.
When you display the result of your query in page one, you have to add a link to servlet2 on each row with the current values of the ResultSet.
ex:
This would be the output of servlet1
Provinces | Details
-------------------- Quebec | <a href=servlet2?province=Quebec>Go</a> Ontario | <a href=servlet2?province=Ontario>Go</a> Manitoba | <a href=servlet2?province=Manitoba>Go</a> Alberta | <a href=servlet2?province=Alberta>Go</a>
In servlet2 you then do something like :
... String lsProv = req.getParameter("Province"); String lsSelect = "SELECT clients WHERE province ='" + lsProv + "'"; ...
note: see also PreparedStatement in Java doc
Claude