File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSP and the fly likes What is best way to display a results set from a database? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "What is best way to display a results set from a database?" Watch "What is best way to display a results set from a database?" New topic
Author

What is best way to display a results set from a database?

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
What would be the best way to achieve a JSP which displays say 10-15 product listings from a database?

For example if I wanted to pull (this is basic, reality would be more data, but it gets the point across) all of the "product names" from the database, then display them in a table on the site, one row after another.

My initial thought is to use a JavaBean, although I am no expert on this - and I am not 100% sure how I would implement this solution just yet either.

What are everyones thoughts on the best way to achieve this?

Any help appreciated.

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Have your business layer grab the data and return it using a collection of some type. Could be an array, could be a List. What it should not be is a result set! Results sets should be opened and closed as quickly as possible at the lowest levels of the application.

The controller places the collection in request scope for access by the JSP, which uses the <c:forEach> JSLT tag to iterate over it and create the HTML for display.

See the JSP FAQ if paging the data is important to you.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Thanks for the quick reply :-)

That should be simple enough to set up a few array lists to pass back with each set of data in accordingly.

I will have a read up on the JSP tag "<c:forEach> as I have not come across this before, same for "paging the data".

Thanks for info, I will follow up if I still don't understand after reading the information :-)

Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Remember the golden rule of JSP: no Java code in a JSP!
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Just tried installing the JSTL to give some code a try and now the project (in Netbeans) won't build and I am not sure why this is happening.



The actual code that it is referring to is... (the middle line)



Anyone come across this error before?

I am running netbeans with Tomcat & a MySQL DB.

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

As this last question is about Netbeans, not JSP, it'd be best to ask in the IDEs forum.

Or better yet, ditch the IDE until you know how to debug issues without the crutch of the IDE.
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Just updated another thread I found about this exact same issue, http://www.coderanch.com/t/454174/vc/module-has-not-been-deployed#2414106

As for figuring this out without the IDE, my guess is that would actually be a lot simpler if I knew what I was looking for and what was causing this issue.

Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Impossible to tell from what you've posted. The problem with IDEs is that sometimes, they hide the real cause of issues.
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Got it fixed.

Now time to get developing again :-)

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Almost got my head around this JSTL stuff and almost got it working.

I am trying to display the data in a table format on my JSP page, but I cannot seem to get my head around the nested c:forEach as what ever I try the display just doesn't do what it should be doing. So either I have been looking at this for too long and not getting anywhere, or I am doing something wrong.

Servlet:


JSP:


From my understanding, this should work....but it isn't quite behaving as expected. When I leave the "columns.clear()" line in, then I just get two bits of "Rows" printed on the jsp page, with no actual data (seems like it is clearing the data before adding the "columns" to the "rows"??). Then when I remove that line of code, I get the two rows of data, but in the same column, then repeated again on the second row.

So if I had three rows it would be displayed as....3 rows of data all on one row (col 1, 2, 3 then col 1, 2, 3 or the next row....but all on one row), then the second row would display the same as the first row.

Hope that all makes sense, if not I can take some screens to help explain more.

Thanks
Michael


Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Should not the items for your inner loop be ${row}?
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
I have just tried changing that to "${row}" and also changing the var="row" in the outer loop (since from my understanding these two are linked when doing nested loops?). It still has the same effect though with displaying the two rows next to each other on the same row, then the two rows next to each other again on the second row. Instead of just showing two rows of data.
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
I have just changed the code slightly, which has got closer to the correct behavior of having only one row or data per row instead of two.

New code in JSP:


What is actually happening now though is that both rows of data are actually the same data (the second row). Which means that somehow the second "rows.add(columns)" is overwriting the first lot when before passing to the JSP. Which is odd since the bits in the while loop look like they should behave normally.

Thanks

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Figured it out (well figured out why the strange behavior is happening anyways).....it is because when you place an ArrayList into an ArrayList (or ArrayLists) you are actually putting the reference in and not the actual contents of the array list. Therefore when the servlet has finished doing its thing, the arrayList (of arraylists) contains the reference to the arrayList which contains the last data.

(I think that is why it is happening anyways!)

So to fix this I have had to set the variable "columns = new ArrayList<String>();" at the beginning of the while loop as this creates a new reference that can be placed in the arrayList of arrayLists.

All working now :-)

Thanks for help

Michael
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
Following on from this message, I have noticed that this method doesn't quite accomplish what I require.

This is a great method for simply displaying the content of the DB sequentially, although it does not allow you to manipulate that data in the JSP before displaying it (for example, wrapping <a> tags around only one of the items in the list, or <b> some of the text (but not all) )

What would be the best way to effectively put all of the results into "something" which can then be called from the JSP like follows....

JSP:


I cannot think of a good way to accomplish this without really ugly code.

I thought of using a JavaBean, although I am not quite sure how that would work on this occasion as I need lots of data stored and I need to do "getNextRowOfData" or similar.

Anyone done anything similar before?

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

If you have what is effectively a two-dimensional array of data*, you can easily deal with each table cell in a row on a case-by-case basis. You seem to be making this a lot harder than it needs to be.


* Could be a 2D array, or a List of Lists, or any combination thereof.
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
I think I am making this a little over complicated...

I can easily store all of the data in a two-dimensional array, although how do I then set all this in Attributes that can be seen for the JSP?

Would is be best to create say 10 arrays/HashMaps that would be set as Attributes for the JSP (row1, row2, row3) with each "row1" then being able to be easily accessed via the JSP?

I thought of this, but then there is the issue that what happens if there are more than 10 rows that I need to set? How would I handle that?

A HashMap per row would be perfect as I can easily access that in the JSP based on the "key" then I can do what I require with the data.

Or, just thought....

I could always create an ArrayList of HashMaps - with each Hashmap effectively being the row of data (which can be easily accessed in the JSP) then I can do the "<c:forEach>" on the ArrayList, which would do all of what is required for each row of data.

I will give that a go now & update later on how this goes.

If you have any other suggestions on best way to accomplish this please let me know.

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Michael Cropper wrote:I can easily store all of the data in a two-dimensional array, although how do I then set all this in Attributes that can be seen for the JSP?

Why would you want to mix display and style info with the data? Messy. The data should just be data.

Would is be best to create say 10 arrays/HashMaps that would be set as Attributes for the JSP (row1, row2, row3) with each "row1" then being able to be easily accessed via the JSP?

Why separate maps? Why not a list of maps? You are limiting yourself to thinking about a fixed number of rows.

I could always create an ArrayList of HashMaps - with each Hashmap effectively being the row of data (which can be easily accessed in the JSP) then I can do the "<c:forEach>" on the ArrayList, which would do all of what is required for each row of data.

Now you're thinking!
Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
I could always create an ArrayList of HashMaps - with each Hashmap effectively being the row of data (which can be easily accessed in the JSP) then I can do the "<c:forEach>" on the ArrayList, which would do all of what is required for each row of data.

Now you're thinking!


It worked like a charm :-)

I consider all of this like learning to ride a bike - once I figure out how to do all of these kind of things I will never forget (hopefully!) :-)

Thanks for help again

Michael
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1026
The other suggestion would be to have a List of Beans. ie define a java bean to encapsulate the information you are retrieving from the database, and return a List of those beans to the JSP page.

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137
For my future reference....

In the JavaBean, how would I create the bean to just use one set of data, then create a new bean which would contain all of the data for row 2?

My knowledge of JavaBeans (at the moment) is limited to playing with one set of data via get/set methods.

Thanks
Michael
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

The properties of the bean would represent the values for each row, and a new bean instance would be created for each row instance, each with their own data values.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What is best way to display a results set from a database?