How to retrieve data multiple row data from oracle database rable and show it on jsp

Question

guravharsha -2 Light Poster

15 Years Ago

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

jsp

JSP_page.txt (34.36 KB)

The attachment preview is chopped off after the first 10 KB. Please download the entire file.

<%-- 
        Document   : IndentPreparations
        Created on : Sep 23, 2008, 10:27:52 AM
        Author     : Administrator
    --%>

    <%@page contentType="text/html" pageEncoding="UTF-8"%>
    <%@ page language ="java"%>
    <%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*,java.text.*;" %>
    <%@ page session="true" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

    <html>  
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Bml</title>


            <SCRIPT LANGUAGE="JavaScript">
        <!--
        function add(){

            document.getElementById("operation").value="add";
            document.getElementById("form1").submit();
        } 
        function doSubmit(){
          
          var Idade= document.form1.dateindent.value; 
          var Unit = document.form1.unit.value; 
           var Division= document.form1.div.value;
           var Itemclass= document.form1.itmclass.value; 
          var Stores= document.form1.store.value; 
        var Reftype = document.form1.reftype.value; 
        var Employee = document.form1.employee.value; 
        var Department  = document.form1.dept.value; 
        var Supplytype = document.form1.suppliertype.value; 
         var Itemcode = document.form1.itmcd1.value; 
         var Itemname = document.form1.itmnm1.value; 
        var Uom = document.form1.uom1.value; 
        var Indentqnty= document.form1.indentqty1.value 
        var Bc= document.form1.bccode1.value;
       var Cc= document.form1.cccode1.value  
       
         if(Idade=='')
             {
                alert('Indent date is empty');
                return;
             }
             else if(Unit=='')
              {
               alert('Unit is empty');
                return;   
              } 
          else if(Division=='')
              {
               alert('Division is empty');
                return;   
              }
else if(Itemclass=='')
              {
               alert('Item Class is empty');
                return;   
              }
else if(Stores=='')
              {
               alert('stores is empty');
                return;   
              }
          else if(Reftype=='')
              {
               alert('RefType is empty');
                return;   
              }
              else if(Employee=='')
              {
               alert('Employee is empty');
                return;   
              }
              else if(Department=='')
              {
               alert('Department is empty');
                return;   
              }
              else if(Supplytype=='')
              {
               alert('Supplytype is empty');
                return;   
              }
              else if(Itemcode=='')
              {
               alert('Item code is empty');
                return;   
              }
              else if(Itemname=='')
              {
               alert('Itemname is empty');
                return;   
              }
              else if(Uom=='')
              {
               alert('Uom is empty');
                return;   
              }
              else if(Indentqnty=='')
              {
               alert('indent Quantity is empty');
                return;   
              }
              else if(Bc=='')
              {
               alert('Bc field is empty');
                return;   
              }
              else if(Cc=='')
              {
               alert('Cc field is empty');
                return;   
              }             
          document.getElementById("operation").value="save";
          document.getElementById("rows").value=i;
           document.getElementById("form1").submit();   
           
        }

        function modify(){

            document.getElementById("operation").value="modify";
            document.getElementById("form1").submit();
        }
        function delet(){

            document.getElementById("operation").value="delet";
            document.getElementById("form1").submit();
        }
        function view(){

            document.getElementById("operation").value="view";
            document.getElementById("entryno").value ="entryno";
            document.getElementById("form1").submit();
        }
        function print(){

            document.getElementById("operation").value="print";
            document.getElementById("form1").submit();
        }
        function dimension(){

            document.getElementById("operation").value="dimension";
            document.getElementById("form1").submit();
        }
        function exit(){

            document.getElementById("operation").value="exit";
            document.getElementById("form1").submit();
        }
        function query(){

            document.getElementById("operation").value="query";
            document.getElementById("form1").submit();
        }
        function po(){

            document.getElementById("operation").value="po";
            document.getElementById("form1").submit();
        }
        function ddelete(){

            document.getElementById("operation").value="ddelete";
            document.getElementById("form1").submit();
        }

        function requisition(){

            document.getElementById("operation").value="requisition";
            document.getElementById("form1").submit();
        }          




        -->
    </SCRIPT>
    <SCRIPT LANGUAGE="JavaScript" SRC="/BM/Stores/CalenderPopup.js"></SCRIPT>
    <script type="text/javascript">
        var cal19 = new CalendarPopup();
        cal19.showYearNavigation();
        cal19.showYearNavigationInput();
        var focuseditem;
        var i=1,j=1;
        function loaded()
        {
            document.getElementById("entryno").focus();
        }

        function trapKey()
        {
            var kCode = window.event.keyCode;
            if(kCode == 120)
            {
                var el = document.forms[0].elements;
                for(var i=0;i<el.length;i++)
                {
                    el[i].onfocus=function(){focuseditem=this.name}
                }

                if(focuseditem=="unit")
                {
                    getunit(focuseditem);
                }
                if(focuseditem=="itmclass")
                {
                    getItemclass(focuseditem);
                }
                if(focuseditem=="store")
                {
                    getStores(focuseditem);
                }
                if(focuseditem=="suppliertype")
                {
                    getSupplytypes(focuseditem);
                }

                if(focuseditem=="reftype")
                {
                    getReferancetypes(focuseditem);
                }
                if(focuseditem=="dept")
                {
                    getDepartments(focuseditem);
                }
                if(focuseditem=="employee")
                {
                    getEmployee(focuseditem);
                }
                if(focuseditem=="todiv")
                {
                    getTodiv(focuseditem);
                }
                if(focuseditem=="delivery")
                {
                    getDelivery(focuseditem);
                }
                if(focuseditem=="div")
                {
                    gediv(focuseditem); 
                }

                if(focuseditem!=null){
                            if(focuseditem.length>5){
                                     if('itmcd'==focuseditem.substring(0,5))
                                            {
                                                    var row = focuseditem.substring(5,focuseditem.length);
                                                    getitemcode(row);
                                            }
                            }
                 } 

                 if(focuseditem!=null){
                            if(focuseditem.length>6){
                                     if('bccode'==focuseditem.substring(0,6))
                                            {
                                                    var row6 = focuseditem.substring(6,focuseditem.length);

                                                    getbc(row6);
                                            }
                            }
                 } 

                if(focuseditem!=null){
                            if(focuseditem.length>6){
                                     if('cccode'==focuseditem.substring(0,6))
                                            {
                                                    var row4 = focuseditem.substring(6,focuseditem.length);
                                                    getcc(row4);
                                            }
                            }
                 } 

                  if(focuseditem!=null){
                            if(focuseditem.length>7){
                                     if('packuom'==focuseditem.substring(0,7))
                                            {
                                                    var row2 = focuseditem.substring(7,focuseditem.length);
                                                    getPuom(row2);
                                            }
                            }
                 } 

              /*  if(focuseditem=="packuom1")
                {

                    getPuom(focuseditem);
                }
                if(focuseditem=="bc1")
                {
                    getbc(focuseditem);
                }
                if(focuseditem=="cc1")
                {
                    getcc(focuseditem);
                }
               */  
            }
        }
        function getunit(item)
        {
            window.open("/BM/Stores/

Servlet.txt (33.85 KB)

The attachment preview is chopped off after the first 10 KB. Please download the entire file.

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.Stores;

import java.sql.*;
import javax.sql.*;
import java.text.*;
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 *
 * @author Administrator
 */
public class IndentPraparation extends HttpServlet {
   
    public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
    {
		doPost(request,response);
	}
    public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
    {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        
      try{
              SimpleDateFormat dashdate = new SimpleDateFormat("dd-MMM-yyyy");
                        SimpleDateFormat slashdate = new SimpleDateFormat("dd/MM/yyyy");
                        SimpleDateFormat databasedate = new SimpleDateFormat("yyyy-MM-dd");
             ArrayList<String> hdr = new ArrayList<String>();
              ArrayList<String> hdrdetails = new ArrayList<String>();
		ArrayList<String> dtl = new ArrayList<String>();
                ArrayList<String> Itemdetail = new ArrayList<String>();
                ArrayList<String> itemcode = new ArrayList<String>();
		ArrayList<String> errorarray = new ArrayList<String>();
		
		String entrynumber = "";
		String errorcode = "";
		int error = 0;
		String errorfield = "";
                String Entryno1="";
                 String Entryno = request.getParameter("entryno");
                  out.println(Entryno); 
                 String divcode="";
               String txtype="";
               String sutypecode="";
                 String deptcode="";
               String loccode="";
               //String Divcode="";
              String todivcode="";
               String empcode="";
              String itemgroupcode="";
                String whcode="";
                 String unitcode1="";
                 String divdesc=request.getParameter("div");
                 out.println(divdesc);
                 String itemdesc=request.getParameter("itmclass");
                 out.println(itemdesc);
                String suptypedesc=request.getParameter("suppliertype");
                out.println(suptypedesc);
                String deptdesc=request.getParameter("dept");
                out.println(deptdesc);
                String storedesc=request.getParameter("store");
                out.println(storedesc);
                 String todivdesc=request.getParameter("todiv");
                 out.println(todivdesc);
                String employeedesc=request.getParameter("employee");
                out.println(employeedesc);
                 String unitdesc=request.getParameter("unit");
                 out.println(unitdesc);
                 String diliverydesc=request.getParameter("delivery");
                 out.println(diliverydesc);
                 String reftypedesc=request.getParameter("reftype");
                 out.println(reftypedesc);
                 String Deptname=request.getParameter("dept");
                 out.println(Deptname);
               //  Entryno1 = request.getParameter("entryno");
                //   out.println(Entryno1);
             //out.println("new page");
    
    
    
    
    
                String date1="";
                String Eno = "";
                String Bname = request.getParameter("operation");
                out.println(Bname);
                Connection connection = null;
                Statement st = null;
                Statement stmt = null;
                Statement stm = null;
                //Statement st1=null;  
                ResultSet rs = null;
               ResultSet rs1 = null;
            /*   hdrdetails.add(unitdesc);
               hdrdetails.add(divdesc);
               hdrdetails.add(itemdesc);
               hdrdetails.add(suptypedesc);
               hdrdetails.add(deptdesc);
               hdrdetails.add(storedesc);
               hdrdetails.add(todivdesc);
               hdrdetails.add(employeedesc);
               hdrdetails.add(diliverydesc);
               hdrdetails.add(reftypedesc);
               hdrdetails.add(Deptname);*/
               
               
              /*   ResultSet rs2 = null;
                ResultSet rs3 = null;
                ResultSet rs4 = null;
                ResultSet rs5 = null;
                ResultSet rs6 = null;
                ResultSet rs7 = null;
                ResultSet rs8 = null;*/

                Class.forName("oracle.jdbc.OracleDriver");
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:bmltest", "system", "manager");
                st = connection.createStatement();
                if (Bname == null) {
                } else if (Bname.equals("add")) {

                     date1=new SimpleDateFormat("dd/MM/yyyy").format(new java.util.Date());
                    
                    try {
                        rs = st.executeQuery("select seqentryno.nextval as Eno from dual");
                        while (rs.next()) {
                            Eno = rs.getString("Eno");
                          out.println(Eno);
                        }

                    } finally {
                        if (rs != null) {
                            rs.close();
                            rs = null;
                        }
                    }
                    try {
                        rs1 = st.executeQuery("select seqentryno.nextval as Eno from dual");
                        while (rs1.next()) {
                            Eno = rs1.getString("Eno");
         
                        }

                    } finally {
                        if (rs1 != null) {
                            rs1.close();
                            rs1 = null;
                        }
                    }

                } else if (Bname.equals("save")) {
                    
                 
                  
                   String query1="select DIVCODE from CMSTDIV where DIVNAME='"+divdesc+"'";
                   out.println(query1);
					 ResultSet rst = stmt.executeQuery(query1);
					
						while(rst.next()){
							divcode =rst.getString(1);
                                                        out.println(divcode);
						
                 query1 = "select txntype from CMSTTXNTYPE where fieldname='ITEMCLASS' AND txnname='"+itemdesc+"'";
                  out.println(query1);
					ResultSet  rst9 = stmt.executeQuery(query1);
					if(rst9!=null){
						while(rst9.next()){
							txtype = rst9.getString(1);
                                                        out.println(txtype);
						}
					}
                            query1 = "select SUPTYPECODE from CMSTSUPTYPE where SUPTYPEDESC='"+suptypedesc+"'";
					ResultSet  rst1 = stmt.executeQuery(query1);
					if(rst1!=null){
						while(rst1.next()){
							deptcode = rst1.getString(1);
                                                        out.println(deptcode);
						}
					}
                       query1 = "select DEPTCODE from CMSTDEPT where DEPTNAME,='"+deptdesc+"'";
					ResultSet rst2 = stmt.executeQuery(query1);
					if(rst2!=null){
						while(rst2.next()){
							deptcode = rst2.getString(1);
                                                        out.println(deptcode);
						}
					}
                                        String query = "select LOCCODE from CMSTLOCMST where LOCNAME='"+diliverydesc+"'";
					ResultSet rst3 = stmt.executeQuery(query);
					if(rst3!=null){
						while(rst3.next()){
							loccode = rst3.getString(1);
                                                        out.println(loccode);
						}
					}
                  query1 = "select DIVCODE from CMSTDIV where DIVNAME='"+todivdesc+"'";
					ResultSet rst4 = stmt.executeQuery(query1);
					if(rst4!=null){
						while(rst4.next()){
							todivcode = rst4.getString(1);
                                                        out.println(todivcode);
						}
					}
                      query1 = "SELECT A1.EMPCODE WHERE A1.DEPTCODE=A2.DEPTCODE AND A2.DEPTNAME='"+Deptname+"' where A1.EMPNAME='"+employeedesc+"'";
					ResultSet rst5 = stmt.executeQuery(query1);
					if(rst5!=null){
						while(rst5.next()){
							empcode = rst5.getString(1);
                                                        out.println(empcode);
						}
					}
                           query1 = "select ITEMGROUPCODE from CMSTITEMGRP where ITEMGROUPNAME='"+reftypedesc+"'";
					ResultSet rst6 = stmt.executeQuery(query1);
					if(rst6!=null){
						while(rst6.next()){
							itemgroupcode = rst6.getString(1);
                                                        out.println(itemgroupcode);
						}
					}
                    query1 = "select WHCODE from CMSTWAREHOUSE where WHNAME='"+storedesc+"'";
					ResultSet rst7 = stmt.executeQuery(query1);
					if(rst7!=null){
						while(rst7.next()){
							whcode = rst7.getString(1);
                                                        out.println(whcode);
						}
					}
                        query1 = "select unitcode from cmstunit where UNITNAME='"+unitdesc+"'";
					ResultSet rst8 = stmt.executeQuery(query1);
					if(rst8!=null){
						while(rst8.next()){
							unitcode1 = rst8.getString(1);
                                                        out.println(unitcode1);
						}
					}
                                                


if(divcode.equals("")){
						errorarray.add("div");
						error = 1;
					}

    if(txtype.equals("")){
						errorarray.add("itmclass");
						error = 1;
					}

if(sutypecode.equals("")){
						errorarray.add("suppliertype");
						error = 1;
					}

if(deptcode.equals("")){
						errorarray.add("dept");
						error = 1;
					}

if(loccode.equals("")){
						errorarray.add("delivery");
						error = 1;

2 Contributors
3 Replies
2K Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by PoovenM

All 3 Replies

PoovenM 30 Junior Poster

15 Years Ago

I'm a little confused and that's probably because I'm not sure what a dynamic row is? I looked at your attachments which are quite long when opened...

I noticed you're using JavaScript. Is there a problem with using Java embedded code? The way that I've been taught to retrieve data from a database is to encapsulate the each row within a Java Bean to form an ArrayList of Beans where a Bean is just a class that has get and set methods. I can describe this method further but I'm not sure if this is what you're looking for?

Actually that leads me to my question to your question... could the above mentioned method not work for you?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

guravharsha -2 Light Poster · Answer 1 · 2008-10-08T18:32:21+00:00

I'm a little confused and that's probably because I'm not sure what a dynamic row is? I looked at your attachments which are quite long when opened...
I noticed you're using JavaScript. Is there a problem with using Java embedded code? The way that I've been taught to retrieve data from a database is to encapsulate the each row within a Java Bean to form an ArrayList of Beans where a Bean is just a class that has get and set methods. I can describe this method further but I'm not sure if this is what you're looking for?
Actually that leads me to my question to your question... could the above mentioned method not work for you?

Hi PoovenM;

can you more elaborate the above?
Thanks and Regards
harshal

PoovenM 30 Junior Poster · Answer 2 · 2008-10-15T18:05:00+00:00

Hi there, I'm not sure which part you wanted me to elaborate on? Suppose you ran an SQL query that retrieved row that had two columns, colA and ColB. You'd then create a Java Bean that abstracts this:

package packagename;
class TwoColBean
{
    String a, b;
    public TwoColBean(String a, String b)
    {
        this.a = a;
        this.b = b;
    }
    public getA()
    {
        return a;
    }
...
}

So the Bean class would just encapsulate the data of those two columns (assuming they were String objects) thus a since Bean stores a single row (since each row consists of two columns). You can use a Java Bean (just like any class) by using the JSP tag:
<jsp:useBean id = "dataBean" scope = "request" class = "packagename.TwoColBean" />

You should look up that if you're unfamiliar with the tag. Of course you'd only do this if you wanted to store data into your database using the bean. To output the data, you'd simply import the class:
<%@ page import = "packagename.TwoColBean" %>

Next you'd query the database (probably from another Bean that's responsible for process database requests) and process each row of the ResultSet and store the values into a List of TwoColBean:

public List getData() throws SQLException
	{
		List dataList = new ArrayList(); // create empty list

		// query database > getDAtaQuery is a PreparedStatment object
		ResultSet results = getDataQuery.executeQuery();

		// get rows from result and create a TwoColBean and add to list
		while ( results.next() ) {
			TwoColBean d = new TwoColBean();
			d.setId(results.getString( 1 ));
			d.setTimestamp(results.getString( 2 ));
			dataList.add(d);
		}

		// return list of "QuotationBeans" to calling jsp page
		return quotationList;
	}

So in your JSP you'd query your database (using a proper mechanism like a Bean) using the above method call and you'd get your list. Then to display the list:

List quoteList = databaseBean.getData();
            Iterator dListIterator = dList.iterator();
            TwoColBean d;

            while ( dListIterator.hasNext() ) {
            	d = (  TwoColBean) dListIterator.next();

         %> <%-- end scriptlet; insert fixed template data --%>
 
               <tr>
                  <td><%= d.getColA() %></td>
                  <td><%= d.getColB() %></td>
               </tr>

         <% // continue scriptlet

            } // end while

         %> <%-- end scriptlet --%>

Hope this makes sense and helps.

How to retrieve data multiple row data from oracle database rable and show it on jsp

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers