@Query annotation not working in JPA Repository

user_19833762 0
Viewed 17
@Query(value = "SELECT * FROM MEMBER m " +
            "LEFT JOIN COMPANY_TYPE c ON c.TYPE_CODE = m.TYPE_CODE " +
            "LEFT JOIN COMPANY_LOCATION l on l.LOCATION_CODE = m.LOCATION_CODE " +
            "WHERE l.LOCATION_CODE = :location AND c.TYPE_CODE = :type", nativeQuery = true)
    List<Member> findByOption(@Param("type")Long companyType, @Param("location")Long companyLocation);

This is the method I'm trying to use in MemberRepository interface that extends JPARepository<Member, Long>.

@ManyToOne
@JoinColumn(name = "TYPE_CODE")
private CompanyType company_Type;

@ManyToOne
@JoinColumn(name = "LOCATION_CODE")
private CompanyLocation company_Location;

And this is part of entity class Member.

I am trying to get filtered list of members according to the company type and location code(id). However, the slice test results always return all of members.

I want to know if there are other settings that needs to be set in order for the @Query annotation to work, and if the native query I put in the annotation is wrong.

Any other ideas of how to get filtered results in Spring Data JPA is also welcomed.

jpa
1 Answers

This is a very subtle mistake, but rather than using SELECT * you should be using SELECT m.*:

Query(value = "SELECT m.* FROM MEMBER m " +
        "LEFT JOIN COMPANY_TYPE c ON c.TYPE_CODE = m.TYPE_CODE " +
        "LEFT JOIN COMPANY_LOCATION l on l.LOCATION_CODE = m.LOCATION_CODE " +
        "WHERE l.LOCATION_CODE = :location AND c.TYPE_CODE = :type", nativeQuery = true)
List<Member> findByOption(@Param("type")Long companyType, @Param("location")Long companyLocation);

The reason for this is that SELECT m.* returns only the columns from the Member entity/table. SELECT * instructs JPA to return columns from all tables in the query. You would need a method return signature of List<Object[]> in that case.

user_18632290
Related