Solved Counting rows in mySQL Database

I need to know how to count the number of rows in a table. And then get how many rows are in the table to an int. Could someone help? i dont have any code yet xD but i do know some sql with prepaired statements in Bukkit/Java.

 

Code:java

int count = 0;
 
while (rs.next()) {
    ++count;
    // Get data from the current row and use it
}
 
if (count == 0) {
    System.out.println("No records found");
}

this is a example how to count the rows maybe not the best method to do it but its a way make a prepared statment with a WHERE `int` = "";

 

There's a SQL statement for that:
"SELECT COUNT(*) FROM TABLE table"
Don't know exactly what you do...

 

To add to the previous two answers (both correct). Which one you use depends on what you are doing.

If you want a count of the whole table or just a count, doing SELECT COUNT(*) as Zupsub suggests will be faster, you then read the value from the ResultSet. If you just want a count of specific data without retrieving it (say all rows with the field name set to "john", SELECT COUNT(*) FROM tableName WHERE name="john" will provide that.

If you want a count of a query you made (say, how many friends names came back). mechoriot's method will suffice, but should not be used if all you want is just a count and nothing else.

 

tehbeard well if i were to do the SELECT COUNT(*) FROM tablename how would i go about getting that int it makes and refrencing it to an int in my code?

 

Tyler Christensen Well....through your resultset obviously. If you are asking how to get the correct row by name, your solution is to give the row a name in the query, like so:
SELECT COUNT(*) AS `count` FROM tablename

 

Result set LOL ty xD ik there was a way of doing it, justkingda.... flew by my mind xD
Ty Rocoty and tehbeard and
Zupsub

Sorry, but how would i go about with the ResultSet to make how many rows there are to an int?

Anyone?

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Doesn't it has a getInt or nextInt method? Use "*" as row name.

 

i recived errors while doing something like that... :/

 

Tyler Christensen if you have a stacktrace or some error post it plz so we can help you the best ways

 

You can use the column index instead of name, so:

Code:

ResultSet rs = ....
rs.next();//Read in the first row.
int rowCount = rs.getInt(1);//Fetch value of first column of result as an int.
rs.close();//Clean up time

 

Another way is also to do this:

Code:java

 
ResultSet rs = blahblah;
 
many code
much java
 
int count = rs.getLast().getRow();
rs.close();
 

Or if you want the count the first thing you do:

Code:Java

ResultSet = veryCode;
if (rs.getLast().getRow() > 20)
{
    rs.getFirst();
    while (rs.hasNext()) doSomething;
}

 

Lurvik there is no such thing as hasNext() though But similar answer to what im looking for... here is what i have now:

And i still think most of you don't get the point of what im doing... in my database, i have several rows... all i want to do is count them... i dont want to get the name of one row, or get it's id or whatever, i want to know how many rows are in the database, then however many there are, make that to an int in java. heres what i have so far from what i know...

Code:java

openCon();
PreparedStatement sqll = connection.prepareStatement("SELECT COUNT(*) FROM 'Hub_inheritance';");
ResultSet rs = sqll.executeQuery();
int r = 0;
while(rs.next()){
r++;
}

 

r will always be 1 if you test that.

Code:java

Statement stmt = connection.createStatement();
ResultSet rs = stmt.execute("SELECT COUNT(*) FROM database.table, database2.table2");
int = rs.getInt("COUNT(*)");

That should do what you want.

 

TYYYYYYYYY Lurvik You da besttttttt!!!!!!!!!