My name
is
Jon Skeet

Storing Count value from query

How do I store the returned count value into a variable, so I can then set a attribute with it? This is my method so far:

public List<Sighting> total() {

     return jdbc.query("select pest_name, count(pest_name) from sighting group by pest_name", new RowMapper<Sighting>() {
         public Sighting mapRow(ResultSet rs, int rowNum) throws SQLException {
                Sighting sighting = new Sighting();
                sighting.setCount(????); // I need to pass a variable to setCount()
                sighting.setPest_name(rs.getString("pest_name"));
                return sighting;
            }      
       });
}

That count value in a query..

You could either specify a name for the count, e.g.

return jdbc.query("select pest_name, count(pest_name) as pest_count from sighting group by pest_name", new RowMapper<Sighting>() {
         public Sighting mapRow(ResultSet rs, int rowNum) throws SQLException {
                Sighting sighting = new Sighting();
                sighting.setCount(rs.getInt("pest_count"));
                sighting.setPest_name(rs.getString("pest_name"));
                return sighting;
            }      
       });

... or just fetch it by column number:

return jdbc.query("select pest_name, count(pest_name) from sighting group by pest_name", new RowMapper<Sighting>() {
         public Sighting mapRow(ResultSet rs, int rowNum) throws SQLException {
                Sighting sighting = new Sighting();
                sighting.setCount(rs.getInt(2));
                sighting.setPest_name(rs.getString("pest_name"));
                return sighting;
            }      
       });

You may need to use getLong instead of getInt.

See more on this question at Stackoverflow