CODEWITHSUNDEEP
javaspringjpaspring-dataspring-data-jpa
Ashutosh Gupta
Ashutosh Gupta

Reputation: 71

Spring data jpa, jparepository returning list of string in place of DTO object

I have a interface implementing JPARepository and have three methods, one of them is having a custom @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
  );
}

Problem is: method with @Query does not return list of PersonDetailsDto but return list of list of strings (List<List<String>>).

PersonDetailsDto is a POJO class with all the variables described in a query output (firstName, lastName, country, city, eligibility) and also a constructor with all the variables as Parameters. Other two methods does return list of Person object.

Any idea?

Upvotes: 7

Views: 23531

Answers (4)

ichak khoury
ichak khoury

Reputation: 217

just call it by its alias, it worked for me like that ex :

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

Upvotes: 0

atul ranjan
atul ranjan

Reputation: 544

Actually JpaRepository<Person, Long> means that, you can use only Person as your dto in jpa repository methods.

For your solution you can just define your dto interface inside the repository :

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

}

Upvotes: 4

prem kumar
prem kumar

Reputation: 5877

You can use new keyword in query of @Query. And make sure you have the appropriate constructor for PersonDetailsDto and also change package name.

@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
);

Similar question's answer.

Upvotes: 0

Grinish Nepal
Grinish Nepal

Reputation: 3075

If I am not wrong the idea behind JPA not looking for specific fields is that is cost (efficiency wise) the same to bring one column or all columns from one row of the table.But to solve your problem you can set nativeQuery = true in the @Query annotation from a Repository class like this:

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

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

I hope this will help you to resolve your problem.

Upvotes: 0

Related Questions