Getting the count of the resultset values

Question

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.

Answer 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

Answer 2

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));
        }