0

Normally, when we want to retrieve a value from our database which is present in the table, we call the appropriate method of ResultSet and pass it the column name which we want to retrieve.

   ResultSet rs= stmt.executeQuery("select name from db.persons where school ='"+sch+"'");
    int count= rs.getString("person_name");

But when we want to get a count of rows (or fields) in a particular column (we use the SQL COUNT function), how do we retrieve the result. What argument should I pass in the rs.getInt() method in the following piece of code?

ResultSet rs= stmt.executeQuery("select count(name) from db.persons where school ='"+sch+"'");
int count= rs.getInt( ????? );
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Solace
  • 8,612
  • 22
  • 95
  • 183
  • Check this answer http://stackoverflow.com/questions/192078/how-do-i-get-the-size-of-a-java-sql-resultset – Nikolay Dimitrov Apr 29 '14 at 16:32
  • 1
    @gubble in this case, applying the result in that answer would give 1, while OP needs to execute a `COUNT` function on database. – Luiggi Mendoza Apr 29 '14 at 16:35
  • My question is different from that question. They want to get the size of the result set. I directly want to execute a query to get the row count! @gubble – Solace Apr 29 '14 at 16:46
  • @a_horse_with_no_name Right! An example typed in haste, I am going to correct that. – Solace Apr 29 '14 at 16:47

1 Answers1

10

Give a name to the column:

ResultSet rs= stmt.executeQuery("select count(name) AS count_name from db.persons where school ='"+sch+"'");
if (rs.next()) {
    int count= rs.getInt("count_name");
}

You could also pass the number of the index of the column (in case you don't want to modify your query) which is 1 based. Check ResultSet#getInt(int columnIndex):

ResultSet rs= stmt.executeQuery("select count(name) from db.persons where school ='"+sch+"'");
if (rs.next()) {
    int count= rs.getInt(1);
}

Apart from this, it would be better if you use a PreparedStatement to execute your queries, it has many advantages over plain Statement as explained here: Difference between Statement and PreparedStatement. Your code would look like:

String sql = "select count(name) AS count_name from db.persons where school = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, sch);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    int count = rs.getInt("count_name");
}
Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
  • Thank you very much for the elaborate answer. I'll learn about prepared statements soon. – Solace Apr 29 '14 at 16:49
  • @Zarah you're welcome. I highly recommend you to use `PreparedStatement`s when working with plain JDBC instead of using `Statement` interface. – Luiggi Mendoza Apr 29 '14 at 16:52