时间戳

首页 > 解决方案 > java - 如何根据Java Servlet中的外键在一列中显示多个值？

java - java - 如何根据Java Servlet中的外键在一列中显示多个值？

问题描述

嘿伙计们，我有两个 MySQL 表：

主表： 用户

和辅助表： users_phones

每个用户可以有多个电话号码——在第二个 SQL 表中 user_id 是指向主表用户 ID 的外键。我试图在 JSP 表中列出所有用户以及每个用户的电话号码，但我无法做到这一点。

到目前为止，这是我想出的：

用户 POJO：

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;
    }
    
    // Getters and Setters
        
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +    
                "} \n";
    }
}

电话号码 POJO：

public class PhoneNumbers {
    private int idPhone;
    private String phoneNumber;
    private int idUser;
    
    public PhoneNumbers() { }

    // Getters and Setters

    @Override
    public String toString() {
        return "UserPhone{" + "idPhone=" + idPhone + ", phoneNumber='" + phoneNumber
                + ", idUser=" + idUser + '\'' + "}\n";
    }
}

用户道：

public class UserDao {
   
    private static final String FIND_ALL_USERS_QUERY = 
            "SELECT *"
            + " FROM users"
            + " INNER JOIN users_phone_nums"
            + " ON 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"));                   

                    PhoneNumbers phoneNumbers = new PhoneNumbers();
                    phoneNumbers.setPhoneNumber(resultSet.getString("phone_number"));
                    phoneNumbers.setIdUser(resultSet.getInt("user_id"));
                    
                    System.out.println("############ " + phoneNumbers);
                    System.out.println("----->>>>> " + user);
                    
                    
                    users.add(user);
                }
                return users;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

用户服务小程序：

@WebServlet("/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/listUsers.jsp").forward(request, response);
    }
}

这是JSP：

<table id="usersTableId">
    <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 list user's phone numbers?</td>
            </tr>
        </c:forEach>
    </tbody>
</table>

有人可以向我解释如何实现这一目标吗？

标签： javahtmlmysqljspservlets

解决方案

一种方法是将 User 类中 userPhoneNumbers 的类型从列表更改为字符串；并准备一个逗号分隔的电话号码字符串。（还建议将PhoneNumberspojo 重命名为PhoneNumber。）

StringBuilder phoneNums = new StringBuilder()
PhoneNumbers phoneNumbers = new PhoneNumbers();

for(PhoneNumbers phoneNumber : phoneNumbers) {
   phoneNums.append(resultSet.getString("phone_number")).append(", ");
}
user.setPhoneNumbers(phoneNums.toString());

现在在 JSP 中，您可以打印这个逗号分隔的字符串值。

另一种选择是继续将其设置为列表并让另一个c:foreach在 JSP 中循环遍历它

推荐阅读