JPA Queries Aggregate function COUNT with two tables

Question

I have two entities: Governor and State with ManyToOne association. I need to get all states where there are/were governors and display their count. I'm using PostgreSQL. It looks like this:

SELECT official_state_name, COUNT (governor.id_governor) from state
RIGHT JOIN governor ON state.id_state = governor.id_state
GROUP BY official_state_name

However, I have no clue how it should be done in Spring Data JPA. I know that I need to create additional class like GovernorsCount and declare those two fields. But these are the fields from two different tables, nor one. So this is my query in JPA:

@Transactional
@Query(value="SELECT official_state_name, COUNT (governor.id_governor) from state\n" +
        "RIGHT JOIN governor ON state.id_state = governor.id_state\n" +
        "GROUP BY official_state_name", nativeQuery = true)
List <State> findAllStatesAndGovernors();

These are two POJO classes:

@Entity
@Table(name = "state")
public class State implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_state")
private Integer idState;

@Column(name = "official_state_name")
private String officialStateName;

@Column(name = "official_language")
private String officialLanguage;

@Column(name = "state_currency")
private String stateCurrency;
//setters and getters



@Entity
@Table(name = "governor")
public class Governor implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_governor")
private Integer idGovernor;

@Column(name = "pib")
private String fullName;

@Column(name = "age")
private Integer age;

@Column(name = "position")
private String position;

@Column(name = "date_of_intercession")
private java.sql.Date dateOfIntercession;

@Column(name = "date_of_resignation")
private java.sql.Date dateOfResignation;

@Column(name = "per_capita_income")
private Double perCapitaIncome;

@Column(name = "population_below_poverty")
private Integer populationBelowPoverty;

@ManyToOne
@JoinColumn(name = "id_state", nullable = false)
private State state;  
//setters and getters

So how do I actually do this operation? Any help is appreciated.

Answer 1

You can return a List<Object[]>.

@Transactional
@Query(value="SELECT official_state_name, COUNT (governor.id_governor) from state\n" +
        "RIGHT JOIN governor ON state.id_state = governor.id_state\n" +
        "GROUP BY official_state_name", nativeQuery = true)
List<Object[]> findAllStatesAndGovernors();

List<Object[]> results = repository.findAllStatesAndGovernors();
for( Object[] row : results) {
   String officialStateName = (String)row[0];
   int count = (Integer)row[1];
}

You can also do it using JPQL queries:

@Transactional
@Query("SELECT state, COUNT(governor) from Governor governor\n" +
        "LEFT JOIN governor.state state\n" +
        "GROUP BY state.officialStateName")
List <Object[]> findAllStatesAndGovernors();

List<Object[]> results = repository.findAllStatesAndGovernors();
for( Object[] row : results) {
   State state = (State)row[0];
   int count = (Integer)row[1];
}

You can change the select clause to SELECT state.officialStateName, COUNT(governor), if you only need the name of the state.