Solved

Dynamic data retrival from the database using jsp and java

Posted on 2004-09-30
17
2,803 Views
Last Modified: 2008-06-29
I have two sucessive dropdowns that are being populated from the database individually. Now I need to modify the dropdowns so that by selecting a value in the first dropdown should bring its relevant data in the second dropdown from the database in the same page.

For Eg:

Table A has User ID, User Name and List of Books owned by the user, the user name and List of Books are to be populated on the page and I have user id as parameter to be passed to the database. Database is oracle, and the coding should be in jsp and java files.  So if I change the user name from the list of users obtained from the database, it should dynamically give me the list of books owned by that user in the second dropdown without a submit button.

This is a urgent requirement please reply.

vinod
0
Comment
Question by:vinodkaky
  • 6
  • 6
  • 2
  • +2
17 Comments
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
I think the easiest way to do this would be to implement a short javascipt call for the onchange property on the first drop-down box. The javascript would do the following:

1. put the value of the drop-down box into the request or session
2. forward the user to a servlet which would grab the information from the database

The servlet would then grab the information from the database, place it in the request, and forward back to the JSP which will take the request information and place it in the appropriate place. I hope this makes sense.
0
 

Author Comment

by:vinodkaky
Comment Utility
Thanks for the information, but I am not comfortable with servlets, do you think a bean might help here or may be if you have a sample code that I can look at it should help me understand it much better.
0
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
I'm not very good at javascript, and I don't have any examples of that anymore. However, here's something you can do without servlets. First, just have the javascript refresh the page after adding the value of the first combo box to something like "user-id" in the request. At the top of your JSP, check to see if there's a value for  the "user-id" property. If there is, you can make your standard database calls to get all the information. That can then be stored in an array (let's call it books). So when you create your combo box with the books:

<select name="book_names">        <-- or strugs tag
<%
for( int i = 0; i < books.length; i++ ) {
%>
<option value="<%=books[i]%>"><%=books[i]%></option>
<%}%>
</select>

If you're feeling really fancy, you can assign the book list to a bean and access it like that using struts tags. Does this seem a little more do-able?
0
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
Oh. In the example above, make sure the books array is always initialized. If you do not have any data for it, make it a 0-length array or you'll have issues.
0
 
LVL 1

Expert Comment

by:helloexpert
Comment Utility
Have a 'onChange:submit()' in the first drop down.

Upon receiving the request in the new jsp page, check for the selected value in the first drop down, then using that query the oracle database (hope u r comfortable in writing jdbc queries)...fetch the result set and populate the second drop down along with the rest of the page..

0
 

Author Comment

by:vinodkaky
Comment Utility
I don't have an option to forward the selected value into a new jsp page, all the required process should take place in the same page.

Yes I can write JDBC queries, and I am able to populate both the values independant of each other.

0
 

Author Comment

by:vinodkaky
Comment Utility
Trekky

Can you tell me how to pass the database values into a jsp array using java ???????

0
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
So, I'm thinking it would look something like this:

String[] books;
if( request.getParameter( "user-id" ) != null ) {
   // create SQL statement
   ResultSet set = /*some statement execution call*/
   Vector temp = new Vector();     // you could just use this vector instead of creating an array from it as I will show below
   while( set.next() ) {
      temp.add( set.getString( /* the name or index of the column with the book titles */ ) );
   }
   books =  temp.toArray( books );    // not necessary if you decide to use the vector
   // clean up your result set and statements as needed
}
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:vinodkaky
Comment Utility
I have my database results stored in a vector like vBookTypeLOVlist, now I want to pass this to a array in javascript, can you tell me how to do that.

I am a bit confused with the code you sent earlier. I am adding my code that is in the jsp page which retrives the lov values from a java object here

<select name="cmbBookType" >
            <option value ="0">&nbsp;</option>
            <%  
            String[] strCoreType = "";
            Vector vBookTypeLOVlist = DbConnect.getBookTypeLOV();
            try{
            if(vBookTypeLOVlist!= null)
                  {
                  for(int j = 0 ; j < vBookTypeLOVlist.size();j++)
                     {
                        db= (oracle..bean.CorEligibility)vCoreBooksLOVlist.elementAt(j);
                        strCoreType = db.getMeaning();
            %>
             <option value = "j"><%=strBookType%></option>
0
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
I have a couple questions about your code. First, where does the selection of the first combo box get factored in? Second, is vBookTypeLOVlist what you want to display in the combo box?

Now that I have a better idea of what you're doing, the code I posted last does not seem as helpful. If you can answer these two questions I should be able to help more.
0
 

Author Comment

by:vinodkaky
Comment Utility
The first Box as of now is not being stored anywhere or sent or being posted anywhere.

Yes the booktypeLOV is what needs to be displayed in the second box. I am framing my question again.

There are two LOVS on a page, the selection on the first lov will alter the display items in the second lov, the items in the second lov can be retrived by the database and the first lov items can be hardcoded. I can redirect the page to the same page if required to submit, but there should not be a visible submit button.
0
 
LVL 5

Expert Comment

by:TrekkyLeaper
Comment Utility
Alright, I have asked some other Java experts to take a look at this question since I'm not doing a very good job. Here is what I think it should look like:


<form name="formname" action="this.jsp">

<select name="select1" onchange="formname.submit()">
<option value="a">a</option>
...
...
</select>

<select name="select2">
<%
if( request.getParameter( "select1" ) != null ) {

//begin your code from above -- you can get the value of the first drop-down box by calling 'request.getParameter( "select1" )'
          String[] strCoreType = "";
          Vector vBookTypeLOVlist = DbConnect.getBookTypeLOV();   //i think this is where you need the information from the other combo box?
          try{
          if(vBookTypeLOVlist!= null)
               {
               for(int j = 0 ; j < vBookTypeLOVlist.size();j++)
                  {
                    db= (oracle..bean.CorEligibility)vCoreBooksLOVlist.elementAt(j);
                    strCoreType = db.getMeaning();
          %>
           <option value = "<%=strCoreType%>"><%=strCoreType%></option>        <----- I think you meant strCoreType on this line?
<%} //end if
}// end for
} catch( Exception ex ) {
//handle exception
}%>
</select>
</form>


If this does not help you at all, you might want to wait for someone else to come look. I'm not sure I can do much better of a job of explaining it. Sorry.
0
 
LVL 30

Accepted Solution

by:
mayankeagle earned 500 total points
Comment Utility
Well, I didn't go through all the comments but I'll try to start this from scratch, as I would've done it.

Ideally, I'd never like to connect to a DB in a JSP. The JSP would forward the request to a Servlet and the data-base connectivity would be done by the Servlet itself or it would invoke an EJB to do it.

First off, catch the onChange event of the drop-down which you're populating with the users, e.g., call a Java-script function on this event.

<select name="userList" onChange = "javascript: fnOnChangeOfUserName ();">

In the Javascript function, submit the form:

function fnOnChangeOfUserName ()
{
  document.frmData.action = "/MyServlet?userId=" + document.frmData.userList.value ;
  document.frmData.submit () ;

} // end of fnOnChangeOfUserName ()

The Servlet 'MyServlet' would obtain the list of values for the 'userId' request-parameter and put it in a list (like a Vector or an ArrayList), and set it as an attribute of the request.

// get the connection
// shoot the query (to get the list of books for the 'userId' and obtain the result-set
// loop through the result-set and populate the Vector - I hope you know how to do this

request.setAttribute ( "BooksList", vList ) ; // 'vList' is the Vector

Forward the result to the same JSP again, using response.SendRedirect () or getServletConfig ().getServletContext ().getRequestDispatcher ( "/jspname.jsp" ).forward ( request, response ) ;

In the JSP, check for the request-attribute "BooksList". If the attribute is not null, then get the Vector, loop through it, and set the values for the options of the books drop-down.
0
 
LVL 21

Expert Comment

by:MogalManic
Comment Utility
Something like this:
<form name='reqForm' action='' method='get>
   ...
   <select name='cmbUserNames' onchange='reqForm.submit()'>
     <%...Code to populate this list...%>
   </select>
   ...
   <select name='cmbBookType'>
      <%...Code to populate this list OR empty list if agencyID is not selected...%>
   </select>

    <input type='submit' name='systemButton'
value='Submit' onclick='javascript:reqForm.action='jsp/processForm.jsp';return true;'/>
...
</form>

This page will submit the form when the user changes the 1st droplist.  The 2nd droplist will be empty because the 1st droplist has no default choice selected.  The action is '' so the page will be submitted back to the same page.  The second page will populate the 1st and seconds droplists again, but this time the second droplist will use the 1st droplist as a key to queryt the DB and populate the 2nd droplist.  When the user clicks the submit button the javascript changes the action to the new page and the form is submitted to the other page.

NOTE: the Onclick for the submit button should probably be moved to a javascript function so you can do validation as well.
 
 
 
0
 

Author Comment

by:vinodkaky
Comment Utility
MogalManic

Your answer is the kind of idea that I am looking for, can you explain it  a bit more. If you want you can mail me at kaky4u@yahoo.com.

Thanks for the Help
0
 
LVL 21

Expert Comment

by:MogalManic
Comment Utility
My recommendation is to do the db queries in a Java class and then forward to the servlet.  This will give you the opportunity to later implement caching if this is a frequenly accessed page.

The class would be something like this:

public class BookAction implements ActionInterface
{
   public boolean handleRequest(HttpServletRequest request, HttpServletResponse response, ServletContext context)
  {
     List users=getAllUsers();  //DB Query to retrieve userList
     request.setAttribute("users", users);

     final String curUserName=request.getParameter("cmbUserNames");
     if (curUserName!=null) {
         List bookTypes=getBookTypes(curUserName);  //DB Query to retrieve BookTypes for selected User
         request.setAttribute("bookTypes", bookTypes);
     }
     context.getRequestDispatcher("/jsp/bookChoice.jsp").forward(request, response);

     return true;    
  }
}

The JSP page just retrieves the "users" and "bookTypes" attributes and builds the <option> tags if the attributes are NOT null.
0
 
LVL 30

Expert Comment

by:mayankeagle
Comment Utility
Don't post e-mail IDs on question-pages.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
A procedure for exporting installed hotfix details of remote computers using powershell
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now