how to find number of records in ResultSet

Question

I am getting ResultSet after an Oracle query. when I iterating through the ResultSet its going in infinite loop.

ResultSet rs = (ResultSet) // getting from statement
while (rs.next()) {
//
//
}

this loop is not terminating so I tried finding number of records using rs.getFetchSize() and its returning a value 10. I want to know if this is the correct method to find out number of records in ResultSet and if the count is 10 why is it going in infinite loop. Please give your opinion.

Is there any weird code inside the loop that might cause the hang? What happens when you step through it with the debugger? — colithium, Dec 01 '11 at 08:03
why don't you try changing the query to a count and work out how many rows are returned by the query... — Michael Wiles, Dec 01 '11 at 08:14
@Cilithium nope.. there are some getter method call, thats all.. — dku.rajkumar, Dec 01 '11 at 08:54
@Michael I am calling a stored procedure in some other server by look up method so I cant do that. — dku.rajkumar, Dec 01 '11 at 08:55
Duplicate of http://stackoverflow.com/questions/7545820/total-number-of-row-resultset-getrow-method — james.garriss, Aug 31 '13 at 14:57

Erich Kitzmueller · Accepted Answer · 2011-12-01T10:57:54.337

8

Actually, the ResultSet doesn't have a clue about the real number of rows it will return. In fact, using a hierachical query or a pipelined function, the number might as well be infinite. 10 is the suggested number of rows that the resultset should/will try to fetch in a single operation. (see comment below).

It's best to check your query, if it returns more rows than you expect.

edited Dec 01 '11 at 10:57

answered Dec 01 '11 at 08:06

Erich Kitzmueller

36,381
5
80
102

getFetchSize() is the *suggested* number of rows that the resultset should/will try to fetch in a single operation. It says nothing about the total size of the resultset or the number of rows fetched so far. – Mark Rotteveel Dec 01 '11 at 10:52
Use rs.__len__() to get the size. – thanga Mar 17 '22 at 14:32

score 5 · Answer 2 · edited Sep 12 '15 at 18:19

5

To know number of records present, try the following code

ResultSet rs =  // getting from statement

try {
        boolean b = rs.last();
        int numberOfRecords = 0;
        if(b){
            numberOfRecords = rs.getRow();
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

edited Sep 12 '15 at 18:19

Overflowh

1,103
6
18
40

answered Dec 01 '11 at 08:11

Sunil Kumar Sahoo

53,011
55
178
243

you should better remove the `int` from `int numberOfRecords = rs.getRow();` – Erich Kitzmueller Dec 01 '11 at 08:59
Anyway, if the loop didn't work out, this method will probably hang just as well. – Erich Kitzmueller Dec 01 '11 at 09:03

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

A simple getRowCount method can look like this :

private int getRowCount(ResultSet resultSet) {
   if (resultSet == null) {
    return 0;
   }
   try {
       resultSet.last();
       return resultSet.getRow();
   } catch (SQLException exp) {
       exp.printStackTrace();
   } finally {
       try {
          resultSet.beforeFirst();
       } catch (SQLException exp) {
          exp.printStackTrace();
       }
   }
   return 0;
}

Your resultSet should be scrollable to use this method.

Just looked this seems to be on similar lines on this question

C.c · Answer 4 · 2012-07-14T08:51:22.677

1

When you execute a query and get a ResultSet, I would say it is really at this moment you or even the program-self actually don't how many results will be returned, this case is very similar Oracle CURSOR, it is just declare to Oracle that you want do such a query, hence then we have to for each ResultSet to get row one by one up to the last one.

As the above guys has ready answered: rs.last will iterate to last one at this time the program has ability to totally how many rows will be returned.

edited Jul 14 '12 at 08:51

answered Dec 01 '11 at 09:51

C.c

1,905
6
30
47

I'd guess it's even worse, it will iterate through the resultset and keep all rows in memory, so if things go bad, it might crash the server by a out-of-memory condition. – Erich Kitzmueller Dec 01 '11 at 10:22

score 0 · Answer 5 · edited May 18 '13 at 13:46

0

if(res.getRow()>0)
{
     // Data present in resultset<br>
}
else
{
      //Data not present in resultset<br>
}

edited May 18 '13 at 13:46

Ben

51,770
36
127
149

answered May 18 '13 at 13:29

Susovan Kayra

1

score 0 · Answer 6 · answered Aug 29 '15 at 11:42

You can look at snippet of code below where you can find how to calculate the loaded number of records from data set. This example is working with external data set (whiich comes in json format) so you can start with yours. The necessary piece of code is placed in script of controller (this page is based on ApPML javascript and cotroller works with loaded objects of ApPML). Code in controller returns number of the loaded reocords of data set and number of fields of data model.

<!DOCTYPE html>
<html lang="en-US">
<title>Customers</title>
<style>
    body {font: 14px Verdana, sans-serif;}
    h1 { color: #996600; }
    table { width: 100%;border-collapse: collapse; }
    th, td { border: 1px solid grey;padding: 5px;text-align: left; }
    table tr:nth-child(odd) {background-color: #f1f1f1;}
</style>
<script src="http://www.w3schools.com/appml/2.0.2/appml.js"></script>
<body>
    <div appml-data="http://www.w3schools.com/appml/customers.aspx" appml-controller="LukController">
        <h1>Customers</h1>
        <p></p>
        <b>It was loaded {{totalRec}} records in total.</b>
        <p></p>
        <table>
            <tr>
                <th>Customer</th>
                <th>City</th>
                <th>Country</th>
            </tr>
            <tr appml-repeat="records">
                <td>{{CustomerName}}</td>
                <td>{{City}}</td>
                <td>{{Country}}</td>
            </tr>
        </table>
    </div>
    <script>
        function LukController($appml) {
            if ($appml.message == "loaded") {
                $appml.totalRec = Object.keys($appml.data.records).length;
            }
        }
        // *****************************************************************
        // Message Description
        //
        // ready Sent after AppML is initiated, and ready to load data.
        // loaded Sent after AppML is fully loaded, ready to display data.
        // display Sent before AppML displays a data item.
        // done     Sent after AppML is done (finished displaying).
        // submit Sent before AppML submits data.
        // error Sent after AppML has encountered an error.
        // *****************************************************************
    </script>
</body>

</html>

score 0 · Answer 7 · edited Oct 29 '15 at 19:22

I got answer:- The below are steps that you need to follow:

Make sure your are using select query(e.g select * from employee).
Don't use count query(e.g select count(*) from employee).

Then use below steps:

Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("select * from employee");
    while(rs.next()){
        rowCount++;
    }
    return rowCount;
}

where rs is object of ResultSet.

Then you will get exact number of row count.

how to find number of records in ResultSet

7 Answers7