I have a jsp which displays items available in the database in groups of their category ordered by catageory name. Each category is to be in a table with heading of the category name in a bigger font. Items belonging to that category will follow in the table in a smaller font having other columns like itemId, an image thumbnail, description etc. Next table will have the next category and items of that category.
I have two tables in the database - an ITEM table which has a referential integrity with CATEGORY table on category_id. CATEGROY table will have the categoryId and category_name columns. category_name is what is to be displayed as the heading for each table in the form.
I have a bean which will have all these columns as fields. I query the database and populate the bean with the results from database. Now, I am at a loss on how to write the code for jsp. I am having an idea that I should have a for loop for each category under which there will be another for loop for items of that category but how do I get the results in the format of category and items of that category?
I wrote the sql for getting the itmes and categoris like this....
select itemid, image, description, category_id, category_name from ITEMS A, CATEGORY B where A.category_id = B.category_id order by category_name. I can populate the beans from teh resultset but I am at a loss on how to organise the results in proper java collections objects for me to be able to use them in the jsp.
Request experienced people to throw some light.
Thanks very much for reading my lengthy post and thanks very much in advance for your ideas.
I am thinking of writing a SQL where I group the results by category_id and have the count(items). This will be my outer loop. I will loop for count no. of times in each category to get items ( another database call) and store them in a map ( category_id is the key and beans of the items in each category in an arraylist). Does it make sense?