aspose file tools*
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


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "nested sql queries using JSP - pls help" Watch "nested sql queries using JSP - pls help" New topic
Author

nested sql queries using JSP - pls help

Chuan Ren
Ranch Hand

Joined: Aug 04, 2003
Posts: 42
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
Ranch Hand

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

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.
Cheers
Sudhakar
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: nested sql queries using JSP - pls help