[days04] 월급 금액 등급별로 사원 정렬해서 출력하기

salgrade, emp,  dept 테이블을 이용해서 아래 결과와 같이 나오도록 코딩하세요.
           
            [실행결과]
         1등급   (     700~1200 ) - 2명
               20   RESEARCH   7369   SMITH   800
               30   SALES         7900   JAMES   950
         2등급   (   1201~1400 ) - 2명
            30   SALES   7654   MARTIN   2650
            30   SALES   7521   WARD      1750   
         3등급   (   1401~2000 ) - 2명
            30   SALES   7499   ALLEN      1900
            30   SALES   7844   TURNER   1500
         4등급   (   2001~3000 ) - 4명
            20   RESEARCH   7902   FORD   3000
            20   RESEARCH   7566   JONES   2975
            30   SALES   7698   BLAKE   2850
            10   ACCOUNTING   7782   CLARK   2450
         5등급   (   3001~9999 ) - 1명   
            10   ACCOUNTING   7839   KING   5000   

 

package days04;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;

import com.util.DBConn;

import days02.EmpDTO;

public class Test {

	public static void main(String[] args) {
    
		//등급별 금액 인원수 출력
		String sql2 = "SELECT grade, losal, hisal "
			    +" ,(SELECT count(*) FROM emp WHERE sal between s.losal and s.hisal) cnt "
			    +" FROM salgrade s ";
				//조인 아니고 상관서브쿼리 씀
		
		Connection con = null;
		
		PreparedStatement ps = null, emppstmt = null;
		ResultSet rs = null, empRs = null;

		con = DBConn.getConnection();
		try {
			ps = con.prepareStatement(sql2);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				
				do {
					
					int  grade = rs.getInt("grade");
					int  losal = rs.getInt("losal");
					int  hisal = rs.getInt("hisal");
					int  cnt = rs.getInt("cnt");
					
					System.out.printf("%d등급 ( %d~%d ) - %d명\n"
									,grade, losal, hisal, cnt);
					
					//사원정보 출력 
					String sql3= "select d.deptno, dname,empno, ename, sal "
						+ " from emp e join dept d on d.deptno = e.deptno"
						+ "  join salgrade s on e.sal between s.losal and s.hisal "
						+ " where grade = ? " 
						+ " order by d.deptno";
					
					 emppstmt = con.prepareStatement(sql3);
					 emppstmt.setInt(1,grade);
					 empRs = emppstmt.executeQuery();
					 
					 while (empRs.next()) {
						 //d.deptno, dname,empno, ename, sal
						int deptno = empRs.getInt(1);
						String dname = empRs.getString(2);
						int empno = empRs.getInt(3);
						String ename  = empRs.getString(4);
						double sal = empRs.getDouble(5);
						
						System.out.printf("\t\t%d\t%s\t%d\t%s\t%.2f\n"
								, deptno, dname,empno, ename, sal);
					}
				}while(rs.next());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				ps.close();
				rs.close();
				DBConn.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			

		}
		
		System.out.println("end");
		
	}

}

 

 

 

*****

테이블이 A B C D E "조인"된 결과물이라면 
그 결과를 담기 위해 -> DTO 객체를 선언하면 된다 고민하지마

package days04;

import java.sql.Date;

public class EmpDeptDTO {

	
	//필드
	private int deptno;
	private String dname; 
	private int empno;
	private String ename;
	private Date hiredate;
	
	
	//게터세터
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	
	
	//생성자
	public EmpDeptDTO() {
		super();
	}
	
	public EmpDeptDTO(int deptno, String dname, int empno, String ename, Date hiredate) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.empno = empno;
		this.ename = ename;
		this.hiredate = hiredate;
	}
	
	
	//오바리이딩
	@Override
	public String toString() {
		return String.format("%d\t%s\t%d\t%s\t%s"
				, deptno, dname, empno, ename, hiredate);
	}
	
	
	
	
}