1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Program Optimization

Discussion in 'JSP' started by zohaib82, Nov 5, 2012.

  1. zohaib82

    zohaib82 New Member

    Joined:
    Aug 1, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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.
     

Share This Page