使用结果集显示多个记录 [英] displaying multiple records by using resultset

问题描述

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

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>

请注意<url-pattern>，您可以通过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

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.

这篇关于使用结果集显示多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！