CODEWITHSUNDEEP
javajdbcresultset
dku.rajkumar
dku.rajkumar

Reputation: 18588

how to find number of records in ResultSet

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.

Upvotes: 5

Views: 27293

Answers (7)

Ashok Baghele
Ashok Baghele

Reputation: 1

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

  1. Make sure your are using select query(e.g select * from employee).
  2. 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.

Upvotes: 0

Sunil Kumar Sahoo
Sunil Kumar Sahoo

Reputation: 53687

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();
    }

Upvotes: 4

LukCAD
LukCAD

Reputation: 9

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>

Upvotes: 0

Susovan Kayra
Susovan Kayra

Reputation: 1

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

Upvotes: 0

C.c
C.c

Reputation: 1945

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.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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.

Upvotes: 8

mprabhat
mprabhat

Reputation: 20323

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

Upvotes: 3

Related Questions