Correct way to find rowcount in Java JDBC

Question

I have tried different ways to get the row count in java JDBC, nut none seemed to be giving the correct result. Is there anything wrong that I am doing ?

Even though the customer table is empty and I should be getting the rowcount as 0, I don't understand why I get a non zero rowcount value.

Method 1 -

query = "SELECT * FROM customer WHERE username ='"+username+"'";
rs = stmt.executeQuery(query);
ResultSetMetaData metaData = rs.getMetaData();
rowcount = metaData.getColumnCount();

Method 2 -

query = "SELECT * FROM customer WHERE username ='"+username+"'";
rs = stmt.executeQuery(query);
rowcount = rs.last() ? rs.getRow() : 0;

You should execute a `COUNT` statement: `String query = "SELECT COUNT(*) FROM customer WHERE username =?"; stmt.setString(1, username); rs = stmt.executeQuery(query);` — Luiggi Mendoza, Oct 27 '12 at 18:43
Method 1 gets the number of columns - why do you think that might work? — mmmmmm, Oct 27 '12 at 18:46
Side question - if you are going to use the data from the original non-aggregated query, is it better to do a second query to get the count, or is it better to either loop through and count, or jump to the last row and check getRow()? Seems like a second query to get count is better, per - https://stackoverflow.com/a/192104/620054 — Michael K, Apr 10 '18 at 22:08

score 14 · Accepted Answer · edited Jan 29 '17 at 22:39

See this snippet of code:

import java.io.*;
import java.sql.*;

public class CountRows{
    public static void main(String[] args) {
        System.out.println("Count number of rows in a specific table!");
        Connection con = null;
        int count = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
            try {
                Statement st = con.createStatement();
                BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
                System.out.println("Enter table name:");
                String table = bf.readLine();
                ResultSet res = st.executeQuery("SELECT COUNT(*) FROM "+table);
                while (res.next()){
                    count = res.getInt(1);
                }
                System.out.println("Number of row:"+count);
            }
            catch (SQLException s){
                System.out.println("SQL statement is not executed!");
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}

This doesn't really answer the question. The question was about how many rows does his query (presumably any query) return, not how to query the number of rows. Also, this answer uses SQL, not Java. — mightycpa, Aug 06 '18 at 16:56

score 4 · Answer 2 · edited May 16 '22 at 06:57

4

This the way I use to get the row count in Java:

String query = "SELECT * FROM yourtable";

Statement st = sql.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
ResultSet rs = st.executeQuery(query);

int rows = 0;
rs.last();
rows = rs.getRow();
rs.beforeFirst();

System.out.println("Your query have " + rows + " rows.");

edited May 16 '22 at 06:57

Dada

6,313
7
24
43

answered Jul 13 '16 at 23:11

Isaac Amezcua

49
3

Why are you doing rs.last() and rs.beforeFirst()? – Oliver Jan 13 '18 at 20:50
1

@Oliver rs.last() will move the ResultSet's cursor to the last result/row. rs.beforeFirst() will move it back to before the beginning (so that any further code that does rs.next() will start at the beginning instead of at the end) – Michael K Apr 10 '18 at 21:58
You may have forgotten to declare rs – Nov 03 '19 at 14:37
The question is about "Correct way" not a opinion-based way... – Yousha Aleayoub Dec 21 '19 at 08:03

score 2 · Answer 3 · answered Jan 29 '14 at 05:18

When you working with JDBC that does not support TYPE_FORWARD_ONLY use this method to get rowcount.

Statement s = cd.createStatement();
ResultSet r = s.executeQuery("SELECT COUNT(*) AS rowcount FROM TableName");
r.next();
int count = r.getInt("rowcount");
r.close();
System.out.println("MyTable has " + count + " row(s).");

You can Get Row count using above method.

Thanks..

score 1 · Answer 4 · answered May 20 '18 at 08:38

1

In Android, having no results returns an error. So check this case before incrementing count in while(resultset.next())

if(resultset!=null)
{
//proceed with incrementing row count function
}
else
{
// No resultset found
}

answered May 20 '18 at 08:38

Anupam Rajanish

11
6

score 0 · Answer 5 · answered Oct 22 '18 at 07:14

0

Just iterate and count

ResultSet result = sta.executeQuery("SELECT * from A3");
            int k=0;
            while(result.next())
                k++;

            System.out.print(k); //k is the no of row

answered Oct 22 '18 at 07:14

suvojit_007

1,690
2
17
23

score -1 · Answer 6 · edited Oct 01 '15 at 08:45

-1

As method name specifies metaData.getColumnCount() will return total number of columns in result set but not total no of rows (count).

edited Oct 01 '15 at 08:45

mynawaz

1,599
1
9
16

answered Oct 01 '15 at 08:08

Krishnamraju Pernedi

1

You should also provide a solution to the question, i.e. which function can be used to get row count – mynawaz Oct 01 '15 at 08:15
I would say "get row count" cannot be retrieved from metaData since it's not Metadata but depends on "real" data. – Krischu Jul 26 '17 at 10:27

Correct way to find rowcount in Java JDBC

6 Answers6