jQuery in Action, 3rd edition
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

Program Optimization

zohaib khan

Joined: Apr 13, 2009
Posts: 22

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
Stefan Evans

Joined: Jul 06, 2005
Posts: 1580
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

Joined: Jan 10, 2002
Posts: 63852

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] [About Bear] [Books by Bear]
    Jayesh A Lalwani
    Saloon Keeper

    Joined: Jan 17, 2008
    Posts: 2746

    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

    Joined: Jan 10, 2002
    Posts: 63852

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

    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.

    I agree. Here's the link: http://aspose.com/file-tools
    subject: Program Optimization
    It's not a secret anymore!