[Day3] JSP/JDBC 3 - 사용자 변경, 여러출력방식, linkedhashmap사용 DTO 저장, MVC, JUnit

[Day3] JSP/JDBC 3 [10/28]

--복습--
1.  dept ,emp 테이블에서
    부서명으로 검색하여  소속된 모든 사원 정보를 출력하는 코딩을 하세요.
      ㄱ. PreparedStatement 사용.
      ㄴ. ArrayList<EmpDTO> empList 사용.
      ㄷ. LIKE 연산자를 사용.

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<EmpDTO> empList = null;
	String pdname = null;
	Scanner scanner = new Scanner(System.in);
	String sql = " SELECT e.* "
			        + " FROM emp e JOIN dept d ON e.deptno = d.deptno "
		        	+ " WHERE d.dname LIKE ? ";  // '%AL%' 
	
	// 
	System.out.print("> 부서명 입력 ? ");
	pdname = scanner.next();		
	//
	conn =  DBConn.getConnection();
	try {
		pstmt =  conn.prepareStatement(sql);
		pstmt.setString(1,   String.format("%%%s%%",   pdname.toUpperCase() ) );
		rs = pstmt.executeQuery();
		
		if (  rs.next()) {
			empList = new ArrayList<EmpDTO>();
			do {
				EmpDTO dto = new EmpDTO(
						 rs.getInt("empno") 
						 , rs.getString("ename")
						 , rs.getString("job")
						 , rs.getDate("hiredate")
						 , rs.getInt("mgr")
						 , rs.getDouble("sal")
						 , rs.getDouble("comm")
						 , rs.getInt("deptno"));				 
				empList.add(dto);
			} while ( rs.next() );
		} // if
		
		// printEmp( empList );
		/*
		if( empList == null ) {
			System.out.println(" 사원 존재 X");
		}else {				
		}
		*/
		
		try {
			Iterator<EmpDTO> ir = empList.iterator();
			while (ir.hasNext()) {
				EmpDTO dto = ir.next();
				System.out.println( dto );
			} // while
		} catch (NullPointerException e) {
			System.out.println(" 사원 존재 X ");
		} catch( Exception e) {
			e.printStackTrace();
		} //  
		
	} catch (SQLException e) {
		
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close();
			DBConn.close();
		} catch (SQLException e) { 
			e.printStackTrace();
		} // try			 
	} // try
} // main

2. ConnectionString.properties 파일을 Properties 컬렉션을 사용해서

hostname, sid, user, password 속성값을 읽어서 DB 연동해서

HR의 departments 테이블의 부서 정보를 출력하는 코딩을 하세요.

ㄱ. PreparedStatement 사용.

ㄴ. ArrayList<DepartmentsDTO> list 사용.

ㄷ. manager_id 가 NULL 인 부서 정보만 출력.

[실행결과]

[department_id=140, department_name=Control And Credit, manager_id=0, location_id=1700]
[department_id=120, department_name=Treasury, manager_id=0, location_id=1700]
[department_id=130, department_name=Corporate Tax, manager_id=0, location_id=1700]
[department_id=150, department_name=Shareholder Services, manager_id=0, location_id=1700]
[department_id=160, department_name=Benefits, manager_id=0, location_id=1700]
[department_id=170, department_name=Manufacturing, manager_id=0, location_id=1700]
[department_id=180, department_name=Construction, manager_id=0, location_id=1700]
[department_id=190, department_name=Contracting, manager_id=0, location_id=1700]
[department_id=200, department_name=Operations, manager_id=0, location_id=1700]
[department_id=210, department_name=IT Support, manager_id=0, location_id=1700]
[department_id=220, department_name=NOC, manager_id=0, location_id=1700]
[department_id=230, department_name=IT Helpdesk, manager_id=0, location_id=1700]
[department_id=240, department_name=Government Sales, manager_id=0, location_id=1700]
[department_id=250, department_name=Retail Sales, manager_id=0, location_id=1700]
[department_id=260, department_name=Recruiting, manager_id=0, location_id=1700]
[department_id=270, department_name=Payroll, manager_id=0, location_id=1700]

1. ConnectionString.properties 수정 hr, lion

2. DepartmentsDTO 선언

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<DepartmentsDTO> list = null;
		
	String sql = " SELECT *  "
		         	 + " FROM DEPARTMENTS "
		         	 + " WHERE manager_id IS NULL";
	
	// conn = DBConn.getConnection();  scott 계정 접속. 
	
	String dir =  System.getProperty("user.dir");
	// C:\Class\WorkSpace\JdbcClass\jdbcPro
	// System.out.println( dir );		
	Path  path =  Paths.get(dir, "src\\com\\util\\ConnectionString.properties");				
	// System.out.println(  path ); 
	
	Reader reader = null;
	Properties p  = null;
	try {
		reader = new FileReader(  path.toString() );
		p  = new Properties();
		p.load(reader);
	} catch (FileNotFoundException e) { 
		e.printStackTrace();
	} catch (IOException e) { 
		e.printStackTrace();
	} // try
	
	// 연결문자열( Connection String )
	String user = p.getProperty("user") ;
	String	password = p.getProperty("password") ;
	String url = p.getProperty("url") ;
	String className = p.getProperty("className") ;		
	//System.out.println( user );
	
	conn =  DBConn.getConnection(url, user, password);
	// oracle.jdbc.driver.T4CConnection@4629104a
	// System.out.println( conn);
	
	try {
		pstmt =  conn.prepareStatement(sql);
		// ? 바인딩 변수 
		rs =  pstmt.executeQuery();
		
		if (  rs.next()) {
			list = new ArrayList<DepartmentsDTO>(); 
			do {
				DepartmentsDTO dto = new DepartmentsDTO(
						rs.getInt(1)
						, rs.getString(2)
						, rs.getInt(3)
						, rs.getInt(4));
				list.add(dto);
			} while ( rs.next() );
		} // if
		
		try {
			Iterator<DepartmentsDTO> ir = list.iterator();
			while (ir.hasNext()) {
				DepartmentsDTO dto = ir.next();
				System.out.println( dto );
			} // while
		} catch (NullPointerException e) {
			System.out.println(" 결과물 X ");
		} catch( Exception e) {
			e.printStackTrace();
		} //  
		
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close();
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	}	
	DBConn.close();
} // main

3. HR의 jobs 테이블에서 job_id 컬럼 또는  job_title 컬럼에
   'RE' 또는 're' 또는  'Re' 또는 'rE' ( 즉, 대소문자 구분 없이 ) 문자열을 포함하는 레코드를
   검색해서 아래와 같이 출력하세요.

JOB_ID JOB_TITLE
--------------------------------------------------------------
AD_P[RE]S P[re]sident
AD_VP Administration Vice P[re]sident
SA_[RE]P Sales [Re]p[re]sentative
MK_[RE]P Marketing [Re]p[re]sentative
HR_[RE]P Human [Re]sources [Re]p[re]sentative
PR_[RE]P Public [Re]lations [Re]p[re]sentative

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<JobsDTO> list = null;

	String sql = " SELECT  REGEXP_REPLACE( job_id, 'RE', '[RE]') job_id "
					+ "       , REGEXP_REPLACE( job_title, '(RE|Re|rE|re)', '[\\1]' ) job_title "
					+ " FROM jobs "
					+ " WHERE REGEXP_LIKE( job_id , 'RE' , 'i') OR REGEXP_LIKE( job_title , 'RE' , 'i')";
	
    String dir =  System.getProperty("user.dir"); 
	Path  path =  Paths.get(dir, "src\\com\\util\\ConnectionString.properties");		 
	Reader reader = null;
	Properties p  = null;
    
	try {
		reader = new FileReader(  path.toString() );
		p  = new Properties();
		p.load(reader);
	} catch (FileNotFoundException e) { 
		e.printStackTrace();
	} catch (IOException e) { 
		e.printStackTrace();
	} // try
    
	String user = p.getProperty("user") ;
	String	password = p.getProperty("password") ;
	String url = p.getProperty("url") ;
	String className = p.getProperty("className") ;		 
	conn =  DBConn.getConnection(url, user, password);
	try {
		pstmt =  conn.prepareStatement(sql);
		// ? 바인딩 변수 
		rs =  pstmt.executeQuery();
		if (  rs.next()) {
			list = new ArrayList<JobsDTO>(); 
			do {
				JobsDTO dto = new JobsDTO(
					 rs.getString(1)
					, rs.getString(2) 
					, 0
					, 0 );
				list.add(dto);
			} while ( rs.next() );
		} // if
		
        try {
			Iterator<JobsDTO> ir = list.iterator();
			while (ir.hasNext()) {
				JobsDTO dto = ir.next();
				System.out.println( dto );
			} // while
		} catch (NullPointerException e) {
			System.out.println(" 결과물 X ");
		} catch( Exception e) {
			e.printStackTrace();
		} //  
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close();
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	}
	DBConn.close();
} // main

JOIN 해서 넣어야하는 DTO가 필요하다면 해당되는 모든 칼럼을 저장할 DTO를 선언하면 됨!

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	DeptEmpSalgradeDTO dto = null;
	ArrayList<DeptEmpSalgradeDTO> list = null;
	String sql =    " SELECT d.deptno, d.dname, empno, ename, hiredate , sal  + NVL(comm, 0) pay , grade "
			+ " FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno "
			+ "                JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal "
			+ " ORDER BY d.deptno ASC      ";  //      ; 세미콜론 X
	
	conn = DBConn.getConnection();
	try {
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		if( rs.next() ) {
			list = new ArrayList<DeptEmpSalgradeDTO>();
			do {
				// deptno,  dname, empno, ename, hiredate ,  pay , grade
				int deptno = rs.getInt(1);
				String dname = rs.getString(2);
				int empno = rs.getInt(3);
				String ename = rs.getString(4);
				Date hiredate = rs.getDate(5);
				double pay = rs.getDouble(6);
				int grade = rs.getInt(7);					
				dto = new DeptEmpSalgradeDTO(deptno, dname, empno, ename, hiredate, pay, grade);					
				list.add(dto);
				
			}while( rs.next() );
		} // if
		
		//
		try {
			Iterator<DeptEmpSalgradeDTO> ir = list.iterator();
			while (ir.hasNext()) {
				dto = ir.next();
				System.out.println( dto );
			} // while
		} catch (NullPointerException e) {
			System.out.println(" 결과물 X ");
		} catch( Exception e) {
			e.printStackTrace();
		} //  
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close(); 
		} catch (SQLException e) { 
			e.printStackTrace();
		} // try
	} // try
	DBConn.close();	
} // main

[실행결과]

grade losal hisal emp-sal count(*)
------ -------------- --------- ---------
1등급 ( 700~1200 ) - 2명
2등급 ( 1201~1400 ) - 2명
3등급 ( 1401~2000 ) - 2명
4등급 ( 2001~3000 ) - 4명
5등급 ( 3001~9999 ) - 1명

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;		 
	SalgradeDTO dto = null;
	ArrayList<SalgradeDTO> list = null;		 
	String sql = "SELECT grade , losal ,   hisal "
				+ ",  count(*) cnt "
				+ " FROM salgrade s JOIN  emp e ON e.sal BETWEEN s.losal AND s.hisal "
				+ " GROUP BY grade , losal ,   hisal "
				+ " ORDER BY grade ASC";
                
	conn = DBConn.getConnection();
	
    try {
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if( rs.next() ) {
			list = new ArrayList<>();
			do { 
				int grade = rs.getInt(1); 
				int losal = rs.getInt(2); 
				int hisal = rs.getInt(3);					
				int cnt = rs.getInt(4);					
				dto = new SalgradeDTO(grade, losal, hisal, cnt);				
				list.add(dto);
			}while( rs.next() );
		} // if

		try {
			Iterator<SalgradeDTO> ir = list.iterator();
			while (ir.hasNext()) {
				dto = ir.next();
				System.out.println( dto );
			} // while
		} catch (NullPointerException e) {
			System.out.println(" 결과물 X ");
		} catch( Exception e) {
			e.printStackTrace();
		} //  
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close(); 
		} catch (SQLException e) { 
			e.printStackTrace();
		} // try
	} // try
	DBConn.close();
} // main

[실행결과]

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명
10 ACCOUNTING 7782 CLARK 2450
20 RESEARCH 7902 FORD 3000
20 RESEARCH 7566 JONES 2975
30 SALES 7698 BLAKE 2850

5등급 ( 3001~9999 ) - 1명
10 ACCOUNTING 7839 KING 5000

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null ,  e_pstmt = null;
	ResultSet rs = null, e_rs= null;		 
	SalgradeDTO dto = null;
	ArrayList<SalgradeDTO> list = null;		 
	String sql = "SELECT grade , losal ,   hisal "
				+ ",  count(*) cnt "
				+ " FROM salgrade s JOIN  emp e ON e.sal BETWEEN s.losal AND s.hisal "
				+ " GROUP BY grade , losal ,   hisal "
				+ " ORDER BY grade ASC";
	String sql2 = "SELECT d.deptno, dname, empno, ename, sal , grade "
				+ " FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno  "
				+ "            JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal "
				+ " WHERE grade = ?            ";
	
	//
	conn = DBConn.getConnection();
	try {
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();

		if( rs.next() ) {
			list = new ArrayList<>();
			do { 
				int grade = rs.getInt(1); 
				int losal = rs.getInt(2); 
				int hisal = rs.getInt(3);					
				int cnt = rs.getInt(4);					
				dto = new SalgradeDTO(grade, losal, hisal, cnt);				
				list.add(dto);
				
				// 등급에 해당하는 사원 정보를 출력. 
				System.out.println(dto);  // 1등급	(  700 ~ 1200 ) - 2명
				
				e_pstmt = conn.prepareStatement(sql2);
				e_pstmt.setInt(1, grade);
				e_rs = e_pstmt.executeQuery();
				
				if( e_rs.next()		) {
					// 컬렉션 클래스 저장
					do {
						int deptno = e_rs.getInt(1);
						String dname = e_rs.getString(2);
						int empno  = e_rs.getInt(3);
						String ename = e_rs.getString(4);
						double sal = e_rs.getDouble(5);
						
                        System.out.printf("\t\t%d\t%s\t%d\t%s\t%.2f\n", 
                        deptno, dname, empno, ename, sal);
					}while( e_rs.next() );
				}else {
					System.out.println("\t\t 사원 존재 X");
				} // if
				e_pstmt.close(); //
				e_rs.close();       // 
			}while( rs.next() );
				
		} // if
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close(); 
		} catch (SQLException e) { 
			e.printStackTrace();
		} // try
	} // try
	DBConn.close();
} // main

하나의 [컬렉션클래스]에 저장 : LinkedHashMap

LinkedHashMap<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>> map

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement pstmt = null ,  e_pstmt = null;
	ResultSet rs = null, e_rs= null;		 
	SalgradeDTO dto = null;   // key
	ArrayList<DeptEmpSalgradeDTO> list = null; // value
	DeptEmpSalgradeDTO desDto = null;
	LinkedHashMap<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>> map = null;
	
	String sql = "SELECT grade , losal ,   hisal "
				+ ",  count(*) cnt "
				+ " FROM salgrade s JOIN  emp e ON e.sal BETWEEN s.losal AND s.hisal "
				+ " GROUP BY grade , losal ,   hisal "
				+ " ORDER BY grade ASC";
	String sql2 = "SELECT d.deptno, dname, empno, ename, sal , grade "
				+ " FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno  "
				+ "            JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal "
				+ " WHERE grade = ?            ";

	conn = DBConn.getConnection();
	try {
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if( rs.next() ) {		
			map = new LinkedHashMap<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>>();
		
			do { 
				int grade = rs.getInt(1); 
				int losal = rs.getInt(2); 
				int hisal = rs.getInt(3);					
				int cnt = rs.getInt(4);			
						
				dto = new SalgradeDTO(grade, losal, hisal, cnt);		// key		
				
				// 등급에 해당하는 사원 정보를 출력. 
				//System.out.println(dto);  // 1등급	(  700 ~ 1200 ) - 2명
				list = null;
				
				e_pstmt = conn.prepareStatement(sql2);
				e_pstmt.setInt(1, grade);
				e_rs = e_pstmt.executeQuery();
				
				if( e_rs.next()		) {
					list = new ArrayList<DeptEmpSalgradeDTO>();
					do {
						int deptno = e_rs.getInt(1);
						String dname = e_rs.getString(2);
						int empno  = e_rs.getInt(3);
						String ename = e_rs.getString(4);
						double sal = e_rs.getDouble(5);
								
						//System.out.printf("\t\t%d\t%s\t%d\t%s\t%.2f\n", 
						//		deptno, dname, empno, ename, sal);
								
						desDto = new DeptEmpSalgradeDTO(deptno, dname, empno, ename, null, sal, grade);								
						list.add(desDto);  // value
								
					}while( e_rs.next() );
                    
					map.put(dto, list); // entry 추가
						
				}// if
				e_pstmt.close(); //
				e_rs.close();       // 
			}while( rs.next() );	
		} // if	
	} catch (SQLException e) { 
			e.printStackTrace();
	} finally {
		try {
			pstmt.close();
			rs.close(); 
		} catch (SQLException e) { 
			e.printStackTrace();
		} // try
	} // try
	DBConn.close();
	printInfo( map );
} // main

private static void printInfo( LinkedHashMap<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>> map ) {
	Set<Entry<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>>>  set = map.entrySet();
		
	Iterator<Entry<SalgradeDTO, ArrayList<DeptEmpSalgradeDTO>>> ir = set.iterator();
		
	while (ir.hasNext()) {
		Entry<SalgradeDTO,ArrayList<DeptEmpSalgradeDTO>> entry =  ir.next();
			
		SalgradeDTO key = entry.getKey();
		System.out.println(  key );
			
		ArrayList<DeptEmpSalgradeDTO>  list =  entry.getValue();
		Iterator<DeptEmpSalgradeDTO>  ir2 = list.iterator();
		while (ir2.hasNext()) {
			DeptEmpSalgradeDTO dto =  ir2.next();
			// System.out.printf("\t\t%d\t%s\t%d\t%s\t%.2f\n"
			// ,dto.getDeptno(), dname, empno, ename, sal);
			System.out.println( dto );
		} // while	
	} // while
}

MVC (model view controller) 패턴

view : 화면

+ 서비스 : 로직 처리하는 모델들을 호출하는 역할

model : 결과물 (로직처리 + 데이터) -> DAO 요청 -> CRUD -> 오라클

controller : 모델과 뷰 상호작용 관리

저작자표시

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

[Day1] JSP/Servlet 1 - 개요 (0)	2022.12.06
[Day5] JSP/JDBC 5 - transaction 처리, callable statement, reflection (0)	2022.11.02
[Day4] JSP/JDBC 4 - Board (0)	2022.11.01
[Day2] JSP/JDBC 2 - 동적쿼리, PreparedStatement (0)	2022.10.31
[Day1] JSP/JDBC 1 - 개요, jdbc driver, dto (0)	2022.10.28

[Day3] JSP/JDBC 3 - 사용자 변경, 여러출력방식, linkedhashmap사용 DTO 저장, MVC, JUnit

[Day3] JSP/JDBC 3 [10/28]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day3] JSP/JDBC 3 [10/28]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바