aspose file tools*
The moose likes JSP and the fly likes Program Optimization Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "Program Optimization" Watch "Program Optimization" New topic
Author

Program Optimization

zohaib khan
Greenhorn

Joined: Apr 13, 2009
Posts: 22
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.







Any idea how to achieve this?

- Thanks
Zohaib.
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1016
The following query should be something similar to what you want for this simple two level structure.
However it won't handle further nesting (but then neither did your original example)


Two thoughts on this
1 - Is this SQL slow? Is there a performance problem?
My guess would be no. You're dealing with miniscule data here. Why waste time optimizing when there isn't going to be any benefit construed from it?
Your efforts would be spent better elsewhere.

2 - Is a database the appropriate storage for this data?
However, I would recommend you rethink your strategy here.
Why is this information being stored in a database?
Why not just store it as a JSON file which can represent the nesting much easier?

Looking at that database table it is nigh on impossible to see the relationships between menu items.
Having it in XML/JSON keeps it in a human readable and easily understandable format, without having to impose these artificial parent/child relationships via foreign keys.

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60057
    
  65

And of course:
  • Using Java code in a JSP is a poor practice
  • Doing SQL in a JSP is an even worse practice

  • See this JspFaq entry.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    Jayesh A Lalwani
    Bartender

    Joined: Jan 17, 2008
    Posts: 2054
        
      22

    If you are trying to store an hierarchiacal structure in database there is no good database agnostic way of doing it. However, Oracle has a special clause called CONNECT BY that helps you traverse a hierarchical structure. Ask Tom can explain it much better that I can. Don't know if you have Oracle, but there it is.
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 60057
        
      65

    If it has to be stored in a DB, it can always be store in a JSON blob. Best of both worlds.
    zohaib khan
    Greenhorn

    Joined: Apr 13, 2009
    Posts: 22
    Thanks Stefan Evans and other people for reply and support.

    I do not write SQL in JSP.I write package and stored procedure in oracle database.

    For forum example I have written sql in jsp.

    I have 2 solutions [1]. JQWidget jqxMenu built from JSON data [2]. Jquery-mega-drop-down-menu.1.3.3.

    Solution [2]. Mega menu. Do not offer JSON solution.

    I like JSON solution and it is faster and modular.

    However I am using both solutions.

    I am able create JSON data from database from single query and working fine.

    I will create required dynamic HTML using Stefan single query.

    Thanks once again.

    Zohaib.
     
     
    subject: Program Optimization
     
    Similar Threads
    (jdbc+oracle) urgent please!!
    Updating a specific entry
    (jdbc+jtable+oracle) urgent please!!
    insert checked checkbox into table
    Can we use two quries using prepared statement in one method