Sunday, May 27, 2012

Extracting a single resultset row from sql:query resultset

I know, JSTL SQL breaks the MVC pattern. Who cares. It's quick and dirty and it works.
I simply hate complicated setups of MVC products, gimme the result now with minimal effort and I will be grateful to you.

So, all the examples are about retrieving a ResultSet with a sql:query:

<sql:query var="rsInterfaceNames" dataSource="jdbc/soainfra_dev2">
select unique InterfaceID from WLI_QS_REPORT_VIEW order by InterfaceID DESC
</sql:query>


and then do a

<c:forEach var="rowIF" items="${rsInterfaceNames.rows}">
do something with ${rowIF.someAttribute}
</c:forEach>


This simply doesn't apply when you retrieve a ResultSet with a single row, suck as in SELECT COUNT(*) FROM SOMETABLE;

Here is the javadoc of the object returned into the resultset var:

http://tomcat.apache.org/taglibs/standard/apidocs/org/apache/taglibs/standard/tag/common/sql/ResultImpl.html

and here is how you can get the value of count(*):

${rsInterfaceCount.getRowsByIndex()[0][0]}


in case you have trouble with conversions, just use select TO_CHAR(count(*)) ...

No comments: