SELECT count(*) FROM ...

[roni suvanto]

I have a bean that handles all my database connections/queries and can't quite figure out how to get results from "SELECT count(*) FROM.." queries displayed on .jsp page.

Normally I use it like this:

  
<jsp:useBean id="foo" class="beans.Foobar" scope="request" />
<jsp:setProperty name="foo" property="message" value="some sql" /> 
 
<%
   foo.connect();
   ResultSet rs = foo.viewDatabase();
%> 
     

and then request data with rs.getString("something")/rs.getInt("something) etc.

But how do I get the count when using "SELECT count(*) FROM"-statement ?

rs.getXXX("xxx") ?

[Blake Minghelli]

You still use the rs.getInt() method, but instead of passing in the column name, you use the column index. Since a SELECT count(*) query returns only a single column, the index is 1, like this:
rs.getInt(1)
[ June 10, 2004: Message edited by: Blake Minghelli ]

[Peter den Haan]

Alternatively, you give the column a name using an alias:

SELECT COUNT(*) AS COUNT FROM MYTABLE WHERE...

int count = rs.getInt("COUNT");

I assume that you know that embedding such database code in your JSP is bad practice... if you really need to, at least use the JSTL SQL tags.

- Peter