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?

Answer 1

If you're using a web framework like spring mvn or struts, you have a controller class that is executed before the actual jsp. This can have a method

ResultSet getResult()

This method will be available as ${result} within your jsp.

If you're not using any of these frameworks you can still use jsp usebean and bind a javaclass to a variable (check usebean documentation). If you usebean to a variable of myBean you access it with ${myBean.result}

Lastly you can also bind the result to the request parameters "somewhere else". In this case you address it as ${param.result}

Answer 2

You don't.

First, placing SQL into JSP, even via tags, is indicative of a horrible design choice. The JSP page is the "view" in the Model View Controller pattern. Its job is to display your model, not actually do anything with it other than display it.

In your controller class, execute your SQL and retrieve actual Java objects that can be displayed via the JSP. Then, in your JSP, display them. Leave your SQL to your controller, and let the JSP focus on simply displaying the data. Not only do you gain clean separation of concerns, but your JSP becomes a lot simpler as a result, and it is much easier to refactor Java code than JSP code later on if it needs to be done (it is a lot simpler to test Java code as well).

Answer 3

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.

Answer 4

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>