Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

nested sql queries using JSP - pls help

 
Chuan Ren
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Use PreparedStatement for the inner query
-------------
Sainudheen
 
Sudhakar Reddy
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic