asked on

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

rrz

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%>;
<%
}
}

radarsh

>> cbox = "checked='checked'";

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

________
radarsh

fargo

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

princehyderabad

ASKER

>> 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";

fargo

may i know which database you are using?

fargo

princehyderabad

ASKER

Oracle

fargo

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

princehyderabad

ASKER

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 ???

fargo

No.

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

fargo

princehyderabad

ASKER

<%

                  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>    <input type=checkbox name="lock" Value="<%=mn%>"><%=mn%>;
                   <%
                   }
                  %>

ERROR:
ORA-00904: "MENU_NAME": invalid identifier

fargo

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

princehyderabad

ASKER

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

princehyderabad

ASKER

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

fargo

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

fargo

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

princehyderabad

ASKER

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

fargo

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

princehyderabad

ASKER

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.

fargo

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

princehyderabad

ASKER

yes the above code worked. It displays Group and sub-group name.

fargo

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

princehyderabad

ASKER

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";

ASKER CERTIFIED SOLUTION

fargo

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

princehyderabad

ASKER

Perfect !! Thanks fargo got it working ;o)

princehyderabad

ASKER

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

https://www.experts-exchange.com/questions/21785002/Query-Help-and-JSP-correction.html

princehyderabad

ASKER

Fargo can you please look into the above Q link. Thanks.