Get Number of Rows returned by ResultSet in Java

Question

I have used a ResultSet that returns certain number of rows  My code is something like this   ResultSet res   getData    if  res next          System out println  No Data Found      while res next             code to display the data in the table      Is there any method to check the number of rows returned by the ResultSet   Or do I have to write my own

User · Answer

res next   method will take the pointer to the next row  and in your code you are using it twice  first for the if condition  cursor moves to first row  then for while condition  cursor moves to second row    So when you access your results  it starts from second row  So shows one row less in results   you can try this     if  res next          System out println  No Data Found        else      do           your code            while res next

User · Answer

You could count with sql and retrieve the answer from the resultset like so   Statment stmt   conn createStatement ResultSet TYPE SCROLL INSENSITIVE                                        ResultSet CONCUR READ ONLY   ResultSet ct   stmt executeQuery  SELECT COUNT    FROM  table name     if ct next        td setTotalNumRows ct getInt 1        Here I m counting everything but you can easily modify the SQL to count based on a criteria

User · Answer

rs last            int rows   rs getRow            rs beforeFirst

User · Answer

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      Just to be aware that this method will need a scroll sensitive resultSet  so while creating the connection you have to specify the scroll option  Default is FORWARD and using this method will throw you exception

User · Answer

In my case  I needed to get the total rows from a ResultSet and also access the ResultSet values   if the total rows did not reach the limit of an XLS file   For that  I had to make two adjustments to my code   1  Change in object construction PreparedStatement  A default ResultSet object has a cursor that moves forward only  Thus  you can iterate through it only once and only from the first row to the last row  It is possible to produce ResultSet objects that are scrollable  The following code fragment illustrates how to make a result set that is scrollable and insensitive to updates by others    PreparedStatement ps   connection prepareStatement sql  ResultSet TYPE SCROLL INSENSITIVE   ResultSet CONCUR READ ONLY     2  Get total rows  The following code fragment illustrates how   ResultSet rs   ps executeQuery    rs last    int totalRowsResult   rs getRow      PS  If the number of records of the query result is too large  you may run out of memory on the Java server by getting an exception  java lang OutOfMemoryError  Java heap space  This exception will occur when executing the rs last    method  3  Access again the ResultSet and you don t get the message  exhaused result set  So  vou need reset the result set to the top  using rs first   or rs absolute 1   The following code fragment illustrates how   rs first    System out println rs getString 1

User · Answer

First  you should create Statement which can be move cursor by command   Statement stmt   con createStatement ResultSet TYPE SCROLL INSENSITIVE  ResultSet CONCUR READ ONLY     Then retrieve the ResultSet as below   ResultSet rs   stmt executeQuery         Move cursor to the latest row and get it   if  rs last          int rows   rs getRow           Move to beginning     rs beforeFirst                Then rows variable will contains number of rows returned by sql

User · Answer

Another way to differentiate between 0 rows or some rows from a ResultSet   ResultSet res   getData     if  res isBeforeFirst                res isBeforeFirst   is true if the cursor                                      is before the first row   If res contains                                      no rows  rs isBeforeFirst   is false       System out println  0 rows      else      while res next                code to display the rows in the table            If you must know the number of rows given a ResultSet  here is a method to get it   public int getRows ResultSet res       int totalRows   0      try           res last            totalRows   res getRow            res beforeFirst               catch Exception ex             return 0            return totalRows

User · Answer

You can use res previous   as follows   ResulerSet res   getDate    if  res next          System out println  No Data Found       else       res previous        while res next              code to display the data in the table

User · Answer

Statement st   con createStatement ResultSet TYPE SCROLL SENSITIVE ResultSet CONCUR UPDATABLE   ResultSet rs st executeQuery  select   from emp where deptno 31    rs last    System out println  NoOfRows    rs getRow       first line of code says that we can move anywhere in the resultset  either to first row or last row or before first row without the need to traverse row by row starting from first row which is time taking  second line of code fetches the records matching the query here i am assuming  25 records   third line of code moves cursor to last row and final line of code gets the current row number which is 25 in my case  if there are no records  rs last returns 0 and getrow moves cursor to before first row hence returning negative value indicates no records in db

User · Answer

If your query is something like this SELECT Count    FROM tranbook  then do this rs next    System out println rs getInt  Count

User · Answer

this my solution   ResultSet rs Statement executeQuery  query         int rowCount 0        if   rs isBeforeFirst                         System out println  No DATA               else               while  rs next                      rowCount                 System out println  data1 data2 data3   etc                                System out println rowCount               rowCount 0              rs close                Statement close

User · Answer

You may think JDBC is a rich API and ResultSet has got so many methods then why not just a getCount   method  Well  For many databases e g  Oracle  MySQL and SQL Server  ResultSet is a streaming API  this means that it does not load  or maybe even fetch  all the rows from the database server   By iterating to the end of the ResultSet you may add significantly to the time taken to execute in certain cases   Btw  if you have to there are a couple of ways to do it e g  by using ResultSet last   and ResultSet getRow   method  that s not the best way to do it but it works if you absolutely need it   Though  getting the column count from a ResultSet is easy in Java  The JDBC API provides a ResultSetMetaData class which contains methods to return the number of columns returned by a query and hold by ResultSet

User · Answer

You could load the ResultSet into a TableModel  then create a JTable that uses that TableModel  and then use the table getRowCount   method  If you are going to display the result of the query  you have to do it anyway   ResultSet resultSet  resultSet   doQuery something  somethingelse   KiransTableModel myTableModel   new KiransTableModel resultSet   JTable table   new JTable KiransTableModel   int rowCount  rowCount   table getRowCount

User · Answer

You could use a do     while loop instead of a while loop  so that rs next   is called after the loop is executed  like this   if   rs next                                   if rs next   returns false                                                then there are no rows      System out println  No records found       else       do              Get data from the current row and use it       while  rs next         Or count the rows yourself as you re getting them   int count   0   while  rs next            count         Get data from the current row and use it    if  count    0        System out println  No records found

[java] Get Number of Rows returned by ResultSet in Java

Examples related to java

Examples related to resultset