-1

I need your assistant and help in getting the count of the values which are available in the resultset. The resultset is having 22 records and in the below code, it is printing the count starting from 1 to 23. Is there any way to get the total no. of records as 22 (one value only) or by subtracting the last value from the first value?

My code is below:

while (rs.next())
        {

        count=0;
        name1=rs.getString("name");  
                    out.println(rs.getRow());

                 }

I tried to use rs.first() and rs.last() and it produced the below error:

java.sql.SQLException: Invalid operation for forward only resultset : first

I tried to add the arguments to the createstatement, but the same exception is there:

 conn.createStatement(rs.TYPE_SCROLL_INSENSITIVE, rs.CONCUR_READ_ONLY);

java.sql.SQLException: Invalid operation for forward only resultset : last

Please help me in the above issue.

Yousha Aleayoub
  • 4,532
  • 4
  • 53
  • 64
user3407440
  • 183
  • 1
  • 2
  • 15

2 Answers2

1

Try this:

int rowcount = 0;
if (rs.last()) {
  rowcount = rs.getRow();
  rs.beforeFirst(); 
}

EDIT:

You need to change the statement of your query like this so that you can use the rs.last()

Statement stmt = con.createStatement(rs.TYPE_SCROLL_INSENSITIVE);
ResultSet rs = stmt.executeQuery("Your query");

Check Oracle Docs

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • why not resultSet.getRow()? – Nabin Aug 24 '14 at 11:37
  • @Nabin:- Thats what I used ;) – Rahul Tripathi Aug 24 '14 at 11:38
  • i mean, why not just that? – Nabin Aug 24 '14 at 11:39
  • @Nabin:- You can but it is a good practice to use it like this! – Rahul Tripathi Aug 24 '14 at 11:40
  • 1
    It didn't solve the issue, I got the this error: java.sql.SQLException: Invalid operation for forward only resultset : last – user3407440 Aug 24 '14 at 11:40
  • @R.T. Please explain the issue mentioned by user3407440 – Nabin Aug 24 '14 at 11:42
  • 2
    @user3407440:- ResultSet.last() and other "absolutely-indexed" query operations are only available when the result set is scrollable. You need to change the Statement of your query like this: `Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE );` – Rahul Tripathi Aug 24 '14 at 11:43
  • @Nabin:- Hope that helps! :) – Rahul Tripathi Aug 24 '14 at 11:46
  • @R.T I got this error:cannot resolve symbol symbol : method createStatement (int) location: interface java.sql.Connection st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE); – user3407440 Aug 24 '14 at 11:53
  • 1
    @user3407440 the [`createStatement`](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createStatement(int,%20int)) call takes at least two arguments see the [documentation](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createStatement(int,%20int)) `conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet. ResultSet.CONCUR_READ_ONLY);` – Alex Beggs Aug 24 '14 at 12:08
  • @Beggs I got the exception: java.sql.SQLException: Invalid operation for forward only resultset : last – user3407440 Aug 25 '14 at 04:55
  • @user3407440 did you change your code from the default conn.createStatement() to the code above? And which database and driver are you using? – Alex Beggs Aug 25 '14 at 13:31
1

You can use MySQL's count(*)

  try{
      Statement stmt = connection.createStatement();
      String selectquery = "select count(*) from YourTable";
      ResultSet rs = stmt.executeQuery(selectquery);
      rs.next();          
      System.out.println("Amount of rows :" + rs.getInt(1));
    }

If you wanna count rows based on "name", then 

try{
          Statement stmt = connection.createStatement();
          String selectquery = "select count(name) from YourTable";
          ResultSet rs = stmt.executeQuery(selectquery);
          rs.next();          
          System.out.println("Amount of rows :" + rs.getInt(1));
        }
Fevly Pallar
  • 3,059
  • 2
  • 15
  • 19