JSTL sql:query variable

Question

I wrote the following code in a JSP file:

<c:catch var="e">
    <%
        int accountNumber = Integer.parseInt(request.getParameter("accountNumber"));
        int depositAmount = Integer.parseInt(request.getParameter("depositAmount"));
    %>
    <sql:query var='account' dataSource="jdbc/bank">
        select * from account where AccountNumber=<%= accountNumber %>      
    </sql:query>
    <c:choose>
        <c:when test="${account.first() == false}">
            <p>Account not found</p>
        </c:when>
        <c:otherwise>
            <h3>Deposit Made</h3>
            <p>Account number: <%= accountNumber %></p>
            <p>Deposit amount: <%= depositAmount %></p>
            <p>New balance: </p>
        </c:otherwise>
    </c:choose>
</c:catch>


<c:if test="${e != null}">
    error
</c:if>

The problem I am having is that the following code throws an javax.el.MethodNotFoundException: Unable to find method [first] with [0] parameters exception:

<c:when test="${account.first() == false}">
  <p>Account not found</p>
</c:when>

I need to access the account variable in the sql:query so I can check to see if a first row exists.

Answer 1

<sql:query var='account' dataSource="jdbc/bank">

As per the <sql:query> documentation, the account is a javax.servlet.jsp.jstl.sql.Result class.

<c:when test="${account.first() == false}">

As per the class' javadoc, that class doesn't have a first() method. There is however a getRowCount() method. I'd suggest to use that instead.

<c:when test="${account.rowCount == 0}">

Answer 2

First off, I highly recommend that you NEVER use scriptlets. If you rewrote this example as a Java Servlet, you'd at least have compile-time checking and it would also give you the ability to write a JUnit test of the logic.

Next, I absolutely hate that they threw <sql> into JSTL, which is a blatant disregard for the Model View Controller pattern, since you are putting data access code into the front-end. This makes for a maintenance nightmare, so I would rewrite that model code using the Java Persistence API (JPA) or the Hibernate framework.

That being said: if you must get the example working, then your problem is that account.first() is not a valid method call. Even though you can only ever return 1 result, the query is returning a list of results. Try something like the following.

<c:forEach var="account" begin="0" items="${account.rows}">
<h3>Deposit Made</h3>
<p>${account.depositAmount}</p>
</c:forEach>

Answer 3

The most foolproof way to implemt this is to implement a DTO with a first variable and then a getFirst() method.

public class account {
    private String first;
    public String getFirst(){
        return first;
    }
    ....
}

And when you call it in JSP, it should look this:

test="${!account.first}"

which will call account.getFirst()

Your SQL data will need to be mapped to this account object in which you would do all the validation, make sure there are no null values etc.