Using Resultset in Java Program

Question

Resultset rs=stmt.executeQuery("select count(*) from feedsca group by score order by score");

Using the above java code above, am retrieving the counts of rows from the table named feedsCA.

While trying to retrieving the counts using rs.getInt(1),rs.getInt(2),rs.getInt(3), I end with an error saying as below,

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The result set has no current row.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetHasCurrentRow(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getterGetColumn(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
    at SimpleMail.main(SimpleMail.java:151)

UPDATE:

The above exception has been resolved.

But I get the following exception, for which I dont know the reason. Please advise.

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyValidColumnIndex(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getterGetColumn(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
    at SimpleMail.main(SimpleMail.java:152)

This is how I have updated my program. Find me a logical way as I can understand well that the loop below will not work as required.

rs=stmt.executeQuery("select count(*) from feedsca group by score order by score");
while(rs.next()){
pw.printf(rowFormat, rs.getLong(1),"0",rs.getLong(2),rs.getLong(3));}

Answer 1

You need to use one of the methods to move the ResultSet cursor to a row before using the getxxx methods. i.e. rs.next(), rs.first() or rs.last(). These methods return true if a valid row has been located so a typical pattern is

if (rs.first()) {
  int field1 = rs.getInt(1); 
  // other columns
}

or for a query that returns multiple rows:

while (rs.next()) {
  int field1 = rs.getInt(1);
  // other columns
}

Answer 2

You need to call rs.next() before accessing the first row.

Typically, you will iterate over the result set like this:

ResultSet rs = ...;
while (rs.next()) {
  ...
}

Update: Note that SELECT COUNT(*) ... returns only one field per row, which is the count. You may have several rows, but each row will have only one field, which has index 1. You need to iterate through the rows to get all the counts:

while (rs.next()) {
  System.out.println(rs.getInt(1));
}

Yet another update: It's bad to assume that your query will always return only 3 rows. However, if you are absolutely sure of this, then you can just call next 3 times manually:

long l1, l2, l3;
rs.next();
l1 = rs.getLong(1);
rs.next();
l2 = rs.getLong(1);
rs.next();
l3 = rs.getLong(1);
pw.printf(rowFormat, l1,"0",l2,l3);

Answer 3

As far as my knowledge, your query will only get one row and column i.e., the total number of rows that your query returns.

Say for example :

Select count(*) from emp; Generally this query will return a value 14.

so your java code

if(rs.next())
    rs.getInt(1); 

will return only one value i.e., 14

So, How can you access rs.getString(2). This will automatically throws an exception which you got in the second case.

Answer 4

You have to move the cursor of the result set to a row - either by resultSet.first() or by resultSet.next(). Initially the cursor is pointing before the first row, hence your exception.

When you want to iterate the ResultSet:

while(rs.next()) {
    ...
}

Update: For your second problem - (as noted by Casablanca) your query seems to return only one column, and you are asking for a 2nd and 3rd - and they are not found. Note that in rs.getX(idx) idx is the column, not the row.