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.