How do I get the row count in JDBC?

Question

I've executed a JDBC query to obtain a resultset. Before iterating over it, I'd like to quickly find out how many rows were returned. How can I do this with high performance?

I'm using Java 6, Oracle 11g, and the latest Oracle JDBC drivers.

score 34 · Accepted Answer · answered Oct 02 '08 at 14:34

You're going to have to do this as a separate query, for example:

SELECT COUNT(1) FROM table_name

Some JDBC drivers might tell you but this is optional behaviour and, more to the point, the driver may not know yet. This can be due to how the query is optimised eg two example execution strategies in Oracle are to get all rows as quickly as possible or to get the first row as quickly as possible.

If you do two separate queries (one a count and the other the query) then you'll need to do them within the same transaction. This will work well on Oracle but can be problematic on other databases (eg SQL Server will either show you uncommitted data or block on an external uncommitted update depending on your isolation level whereas Oracle supports an isolation level that gives you a transactionally consistent view of the data without blocking on external updates).

Normally though it doesn't really matter how many rows there are. Typically this sort of query is either batch processed or paged and either way you have progress information in the form of rows loaded/processed and you can detect the end of the result set (obviously).

Good answer, though I don't understand the final comment about having progress information. Where is this coming from? — Aktau, Nov 08 '11 at 14:48
This requires another query. If you have a scrollable resultset there are better options in other answers. — JDuarteDJ, Dec 10 '15 at 14:34
Is it only me, or does this accepted answer address a completely different question? The given query returns row count for a _table_, while @cletus wanted to know "how many rows were returned" "before iterating over it" (a `ResultSet`) — GreenhouseVeg, May 20 '19 at 12:58

thezar · Answer 2 · 2012-04-16T09:03:26.780

23

ResultSet rs = stmt.executeQuery(sql);
int rowCount = rs.last() ? rs.getRow() : 0; // Number of rows in result set. Don't forget to set cyrsor to beforeFirst() row! :)

edited Apr 16 '12 at 09:03

answered Apr 13 '12 at 10:33

thezar

1,278
13
17

6

Works nice. You should add that you need a scrollable result set for this. – Sebastian vom Meer Dec 04 '12 at 09:18
Support previous comment plus I think this should the the correct answer, as it provides a shorter and more sirect way to do it! – JDuarteDJ Dec 10 '15 at 14:34
1

@Winter It moves to the last row in the result set (`rs.last()`), then gets the row id (not to be confused with Oracle's ROWID, but the number of the row in this resultset, which starts at 1). Do not forget to rewind the resultset with `rs.beforeFirst()`. – Koshinae Jun 08 '17 at 10:58

score 20 · Answer 3 · answered Oct 02 '08 at 14:26

Short answer: you can't.

Long answer: you can't, partly because the database may be lazily evaluating the query, only returning rows as you ask for them.

EDIT: Using a scrollable ResultSet you can :)

Indeed, I asked this very question in the Java databases newsgroup a long time ago (back in 2001!) and had some helpful responses.

score 19 · Answer 4 · edited Jul 18 '13 at 08:33

19

To get the number of rows from JDBC:

ResultSet rs = st.executeQuery("select count(*) from TABLE_NAME");
rs.next();
int count = rs.getInt(1);

edited Jul 18 '13 at 08:33

Jason Sundram

12,225
19
71
86

answered Jul 18 '13 at 08:11

user2594537

231
2
2

7

nobody is asking for how many rows in a table, the question is about how many rows in resultset. – ernesto Jul 01 '15 at 09:22

score 4 · Answer 5 · edited Dec 21 '19 at 08:52

4

If your driver supports it(!), you can call ResultSet.afterLast() ResultSet.getRow() ResultSet.beforeFirst(). Performance may or may not be good.

A better solution would be to rewrite your algorithm not to require the size up front.

edited Dec 21 '19 at 08:52

Yousha Aleayoub

4,532
4
53
64

answered Oct 02 '08 at 14:30

Tom Hawtin - tackline

145,806
30
211
305

score 2 · Answer 6 · answered Jul 26 '18 at 11:08

2

Without ternary operator

rs.last();  // Moves the cursor to the last row in this ResultSet object.
int rowCount = rs.getRow();  //Retrieves the current row number.
rs.beforeFirst(); //Moves the cursor to the front of this ResultSet object,just before the first row.

With ternary operator one line

int rowCount = rs.last() ? rs.getRow() : 0; 
rs.beforeFirst();

answered Jul 26 '18 at 11:08

v8-E

1,077
2
14
21

This should work but it's worth mentioning that moving the cursor requires a result set of the type different than [ResultSet.TYPE_FORWARD_ONLY](https://docs.oracle.com/javase/10/docs/api/java/sql/ResultSet.html#TYPE_FORWARD_ONLY) – GreenhouseVeg May 20 '19 at 13:03

score 0 · Answer 7 · edited Dec 23 '15 at 07:58

0

Code:

//Create a Statement class to execute the SQL statement
Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS COUNT FROM
TABLENAME");

 while(rs.next()) {
    System.out.println("The count is " + rs.getInt("COUNT"));
 }

 //Closing the connection
 con.close();

edited Dec 23 '15 at 07:58

K DawG

13,287
9
35
66

answered Apr 28 '13 at 21:54

MindBrain

7,398
11
54
74

This should be the answer. Works perfectly. – Toby Mellor Jun 14 '15 at 08:40
2

The question is about how many rows in resultset. Not row count of table – ernesto Jul 01 '15 at 09:24
it is a one way around but not the exact what needed. – Pardeep Sharma Aug 21 '19 at 11:01

How do I get the row count in JDBC?

7 Answers7

Linked

Related