How do I make a Java ResultSet available in my jsp?

Question

I'd like to swap out an sql:query for some Java code that builds a complex query with several parameters. The current sql is a simple select.

<sql:query
   var="result"
   dataSource="${dSource}"
   sql="select * from TABLE ">
</sql:query>

How do I take my Java ResultSet (ie. rs = stmt.executeQuery(sql);) and make the results available in my JSP so I can do this textbook JSP?

To be more clear, I want to remove the above query and replace it with Java.

<%
  ResultSet rs = stmt.executeQuery(sql); // Messy code will be in some Controller
%>

<c:forEach var="row" items="${result.rows}">
  <c:out value="${row.name}"/>
</c:forEach>

Do I set the session/page variable in the Java section or is there some EL trick that I can use to access the variable?

Solution 1

Model (Row):

public class Row { 
    private String name;
    // Add/generate constructor(s), getters and setters.
}

DAO:

public List<Row> list() throws SQLException {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<Row> rows = new ArrayList<Row>();

    try {
        connection = database.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(SQL_LIST);
        while (resultSet.next()) {
            Row row = new Row();
            row.setName(resultSet.getString("name"));
            // ...
            rows.add(row);
        }
    } 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 rows;
}

Controller (servlet):

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
        List<Row> rows = someDAO.list();
        request.setAttribute("rows", rows);
    } catch (SQLException e) {
        request.setAttribute("error", "Retrieving rows failed.");
        e.printStackTrace();
    }
    request.getRequestDispatcher("page.jsp").forward(request, response);
}

View (page.jsp):

<c:forEach items="${rows}" var="row">
    <c:out value="${row.name}" />
    ...
</c:forEach>
<c:if test="${not empty error}">Error: ${error}</c:if>

Solution 2

You set up a session/request attribute from the Java code.

However, I would suggest not using a ResultSet, as it has some lifecycle issues (i.e. needs to be closed). I would suggest fetching the ResultSet object in the Java code, iterating over it building, say a List, closing the ResultSet and pass the List to the JSP.

If you are using Spring, the JdbcTemplates provide methods that take an SQL string and parameters and return a List> with the results of the query, which might come in very handy for this.