Rowcount in Resultset

[Mouly Kumaraswamy]

How can I find the number of rows returned by a SQL query without iterating through all the rows? One way would be to add rowcount column in the select clause and order it - but this is very expensive and clumsy.

Also can is it possible to identify upfront if zero rows have been returned?

[steve souza]

You can't tell how many rows are in a ResultSet or if it is empty. I created my own utility class to do such things. However, it is easy to do the same. The idea would be to put the rows of the ResultSet into an ArrayList. I use that approach and right before returning i convert it to an Object[][] which for most of my needs is more convenient. There is a discussion of this issue here http://forum.java.sun.com/thread.jspa?threadID=484092&messageID=2262448.

I extracted my post and pasted it below.

<<The open source FormattedDataSet has a ResultSetConverter object that converts a ResultSet to an Object[][]. From this you could easily put the array in a collection or just use the array directly.

>>

 
// You can create the ResultSetConverter by passing a ResultSet to the constructor.  If you already have a ResultSet it is this easy.
ResultSetConverter rsc=new ResultSetConverter(resultSet);
 
// or you can execute the sql directly and hide all jdbc calls.  DataAccess is also monitored with JAMon.
DataAccess da=new DataAccessClient("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/databasename?user=root", false);
rsc=da.getResultSetConverter("select * from mytable");
 
// get ResultSet as Object[][]
Object[][] data=rsc.getResultSet();
 
/* Other ResultSetConverter methods
getColumnCount()  -  Returns the number of columns in the ResultSet
getMetaData()  -  Returns at 1 dimensional array of column names from the ResultSet.
getResultSet()  -   Returns a 2 dimensional array containing the data in the ResultSet
getRowCount() -   Returns the number of rows in the ResultSet
isEmpty() - Returns true if the ResultSet has no rows
 
*/
 

See the following sites for more info including javadocs, and a live demo.

http://www.fdsapi.com - The easiest way to generate dynamic XML and HTML
http://www.jamonapi.com - A performance tuning API>>