CODEWITHSUNDEEP
javasqljspservletsresultset
user3691923
user3691923

Reputation: 71

How to iterate through ResultSet from JSP file?

I have a simple statement in a servlet file that will go through the database and get the result set

ResultSet AssignmentList = stmt.executeQuery("SELECT * FROM Assignments WHERE " + projectID + "= Pid;");
request.setAttribute("assignmentList", AssignmentList);

As you can see I'm sending the ResultSet to a JSP file. And in that JSP file I tried to iterate through it like this.

<table class="CSSTableGenerator">
    <thead>
    <tr>
        <th>Aid</th>
        <th>Title</th>
        <th>Task</th>
        <th>ID of User Responsible</th>
    </tr>
    </thead>
    <tbody>

    <c:forEach items="${assignmentList}" var="item">
        <tr>
            <td>${item.Aid}</td>
            <td>${item.Title}</td>
            <td>${item.Task}</td>
            <td> ${item.UserID}</td>            
        </tr>
    </c:forEach>

    </tbody>
</table>

but when I do that I get an error. Am I suppose to iterating in wrong way, or is it not possible to iterate through a ResultSet.

Upvotes: 2

Views: 4689

Answers (4)

user207421
user207421

Reputation: 311048

There a class somewhere in JSP called Results that does exactly what you need. You construct it from a ResultSet, you can close the ResultSet and the connection, and the Results has an Iterable array of rows that are themselves maps.

Upvotes: 2

Elliott Frisch
Elliott Frisch

Reputation: 201527

Assuming you have a fairly standard ItemBean (I made everything a String, except projectId). Then, I would probably start with a PreparedStatement in a DAO helper method like so

private static List<ItemBean> queryForItems(
    Connection conn, int projectId) {
  PreparedStatement ps = null;
  ResultSet rs = null;
  // The Query is using a bind parameter
  final String ITEM_SQL = "SELECT * FROM Assignments WHERE Pid = ?";

  // This is the Diamond Operator... if pre Java 7, use new ArrayList<ItemBean>();
  List<ItemBean> al = new ArrayList<>();
  try {
    // Prepare the statement
    ps = conn.prepareStatement(ITEM_SQL);
    ps.setInt(1, projectId); // <-- bind the parameter
    rs = ps.executeQuery();
    while (rs.next()) { // <-- "Iterate" the ResultSet
      ItemBean ib = new ItemBean();
      ib.setPid(projectId);
      ib.setAid(rs.getString("Aid"));
      ib.setTitle(rs.getString("Title"));
      ib.setTask(rs.getString("Task"));
      ib.setUserId(rs.getString("UserID"));
    }
  } catch (SQLException se) {
    System.err.println("Error with " + ITEM_SQL + " and projectId = " + projectId);
    se.printStackTrace(System.err);
  } finally {
    // Clean up!
    try {
      rs.close();
    } catch (SQLException ignored) {
    }
    try {
      ps.close();
    } catch (SQLException ignored) {
    }
  }
  return al;
}

Upvotes: 0

Braj
Braj

Reputation: 46881

You can try with SQL Tag Library that is designed for accessing databases in a JSP page.

I have already shared an approach for Getting values from mysql database to show in JSP (JAVA)) along with sample code that might help you.

Logic: Simply fetch the data form the database in Servlet and populate the data in a POJO class and set the final list of all the records as request attribute and finally forward the request to the JSP page.

Upvotes: 0

fajarkoe
fajarkoe

Reputation: 1563

After we read the ResultSet, we need to close the database connection. Because you read the ResultSet in JSP, you can only close the connection in JSP. This, in my opinion, is not a good practice.

After you query the database in the servlet, read the ResultSet, store the result in a List, and then close the database connection. Then, pass this List to JSP in a request attribute. Your JSP retrieve the List from request attribute using EL. You can use JSTL to iterate over this List.

Upvotes: 2

Related Questions