title: Spring JDBC query for count of a specific field
body: <p>I would like to know how I would get the count of a specific column within my database. Each distinct value in my column I would like to store the returned column value and its count within a Java List. This is what I have so far:</p>
  
  <pre><code>public List&lt;Sighting&gt; getCountPest() {
      return jdbc.query("select pest_name, count(pest_name) from sighting group by pest_name", new RowMapper&lt;Sighting&gt;() {
                   public Sighting mapRow(ResultSet rs, int rowNum) throws SQLException {
                          Sighting sighting = new Sighting();
                          sighting.setCount(rs.getInt("count")); // will not work as  no column name in table
                          sighting.setPest_name(rs.getString("pest_name"));
                          return sighting;
                      }      
                 });
  }
  </code></pre>
  
  <p>Essentially I would like to use pest_name and returned count value for a chart.
  This is my sightingRowMapper if it helps:</p>
  
  <pre><code>public Sighting mapRow(ResultSet rs, int rowNum) throws SQLException {
  
      User user = new User();
      user.setUsername(rs.getString("username")); // setting the username if logged in
  
      Sighting sighting = new Sighting();
      sighting.setId(rs.getInt("id"));
      sighting.setTotal_pests(rs.getInt("total_pests"));
      sighting.setDate(rs.getString("date"));
      sighting.setInformation(rs.getString("information"));
      sighting.setPest_name(rs.getString("pest_name"));
      sighting.setPark(rs.getString("park"));
      sighting.setLocation(rs.getString("location"));
      sighting.set_Username(rs.getString("username"));
      sighting.setUser(user);
  
      return sighting;
  }
  </code></pre>