File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JSP and the fly likes nested sql queries using JSP - pls help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "nested sql queries using JSP - pls help" Watch "nested sql queries using JSP - pls help" New topic

nested sql queries using JSP - pls help

Chuan Ren
Ranch Hand

Joined: Aug 04, 2003
Posts: 42
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_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.*" %>

ResultSet cset = CM.executeQuery(listCourse);
while ({
+ <%= 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
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Use PreparedStatement for the inner query
Sudhakar Reddy

Joined: Aug 28, 2003
Posts: 9
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.
I agree. Here's the link:
subject: nested sql queries using JSP - pls help
jQuery in Action, 3rd edition