Code to make short

hi experts,

I think I took longer approach when it can be done in simplified way. Can anyone shorter this code. its working fine with no error.


String m1 = "select DISTINCT menu_group from tmenus";
                  ResultSet rs6= stmt.executeQuery(m1);      
                          while (rs6.next())                   
                    {
                               String mg = rs6.getString("MENU_GROUP");
                               if (mg != null)
                               {
                               out.print("<br><b>"+mg+"</b>");      
                               }
                               String m2 = "SELECT MENU_NAME FROM tMENUS where MENU_GROUP='"+mg+"' Order By MENU_NAME";
                               ResultSet rs7= stmt2.executeQuery(m2);
                               
                               while (rs7.next())
                               {
                               String cbox=null;
                               String mn = rs7.getString("MENU_NAME");
                                     ResultSet rs8= stmt3.executeQuery(//DB Query checking USER=Assigned Menu);                                                                           
                                    while (rs8.next())
                                     {
                                    String checked = rs8.getString("MENU_NAME");
                                          if (checked.equals(mn))
                                          {
                                                cbox = "checked";
                                          }      
                                    }
                          %><BR><input type=checkbox name="<%=mn%>"  <% if (cbox != null) { out.print(cbox); } %> value="<%=mn%>"><%=mn%>;      
                          <%                                                                      
                          }
                    }

Basic idea is to display Group Menu Name, and under it display Menu Name with checkbox. Checkbox should be checked if its assigned to user.

Group Menu Name1
    [ ]Menu Name1
    [ ]Menu Name2
    [ ]Menu Name3
Group Menu Name2
    [ ]Menu Name11
    [ ]Menu Name22
    [ ]Menu Name33

thx,
PH
princehyderabadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rrzCommented:
All I could see are minor improvements.  

String m1 = "select DISTINCT menu_group from tmenus";
               ResultSet rs6= stmt.executeQuery(m1);    
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (mg != null)
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }
                           String m2 = "SELECT MENU_NAME FROM tMENUS where MENU_GROUP='"+mg+"' Order By MENU_NAME";
                           ResultSet rs7= stmt2.executeQuery(m2);
                           
                           while (rs7.next())
                           {
                           String cbox="";
                           String mn = rs7.getString("MENU_NAME");
                                ResultSet rs8= stmt3.executeQuery(//DB Query checking USER=Assigned Menu);                                                                
                              while (rs8.next())
                                {
                              String checked = rs8.getString("MENU_NAME");
                                   if (checked.equals(mn))
                                   {
                                        cbox = "checked='checked'";
                                   }    
                              }
                       %><BR><input type=checkbox name="<%=mn%>" + <%=cbox%> value="<%=mn%>"><%=mn%>;    
                       <%                                                              
                       }
                  }
radarshCommented:
>> cbox = "checked='checked'";

Is not required. Just cbox = "checked" should do as the princehyderabad had written.

________
radarsh
fargoCommented:
Hi,

In my opinion, instead of making three queries to get the content. It's more good to make it in database query level.

PROS: You need not to handle so many resultset making/closing and all the overhead.
CONS: Handling of the resultset returned from the main query need to be done programatically.

something like

query = "select menu_group, menu_name, assigned_menu from tmenus ORDER BY menu_group, menu_name" ;

I haven't included the how to check the assigned_menu in the above query, but this can be very well added by you.
Doing this, you can iterate over a single resultset and make the formatted output.

fargo
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

princehyderabadAuthor Commented:
>> query = "select menu_group, menu_name, assigned_menu from tmenus ORDER BY menu_group, menu_name" ;

There is no assigned_menu in tMenus table. That field is on other table thats why I have used other query in rs8.
FYI I'm writing what is query used in rs8:
      "SELECT MENU_NAME FROM TMENUS, TUSERACCESS WHERE TMENUS.MENU_ID = TUSERACCESS.MENU_ID AND USERID='"+user+"' ORDER BY MENU_NAME";
fargoCommented:
may i know which database you are using?

fargo
princehyderabadAuthor Commented:
Oracle
fargoCommented:
Hi,

The following query will give you complete resultset

SELECT t1.menu_group, t1.menu_name, DECODE(NVL(t2.menu_id,''),'','0','1') AS assigned FROM tmenus t1 LEFT OUTER JOIN
(SELECT MENU_NAME, MENU_ID FROM TUSERACCESS WHERE USERID='"+user+"' ORDER BY MENU_NAME) t2
ON t1.MENU_ID = t2.MENU_ID

fargo


 
princehyderabadAuthor Commented:
Sorry I'm confused doing few things at a time. The query you provided should be replace for rs8 ???
ie:
ResultSet rs8= stmt3.executeQuery(//DB Query checking USER=Assigned Menu);  

Correct ???
fargoCommented:
No.

It replaces all your three queries. And give you resultset you need to programatically seperate and make the menus.

fargo
princehyderabadAuthor Commented:
<%      
                  
                  String hoh = "SELECT t1.menu_group, t1.menu_name, DECODE(NVL(t2.menu_id,''),'','0','1') AS assigned FROM tmenus t1 LEFT OUTER JOIN
                                             (SELECT MENU_NAME, MENU_ID FROM TUSERACCESS WHERE USERID='"+user+"' ORDER BY MENU_NAME) t2
                                              ON t1.MENU_ID = t2.MENU_ID";

                  ResultSet rs6= stmt.executeQuery(hoh);      
                          while (rs6.next())                   
                    {
                               String mg = rs6.getString("MENU_GROUP");
                               if (mg != null)
                               {
                               out.print("<br><b>"+mg+"</b>");      
                               }                                                        
                               String mn = rs6.getString("MENU_NAME");                                                                    
                          %><BR>&nbsp;&nbsp;&nbsp;&nbsp;<input type=checkbox name="lock"  Value="<%=mn%>"><%=mn%>;      
                          <%                                                                      
                   }                  
                  %>

ERROR:
ORA-00904: "MENU_NAME": invalid identifier
fargoCommented:
Does tuseraccess table has menu_name column?

If not, replace it with this

....(SELECT MENU_ID FROM TUSERACCESS WHERE USERID='"+user+"' ) t2 ...

and first try to execute the query directly with some db client, to check the results.

fargo
princehyderabadAuthor Commented:
Okay now the Query is fine. But I'hv messed my display format, can you help there as well.

This is my current way:

ResultSet rs6= stmt.executeQuery(hoh);      
                          while (rs6.next())                   
                    {
                               String mg = rs6.getString("MENU_GROUP");
                               if (mg != null)
                               {
                               out.print("<br><b>"+mg+"</b>");      
                               }                                                              
                               String cbox="";
                               String mn = rs6.getString("MENU_NAME");                                                                    
                          %><BR><input type=checkbox name="lock"  Value="<%=mn%>"><%=mn%>;

with above code the it displaying like this:
MENU_GROP1
 []AA
MENU_GROP1
 []BB
MENU_GROP1
 []CC
MENU_GROP2
 []11
MENU_GROP2
 []22
MENU_GROP3
 []33

princehyderabadAuthor Commented:
Also can you hlep me to "check" checkbox if that menu_name already assigned to user. tuseraccess table records if the menu_name is assigned or not.
eg:
<input type=checkbox <%if assigned display "checked" %> name=.. value=...>
fargoCommented:
Hi,

ResultSet rs6= stmt.executeQuery(hoh);    
String tempMenuGroup="";
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (!mg.equals(tempMenuGroup))
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }                                                      

                           String cbox="";
                           String mn = rs6.getString("MENU_NAME");                                                          
                           out.print("<br>"+mn);            
                           tempMenuGroup = mg;
                    }

try this one, it doesn't print the checkbox at the moment. We will add it once, the above work.

P.S: Please check it fast, i am going to sleep.

fargo
fargoCommented:
ResultSet rs6= stmt.executeQuery(hoh);    
String tempMenuGroup="";
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (!mg.equals(tempMenuGroup))
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }                                                      

                           String cbox="";
                           String mn = rs6.getString("MENU_NAME");                                                          
                           String assigned = rs6.getString("assigned");                                                          
                           String checked="";
                           if(assigned.equals("1")){
                                    checked="checked='checked'";
                            }
                          %>
         <BR><input type=checkbox name="lock" <%= checked%> Value="<%=mn%>"><%=mn%>;                       <%
                                tempMenuGroup = mg;
                    }

The above one with checkbox.

fargo

princehyderabadAuthor Commented:
Sorry for late:
the 1st one is having error: 500 Servlet jspservlet: unable to service request: Servlet jspservlet threw the exception: java.lang.NullPointerException
the 2nd one is taking forever to display..... I think bcoz there is no field "assigned"
Bacially "MENU_ID" in tuseraccess table tells about the menu assignement.

By the way I hv to go now, will catch tomorrow....
fargoCommented:
Did you remove this section of my sql code from your's
........DECODE(NVL(t2.menu_id,''),'','0','1') AS assigned......

I agree that the menu_id in tuseraccess tells about the menu assignment and this is what we have handled in the above piece of code.
When you run the query in some db-client, what is the result set you are getting? Is the resultset is same as we expect or not?

fargo


princehyderabadAuthor Commented:
No, I didnt removed that part. I tried in DB-Client and the query is perfect that is what I wanted in result.
But while runing on web page it is stopping at the middle of page and when I tried to see log here is the errors:

#1 ServletException contained the following root cause exception:java.lang.NullPointerException

#2 Servlet jspservlet: unable to send error message to client: java.lang.IllegalStateException: Response has been committed.
java.lang.IllegalStateException: Response has been committed.
fargoCommented:
Hi,

with the first one, let's try the following:

ResultSet rs6= stmt.executeQuery(hoh);    
String tempMenuGroup="";
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (mg!=null && !mg.equals(tempMenuGroup))
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }                                                      

                           String mn = rs6.getString("MENU_NAME");                                                          
                           if(mn!=null){
                           out.print("<br>"+mn);            
                           }  
                           tempMenuGroup = mg;
                    }

well, i don't think there was any problem with the last one either. But let's try this.

Moreover, if you still get null pointer..check the logs and see the stack trace.

#2 --> Are you closing the resultset after use.

regards.
fargo

princehyderabadAuthor Commented:
yes the above code worked. It displays Group and sub-group name.
fargoCommented:
ResultSet rs6= stmt.executeQuery(hoh);    
String tempMenuGroup="";
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (mg!=null && !mg.equals(tempMenuGroup))
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }                                                      

                           String mn = rs6.getString("MENU_NAME");                                                          
                         
                           String checked="";
                           if(assigned!=null && assigned.equals("1")){
                                    checked="checked='checked'";
                            }
                           if(mn!=null){
                          %>
         <input type=checkbox name="lock" <%= checked%> Value="<%=mn%>"><%=mn%>;                        
                           <%
                           }  
                           tempMenuGroup = mg;
                    }

ok. this is the second one.

fargo
princehyderabadAuthor Commented:
hmmm...interesting error:

Undefined variable: assigned if(assigned!=null && assigned.equals("1")){ ^

FYI: my string is exactly like this:
 String hoh = "SELECT t1.menu_group, t1.menu_name, DECODE(NVL(t2.menu_id,''),'','0','1') AS assigned FROM tmenus t1 LEFT OUTER JOIN"
                                          +"\n    (SELECT MENU_NAME, MENU_ID FROM TUSERACCESS WHERE USERID='"+user+"' ORDER BY MENU_NAME) t2"
                                           +"\n   ON t1.MENU_ID = t2.MENU_ID";
fargoCommented:
ResultSet rs6= stmt.executeQuery(hoh);    
String tempMenuGroup="";
                      while (rs6.next())                
                  {
                           String mg = rs6.getString("MENU_GROUP");
                           if (mg!=null && !mg.equals(tempMenuGroup))
                           {
                           out.print("<br><b>"+mg+"</b>");    
                           }                                                      

                           String mn = rs6.getString("MENU_NAME");                                                          
                         
                           String checked="";

                           String assigned=rs6.getString("assigned");
                           if(assigned!=null && assigned.equals("1")){
                                    checked="checked='checked'";
                            }
                           if(mn!=null){
                          %>
         <input type=checkbox name="lock" <%= checked%> Value="<%=mn%>"><%=mn%>;                        
                           <%
                           }  
                           tempMenuGroup = mg;
                    }

fargo

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
princehyderabadAuthor Commented:
Perfect !! Thanks fargo got it working ;o)

princehyderabadAuthor Commented:
Fargo, I got 1 more simiplar question. I think u can do it easilly !!!

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_21785002.html
princehyderabadAuthor Commented:
Fargo can you please look into the above Q link. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.