Program Optimization
Hi,
I am developing HTML dynamically using looping over result set 2 times and using 2 sql query.
I want to get the same result using 1 sql query or any other way like using arraylist etc
Following is the sql and jsp code.
Code:
CREATE TABLE "SYSTEM"."JSON_DATA"
(
"ID" NUMBER NOT NULL ENABLE,
"PARENTID" NUMBER,
"TITLE" VARCHAR2(255 BYTE),
"HAS_CHILD" NUMBER
)
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (1,-1,'Home',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (2,-1,'Menu2',1);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (3,2,'Menu2 Child1',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (4,2,'Menu2 Child2',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (5,2,'Menu2 Child3',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (6,2,'Menu2 Child4',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (7,2,'Menu2 Child5',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (8,2,'Menu2 Child6',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (9,2,'Menu2 Child7',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (10,2,'Menu2 Child8',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (11,2,'Menu2 Child9',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (12,-1,'Menu3',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (13,12,'????? ???? ??????????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (14,12,'????? ???? ???????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (15,12,'?????? ??????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (16,12,'???????? ?????????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (17,-1,'Menu4',1);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (18,17,'???? ??????? ? ???? ??????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (19,17,'???? ???????? ??????',0);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (20,-1,'Menu5',1);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (25,-1,'Menu6',1);
Insert into JSON_DATA (ID,PARENTID,TITLE,HAS_CHILD) values (29,-1,'Menu7',1);
Code:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.util.Random.*"%>
<%@include file="connection.jsp" %>
<%
String data = "";
int id=2;
int parentid=0;
String title="";
int has_child=0;
String str="";
String supp="";
try
{
PreparedStatement stat= conn.prepareStatement("SELECT ID,PARENTID,TITLE,HAS_CHILD FROM JSON_DATA ORDER BY ID DESC");
rs = stat.executeQuery();
str = "";
int ObjectID=0;
while(rs.next())
{
supp = "";
id = rs.getInt(1);
parentid = rs.getInt(2);
title = rs.getString(3);
has_child = rs.getInt(4);
if (parentid == -1)
{
if(has_child==0)
{
str += "<li><a href='#'>"+title+"</a></li>";
}
if(has_child==1)
{
str += "<li><a href='#'>"+title+"</a>";
str += "<ul>";
try
{
PreparedStatement stat2= conn.prepareStatement("SELECT TITLE FROM JSON_DATA WHERE PARENTID="+id+" ORDER BY ID ");
rs2 = stat2.executeQuery();
while(rs2.next())
{
str += "<li><a href='#'>"+rs2.getString(1)+"</a></li>";
}
}
catch(Exception E)
{
out.println("Error "+E);
}
str += "</ul>";
str += "</li>";
}
}
}
}
catch(Exception E)
{
out.println("Error "+E);
}
finally
{
//rs.close();
//conn.close();
}
out.print(str);
%>
Any idea how to achieve this?
- Thanks
Zohaib. |
