java Spring数据jpa，jpa替换DTO对象的字符串的正向返回列表

Question

我有一个interface实现了JPARepository并且有三个方法，其中一个是自定义的@Query

public interface PersonRepository extends JpaRepository<Person, Long> {

  List<Person> getPersonBycountryCode(String countryCode);

  List<Person> findByCountryCodeAndCity(String string,String city);

  @Query(value = "SELECT person.firstName as firstName, person.lastName as lastName, person.countryCode as country, person.city as city,"
              + " SQRT(POWER((69.1 * (person.age - :age )) , 2 )"
              + " + POWER((53 * (person.experience - :experience )), 2)) as eligibility"
              + " FROM Person person"
              + " ORDER BY eligibility ASC")
  List<PersonDetailsDto> findPersonDetailsByEligibility(
          @Param("age") BigDecimal age,
          @Param("experience") BigDecimal experience,
          Pageable pageable
  );
}

问题是：使用@Query的方法不返回PersonDetailsDto列表，而是返回字符串列表（List<List<String>>）

PersonDetailsDto是一个POJO类，包含查询输出中描述的所有变量（firstName、lastName、country、city、qualification），也是一个构造函数，所有变量都作为参数。其他两个方法不返回Person对象的列表

有什么想法吗

Answer 1

您可以在@query的查询中使用new关键字。并确保为PersonDetailsDto提供了适当的构造函数，并更改包名

@Query(value = "SELECT new com.company.PersonDetailsDto(person.firstName, person.lastName, person.countryCode , person.city ,"
          + " SQRT(POWER((69.1 * (person.age - :age )) , 2 )"
          + " + POWER((53 * (person.experience - :experience )), 2)) "
          + " FROM Person person"
          + " ORDER BY eligibility ASC")
List<PersonDetailsDto> findPersonDetailsByEligibility(
      @Param("age") BigDecimal age,
      @Param("experience") BigDecimal experience,
      Pageable pageable
);

类似的question's answer

Answer 2

就叫它别名吧，对我来说就是这样 例：

@Query(value = "SELECT person FROM Person person"
          + " ORDER BY eligibility ASC")
List<PersonDetailsDto> findPersonDetailsByEligibility(
      @Param("age") BigDecimal age,
      @Param("experience") BigDecimal experience,
      Pageable pageable
);

Answer 3

实际上JpaRepository<Person, Long>意味着，在jpa存储库方法中只能使用Person作为dto

对于您的解决方案，您只需在存储库中定义dto接口：

public interface PersonRepository extends JpaRepository<Person, Long> {

  List<Person> getPersonBycountryCode(String countryCode);

  List<Person> findByCountryCodeAndCity(String string,String city);

  @Query(value = "SELECT person.firstName as firstName, person.lastName as lastName, person.countryCode as country, person.city as city,"
              + " SQRT(POWER((69.1 * (person.age - :age )) , 2 )"
              + " + POWER((53 * (person.experience - :experience )), 2)) as eligibility"
              + " FROM Person person"
              + " ORDER BY eligibility ASC")
  List<PersonDetailsDto> findPersonDetailsByEligibility(
          @Param("age") BigDecimal age,
          @Param("experience") BigDecimal experience,
          Pageable pageable
  );

 //define the interface here
 public interface PersonDetailsDto{
   public String getFirstName();
   public String getLastName();
   public String getCountry();
   public String getCity();
   public Integer getEligibility();
 }

}

Answer 4

如果我没有错的话，JPA不寻找特定字段的想法是，从表的一行中获取一列或所有列的成本（效率）是相同的。但要解决您的问题，您可以从如下存储库类在@Query注释中设置nativeQuery = true：

public static final String FIND_SOMETHING = "SELECT somethingId, somethingName FROM something";

@Query(FIND_SOMETHING, nativeQuery = true)
public List<Object[]> findSomethings();

我希望这能帮助你解决你的问题