• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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
0
princehyderabad
Asked:
princehyderabad
  • 13
  • 11
  • +1
1 Solution
 
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%>;    
                       <%                                                              
                       }
                  }
0
 
radarshCommented:
>> cbox = "checked='checked'";

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

________
radarsh
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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";
0
 
fargoCommented:
may i know which database you are using?

fargo
0
 
princehyderabadAuthor Commented:
Oracle
0
 
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


 
0
 
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 ???
0
 
fargoCommented:
No.

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

fargo
0
 
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
0
 
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
0
 
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

0
 
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=...>
0
 
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
0
 
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

0
 
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....
0
 
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


0
 
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.
0
 
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

0
 
princehyderabadAuthor Commented:
yes the above code worked. It displays Group and sub-group name.
0
 
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
0
 
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";
0
 
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
0
 
princehyderabadAuthor Commented:
Perfect !! Thanks fargo got it working ;o)

0
 
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
0
 
princehyderabadAuthor Commented:
Fargo can you please look into the above Q link. Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 13
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now