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.

BalusC · Accepted Answer · 2011-05-23T03:37:56.820

5

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

edited May 23 '11 at 03:37

answered May 23 '11 at 03:13

BalusC

1,082,665
372
3,610
3,555

score 3 · Answer 2 · edited May 23 '17 at 12:13

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>

This is for a homework assignment. I will be learning soon how to set this up using MVC. I need to check to make sure that the result set isn't empty. The only way I figured it out was to look at the first element to see if it returns true or false using .first(). I'm confused on why .first() isn't a valid method call when it's a method of result set. In any case, I still need to see if the result set has a record in it. If not I need to display an error message. — Brian, May 22 '11 at 22:30

score 1 · Answer 3 · answered May 22 '11 at 21:57

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.

JSTL sql:query variable

3 Answers3

Linked