How to get row count using ResultSet in Java

Question

I m trying to create a simple method that receives a ResultSet as a parameter and returns an int that contains the row count of the ResultSet  Is this a valid way of doing this or not so much   int size   0      try           while rs next                 size                        catch Exception ex            System out println  ------------------Tablerize getRowCount-----------------            System out println  Cannot get resultSet row count      ex           System out println  --------------------------------------------------------            I tried this   int size   0  try       resultSet last        size   resultSet getRow        resultSet beforeFirst      catch Exception ex        return 0    return size    But I got an error saying com microsoft sqlserver jdbc SQLServerException  The requested operation is not supported on forward only result sets   Thanks in advance for the pointers

User · Answer

your sql Statement creating code may be like

statement = connection.createStatement();

To solve "com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets" exception change above code with

statement = connection.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE, 
    ResultSet.CONCUR_READ_ONLY);

After above change you can use

int size = 0;
try {
    resultSet.last();
    size = resultSet.getRow();
    resultSet.beforeFirst();
}
catch(Exception ex) {
    return 0;
}
return size;

to get row count

User · Answer

If you have access to the prepared statement that results in this resultset  you can use  connection prepareStatement sql     ResultSet TYPE SCROLL INSENSITIVE     ResultSet CONCUR READ ONLY     This prepares your statement in a way that you can rewind the cursor  This is also documented in the ResultSet Javadoc  In general  however  forwarding and rewinding cursors may be quite inefficient for large result sets  Another option in SQL Server would be to calculate the total number of rows directly in your SQL statement   SELECT my table    count    over    total rows FROM my table WHERE

User · Answer

From http   docs oracle com javase 1 4 2 docs api java sql ResultSetMetaData html   ResultSet rs   stmt executeQuery  SELECT a  b  c FROM TABLE2     ResultSetMetaData rsmd   rs getMetaData     int numberOfColumns   rsmd getColumnCount      A ResultSet contains metadata which gives the number of rows

User · Answer

Statement s   cd createStatement    ResultSet r   s executeQuery  SELECT COUNT    AS rowcount FROM FieldMaster    r next    int count   r getInt  rowcount    r close    System out println  MyTable has     count     row s        Sometimes JDBC does not support following method gives Error like  TYPE FORWARD ONLY  use this solution  Sqlite does not support in JDBC   resultSet last    size   resultSet getRow    resultSet beforeFirst      So at that time use this solution   Thanks

User · Answer

If you have table and storing the ID as primary and auto increment then this will work   Example code to get the total row count http   www java2s com Tutorial Java 0340  Database GettheNumberofRowsinaDatabaseTable htm  Below is code  import java sql Connection  import java sql DriverManager  import java sql ResultSet  import java sql ResultSetMetaData  import java sql Statement   public class Main     public static void main String   args  throws Exception       Connection conn   getConnection        Statement st   conn createStatement ResultSet TYPE SCROLL INSENSITIVE          ResultSet CONCUR UPDATABLE        st executeUpdate  create table survey  id int name varchar 30           st executeUpdate  insert into survey  id name   values  1  nameValue          st executeUpdate  insert into survey  id name   values  2 null         st executeUpdate  insert into survey  id name   values  3  Tom          st   conn createStatement        ResultSet rs   st executeQuery  SELECT   FROM survey         rs   st executeQuery  SELECT COUNT    FROM survey           get the number of rows from the result set     rs next        int rowCount   rs getInt 1       System out println rowCount        rs close        st close        conn close            private static Connection getConnection   throws Exception       Class forName  org hsqldb jdbcDriver        String url    jdbc hsqldb mem data tutorial        return DriverManager getConnection url   sa

User · Answer

Others have already answered how to solve your problem  so I won t repeat what has already been said  but I will says this  you should probably figure out a way to solve your problems without knowing the result set count prior to reading through the results    There are very few circumstances where the row count is actually needed prior to reading the result set  especially in a language like Java   The only case I think of where a row count would be necessary is when the row count is the only data you need in which case a count query would be superior    Otherwise  you are better off using a wrapper object to represent your table data  and storing these objects in a dynamic container such as an ArrayList   Then  once the result set has been iterated over  you can get the array list count   For every solution that requires knowing the row count before reading the result set  you can probably think of a solution that does so without knowing the row count before reading without much effort   By thinking of solutions that bypass the need to know the row count before processing  you save the ResultSet the trouble of scrolling to the end of the result set  then back to the beginning  which can be a VERY expensive operation for large result sets      Now of course I m not saying there are never situations where you may need the row count before reading a result set   I m just saying that in most circumstances  when people think they need the result set count prior to reading it  they probably don t  and it s worth taking 5 minutes to think about whether there is another way   Just wanted to offer my 2 cents on the topic

User · Answer

Do a SELECT COUNT    FROM     query instead

User · Answer

Your function will return the size of a ResultSet  but its cursor will be set after last record  so without rewinding it by calling beforeFirst    first   or previous   you won t be able to read its rows  and rewinding methods won t work with forward only ResultSet  you ll get the same exception you re getting in your second code fragment

User · Answer

The ResultSet has it s methods that move the Cursor back and forth depending on the option provided  By default  it s forward moving TYPE FORWARD ONLY ResultSet type    Unless CONSTANTS indicating Scrollability and Update of ResultSet properly  you might end up getting an error  E g  beforeLast   This method has no effect if the result set contains no rows  Throws Error if it s not TYPE FORWARD ONLY   The best way to check if empty rows got fetched --- Just to insert new record after checking non-existence  if  rs next           Do nothing   else     No records fetched      See here

User · Answer

Following two options worked for me   1  A function that returns the number of rows in your ResultSet   private int resultSetCount ResultSet resultSet  throws SQLException      try          int i   0          while  resultSet next                  i                      return i        catch  Exception e          System out println  Error getting row count           e printStackTrace              return 0      2  Create a second SQL statement with the COUNT option

User · Answer

Here s some code that avoids getting the count to instantiate an array  but uses an ArrayList instead and just before returning converts the ArrayList to the needed array type   Note that Supervisor class here implements ISupervisor interface  but in Java you can t cast from object    that ArrayList s plain toArray   method returns  to ISupervisor    as I think you are able to do in C    so you have to iterate through all list items and populate the result array          Get Supervisors for given program id     param connection     param programId     return ISupervisor       throws SQLException     public static ISupervisor   getSupervisors Connection connection  String programId    throws SQLException     ArrayList supervisors   new ArrayList       PreparedStatement statement   connection prepareStatement SQL GET SUPERVISORS     try       statement setString SQL GET SUPERVISORS PARAM PROGRAMID  programId       ResultSet resultSet   statement executeQuery           if  resultSet    null          while  resultSet next              Supervisor s   new Supervisor            s setId resultSet getInt SQL GET SUPERVISORS RESULT ID            s setFirstName resultSet getString SQL GET SUPERVISORS RESULT FIRSTNAME            s setLastName resultSet getString SQL GET SUPERVISORS RESULT LASTNAME            s setAssignmentCount resultSet getInt SQL GET SUPERVISORS RESULT ASSIGNMENT COUNT            s setAssignment2Count resultSet getInt SQL GET SUPERVISORS RESULT ASSIGNMENT2 COUNT            supervisors add s                 resultSet close              finally       statement close           int count   supervisors size      ISupervisor   result   new ISupervisor count     for  int i 0  i lt count  i        result i     ISupervisor supervisors get i     return result

User · Answer

I just made a getter method   public int getNumberRows        try         statement   connection creatStatement           resultset   statement executeQuery  your query here           if resultset last               return resultset getRow             else              return 0    just cus I like to always do some kinda else statement                 catch  Exception e          System out println  Error getting row count           e printStackTrace              return 0

User · Answer

Most drivers support forward only resultset - so method like last  beforeFirst etc are not supported   The first approach is suitable if you are also getting the data in the same loop - otherwise the resultSet has already been iterated and can not be used again   In most cases the requirement is to get the number of rows a query would return without fetching the rows  Iterating through the result set to find the row count is almost same as processing the data   It is better to do another count    query instead

[java] How to get row count using ResultSet in Java?

Examples related to java

Examples related to jdbc

Examples related to resultset