CODEWITHSUNDEEP
javamysqldatabaserow
Blaiz
Blaiz

Reputation: 105

Count number of rows retrieved from mysql database

public int countBookings() throws SQLException{

    ResultSet rs=null;
    PMDBController db=new PMDBController();
    int rowCount=0;

    db.getConnection();

    String dbQuery="SELECT COUNT(User) AS UserCount FROM INSTRUCTORBOOKING WHERE USER ='"+instructorId+"'";

    rs=db.readRequest(dbQuery);

    try{
        if(rs.next()){
            instructorId=rs.getString("UserCount");
        }
    }catch(Exception e){
        e.printStackTrace();
    }

    rs.last();
    rowCount=rs.getRow();
    db.terminate();

    return rowCount;
}

Basically what this method is supposed to do is count the number of rows gotten from the database. However, it always returns 1 no matter what is inside. Help!

Upvotes: 0

Views: 4836

Answers (3)

mtk
mtk

Reputation: 13717

The number of rows will always be 1. It's the count i.e. the value of that row you need to look at as your query is designed to return the count of rows and not the actual rows.

SELECT COUNT(User) AS UserCount FROM INSTRUCTORBOOKING WHERE USER ='"+instructorId+"'"

You have wrongly interpreted that the number of rows would be the count you are looking for.

Upvotes: 0

Cromax
Cromax

Reputation: 2052

rowCount = rs.getInt("UserCount"); instead of instructorId = rs.getString("UserCount"); would do the trick. Or in other words --- you read the number of rows but into variable instructorId.

Upvotes: 0

Jeroen
Jeroen

Reputation: 3146

It seems you have a problem in your query. Since you only select 1 user you will always get a count of 1.

"SELECT COUNT(User) AS UserCount FROM INSTRUCTORBOOKING WHERE USER ='"+instructorId+"'"

Try removing your WHERE clause? Maybe that's not exactly what you want, but we can't see your data model from just one query.

Upvotes: 1

Related Questions