CODEWITHSUNDEEP
javajdbc
Student
Student

Reputation: 4563

Correct way to find rowcount in Java JDBC

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;

Upvotes: 9

Views: 57975

Answers (6)

Isaac Amezcua
Isaac Amezcua

Reputation: 49

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

Upvotes: 4

suvojit_007
suvojit_007

Reputation: 1728

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

Upvotes: 0

Anupam Rajanish
Anupam Rajanish

Reputation: 11

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
}

Upvotes: 1

Kumar Vivek Mitra
Kumar Vivek Mitra

Reputation: 33534

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

Upvotes: 14

Krishnamraju Pernedi
Krishnamraju Pernedi

Reputation: 1

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

Upvotes: -1

Java Man
Java Man

Reputation: 1860

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

Upvotes: 2

Related Questions