Total Number of Row Resultset getRow Method

Question

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

User · Answer

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:

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.
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.
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.

User · Answer

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 lt Operations gt  list   throws SQLException          Declare Connection  Statement  ResultSet  List lt Operation gt        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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

BalusC s answer is right  but I have to mention according to the user instance variable such as   rSet last     total   rSet getRow      and then which you are missing   rSet beforeFirst      the remaining code is same you will get your desire result

User · Answer

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

User · Answer

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    ResultSet last   ResultSet getRow   to get the total number of rows ResultSet beforeFirst   Process the ResultSet normally

[java] Total Number of Row Resultset getRow Method

Examples related to java

Examples related to jdbc