java - 如何使用带有共享字段的 Spring Data JPA 创建自定义查询？

Question

我正在尝试创建一个查询方法，以使用Spring Data Rest在其他实体中引入一些共享字段，如下例所示:我正在使用 native 查询，因为我不知道当字段不在实体中时如何使用 JPA 来实现，所以我尝试使用 Native SQL 来实现。

@Entity
public class Citizen{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String lastName;
    @ManyToOne
    @JoinColumn(name = "fk_city")
    private City city;

    //getters and setters
}

@Entity
public class City{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String cityName;
    @OneToMany(mappedBy = "city")
    private List<Citizen> citizens;

    @ManyToOne
    @JoinColumn(name = "fk_country")
    private Country country;

    //getter and setters
}

@Entity
public class Country{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String countryName;
    @OneToMany(mappedBy = "country")
    private List<City> cities;

    // getter and setters
}

@RepositoryRestResource(path = "users")
public interface CustomQueries extends PagingAndSortingRepository<Citizen, Long>{

    @RestResource(path = "users-custom-query")
    @Query(value = "SELECT c1.name, c2.name, c3.name FROM Citizen AS c1 INNER JOIN City AS c2 ON "+
           "c1.fk_city = c2.id INNER JOIN Country AS c3 ON "+
           "c2.fk_country = c3.id", nativeQuery = true)
    Page<Object[]> getCitizensInCitiesAndCountries(Pageable p);

}

问题:这给了我一个错误，我认为这是因为没有实体“映射”到那种响应或一些 DTO 来共享这些属性，我不知道这是否是因为通用PagingAndSorting<Citizen, Long>或者因为返回类型 Page<Object[]>...但是该查询在 MySQL 中工作正常，但 JPA 拒绝它:c，如何将这些字段引入 Java？

Answer 1

您可以使用Projection使用接口(interface)

创建界面

public interface CitizenData{

    String geName();
    String geCityName();
    String geCountryName();
}

为接口(interface)映射的选定列设置别名

@Query(value = "SELECT c1.name AS name, c2.name As cityName, c3.name AS countryName FROM Citizen AS c1 INNER JOIN City AS c2 ON "+
           "c1.fk_city = c2.id INNER JOIN Country AS c3 ON "+
           "c2.fk_country = c3.id", nativeQuery = true)
    Page<CitizenData> getCitizensInCitiesAndCountries(Pageable p);