MySQL query to fill HTML combo box?

[Bryan Scarbrough]

I am developing a Web App and I need to fill an HTML combo box with the data from a mysql query. How can I do this?

Do I need to fill an Array with the data from the query? If so then how do I iterate over that array in JSP to fill in the Combo Box values?

Also, I want to make the system set the currently displayed value of this combo box to the value that applies to the current user.

Is all of this possible? If so, how? I have been researching for a while and connot figure it out.

Any help?

Side Note: I am making the query in a JavaBean and do not wish to use JSP scriptlets.

[Tom Blough]

Bryan,

One method is to create an inner class to act as a data storage object for a row of data. Since it's an inner class, you can get away with making the fields public. Then, after your database query iterate through the rowset and store the values in your DSO. Collect the DSOs in a collection of some kind, vector if you don't need to search them later, HashMap or LinkedHashMap if you need to search and order is not/is important respectively.

Depending on the scope of your query, you might consider putting this all in the bean constructor, or even a static block so the database isn't being called all the time.

Then, add a method to your bean like getHTMLList that returns a String, and build the Select and Option statements by iterating your collection within your bean.

Cheers,

[Bryan Scarbrough]

Thanks for your reply.

I do not wish to sound like an idiot, but what is a dso, where do I get one and what do I do with it once I have it?

Anyway, I returned the query from the Bean and then tried a small scriptlet in my JSP page and I recieved an error stating that I cannot insert a scriptlet.

Scripting elements ( <%!, <jsp :mrgreen: eclaration, <%=, <jsp:expression, <%, <jsp:scriptlet ) are disallowed here

What could cause this error??

I returned the raw ResultSet to the JSP page and then used a while loop to iterate over the result set and populate the drop down list.

Is this a bad method? What would be a better way?

[Bear Bibeault]

Since this seems to be a lot more about the JSP aspects of things, moved to the JSP forum.

[Bear Bibeault]

It sound like you are having less trouble fetching the data from the DB, and your difficulty is in dealing with the values on the page.

Remember that the option elements of a select box have two pieces of information associated with them: a value that is submitted as the selection and a display string.

You did not mention whether the display string and the value are one and the same. Since they frequently are, I'm going to make that assumption for the moment for simplicity's sake.

So, assuming that you have gathered your string values from your DB query and put them into a List (could be an ArrayList or any other collection that implements List), you would place that collection object onto the request as a scoped variable (via request.setAttribute()). Let's say you called this variable optionsList.

On the page, you would use something like:

<select name="whatever">
  <c:forEach items="${optionsList}" var="option">
    <option value="<c:out value="${option}"/>"><c:out value="${option}"/></option>
  <c:forEach>
</select>
 

Under JSP 2.0, you could drop the <c:out> tags in favor of unadorned EL if the string don't have an characters that need to be encoded.

If your options value and display string are not the same, then what you would place in the List would be a small bean that defined the two values. The on-page code would need a minor adjustment to reference the two values of the bean.

[Bryan Scarbrough]

Thanks for the replies!

You are right Bear, I am having the most problems in the JSP portion of this app. Thanks for noticing.

I guess the only question I have now is how do I

place that collection object onto the request as a scoped variable (via request.setAttribute()).

Is this done in the JSP page or in the Bean?

Also, the value and display string are different. Do I place these in a HashMap or Array of some sort in the Bean so that the jstl tag can iterate over it or can I return a raw ResultSet?

Thanks again!

[Ben Souther]

In Bear's example the list was iterated over in the JSP.
Another approach is to do all the processing in the bean and return the options list as a property.

In this case the data is in an array.

    private static final String[][] MONTHS = new String[][]{
        {"1",  "January"},
        {"2",  "Feburary"},
        {"3",  "March"},
        {"4",  "April"},
        {"5",  "May"},
        {"6",  "June"},
        {"7",  "July"},
        {"8",  "August"},
        {"9",  "September"},
        {"10", "October"},
        {"11", "November"},
        {"12", "December"},
        {"0",  "All"}};
 
    public String getMonthsAsOptions(){
        final int INDEX = 0;
        final int NAME  = 1;
                                                                                
        StringBuffer sb = new StringBuffer();
        for(int i = 0; i < MONTHS.length; i++){
            sb.append("<option value=\"");
            sb.append(MONTHS[i][INDEX]);
            sb.append("\"");
            if(this.month != -1 && String.valueOf(this.month).equals(MONTHS[i][INDEX]))
                sb.append(" selected");
            sb.append(">");
            sb.append(MONTHS[i][NAME]);
            sb.append("</option>\n");
        }
        return sb.toString();
    }
                                                                                

The values can then be retrieved from your JSP as you would any bean property.

 
<select name="month">
  ${monthsBean.monthsAsOptions}
</select>
 
or for older versions..
<select name="month">
  <jsp:getProperty name="monthsBean" property="monthsAsOptions" />
</select>

Now, bear in mind (or should I say Bear in mind), some would rather sacrifice their first born child than generate a line of HTML from Java code. I would rather build a small, limited amount of HTML, like an option set, in the bean than have to resort to looping and branching in my view tier.

[Bryan Scarbrough]

Sweet! This is exactly what I was looking for! Thanks guys I am excited about trying this out and seeing just how I can make it work.

Although I also am a bit hesitant about generating any HTML from my Bean, but it seems to be the best method so far (at least the best that I can understand).

Thanks again, you guys are awesome!

[Bear Bibeault]

Ah yes, Ben knows me well. Call me a "strict interpretationist" when it comes to separation of concerns. But not a dogmatic one.

There are times when practicality or just good old engineering trade-offs dictate that following rules to the point of dogma makes no sense. And if the viable alternatives end up being a choice of generating minimal HTML in a bean (or other Java code) and introducing Java scriplets into a page, the former wins hands down.

In this particuar scenario, I would pesonally still opt for passing a name-value pair abstraction to the page but have no strong objection to Ben's plan of attack either. (Of course pesonally, I would have a custom tag to generate the select and its options so that on-page iteration is unnecessary -- but that's another show).

[Bryan Scarbrough]

All that, in a perfect world, would likely be my personal take on it as well, but my current time constraints do not allow for such actions. The project is due at the end of September and I will only have a couple of Good weeks to work on it between now and then.

This pretty well limits me to what I know instead of those fancy-schmancy "custom tags".
Thanks again for your help, one final question, however:

How can I use the example provided by Ben and pass variables into it from the JSP page?

I have some code, will it work, or am I going in the right direction?

public String getOptionList(String optionType){
    try{
      Context ctx = new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/sql");
      Connection conn = ds.getConnection();
 
      if(conn != null)  {
if (optionType = "someValueFromHTML")
  String query = "SELECT rankid, rank FROM rank";
        else if (optionType = "someOtherValue")
          String query = "Some other query"
        else if...
 
PreparedStatement statement = conn.prepareStatement(query);
        ResultSet result = statement.executeQuery();
        
        while(result.next()) {
           listSb.append("<option value=\"");
           listSb.append(result.getInt(1);
           listSb.append("\" >");
   listSb.append(result.getString(2);
           listSb.append("</option>\n");
        }
     }
     conn.close();
     } catch(Exception e) {
       e.printStackTrace();
   }
   return rankSb.toString();
}

Any direction? Can I even pass variables into the Bean in this way?
Thanks

[Ben Souther]

JSP pages can't call methods that require arguments.

I would:
Make the request to the servlet.
The servlet would read the requestParameters, instanciate my Data Access Objects (DAO) and call their methods (passing in any required arguments).

The DAO objects will handle all database communication and return a bean containing all the data that will be needed to build the JSP.

The data is usually contained in an list or map.
The bean can also have methods, such as the one I posted earlier, to help out with tasks such as building option lists to help keep scripting (scriptlets, jstl, or otherwise) logic from having to be performed in the JSP.

The servlet would bind this bean to either request, or session scope and forward to the JSP.

The benefit of having all of your database code in a DAO is that it is separated from (and can be called without) a servlet container. You can add a main method to the object and call it directly from the command line. I do this before I ever start working on servlets and JSPs. They can also be called from a testing script. I draw the line by never importing java.sql.* in the servlets or JSPs (or any object accessed directly by a JSP) and by never importing javax.servlet.* from any data access object.

NOTE: The differences between my and Bear's approach are trivial. The main thing is a good separation between your Model (data access, business rules, etc..), the control flow of your web app (Servlet Controllers), and the way you present it (JSP View). If you build your bean as I described, the JSP developer can choose either approach.

[Bryan Scarbrough]

So, I have 9 option lists that need to be generated at some point throughout the process and I need to create a seperate method for each one?

Well that just stinks doesen't it...

Oh, well, such is life...

Thanks again for the help!!

P.S. One last ditch effort -- Is there a way to pass the listType variable into the servlet and then have the servlet pass it to the Bean?

[Ben Souther]

[slightly off topic]

Bear: (Of course pesonally, I would have a custom tag to generate the select and its options so that on-page iteration is unnecessary -- but that's another show).

The reason I like to generate the option tags but not the select tag itself in the bean is that, doing so gives the JSP developer/designer full control over both the 'look and feel' of the select box and the JS events. The only thing they're pulling from the bean is the data itself.

This technique brings select lists more inline with text type input boxes.
With an text input field, the JSP developer builds the entire tag and pulls the data from the bean.

<input type="text" name="firstField" value="${firstField}" />
 
<select name="secondField">
  ${secondFieldAsOptions}
</select>

There is no need for the JSP designer to learn the semantics of my framework to add an onclick or onchange event attribute to any of the fields.

To me, this is a more strict interpretation of "separation of concerns" than using JSTL to iterate over a collection and branching within each iteration to test a value to see if it's a currently selected one, and then, add "SELECTED" to the ones that are.

A custom tag that builds the entire select list tag smells too much like Struts.
</slightly off topic>

[Ben Souther]

Originally posted by Bryan Scarbrough:
So, I have 9 option lists that need to be generated at some point throughout the process and I need to create a seperate method for each one?

Well that just stinks doesen't it...

Oh, well, such is life...

Thanks again for the help!!

P.S. One last ditch effort -- Is there a way to pass the listType variable into the servlet and then have the servlet pass it to the Bean?

You could build one "generateOptionList" method that takes either a 2D string array or a list of "option" beans as it's argument and returns a string containing the option tags.