ResultSet size

[bugger]

I am quering a table in database and getting records in a ResultSet object. I want to know the number of records in ResultSet. How to do that?

I want want to query the table foe te number of records:

Code:

select count(*) from tablename where ...

[felixtfelix]

Actually There is no direct way to get the no of rows in the resultset. I can suggest one way. Make the resultSet scrollable. Then

rs.last

rs.getRow() which will give you the no of the current row.

Or

you can make use of a counter in the while loop

int recordCount =0;
while(rs.next())
{

recordCount++;
}

This will do?

[anagani80]

is thare any workaround to find resultset size?

[felixtfelix]

hi,

this is the another way to find the resultset size.

select * from table_name select @@rowcount no_of_rows

which will give two resultset

first one is the actual data

second one is the no of rows in the first resultset.

eg.

If the select query have three values

Emp_no emp_name
--------------------
1 AA
2 BB
3 CC

the above one is the first result set

no_of_rows
-----------
3

[rico16135]

can't you get this the size from the ResultSet metadata? I've never done it, but I remotely remember reading something about this.

[felixtfelix]

Hi,
Otherwise you can use the following code, as i said in the earlier post rs.getRow() will give the no of the row. so you can make the resultset scrollable and use the following code.

Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR _READ_ONLY);

ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

resultSet.last();
int rowCount = resultSet.getRow();

Will it help.

Regards,
Felix T

[fishtoprecords]

Quote:

Originally Posted by bugger

Code:

select count(*) from tablename where ...

I do this all the time. What problem do you have?
You have to use a relative resultSet.getInteger(0));
to pull the value out, since "count(*)" is not named, but it works great.

Its bad form to get all the individual records (as others have suggested) when all you want is the count(*) value

You can do this for other functions, such as min(fieldX), max(), etc.

[felixtfelix]

hi,

If u have some other fields say for example,

select column1,column2,column3 from <tablename> where ...

and if u add the count(*) with the above query then always remember that you have to add the other fields in the group by clause which will may make the data irrelevant than what you are looking for as the data may be get grouped.

Regards

Felix T