Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

How to create a view of two tables, depending on foreign key with Java Servlets?

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey guys! I am learning Java Servlets and trying to exercise with a project of my own. I am using two MySQL tables Users & Users Phones.
As you can see from the attached files, each user can have multiple phone numbers.
However I fail to make a jsp view of this records.

This is my code:
UserPOJO



UserDAO


UserServlet


and the jsp Table



So, my question is: How can I list each user's phone numbers in a c:forEach loop -> dropdown list? Is this the right SQL query - FIND_ALL_USERS_QUERY or is it done another way?
db_1.png
users
users
db_2.png
users_phone_numbers
users_phone_numbers
 
Marshal
Posts: 26912
82
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch!

That looks approximately like what you should do. Of course a lot depends on the details. I could point to various things which could possibly be problems, but why don't we start with you describing your actual problem? Tell us what you saw in your output, post error messages, and whatever else is included in the word "fail".
 
Bogdan Lozev
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well I have tried different approaches, however the closest I get is this - find the image below, and it's not the result which I have in mind - in the dropdowns there is only one phone number listed. Each user should be listed only once and in the dropdown there're all his phones.

I don't have any errors, but my question is really: How can I do this? What is the methodology , because in my learning examples and the "whole" Internet I can't find an example near to mine and as a newbie I am quite confused why is that... Do I need to make another POJO and DAO just for the phone numbers , How to link the user id from users table and the foreign key from phone numbers table using JAVA?
table.png
[Thumbnail for table.png]
 
Sheriff
Posts: 67595
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For the phone numbers, you'll want to create a select element, and forEach over the list of number to create the options. No additional Java is needed; you already have all the info you need.
 
Bogdan Lozev
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I didn't add any Java code, just <c:forEach> , however I got an empty array in the dropdown and no errors whatsoever.
 
Saloon Keeper
Posts: 24548
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I read this correctly, you'd need an inner forEach to iterate the phone numbers based on your Data Transfer Object structures.

But that's not really a good approach here. Rather than model entity relationships like you'd do in an ORM, what would be simpler is to define the "Users" class to include a single instance of a phone number and iterate thrpugh that.

Your SQL doesn't make that apparent though, since you simply said "SELECT * FROM Users". Which, incidentally isn't good practice either, since if someone changes the database schema it would break your code.. What you really need looks more like this:


This will return a set of rows where the user information repeats as each row is read, but the phone numbers change, then when all phone numbers for a given user are returned, returns a set for the next user ID, and so forth. It might seem wasteful, but your result set will hopefully be be short anyway. Long reports aren't really what webapps are intended for.
 
Bogdan Lozev
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have done it your way, however I still get an empty array in this line of code:
 

This is the results I get:


and so on... you can see that the userPhoneNumbers is empty...
 
Tim Holloway
Saloon Keeper
Posts: 24548
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You never set the userPhoneNumbers values returned from the query.
 
Bogdan Lozev
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh my bad, yes I did set it , HOWEVER when I do the



I get this result in the image attached. In every dropdown I get all the numbers from the database and not the specific phone numbers which correspond to the user. First user has 4 phone numbers, the second user has 3 phone numbers, etc.
1.png
[Thumbnail for 1.png]
 
Tim Holloway
Saloon Keeper
Posts: 24548
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You did the JOIN wrong. You said:


You should have said:

The WHERE clause, if present applies to the results of the join (for example, WHERE groupid = 7).
 
reply
    Bookmark Topic Watch Topic
  • New Topic