Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamic data retrival from the database using jsp and java

Posted on 2004-09-30
17
Medium Priority
?
3,207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +2
17 Comments
 
LVL 5

Expert Comment

by:TrekkyLeaper
ID: 12197155
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
ID: 12197159
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
ID: 12197193
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:TrekkyLeaper
ID: 12197195
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
ID: 12201099
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
ID: 12201599
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
ID: 12201620
Trekky

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

0
 
LVL 5

Expert Comment

by:TrekkyLeaper
ID: 12201905
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
 

Author Comment

by:vinodkaky
ID: 12202095
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
ID: 12202246
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
ID: 12202561
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
ID: 12203275
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:
Mayank S earned 1500 total points
ID: 12206487
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
ID: 12225013
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
ID: 12228860
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
ID: 12235886
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:Mayank S
ID: 12236821
Don't post e-mail IDs on question-pages.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
Working from home is a dream for many people who aren’t happy about getting up early, going to the office, and spending long hours at work. There are lots of benefits of remote work for employees.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

596 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