[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6897
  • Last Modified:

Getting the number of rows returned by resultset.getFetchSize()

Hi Experts,

Just having a small problem with getting the number of rows returned from a db using the getFetchSize() ftn. Have a look @ the code below:

//variable rooms is a JTextField
//variable room_type is a JCheckBoxGroup

 public String searchs(){  
        String theText = room_type.getSelectedCheckbox().getLabel();
        try
        {     String filename = "H:/db2.mdb";
                 String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                 database+= filename.trim() + ";DriverID=22;READONLY=False}";
                 // get the connection from the DriverManager*/
                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection con = DriverManager.getConnection( database ,"","");
                  Statement stmt = con.createStatement();                  
                 
                  System.out.println("theText: "+theText);
                  String theQuery = new String ("SELECT * from RoomType WHERE Room_Type  '"             +   theText + "'" );
                  System.out.println("theQuery: "+theQuery);
               
                    ResultSet rs = stmt.executeQuery(theQuery);
                 
                    System.out.println("rs size: "+rs.getFetchSize());
                    int numResults = rs.getFetchSize();
                    System.out.println(numResults);
                    if(numResults !=0){
                        rooms.setText(String.valueOf(numResults));
                    }
                    else
                        rooms.setText("None");
                                 
       }
        catch (Exception err)
                    {
                        System.out.println("ERROR: " + err);
                    }      
           return theText;        
    }


THE PROBLEM: I always seem to be getting the value 1 for numResults (= rs.getFetchSize) and yet there are obviously more values of rows that fit the description within the database.
Below is an example of what is printed out to System:

 
theText: Single
theQuery: SELECT * from RoomType WHERE Room_Type ='Single'
rs size: 1
1

Bonus marks for a helpful response within 2 hours as I need it asap.
Thanks

TheEastern


0
TheEastern
Asked:
TheEastern
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
neonlinesCommented:
getFetchSize retrieves the number of result set rows that is the default fetch size for result sets generated from this Statement object. If this Statement object has not set a fetch size by calling the method setFetchSize, the return value is implementation-specific.

It returns the default fetch size for result sets generated from this Statement object.

to get the size of ur resultset u try with the following code

       int count = 0;
        rs.last();
        count = rs.getRow();
        rs.beforeFirst();

I think this will solve ur problem


0
 
neonlinesCommented:
Code explaination as follows

// Initialize a variable
int count = 0;

//Move the resultset to Last row
rs.last();

//Get the current row number (The first row is number 1, the second number 2, and so on)
count = rs.getRow(); // 0 if there is no current row

//Again move the resultset pointer to first row for further operation with ur resultset
rs.beforeFirst();

0
 
MogalManicCommented:
The getFetchSize() method only returns the # of rows fetched by the cursor.  For a forward only cursor(the default) there is no way to get the row count until you have retrieved ALL of the rows in the cursor.  The only way to get a row count of a result set is to go to the LAST row and get the row number.  This can be done using a TYPE_SCROLL_INSENSITIVE  or TYPE_SCROLL_SENSITIVE  cursor like this:

                  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);                  
                 
                  System.out.println("theText: "+theText);
                  String theQuery = new String ("SELECT * from RoomType WHERE Room_Type  '"             +   theText + "'" );
                  System.out.println("theQuery: "+theQuery);
               
                    ResultSet rs = stmt.executeQuery(theQuery);
                 
                    rs.last();
                    int numResults = rs.getRow();
                    System.out.println(numResults);
                    if(numResults !=0){
                        rooms.setText(String.valueOf(numResults));
       
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TheEasternAuthor Commented:
Guys,
Thanks for the quick responses.
neonlines :
This is the output I'm getting from the code:

this is a single room
theText: Single
theQuery: SELECT * from RoomType WHERE Room_Type ='Single'

ERROR: java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY


MogalManic:
Gonna try your code now. will get back in 5

TheEastern
0
 
TheEasternAuthor Commented:
You guys are stars!

MagalManic:
Your code is working like a charm

neonlines:
Gonna give you some points coz you pointed me in the right direction, plus some of the code you provided is contained in the accepted answer.

Increased the point value as promised.

Cheers,

TheEasten
0
 
CEHJCommented:
Your query will still use cursor-like functionality and will not perform well probably. If all you want is the row count and don't wish to retrieve rows then why not do:

String theQuery = "SELECT Count(Room_Type) from RoomType WHERE Room_Type = '" +   theText + "'" ;

?
0
 
CEHJCommented:
?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now