displaying multiple records by using resultset

Question

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

Answer 1

You have to create a POJO to store the data obtained via your query and then via ResultSet you can iterate each row obtained from DB Query execution.

So you will get a List of object(POJO). This can be used wherever you want. http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2691&lngWId=2

Answer 2

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.

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;
}

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);
}

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>

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

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>

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