aspose file tools*
The moose likes JSP and the fly likes How to populate a select list with data from a database table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "How to populate a select list with data from a database table" Watch "How to populate a select list with data from a database table" New topic
Author

How to populate a select list with data from a database table

Graham Farrell
Greenhorn

Joined: Jun 07, 2012
Posts: 5
Hi all,

This is my first post so my apologies if I’m asking something simple but I’m new to JSP coding.

I’m trying to code a simple application and wish to know how to populate a Select list with records from a specific database table using the MVC structure so I wish to avoid placing any code in the JSP.

Currently the welcome page of the application is a JSP (add_owner.jsp) that allows a user to register with the site.

A user must include what county (in the U.S. the equivalent would be a state) hence the user table has a foreign key relationship with the counties table.

When the form in the JSP is filled out is passed to its servlet (AddOwnerServlet) that saves the form data to the correct database table and then displays the entered record in a second JSP (display_new_owner.jsp).

At the moment I’m just using a text box and manually entering an int to represent the county value for testing purposes however this is not an option in a real application.

Currently I have a County class that defines a County object and a CountyDB class that I intended to use to select all counties or a single county.






I’ve searched but as a newbie I’m slightly confused as to how to code the select list to have the countyID (hidden) and county name displayed in the select list. I understand (incorrectly/correctly) that a c:forEach tag should be used to fill populate the options for the select element however I have not been able to find a clear newbie example as how to complete this using the MVC structure.

Could someone please point me in the right direction please?

Many thanks in advance.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61221
    
  66

Welcome to the Ranch!

The page controller for the page containing the drop down is responsible for obtaining the entries from the model. It should place these on the request as a scope variable. The JSP can then iterate over them to create the option elements,

You might find this article helpful as an overview of how to structure modern web applications.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Graham Farrell
Greenhorn

Joined: Jun 07, 2012
Posts: 5
Hi Bear,

Thank you for your reply, I will read the document provided.

As I understand it the controller for the JSP would be it's servlet class, would this be correct?

My knowledge is limited currently I have only doPost and doGet methods within the servlet which I believe are utilized when the JSP form is submitted.

Currently I've added the following to my servlet



Would this be correct?

Within the JSP I have the following code however the select list is not populated.



Many thanks for your help.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61221
    
  66

Graham Farrell wrote:As I understand it the controller for the JSP would be it's servlet class, would this be correct?

Yes, the correct term for this would be the JSP's page controller.

My knowledge is limited currently I have only doPost and doGet methods within the servlet which I believe are utilized when the JSP form is submitted.

GET should be used when the JSP is displayed, POST when a form is submitted. Be sure to put method="post" on all your <form> elements.

Currently I've added the following to my servlet
Would this be correct?


See below:

ArrayList<County> counties = CountyDB.selectCounties();

Here I assume you are obtaining a list of counties from the model. So far so good.

HttpSession session = request.getSession();
session.setAttribute("user", user);
session.setAttribute("counties", counties);

What is user an why are you putting it into the session?

Why are you putting counties into the session as opposed to the request?

RequestDispatcher dispatch = getServletContext().getRequestDispatcher(url);
dispatch.forward(request, response);

RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(hurl);
dispatcher.forward(request, response);

Two forwards? That's most definitely not right. You can forward to one JSP.

Within the JSP I have the following code however the select list is not populated.


That all looks right on first inspection (work on your indentation -- it's all wonky), but the problems with the servlet need to be solved first.
Graham Farrell
Greenhorn

Joined: Jun 07, 2012
Posts: 5
Sorry Bear,

Maybe I'm not explaining myself correctly.

Currently as I only beginning I have only two JSP pages and one serlvet.

add_user.jsp (index page)

AddUserServlet

display_user.jsp

When the app loads the index file (at the moment) is add_user.jsp

This page contains the form which allows a user register with the site. On this form I wish to allow the user select what county they live in.

When the data is filled and the form is posted, control is passed to the AddUserServlet the record is saved and then the new record is displayed in display_user.jsp

Would it be an option to import the CountyDB class as a bean into the add_user page?

My apologies my next reply will be late as I'm in work at 4am so will reply as soon as possible.

Thanks again for your help.


Graham.

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61221
    
  66

Graham Farrell wrote:When the app loads the index file (at the moment) is add_user.jsp

Problem. You are loading a JSP directly without invoking its controller. How is the page supposed to get the list of counties from the controller if the controller isn't invoked?

A JSP should never (ok, rarely) be invoked directly. In fact, the JSPs should be placed under the WEB-INF hierarchy so that they never can be directly addressed without going through their controllers.

The first thing that should be invoked is the controller, not the JSP.

Would it be an option to import the CountyDB class as a bean into the add_user page?

No. The JSP should never do the job of its controller. The JSP should be as dumb as possible and only create the view. Fetching stuff from the model is the job of the servlet controller, not the JSP.

My apologies my next reply will be late as I'm in work at 4am so will reply as soon as possible.

No problem.
Graham Farrell
Greenhorn

Joined: Jun 07, 2012
Posts: 5
Hi Bear,

I have this working now based on your advice but I'm not 100% sure of the correctness of my implementation.

In relation to the location of my jsp files I'm following the layout of the book (a poor one I'm beginning to think) that I'm using to teach myself so all my JSPs are within the Web Content folder not the WEB-INF folder.

Anyway I edited my servlet to include a doGet method as follows



I then edit my web.xml file to have the servlet as my welcome file and on loading the application the form and on posting the form the correct data is saved to the database.

While this works I'm wondering if this is the correct method of completing this action or have I just hacked my way into a fix :-)


Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61221
    
  66

Graham Farrell wrote:I have this working now ...

Super!

In relation to the location of my jsp files I'm following the layout of the book (a poor one I'm beginning to think) that I'm using to teach myself so all my JSPs are within the Web Content folder not the WEB-INF folder.

That's fine for JSPs that need to be directly addressed (those few that have no controller), but it's a better practice to place JSPs with page controllers under WEB-INF so that you can't screw up and directly address them.

"WebContent" by the way is simply an IDE artifact. It's not part of any standard so don't get too enamored of it or think that it's mandatory.

Anyway I edited my servlet to include a doGet method as follows

Looks good!

If you're interested in nit-picking:


Code to interfaces. The fact that it's an ArrayList is irrelevant. The variable should be declared as List<County>.


Having this declared as a separate string in an automatic variable doesn't do anything good for you. It just separates the declaration from its use needlessly with no gain. I'd use one of two approaches:
  • If you want it declared separately, use a class-level final (constant) declaration.
  • Just use the string literal inline.

  • I tend to favor the first approach as it makes it easy to see the strings.

    (I'm also not too fond of the variable name: hurl.)


    Why the session? Bad choice. This means that this is going to stay around and never be garbage collected until the session ends at a later point, or you specifically clean it up. Only use the session for things that need to stay around longer than the current request. Use request scope for "one shot" variables.

    Now, if grabbing the list of counties is expensive, then caching it in the session (for user-specific stuff) or app context (for stuff that's app-wide) isn't a bad idea. But you're not doing that here.

    Other than that, looking good!

    Graham Farrell
    Greenhorn

    Joined: Jun 07, 2012
    Posts: 5
    Hiya Bear.


    If you're interested in nit-picking:


    Nit pick away its the only way to learn.

    Thanks again for all your help.

    Graham.
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: How to populate a select list with data from a database table