• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

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

 
Greenhorn
Posts: 3
  • 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: 26458
81
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: 3
  • 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]
 
Marshal
Posts: 67510
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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: 3
  • 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: 23409
159
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.
 
You firghten me terribly. I would like to go home now. Here, take this tiny ad:
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic