How to retrieve multiple row data from oracle table and show it in jsp page

[Harshal Gurav]

Currently I am working on one form development using Java. In JSP page I retrieved the data from table using F9 keys i.e for any input field variable available in JSP page, once I pressed the F9 key, all the related data is displayed in the popup window. Once I select particular item , item description would be displayed on page and related item code was used to store the particular item in database. In this form I used add new row as a button to add a new row (this is for the automatic row increment purposes) it work fine for adding the item into table/
Now in next task I have to retrieve all this data from particular table which is available in multiple row format in table . How can I retrieve all this data for particular dynamic rows.
Here with I have attached the JSP page which is showing adding multiple row in dynamically. Also I am attaching related servlet page used to retrieve the data .
Any suggestion is highly appreciated .




Thanks,
Harshal
[ October 07, 2008: Message edited by: Harshal Gurav ]

[Jeanne Boyarsky]

Harshal,
You know this forum doesn't support attachments, right? Please show the relevant code snippets (not the whole thing.) Also, please provide more information about where you are stuck. Do you have a working query yet?

[Harshal Gurav]

Hi Jeanne,
Thanks for your kind support.
The below JavaScript code is used to add row in the table when I want to add multiple row data into table for single entry no field.
I am unable to add java sript code here which used to add multiple rows for footer table.
When i am trying to add it, the post shows the error.


Then this data are send to the next Servlet for adding into two table.
My header portion data are added into one table which added only one row in table. while footer section data are added into the no of rows in another table dependent on No. of
Rows added into jsp page.
Here is an code for that logic.

 & lthtml>
ArrayList& ltString> mucode = new ArrayList& ltString>();
                            ArrayList& ltInteger> serials = new ArrayList& ltInteger>();
                            ArrayList& ltInteger> apxrate = new ArrayList& ltInteger>();
                            ArrayList& ltInteger> srname = new ArrayList& ltInteger>();
                            ArrayList& ltString> itcode = new ArrayList& ltString>();
                            ArrayList& ltString> itname = new ArrayList& ltString>();
                            ArrayList& ltInteger> iqnty = new ArrayList& ltInteger>();
                            ArrayList& ltString> iuom = new ArrayList& ltString>();
                            ArrayList& ltInteger> pqnty = new ArrayList& ltInteger>();
                            ArrayList& ltString> puom1 = new ArrayList& ltString>();
                           ArrayList& ltInteger> arate = new ArrayList& ltInteger>();
                            ArrayList& ltString> rdate = new ArrayList& ltString>();
                            ArrayList& ltString> bcs = new ArrayList& ltString>();
                            ArrayList& ltString> ccs = new ArrayList& ltString>();
                            ArrayList& ltString> remarkss = new ArrayList& ltString>();
                          
                            //ArrayList& ltInteger> qtyrecs = new ArrayList& ltInteger>();
                            //ArrayList& ltString> dors = new ArrayList& ltString>();
                            //ArrayList& ltString> remarks = new ArrayList& ltString>();
                 String entryn = request.getParameter("entryno");        
                    String rows = request.getParameter("rows");
                    out.println(rows);   
                    //String Entryno = request.getParameter("entryno");
                   // out.println(Entryno);
 
                  int entryno = 0,reqqty = 0,srno = 0,deprequest = 0,rowcount = 0;
 
                            if(!Entryno.equals("")){
                                    entryno = Integer.valueOf(Entryno);
                            }
                            if(!rows.equals("")){
                                    rowcount = Integer.valueOf(rows);
                            }
 

                           for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("srno"+i)!=null){
                                            serials.add(Integer.valueOf(request.getParameter("srno"+i).trim()));
                                            out.println(serials.size());
                                    }}
 
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("srno"+i)!=null){
                                            srname.add(Integer.valueOf(request.getParameter("srno"+i).trim()));
                                    }
                            out.println(srname.get(0));
                            }
                            for(int i=1;i& lt=rowcount;i++){
 
                                    if(request.getParameter("itmcd"+i)!=null){
                                            itcode.add(request.getParameter("itmcd"+i).trim());
 
                                    } //out.println(itcode.get(i));
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("itmnm"+i)!=null){
                                            itname.add(request.getParameter("itmnm"+i).trim());
                                    }//out.println(itname.get(i));
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("indentqty"+i)!=null){
                                            iqnty.add(Integer.valueOf(request.getParameter("indentqty"+i).trim()));
                                    }}
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("uom"+i)!=null){
                                            iuom.add(request.getParameter("uom"+i).trim());
                                    }//out.println(iuom.get(i));
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("mcode"+i)!=null){
                                            mucode.add(request.getParameter("mcode"+i).trim());
                                    }
                            }
                           for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("packqty"+i).equals("")){
                                      pqnty.add(0);
 
                                    }else
                                        {
                                        pqnty.add(Integer.valueOf(request.getParameter("packqty"+i).trim()));
                                    }
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("pack"+i)!=null){
                                            puom1.add(request.getParameter("pack"+i).trim());
                                   }else
                                        {
                                    puom1.add("");
                                    }
                            }  
                           for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("rate"+i).equals("")){                                      
                                        arate.add(0);
 
                                    }else
                                        {
                                    arate.add(Integer.valueOf(request.getParameter("rate"+i).trim()));    
                                    
                                    }
                           }  
                 /* for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("rate"+i)!=null){
                                            arate.add(Integer.valueOf(request.getParameter("rate"+i).trim()));
                                    }}*/
                    
                          for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("dor"+i)!=null){
                                            try{
                                                    rdate.add(dashdate.format(slashdate.parse(request.getParameter("dor"+i).trim())));
                                            }catch(ParseException p){p.printStackTrace();}
                                    }else
                                       { rdate.add("");}
                             }
                               for(int i=1;i& lt=rowcount;i++){
                             if(request.getParameter("bcc"+i)!=null){
                                            bcs.add(request.getParameter("bcc"+i).trim());
                                    }out.println(bcs.get(0));
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("ccc"+i)!=null){
                                            ccs.add(request.getParameter("ccc"+i).trim());
                                    }out.println(ccs.get(0));
                            }
                            for(int i=1;i& lt=rowcount;i++){
                                out.println("remark2");
                                    if(request.getParameter("remark2"+i)!=null){
                                            remarkss.add(request.getParameter("remark2"+i).trim());
                                    }out.println(remarkss.get(0));
                            }
 

 

                    ArrayList& ltString> Idate = new ArrayList& ltString>();
 
                    for(int i=1;i& lt=rowcount;i++){
                                    if(request.getParameter("dateindent"+i)!=null){
                                            try{
                                                    Idate.add(dashdate.format(dashdate.parse(request.getParameter("dateindent"+i).trim())));
                                            }catch(ParseException p){p.printStackTrace();}
                                    }
                            }
 

                String Rdate = dashdate.format(new java.util.Date());
                 String tdate = dashdate.format(new java.util.Date());     
                 // String Indentdate = dashdate.format(new java.util.Date());
               //  String ApprovedT1 = dashdate.format(new java.util.Date());
               //  String ApprovedT2 = dashdate.format(new java.util.Date());
 
                   // String ApprovedT1=" ";
                    //String ApprovedT2="";*/
                String ApprovedT1= dashdate.format(new java.util.Date());
               out.println (ApprovedT1);
                  String ApprovedT2=dashdate.format(new java.util.Date()); 
                   out.println(ApprovedT2);
 
                String Indentdate=(dashdate.format(slashdate.parse(request.getParameter("dateindent").trim())));
                   out.println(Indentdate);
 

 
                    String Cocode ="BML001";   
                    out.println(Cocode);
                    String Deptcode = request.getParameter("dept1"); 
                    out.println(Deptcode);
                    String Empcode = request.getParameter("emp"); 
                    out.println(Empcode);
                    String Refno =request.getParameter("rtype");  
                     out.println(Refno);
                    String Divcode = request.getParameter("todiv1"); 
                    out.println(Divcode);
                    String Usercode = "CIRIUS";     
                     String Whcode = request.getParameter("stor"); 
                    out.println(Whcode);
                    // String Itemgroupcode = request.getParameter(""); 
                     String Itemgroupcode ="120000";
                     out.println(Itemgroupcode);
                    String Supplytypecode = request.getParameter("stype"); 
                    out.println(Supplytypecode);
                    String Delcode = request.getParameter("deliverycode"); 
                    out.println(Delcode);
                    String Itemclass="WS";
                    out.println(Itemclass);
                    // String Itemclass = request.getParameter("iclass"); 
                   // out.println(Itemclass);
                    String unitcode = request.getParameter("uni");
                    out.println(unitcode);
                     String Todivcode = request.getParameter("todiv1");
                    out.println(Todivcode);
 

                    String Appxrate = request.getParameter("rate");
                    out.println(Appxrate);
 
                    String Srno = request.getParameter("srno");
                    out.println(Srno);                 
                /*    String Indqty = request.getParameter("indentqty");
                  out.println(Indqty);*/
                    
              String Itemcode = request.getParameter("itmcd");
                   out.println(Itemcode);
                   String Othersp = request.getParameter("remark1");
                    out.println(Othersp);
                    String Reqdt = request.getParameter("dor");
                    out.println(Reqdt);
                    String Munitcode = request.getParameter("mcode");
                    out.println(Munitcode);
                    String Packqty = request.getParameter("packqty");
                    out.println(Packqty);                
                    String Packuom = request.getParameter("pack");
                    out.println(Packuom);
                    String Remark2 = request.getParameter("remark2");
                    out.println(Remark2);
                    String BC = request.getParameter("bcc");
                    out.println(BC);
                    String CC = request.getParameter("ccc");
                    out.println(CC);
 
                    try{
                        
                        st=connection.createStatement();
                        connection.setAutoCommit(false);
                        String sql="INSERT INTO PTXNINDHDR(COCODE,DEPTCODE,EMPCODE,APPROVEDT1,APPROVEDT2,INDDT,ENTRYNO,REFNO,REMARKS,DIVCODE,USERCODE,WHCODE,ITEMGROUPCODE,SUPTYPECODE,DELCODE,UNITCODE,TODIVCODE,ITEMCLASS)VALUES('"+Cocode+"','"+Deptcode+"','"+Empcode+"','"+ApprovedT1+"','"+ApprovedT2+"','"+Indentdate+"',"+Entryno+",'"+Refno+"','"+Othersp+"','"+Divcode+"','"+Usercode+"','"+Whcode+"','"+Itemgroupcode+"','"+Supplytypecode+"','"+Delcode+"','"+unitcode+"','"+Todivcode+"','"+Itemclass+"')";
                        out.println(sql);
                        st.addBatch(sql);
                        for(int i=0;i& ltserials.size();i++){
                            out.println("Inside the Statement");
                            String query3="test query for u";
                            out.println(query3);
                           String queryx="Insert into PTXNINDDTL(APXRATE,ENTRYNO,BRKNO,INDQTY,ITEMCODE,OTHERSPFCS,MUNITCODE,PACKQTY,PACKUOM,REMARKS,DIMSUBGRPCODE,DIMCODE,REQDT)VALUES("+arate.get(i)+","+entryno+","+srname.get(i)+","+iqnty.get(i)+","+itcode.get(i)+",'"+Othersp+"','"+mucode.get(i)+"',"+pqnty.get(i)+",'"+puom1.get(i)+"','"+remarkss.get(i)+"','"+bcs.get(i)+"','"+ccs.get(i)+"','"+rdate.get(i)+"')";
                           out.println(queryx);
                            st.addBatch(queryx);
                         }
                       int[] result=st.executeBatch();
                       connection.commit();
                       for(int k=0;k& ltresult.length;k++)
                        {
                       out.println("rows updated by "+(k+1)+"insert sta:"+result[k]+"");
                       }   
               }    
                    catch(BatchUpdateException bue)
                            {
                    out.println("error1;"+bue+"");
                    }
                    catch(SQLException sql) 
                            {
                    out.println("error2;"+sql+"");
                    }
                    catch(Exception l)
                            {
                    out.println("error3;"+l+"");
                    }
 
& lt/html>
   

Now I looking for to retrieve this footer section data available in multiple rows from footer table and present it in jsp page .
I am finding difficulties in how to show this multiple row data for dynamic no of rows .i.e. variable no. of rows.
I have able to show the data in Header portions of page in this ways
here i am adding the part of code which shows the data from header part of table i.e from Header table

  & lthtml>
& lth2 align="center">& ltb>Indent Preparation& lt/b>& lt/h2>
    & ltdiv align="left">
        & lttable width="849" border="0" cellspacing="3" cellpadding="3" align="center">
            & lttr>
                & lttd >& ltdiv align="left">& ltb>Indent No.& lt/b>& lt/div>& lt/td>
                & lttd >& ltlabel>
                        & ltinput name="indentno" type="text" id="indentno" size="15" value="" />& ltinput type="hidden" name="no" id="no">
                & lt/label>& lt/td>
                & lttd >& ltdiv align="center">& ltstrong>Indent Date& lt/strong>& lt/div>& lt/td>
                & lttd >& ltlabel>
 
                        & ltdiv align="center">
                            & ltinput name="dateindent" type="text" id="dateindent"value="& lt%=date1%>"/>& ltinput type="hidden" name="no" id="no">
                        & lt/div>
                & lt/label>& lt/td>
                & lttd> & lt/td>
                & lttd>& ltdiv align="right">& ltstrong>Entry No.& lt/strong>& lt/div>& lt/td>
                 & lt%if(oper!=null && oper.equals("view") && hdrcode!=null && hdrdetails!=null){%>
        & lttd>& ltinput type="text" value="& lt%=hdrcode.get(3)%>" size="10">& lt/td>
& lt%}else{%>
          & lttd>& ltinput type="text" name="entryno" id="entryno" value="& lt%=entryNo%>"/>& lt/td>
                    & lt%}%>
                        & ltdiv align="right">& lt/div>
               
            & lt/tr>
            & lttr>
                & lttd>& ltb>Division& lt/b>& lt/td>
                & lt%if(oper!=null && oper.equals("view") && hdrcode!=null && hdrdetails!=null){%>
& lttd>& ltinput type="text" value="& lt%=hdrdetails.get(9)%>" size="20"& lt/td>
& lttd>& ltinput type="hidden" name="div1" id="div1" value='& lt%=hdrcode.get(10)%>'>& lt/td>
& lt%}else{%>
          & lttd>& ltinput type="text" name="div" id="div" />& lt/td>
          & lttd>& ltinput type="hidden" name="div1" id="div1" /> & lt/td>
          & lt%}%>
                & lttd> & lt/td>
                & lttd> & lt/td>
                & lttd>& ltdiv align="right">& ltstrong>Unit& lt/strong>& lt/div>& lt/td>
               & lt%if(oper!=null && oper.equals("view") && hdrcode!=null && hdrdetails!=null){%>
& lttd>& ltinput type="text" value="& lt%=hdrdetails.get(14)%>" size="20"& lt/td>
& lttd>& ltinput type="hidden" name="uni" id="uni" value='& lt%=hdrcode.get(12)%>'>& lt/td>
& lt%}else{%>
          & lttd>& ltinput type="text" name="unit" id="unit" />& lt/td>
          & lttd>& ltinput type="hidden" name="uni" id="uni" /> & lt/td>
          & lt%}%>
            & lt/tr>
            & lttr>
& lt/html>
  

Any suggestion on any above works is highly appreciated.
Thanks and regards
harshal