SQL aggregation GROUP BY and COUNT in Spring JPA

Question

I have an SQL table:

@Table(name = "population_table")
public class Population {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String country;
  private String state;
  private String area;
  private String population;
}

I want to get a count, grouping by country and state with the output class being List of Count:

  private static class Count {
    private String country;
    private String state;
    private long count;
  }

I know the query is

SELECT country, state, Count(*)
FROM population_table
GROUP BY country, state

But I want to do this using JPA Specification. How can I achieve this using JPA Specification in spring boot?

what do you mean by "I want to do this using Specifications" ? — Nitish Kumar, Apr 23 '20 at 19:19
This will help [Returning Specific type using DTO Projection](https://stackoverflow.com/questions/10829197/how-to-return-a-list-of-specific-type-instead-of-listobject-in-hibernate) — Nitish Kumar, Apr 23 '20 at 19:41

score 8 · Accepted Answer · edited Dec 23 '22 at 07:36

You could achieve this by using Spring Data JPA Projections in Spring Data JPA.

Create a custom Repository method like

@Repository
public interface PopulationRepository extends JpaRepository<Population, Long> {

@Query("select new com.example.Count(country, state, count(p) )
       from Population p
       group by p.country, p.state")
public List<Count> getCountByCountryAndState();

}

Also you must define the specific constructor in Count class which will handle this projection

private static class Count {
 private String country;
 private String state;
 private long count;
 
 //This constructor will be used by Spring Data JPA 
 //for creating this class instances as per result set
 public Count(String country,String state, long count){
   this.country = country;
   this.state = state;
   this.count = count;
 }
}

Seldo97 · Answer 2 · 2020-04-23T20:02:59.167

You can use JpaRepository interface. Example:

@Repository
public interface PopulationRepository extends JpaRepository<Population, Long> {

    public int countAllByCountryAndState(String countryName, String stateName);

}

And in your service:

@Service
@Transactional
public class PopulationService {

    @Autowired
    private PopulationRepository populationRepository;

    public int countPopulationByCountryAndState(String countryName, String stateName) { 

         return populationRepository.countAllByCountryAndState(countryName, stateName);
    }

}

Sorry, I made mistake it can be simpler. I edited my code.

SQL aggregation GROUP BY and COUNT in Spring JPA

2 Answers2

Linked