We are working on a club management project.

There is a club president on the club table and the user ID is received as a foreign key.

I want to join two tables while using jpa and get the results

If you specify the mapped club table as a type, an error appears (image 1)

If you interface the resulting field, only null values are returned. (Image 2)

How shall I do it?

(Image1)

org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [@org.springframework.data.jpa.repository.Query com.wodongso.wodongso.entity.Society] for value '{호남대학교, 왕유저13, 두 발의 자유1, 스포츠, 두 바퀴만 있다면 지원가능!!1, 허벅지 터지도록 활동합니다1, true}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.String] to type [@org.springframework.data.jpa.repository.Query com.wodongso.wodongso.entity.Society]

(Image2)

society entity

user entity

@Entity
@Data
public class User {

    @Id
    private String id;

    private String name;

    private String nickname;

    private String password;

    private String role;

    private String contact;

    @Column(name = "profile_url")
    private String profileUrl;

    private String region;

    private String university;

    private String major;

    @Column(name = "class_of")
    private Integer classOf;

    private boolean enabled;

    @Column(name = "created_at")
    private Date createdAt;

}

SocietyRepository

@Repository
public interface SocietyRepository extends JpaRepository<Society, Integer> {
    Page<Society> findByNameContaining(String searchKeyword, Pageable pageable);

    @Query("SELECT s FROM Society s WHERE s.enabled = :isEnable")
    Page<Society> findByEnabledPage(@Param("isEnable") boolean isEnable, Pageable pageable);


    @Query("SELECT u.university, u.name, s.name, s.category, s.simpleDesc, s.detailDesc, s.enabled "  
            "FROM Society s "  
            "INNER join User u "  
            "ON u.id = s.officerId "  
            "WHERE u.university = :university")
    List<Society> findAllByUniversity(@Param("university") String university);


}

CodePudding user response：

Create a class which contains all the fields and add an all args constructor and than use the query:

@Query("SELECT new SocietyWithUser(u.university, u.name, s.name, s.category, s.simpleDesc, s.detailDesc, s.enabled) "  
            "FROM Society s "  
            "INNER join User u "  
            "ON u.id = s.officerId "  
            "WHERE u.university = :university")
List<SocietyWithUser> findAllByUniversity(@Param("university") String university);