使用结果集显示多条记录

2021-12-21 00:00:00 sql-server jdbc servlets

如何使用结果集语句显示数据库中具有相同字段值的多条记录

how to display multiple records from database having the same field value by using result set statement

推荐答案

首先创建一个代表表格一行的 Javabean 类.我不知道你在说什么数据，但让我们以 User 作为现实世界的例子:

First create a Javabean class which represents one row of the table. I have no idea what data you're talking about, but let's take an User as real world example:

public class User {
    private Long id;
    private String name;
    private Integer age;
    // Add/generate public getters and setters.
}

以上当然是一个例子.您需要根据实际数据所代表的内容为类和属性命名.

The above is of course an example. You need to name the class and the properties accordingly what the actual data represents.

现在创建 DAO 类，它在 JDBC.您只需要确保在类路径中具有正确的 SQL Server JDBC 驱动程序.我可以为此推荐 jTDS，因为它比 Microsoft 自己的 JDBC 驱动程序更好更快.好的，假设您要列出所有具有相同 age 的 User:

Now create DAO class which does the desired database interaction task with help of JDBC. You only need to ensure that you have the correct SQL Server JDBC driver in the classpath. I can recommend jTDS for this as it is much better and faster than Microsoft's own JDBC drivers. OK, let's assume that you want to list all Users which have the same age:

public List<User> listByAge(Integer age) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, name, age FROM user WHERE age = ?");
        statement.setInt(1, age);
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getLong("id"));
            user.setName(resultSet.getString("name"));
            user.setAge(resultSet.getInt("age"));
            users.add(user);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return users;
}

现在创建一个 Servlet 类 UsersServlet，它对 doGet() 方法中的数据进行预处理.

Now create a Servlet class UsersServlet which does the preprocessing of the data in the doGet() method.

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    List<User> users = userDAO.list();
    request.setAttribute("users", users);
    request.getRequestDispatcher("/WEB-INF/users.jsp").forward(request, response);
}

在 web.xml 中映射这个 servlet，如下所示:

Map this servlet in web.xml as follows:

    <servlet>
        <servlet-name>users</servlet-name>
        <servlet-class>mypackage.UsersServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>users</servlet-name>
        <url-pattern>/users</url-pattern>
    </servlet-mapping>

注意，你可以通过http://example.com/context/users来执行这个servlet.

Note the <url-pattern>, you can execute this servlet by http://example.com/context/users.

现在创建一个 JSP 文件 users.jsp，将它放在 WEB-INF 文件夹中，这样任何人都可以不使用 servlet 直接访问它.您可以使用 JSTL c:forEach 迭代一个 列表:

Now create a JSP file users.jsp which you place in WEB-INF folder so that nobody can directly access it without using the servlet. You can use JSTL c:forEach to iterate over a List:

<table>
    <thead>
        <tr><th>ID</th><th>Name</th><th>Age</th></tr>
    </thead>
    <tbody>
        <c:forEach items="${users}" var="user">
            <tr><td>${user.id}</td><td>${user.name}</td><td>${user.age}</td></tr>
        </c:forEach>
    </tbody>
</table>

通过http://example.com/context/users 执行.应该是这样.

Execute it by http://example.com/context/users. That should be it.

相关文章