[JPA] JPQL 사용하여 TABLE JOIN 하기

JPA에서 TABLE JOIN할 때, NativeQuery를 이용할 수도 있지만

그렇게 하면 Query를 최소한으로 사용하는 JPA의 의미가 퇴색되므로,

되도록 JPQL을 이용하여 JOIN을 하는 것이 좋다.

작성하는 과정에서 Error가 존재한 이전 코드와 문제를 해결한 이후 코드를 정리해보았다.

 

# 기존 코드

// Member Class
@Getter
@Setter
@ToString
@Entity
@Table(name = "tbl_members")
@EqualsAndHashCode(of="uid")
public class Member {

	@Id
	private String uid;
	private String upw;
	private String uname;	

}

// Profile Class
@Getter
@Setter
@ToString(exclude = "member")
@Entity
@Table(name = "tbl_profile")
@EqualsAndHashCode(of = "fname")
public class Profile {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long fno;

	private String fname;
	private boolean current;

	@ManyToOne
	private Member member;

}

// MemberRepository
public interface MemberRepository extends CrudRepository<Member, String> {
	
	@Query("SELECT m.uid, count(p) FROM Member m LEFT OUTER JOIN Profile p ON m.uid = p.member WHERE m.uid = ?1 GROUP BY m")
	public List<Object[]> getMemberWithProfileCount(String uid);	

}

// ProfileRepository
public interface ProfileRepository extends CrudRepository<Profile, String>{

}

// ProfileTests
@RunWith(SpringRunner.class)
@SpringBootTest
@Log
@Commit
public class ProfileTests {
	
	@Autowired
	MemberRepository memberRepo;
	
	@Autowired
	ProfileRepository profileRepo;
		
	@Test
	public void testFetchJoin1() {
		List<Object[]> result = memberRepo.getMemberWithProfileCount("user1");		
		result.forEach(arr -> System.out.println(Arrays.toString(arr)));
	}	

}

 

# 기존 결과

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'boot04Application': Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'memberRepository' defined in org.zerock.persistence.MemberRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Could not create query for public abstract java.util.List org.zerock.persistence.MemberRepository.getMemberWithProfileCount(java.lang.String); Reason: Validation failed for query for method public abstract java.util.List org.zerock.persistence.MemberRepository.getMemberWithProfileCount(java.lang.String)

 

# 수정 코드

// Member Class
@Entity
@Table(name = "tbl_members")
@Getter
@Setter
@ToString
public class Member {

	@Id
	private String uid;
	private String upw;
	private String uname;
	
	@OneToMany(mappedBy = "member")
	private List<Profile> profiles;

}

// Profile Class
@Entity
@Table(name = "tbl_profile")
@Getter
@Setter
@ToString(exclude = "member")
public class Profile {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long fno;

	private String fname;
	private boolean current;

	@ManyToOne
	@JoinColumn(name = "member_uid") // JoinColumn을 사용하여 매핑할 컬럼명을 명시적으로 지정
	private Member member;

}

// MemberRepository
public interface MemberRepository extends CrudRepository<Member, String> {

	@Query("SELECT m.uid, count(p.fno) FROM Member m LEFT OUTER JOIN m.profiles p WHERE m.uid = ?1 GROUP BY m.uid")
	public List<Object[]> getMemberWithProfileCount(String uid);
	
}

// ProfileRepository
public interface ProfileRepository extends CrudRepository<Profile, String>{

}

// ProfileTests
@RunWith(SpringRunner.class)
@SpringBootTest
@Log
@Commit
public class ProfileTests {
	
	@Autowired
	MemberRepository memberRepo;
	
	@Autowired
	ProfileRepository profileRepo;
		
	@Test
	public void testFetchJoin1() {
		List<Object[]> result = memberRepo.getMemberWithProfileCount("user1");		
		result.forEach(arr -> System.out.println(Arrays.toString(arr)));
	}

}

 

# 수정 결과

Hibernate: select m1_0.uid,count(p1_0.fno) from tbl_members m1_0 left join tbl_profile p1_0 on m1_0.uid=p1_0.member_uid where m1_0.uid=? group by m1_0.uid

[user1, 4]