nested sql queries using JSP - pls help

[Chuan Ren]

Hi!
I have two tables and would like to display the combined content of the two tables. I appreciate if you could assist me.
The two tables are:
course
------
course_id
course_name
session
-------
session_id
session_time
course_id (FK)

This is the output I would like to achieve. when click on the + , the related session belongs to a course will show.
+ course 1
- session 1
- session 2
+ course 2
- session 4
In the following code, I sort of pull everything out and thinking of using javascript to hide it (I have not done this one yet). When user click on it, it opens. I am not sure if its even possible to retrieve the data only when user click on the + and not preloaded.
This is a snapshot of my code:
The error message is:
"Operation not allowed after ResultSet closed"
-------------------------------------
<jsp:useBean class="com.db.ConnectionManager" id="CM" scope="session" />
<%@ page import ="java.util.*"%>
<%@ page import="java.sql.*" %>

<%
String listCourse = "SELECT * FROM COURSE ORDER BY COURSE_ID";
ResultSet cset = CM.executeQuery(listCourse);
while (cset.next()){
%>
+ <%= cset.getString("COURSE_NAME") %>
// I would like to pull out the session correspond to the course id
<%
String listSession = "SELECT * FROM SESSION WHERE SESSION.COURSE_ID = cset.getString("COURSE_ID"); <--- How do I put in the variable??
ResultSet sset = CM.executeQuery(listSession);
%>
<%= sset.getString("SESSION_ID") %>
<%= sset.getString("SESSION_TIME") %>

<%
} // end while
%>
your assistance is very much appreciated.

[Sainudheen Mydeen]

Hi
Use PreparedStatement for the inner query
-------------
Sainudheen

[Sudhakar Reddy]

Hi Chuan ,
Using SQL statement in JSP scriplets is not a good idea according to MVC architecture.
The best way to move all the SQL code to java bean and use it in JSP page.

You can combine those SQL statements in one using SQL inner join. e.g.
String listCourse = "SELECT * FROM COURSE,SESSION "
listCourse = listCourse + " WHERE course.course_id = session.course_id"
listCourse = listCourse + " and SESSION.COURSE_ID = "+ cset.getString("COURSE_ID");
listCourse = listCourse + " group by course_id"

I hope this will help you.
Cheers
Sudhakar