JPA Queries Aggregate function COUNT with two tables-CodePudding

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.

CodePudding user response：

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 return state.officialStateName, if you only need the name of the state.

CodePudding user response：

First, the entities have to be defined in the following way

@Entity
public class State {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    ...         
    @OneToMany(mappedBy = "state")
    private List<Governor> governors;
    ...
}

@Entity
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    ...
    @ManyToOne
    private State state;
    ...
}

Above is a bi-directional OneToMany mapping.

For getting the count you can use below query using JPA.

for(State state : stateRepository.findAll()) {
        List<Governor> governors = state.getGovernors();
        System.out.printf("%s %s\n", state.getName(), governors.size());
    }