Click here to Skip to main content
15,304,157 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

These are my two DB tables:
Users[^]
User's phone numbers[^]

What I have tried:

UserPOJO

Java
public class User {
    private int id;
    private String username;
    private String email;
    private String password; 
     
    private List<String> userPhoneNumbers = new ArrayList<>();   
     
     
    public User() { }
     
     
    public User(String username, String email, String password, List<String> userPhoneNumbers) {
        this.username = username;
        this.email = email;
        this.password = password;  
        this.userPhoneNumbers = userPhoneNumbers;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }    
     
     
    public List<String> getUserPhoneNumbers() {
        return userPhoneNumbers;
    }
    public void setUserPhoneNumbers(List<String> userPhoneNumbers) {
        this.userPhoneNumbers = userPhoneNumbers;
    }   
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                ", userGroupId=" + userGroupId +
                ", userGroupName=" + userGroupName +     
                ", userPhoneNumbers=" + userPhoneNumbers +
                "} \n";
    }   
}


UserDAO

Java
public class UserDao {
     
    private static final String FIND_ALL_USERS_QUERY = 
          "select *"+
            " from users"+
            " inner join users_phone_nums"+
            " where users.id=users_phone_nums.user_id";
             
             
    public List<User> findAll() {
        return getUsers(-1, FIND_ALL_USERS_QUERY);
    }
 
    private List<User> getUsers(int id, String query) {
        try (Connection conn = DbUtil.getConnection()) {
            List<User> users = new ArrayList<>();
             
            PreparedStatement statement = conn.prepareStatement(query);
 
            if (id != -1) {
                statement.setInt(1, id);
            }
 
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    User user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUsername(resultSet.getString("username"));
                    user.setEmail(resultSet.getString("email"));
                    user.setPassword(resultSet.getString("password"));
                     
                     
                    System.out.println("----->>>>> " + user);
                    users.add(user);
                }
                return users;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}


UserServlet

Java
@WebServlet("/admin/user")
public class UserServlet extends HttpServlet {
 
    private static final long serialVersionUID = 1L;
 
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
    }
 
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
          throws ServletException, IOException {
        UserDao userDao = new UserDao();
         
        request.setAttribute("users", userDao.findAll());
         
        getServletContext().getRequestDispatcher("/web/user/listUserAdmin.jsp").forward(request, response);
    }
}


the jsp
HTML
<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Username</th>
            <th>Email</th>
            <th>Password</th>
             
            <th>User Phone</th>
        </tr>
    </thead>
    <tbody>
        <c:forEach items="${users}" var="user">
            <tr>
                <td>${user.id}</td>
                <td>${user.username}</td>
                <td>${user.email}</td>                
                <td>${user.password}</td>               
                     
                <td>
                    How To View Phone Numbers?
                    in 
                    Dropdown and Another <c:forEach> List with Options </c:forEach>
                </td>
            </tr>
        </c:forEach>
    </tbody>
</table>
Posted
Comments
W Balboos, GHB 5-Apr-21 10:04am
   
Quick Hint: make your query first - then, once it's working correctly plug it into your code.
laken1234 5-Apr-21 10:24am
   
I think it is working correctly in MySQL Workbench, however I can't display the phone numbers in a dropdown list - I don't know how and can't find a solution with viewing two database tables in one jsp
Richard Deeming 6-Apr-21 8:32am
   
You appear to be storing your users' passwords in plain text. Don't do that!
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900