Dynamic data retrival from the database using jsp and java

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.

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.

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?

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.

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

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.

Trekky

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

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
}

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>

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.

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.

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.

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.
 
 
 

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

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.

Don't post e-mail IDs on question-pages.