Scott 과 함께 - Recursive Query 구현하기

- 3월 01, 2021

Recursive Query

Recursive Query는 계층형(hierarchical) 데이터를 조회할 때 사용하는 쿼리다. 기업에서 흔히 조직도와 BOM(Bill of Materials)에서 사용한다. Oracle은 'Connect By' 그리고 타 DB는 CTE(Common Table Expressions)로 계층형 데이터를 조회할 수 있다. Java의 경우, Entity를 연결해 Graph탐색할 수 있다. 이번 Post는 JPA로 Employee객체의 조직도를 구현하는 기록을 남긴다.

Oracle Native Query 결과.

EMP 테이블은 직원 1명에 관리자 1명으로 구성되어 있다. 관리자는 다른 상위 관리자 1명과 연결한다. 'KING'은 사장님이시기 때문에 관리자 값이 Null이다.

'Connect By' 문장은 연결할 데이터 컬럼을 정의한다. 'Start with'는 계층형 데이터의 시작이 어디인지 알려준다. 'Prior' 은 상향식과 하향식으로 데이터 조회 방향을 결정한다.

SELECT LEVEL,
       lpad('  ', LEVEL * 3, '.') || decode(LEVEL, 1, NULL, '└▶ ') || e.ename AS hierarchy,
       e.*
  FROM emp e
 WHERE 1 = 1
 START WITH e.mgr IS NULL
CONNECT BY e.mgr = PRIOR e.empno

상향식 결과

SELECT LEVEL,
       lpad('  ', LEVEL * 3, '.') || decode(LEVEL, 1, NULL, '└▶ ') || e.ename AS hierarchy,
       e.*
  FROM emp e
 WHERE 1 = 1
 START WITH e.ename like 'ADAMS'
CONNECT BY PRIOR e.mgr = e.empno

하향식 결과

Java 구현.

논문에서 찾아본 내용.

처음 검색했을 때, 아래와 같이 Hibernate에서 지원하는 것으로 착각했다. Annotation 설정과 성능이 좋다는 내용을 보고 좋아했지만, 상용화하지 않았다. (좋다 말았다...). 자세한 논문은 아래 첫 번째 참조를 확인하자.

@RecursiveQuery (maxLevel = 4)
@Tables (name = "Emp")
@RecursiveCondition (on= "Emp. bossID" ,to= "Emp. empId")
@Summands ( conc = { "Emp. empId" , "Emp. sname" })
@Filter(seed = "Emp.sname = $Param(sname)")
public class Suboridnates {
  @Column(name = "Emp. empID")
  public String id;
  ...
}

지원 Library (Blaze, JOOQ, ...)

CTE기능을 지원하는 라이브러리들이 있다. 다음 기회가 있으면 구현해볼 예정이다.

JPA 구현

Self-reference 를 통한 재귀 호출.

우리는 연관관계를 통해 Self-reference를 만들었다. 연관관계를 사용해서 라이브러리의 도움없이 재귀호출을 도전한다. 상향식 예제와 하향식 예제가 있다.

@Entity
@Table(name = "emp")
public class Employee {
  ...
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "mgr")
  private Employee manager;

  @OneToMany(mappedBy = "manager")
  private List<Employee> members = new ArrayList<>();
  ...
}

Manager -> Member

관리자가 직원 List를 가져오는 method다. getMembers()의 size가 0이 되는 시점에서 종료한다.

public void printMember(Employee manager, int level) {
  List<Employee> members = manager.getMembers();

  if (members.size() != 0) {
    level++;
    for (Employee member : members) {
      System.out.printf("%" + (level + member.toString().length() + 3) + "s \n",
              " └▶ " + member.toString());
      printMember(member, level);
    }
  }
}

Member -> Manager

직원 1명의 상위 관리자들을 가져오는 method다. manager는 연관관계로 연결되서 getManager()가 null이 아닐 때까지 실행한다.

public void printManager(Employee member, int level) {
  if (member.getManager() != null) {
    level++;
    System.out.printf("%" + (level + member.getManager().toString().length() + 3) + "s \n", " └▶ " + member.getManager().toString());
    printManager(member.getManager(), level);
  }
}

결과

마치며

JPA를 공부하며 외래키로 데이터를 설계는 어렵지 않았다. 하지만 Recursive Query는 생각했던 지원이 없어 많은 검색이 필요했다. 코드는 Github에 있으며 실행코드는 ScottTest에 있다.

이 블로그 검색

코드유희