[java] Total Number of Row Resultset getRow Method

Read the Following Code:

public class selectTable {

public static ResultSet rSet;
public static int total=0;
public static ResultSet onLoad_Opetations(Connection Conn, int rownum,String sql)
{
int rowNum=rownum;
int totalrec=0;
try
{
   Conn=ConnectionODBC.getConnection();
   Statement stmt = Conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);        
    String sqlStmt = sql;        
    rSet = stmt.executeQuery(sqlStmt);
    total = rSet.getRow();        
    }
    catch(Exception e)
    {
        System.out.println(e.getMessage());
    }
    System.out.println("Total Number of Records="+totalrec);
    return rSet;
    }

}

The folowing code dos't show actual total:

total = rSet.getRow();

my jTable display 4 record in jTable but total = 0; when I evaluate through debug, it shows:

total=(int)0; 

rather than total=(int)4 And if I use

rSet=last(); above from the code  total = rSet.getRow();

then total shows accurate value = 4 but rSet return nothing. then jTable is empty. Update me!

The best way to get number of rows from resultset is using count function query for database access and then rs.getInt(1) method to get number of rows. from my code look it:

    String query = "SELECT COUNT() FROM table";
ResultSet rs = new DatabaseConnection().selectData(query);
rs.getInt(1);

this will return int value number of rows fetched from database. Here DatabaseConnection().selectData() is my code for accessing database. I was also stuck here but then solved...

The getRow() method will always yield 0 after a query:

ResultSet.getRow()

Retrieves the current row number.

Second, you output totalrec but never assign anything to it.

As others have answered there is no way to get the count of rows without iterating till the end. You could do that, but you may not want to, note the following points:

1. For a many RDBMS systems ResultSet is a streaming API, this means that it does not load (or maybe even fetch) all the rows from the database server. See this question on SO. By iterating to the end of the ResultSet you may add significantly to the time taken to execute in certain cases.

2. A default ResultSet object is not updatable and has a cursor that moves forward only. I think this means that unless you execute the query with ResultSet.TYPE_SCROLL_INSENSITIVE rSet.beforeFirst() will throw SQLException. The reason it is this way is because there is cost with scrollable cursor. According to the documentation, it may throw SQLFeatureNotSupportedException even if you create a scrollable cursor.

3. Populating and returning a List<Operations> means that you will also need extra memory. For very large resultsets this will not work at all.

So the big question is which RDBMS?. All in all I would suggest not logging the number of records.

I have solved that problem. The only I do is:

private int num_rows;

And then in your method using the resultset put this code

while (this.rs.next())
{

    this.num_rows++;
}

That's all

The getRow() method retrieves the current row number, not the number of rows. So before starting to iterate over the ResultSet, getRow() returns 0.

To get the actual number of rows returned after executing your query, there is no free method: you are supposed to iterate over it.

Yet, if you really need to retrieve the total number of rows before processing them, you can:

1. ResultSet.last()
2. ResultSet.getRow() to get the total number of rows
3. ResultSet.beforeFirst()
4. Process the ResultSet normally

You can't get the number of rows returned in a ResultSet without iterating through it. And why would you return a ResultSet without iterating through it? There'd be no point in executing the query in the first place.

A better solution would be to separate persistence from view. Create a separate Data Access Object that handles all the database queries for you. Let it get the values to be displayed in the JTable, load them into a data structure, and then return it to the UI for display. The UI will have all the information it needs then.

You need to call ResultSet#beforeFirst() to put the cursor back to before the first row before you return the ResultSet object. This way the user will be able to use next() the usual way.

resultSet.last();
rows = resultSet.getRow();
resultSet.beforeFirst();
return resultSet;

However, you have bigger problems with the code given as far. It's leaking DB resources and it is also not a proper OOP approach. Lookup the DAO pattern. Ultimately you'd like to end up as

public List<Operations> list() throws SQLException {
    // Declare Connection, Statement, ResultSet, List<Operation>.

    try {
        // Use Connection, Statement, ResultSet.

        while (resultSet.next()) {
            // Add new Operation to list.
        }
    } finally {
        // Close ResultSet, Statement, Connection.
    }

    return list;
}

This way the caller has just to use List#size() to know about the number of records.

One better way would be to use SELECT COUNT statement of SQL.

Just when you need the count of number of rows returned, execute another query returning the exact number of result of that query.

 try
{
   Conn=ConnectionODBC.getConnection();
   Statement stmt = Conn.createStatement();        
    String sqlStmt = sql;        
    String sqlrow = SELECT COUNT(*) from (sql) rowquery;
    String total = stmt.executeQuery(sqlrow);
    int rowcount = total.getInt(1);
    }