JPA查询两个表的聚合函数计数

Question

我有两个实体:州长和州与ManyToOne协会。我需要得到所有有州长/州长的州，并显示它们的数量。我在用PostgreSQL。看起来是这样的：

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

但是，我不知道在中应该如何做。我知道我需要创建像GovernorsCount这样的额外类并声明这两个字段。但这是两个不同的表中的字段，也不是一个。这是我在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();

以下是两个POJO类：

@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

我是怎么做这个手术的？任何帮助都是非常感谢的。

Answer 1

您可以返回一个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];
}

您还可以使用JPQL查询来完成该任务：

@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];
}

如果只需要状态名称，则可以将select子句更改为SELECT state.officialStateName, COUNT(governor)。

Answer 2

首先，必须以下列方式定义实体

@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;
    ...
}

上面是一个双向的OneToMany映射.

为了获得计数，您可以使用JPA使用下面的查询。

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