CODEWITHSUNDEEP
javajdbc
user225269
user225269

Reputation: 10913

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

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

Upvotes: 1

Views: 17365

Answers (5)

Anptk
Anptk

Reputation: 1123

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

Upvotes: 1

BalusC
BalusC

Reputation: 1108537

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:

Upvotes: 4

Dead Programmer
Dead Programmer

Reputation: 12575

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>';

Upvotes: -1

Stephen C
Stephen C

Reputation: 718678

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.)

Upvotes: 2

Bala R
Bala R

Reputation: 108937

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.

Upvotes: 2

Related Questions