Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Program Optimization

 
zohaib khan
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1720
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64838
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
     
    Jayesh A Lalwani
    Rancher
    Posts: 2756
    32
    Eclipse IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Pie
    Posts: 64838
    86
    IntelliJ IDE Java jQuery Mac Mac OS X
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 22
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic