JPA查询汇总函数与两个表

Question

我有两个实体：州长和州与许多人协会。我需要将所有州的所有州都带到/是州长的位置并显示他们的计数。我正在使用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

但是，我不知道在春季数据JPA中应该如何完成。我知道我需要创建其他类，例如ContorsScount并声明这两个字段。但是这些是两个不同表的字段，也是一个。因此，这是我在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＆lt; object []＆gt;。

@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 state.officialstateName，count（consorr）。

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());
    }