How to get the size of a java.sql.ResultSet?

Question

I want to get the size of the ResultSet inside the while loop.

Tried the code below, and I got the results that I want. But it seems to be messing up with result.next() and the while loop only loops once if I do this. What's the proper way of doing this?

result.first();
while (result.next()){

    System.out.println(result.getString(2));
    System.out.println("A. " + result.getString(5) + "\n" + "B. " + result.getString(6) + "\n" + "C. " + result.getString(7) + "\n" + "D. " + result.getString(8));
    System.out.println("Answer: ");
    answer = inputquiz.next();

    result.last();

    if (answer.equals(result.getString(10))) {
        score++;
        System.out.println(score + "/" + result.getRow());
    } else {
        System.out.println(score + "/" + result.getRow());
    }
}

Answer 1

What's the proper way of doing this?

Map it to a List<Entity>. Since your code is far from self-documenting (you're using indexes instead of column names), I can't give a well suited example. So I'll take a Person as example.

First create a javabean class representing whatever a single row contains.

public class Person {
    private Long id;
    private String firstName;
    private String lastName;
    private Date dateOfBirth;

    // Add/generate c'tors/getters/setters/equals/hashcode and other boilerplate.
}

(a bit decent IDE like Eclipse can autogenerate them)

Then let JDBC do the following job.

List<Person> persons = new ArrayList<Person>();

while (resultSet.next()) {
    Person person = new Person();
    person.setId(resultSet.getLong("id"));
    person.setFirstName(resultSet.getString("fistName"));
    person.setLastName(resultSet.getString("lastName"));
    person.setDataOfBirth(resultSet.getDate("dateOfBirth"));
    persons.add(person);
}

// Close resultSet/statement/connection in finally block.

return persons;

Then you can just do

int size = persons.size();

And then to substitute your code example

for (int i = 0; i < persons.size(); i++) {
    Person person = persons.get(i);
    System.out.println(person.getFirstName());
    int size = persons.size(); // Do with it whatever you want.
}

See also:

• How to check if there is zero-or-one result or one-or-more results and their size

Answer 2

you could do result.last(); and call result.getRow(); (which retrieves the current row number) to get count. but it'll have load the all the rows and if it's a big result set, it might not be very efficient. The best way to go about is to do a SELECT COUNT(*) on you query and get the count like it's demonstrated in this post, beforehand.

Answer 3

This is a tricky question.

1. Normally, result.last() scrolls to the end of the ResultSet, and you can't go back.

2. If you created the statement using one of the createStatement or prepareStatement methods with a "resultSetType" parameter, and you've set the parameter to ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE, then you can scroll the ResultSet using first() or relative() or some other methods.

However, I'm not sure if all databases / JDBC drivers support scrollable result sets, and there are likely to be performance implications in doing this. (A scrollable result set implies that either the database or the JVM needs to buffer the entire resultset somewhere ... or recalculate it ... and that's expensive for a large resultset.)

Answer 4

The way of getting size of ResultSet, No need of using ArrayList etc

int size =0;  
if (rs != null)   
{  
rs.beforeFirst();  
 rs.last();  
size = rs.getRow();
}

Now You will get size, And if you want print the ResultSet, before printing use following line of code too,

rs.beforeFirst();  

Answer 5

There are also another way to get the count from DB.

Note : This column gets updated when DBA'S do realtime statistics

select num_rows from all_Tables where table_name ='<TABLE_NAME>';