오전에는 subquery연습을 했고 오후에는 미니 프로젝트를 진행했는데 과정이 길어서 미니 프로젝트는 다음 글에서 소개해 드리도록 하겠습니다.

오늘은 예제를 통한 subquery연습을 짧게 보여드리려 합니다.

sun cloud

참고사항

(강사님께서 수업 중 조언해주셨던 사항들입니다.)

  • DB와 연결이 끊겼을 경우
    • ping 확인(이상 있을 시 계속 진행)
    • 윈도우에서 서비스 실행
    • OracleServiceXE, OracleServiceXETNSListener 실행
  • 데이터를 직접 신속하게 수정하고 싶을 경우

    • Data Source Explorer > Oracle > xe > Schemas > username > Tables에서 수정

      (공부하는 입장에서 직접 수정은 권장하지 않음)


test 코드를 작성해서 단위 테스트를 진행하는 연습이 중요!


이제 본격적으로 수업 내용을 복습하도록 하겠습니다.

Subquery 예제

전 글에서 SQL문으로 subquery 작성하는 법을 살펴봤습니다.
이제 자바에서 subquery문을 활용해보도록 하겠습니다.

이번 예제는 subquery연습을 위해 특정 job(부서)의 사원 중 가장 높은 salary를 받는 사원 정보를 조회하는 메서드를 만드는 것이 목적입니다.

진행순서

다음과 같이 진행합니다.

  1. Employee 테이블을 만들어서 사원번호, 이름, 부서, 월급의 컬럼을 생성

  2. sql에서 단위테스트를 진행

  3. test 패키시 생성, TestEmployeeDAO 클래스(main클래스) 작성

  4. model 패키지 내에 Employee 클래스 파일 생성
    • 인스턴스 변수 생성
    • 생성자: 기본(단일 등록 시 사용), 사원번호만 제외(insert 시 사용), 전부(조회 시 사용)
    • getter setter
    • toString()

    </div> </details>

  5. model 패키지 내에 DbInfo interface 생성
    • DRIVER, URL, ID, PASSWORD를 static final 상수로 생성
      (인터페이스이므로 field(변수+상수)는 public static final로 인식된다)

    </div> </details>

  6. model 패키지 내에 EmployeeDAO 생성 
    
      
    
    
      
      
    • 드라이버 로딩
      • 
      
    • closeAll() 메서드 생성, 오버라이딩
      • 
      
    • 메서드 작성
        
        
          
      1. register() 메서드를 만들어서 데이터를 저장
        2. 
          
      2. 해당 job(부서)의 사원 중 가장 높은 salary를 받는 사원 정보를 조회하는 메서드 생성
2-1. sql 단위 테스트 진행(subquery 사용)
​	2-1-1. job에 해당하는 사원의 가장 높은 salary를 조회
​	: SELECT MAX(salary) FROM s_employee WHERE job='개발';
​	2-1-2.위 sql에서 조회된 salary를 받는 사원 정보(job에 해당하는 사원에 한함)
​	: SELECT empno, name, job, salary FROM s_employee WHERE job='개발' AND salary=(3-1-1의 sql문)
2-2. ArrayList<Employee> 타입의 list를 생성하고 sql문을 활용하여 데이터를 list에 할당합니다.
        3. 
        
      
      
      • 
    
    
    
    </pre>
</div>
</details>
    
  
    7. 








 이번 예제를 연습하는데 있어서 핵심 내용은 subquery의 활용입니다. 
 전체 코드는 본 글 최하단의 링크를 통해 확인하실 수 있습니다.
 여기서는 다른 부분보다는 서브쿼리 활용에 초점을 맞춰 진행하도록 하겠습니다.








  
SQL테스트





자바 프로그램에서 코드를 작성하기전에 먼저 테스트를 해보는 것이 중요합니다.
서브쿼리 연습을 위해 sql 단위테스트를 두 번의 단계로 나누어서 진행을 하겠습니다.
carbon 



핵심은 31번 라인의 job='개발'인 WHERE절을 두 번 사용해야 한다는 부분입니다.
 단순하게 생각했을 경우 가장 높은 salary를 받는 job ='개발'인 사원 정보를 조회해야 하기에 MAX(salary)를 조회하는 단계에서 WHERE job='개발' 부분을 빠뜨리기 쉽습니다.
 이 경우 다른 부서의 salary가 더 높을 때 저희가 원하는 정보를 조회할 수 없게 됩니다.





  
JAVA 작성





subquery에 초점을 맞춰서 해당 job(직종)의 사원 중 가장 높은 salary를 받는 사원 정보 empno, name, job, salary를 조회하는 메서드만 진행하도록 하겠습니다. 



getEmployeeListByHishSalAndJob메서드를 통해 개발직종에 해당하는 사원들 중 가장 높은 salary를 받는 사원의 정보를 조회합니다. 
반환받는 결과가 1명이 아닐 수 있기에 ArrayList를 사용합니다.



EmployeeDAO 클래스에서 메서드를 작성하게 될텐데 먼저 DB와의 연결을 위해 Driver loading을 해주고 자주 사용하게 될 메서드들을 만들어 줍니다. 그리고 저희가 사용할 메서드를 작성해주는데 아래와 같습니다.



carbon



해당 사원의 정보를 list로 반환받기 위해 먼저 ArrayList를 선언합니다. try블록 밖에서 선언해주는 이유는 블록 안에서도 사용을 하지만 반환해주기 위해서는 블록 밖의 영역에서 다시 사용해야하기 때문입니다.(지역변수 개념)



결과값이 반환되기 때문에 ResultSet을 사용하고, 진행 방식은 기존의 쿼리문 진행 방식과 같습니다.
 다만 여기서 sql문에 서브쿼리문을 사용해주면 되는데, 단일 쿼리문이 아니기에 보통 길이가 긴 편입니다. 가독성과 데이터 사용 효율을 높이기 위해 StringBuilder를 사용하도록 하겠습니다.
 여기서 주의해야 할 점은 sql문의 마지막 라인을 제외하고 모두 마지막에 공백을 넣어줘야 한다는 점입니다. 공백이 없다면 뒤의 sql문과 공백없이 이어져서 완성되기에 java.sql.SQLSyntaxErrorException이 발생합니다.



sql문을 완성하면 이전에 배웠던 것처럼 PreparedStatement를 사용해 미리 sql문을 컴파일해서 사용하고, 쿼리 전송 전에 작성해둔 치환문자에 값(여기서는 job)을 입력해줍니다.
 그리고 ResultSet의 참조변수인 rs에 결과를 담아서 list에 필요한 값을 입력해주고 반환하면 됩니다.



전체 코드는 아래의 링크에서 보실 수 있습니다.



깃허브링크





        
        
        

        
Database 카테고리 내 다른 글 보러가기

        


  

  

  

  

  

  
  	
  	
  	
  	
  	


  

        


      

      


      


  

  

  

  

  

  
  	
  	
  	
  	
  	


  

    


    
      

  
  
      
댓글남기기

      

    



    
  


  
  
    

      
참고