회원등록, DB -22.06.23

웹프로그래밍 2022. 6. 23. 18:31

728x90

오늘수업 목표

타일즈 프레임워크 ?

마이바티스 프레임워크 -> 롬북 프레임워크

필터를 얹어보겠다. 리스너 구조를 얹어보자

컨트롤 쉬프트 엘 -> 외우기

MEMBER 끌어다가 놓고 insert누르면 됨

컨트롤 +f 해서 찾기 한다음에 : 영문자와숫자를 포함한 한글자

영문자와숫자를 포함한 한글자로 만들어서 ? 로 대체

한번더 수정!!!!

SELECT 'stmt.set' ||
    DECODE(DATA_TYPE, 'NUMBER', 'Int', 'String')||
    '(idx++'||','|| 
    LOWER(TABLE_NAME)||'.get'||REPLACE(INITCAP(COLUMN_NAME),'_','')||
    '());'
FROM COLS
WHERE TABLE_NAME ='MEMBER'
ORDER BY COLUMN_ID;

int idx =1;
		stmt.setString(idx++,member.getMemId());

DAO만 테스트하고싶어

=>만약에 안된다면 pom.xml에 Junit추가해줘야한다.

package kr.or.ddit.member.dao;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;

import kr.or.ddit.vo.MemberVO;

public class MemberDAOTest {
	MemberDAO dao = new MemberDAOImpl();
	
	@Test
	public void testSelectMemberForAuth() {
		MemberVO inputData = new MemberVO();
		inputData.setMemId("a001");
		MemberVO member = dao.selectMemberForAuth(inputData);
		assertNotNull(member);
	}

	@Test
	public void testInsertMember() {
		fail("Not yet implemented");
	}

	@Test
	public void testSelectMemberList() {
		List<MemberVO> memberList =dao.selectMemberList();
		assertNotNull(memberList);
		assertNotEquals(0, memberList.size());
	}

}

단정 메소드(assert method)

· JUnit에서 가장 많이 이용되는 단정(assert) 메소드입니다.

· 단정 메서드로 테스트 케이스의 수행 결과를 판별합니다.

assertNull(o)	· 객체o가 null인지 확인합니다.
assertNotNull(o)	· 객체o가 null이 아닌지 확인합니다.
assertEquals(x, y)	. 객체 x와 y가 일치함을 확인합니다. · x(예상 값)와 y(실제 값)가 같으면 테스트 통과

testSelectMemberForAuth()했을떄 결과

=> testSelectMemberForAuth()했을떄 결과

=>마일리지는 1000으로 코딩했기 때문에 이미 1000있으므로 지우기

바꾸기

이 테스트 메소드는 RuntimeException이 발생해야 테스트가 성공, 그렇지 않으면 실패입니다.

insert확인하기

package kr.or.ddit.member.dao;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.util.List;

import org.junit.Test;

import kr.or.ddit.vo.MemberVO;

public class MemberDAOTest {
	MemberDAO dao = new MemberDAOImpl();
	
	@Test()
	public void testSelectMemberForAuth() {
		MemberVO inputData = new MemberVO();
		inputData.setMemId("a001");
		MemberVO member = dao.selectMemberForAuth(inputData);
		assertNotNull(member);
	}

	@Test(expected=RuntimeException.class)
	public void testInsertMemberThrow() {
		MemberVO member = new MemberVO();
		dao.insertMember(member);
	}
	@Test
	public void testInsertMember() {
		MemberVO member = new MemberVO();
		member.setMemId("a002");
		member.setMemPass("java");
		member.setMemName("신규");
		member.setMemBir("1999-01-01");
		member.setMemZip("000-000");
		member.setMemAdd1("대전");
		member.setMemAdd2("오류");
		member.setMemHp("000-000-0000");
		member.setMemMail("aa@gmail.net");
		
		int rowcnt =dao.insertMember(member);
		assertEquals(1, rowcnt);
	}

	@Test
	public void testSelectMemberList() {
		List<MemberVO> memberList =dao.selectMemberList();
		assertNotNull(memberList);
		assertNotEquals(0, memberList.size());
	}

}

성공!!!

근데 롤백을 했어야 맞음 테스트 하는과정에서 들어간 데이터 거기서끝나도록

트랜잭션 관리를 직접해야함 -> 나중에 프레임통해서 할것임 그냥 기억만 해둬!!!

<insert하는 MemberDAOImpl.java>코드

@Override
public int insertMember(MemberVO member) {
	
	StringBuffer sql = new StringBuffer();                       
	sql.append("	INSERT INTO member (                              ");
	sql.append("		    MEM_ID, MEM_PASS, MEM_NAME,                 ");
	sql.append("		    MEM_REGNO1, MEM_REGNO2, MEM_BIR,            ");
	sql.append("		    MEM_ZIP, MEM_ADD1, MEM_ADD2,                ");
	sql.append("		    MEM_HOMETEL, MEM_COMTEL, MEM_HP,            ");
	sql.append("		    MEM_MAIL ,MEM_JOB ,MEM_LIKE,                ");
	sql.append("		    MEM_MEMORIAL, MEM_MEMORIALDAY, MEM_MILEAGE ");
	sql.append("		) VALUES (                                    ");
	sql.append("	        ?,?,?,                              ");
	sql.append("			?,?,TO_DATE(?,'YYYY-MM-DD'),        ");
	sql.append("			?,?,?,                            ");
	sql.append("			?,?,?,                            ");
	sql.append("			?,?,?,                            ");
	sql.append("			?,TO_DATE(?,'YYYY-MM-DD'),1000     ");
	sql.append(")                                               ");
	
		
	
	try (Connection conn = ConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql.toString());) {
		
		int idx =1;
		stmt.setString(idx++,member.getMemId());
		stmt.setString(idx++,member.getMemPass());
		stmt.setString(idx++,member.getMemName());
		stmt.setString(idx++,member.getMemRegno1());
		stmt.setString(idx++,member.getMemRegno2());
		stmt.setString(idx++,member.getMemBir());
		stmt.setString(idx++,member.getMemZip());
		stmt.setString(idx++,member.getMemAdd1());
		stmt.setString(idx++,member.getMemAdd2());
		stmt.setString(idx++,member.getMemHometel());
		stmt.setString(idx++,member.getMemComtel());
		stmt.setString(idx++,member.getMemHp());
		stmt.setString(idx++,member.getMemMail());
		stmt.setString(idx++,member.getMemJob());
		stmt.setString(idx++,member.getMemLike());
		stmt.setString(idx++,member.getMemMemorial());
		stmt.setString(idx++,member.getMemMemorialday());
		
		
		return stmt.executeUpdate();

	
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}

TDD(Test Driven Development : 테스트 주도형 개발) 개발방법론

MemberService.java에서 Junit테스트 하기

처음엔 로직하나도 구현x -> 테스트케이스를 만들고 -> 로직이 테스트 케이스만족할 수 있도록 로직만들고-> 테스트케이스로직 수정하고 이런식으로 반복 하는것 수정하고 만족시키고를 반복

package kr.or.ddit.member.service;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;

import kr.or.ddit.enumpkg.ServiceResult;
import kr.or.ddit.vo.MemberVO;
/**
 * TDD(Test Driven Development : 테스트 주도형 개발) 개발방법론
 * EDD(Event Driven Development: 이벤트 주도형 개발)
 *
 */
public class MemberServiceTest {
   MemberService service = new MemberServiceImpl();

   @Test
   public void testCreateMember() {
      MemberVO member = new MemberVO();
      member.setMemId("a002");
      member.setMemPass("java");
      member.setMemName("신규");
      member.setMemBir("1999-01-01");
      member.setMemZip("000-000");
      member.setMemAdd1("대전");
      member.setMemAdd2("오류");
      member.setMemHp("000-000-0000");
      member.setMemMail("aa@gmail.net");
      ServiceResult result = service.createMember(member);
      assertEquals(ServiceResult.PKDUPLICATED, result);
      member.setMemId("a003");
      result = service.createMember(member);
      assertEquals(ServiceResult.OK, result);
      
   }

   @Test
   public void testRetrieveMemberList() {
      List<MemberVO> memberList = service.retrieveMemberList();
      assertNotNull(memberList);
      
      
   }

}

MemberInsertServle 수정

package kr.or.ddit.member.web;

import java.io.IOException;
import java.util.Enumeration;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;

import kr.or.ddit.mvc.DelegatingViewResolver;
import kr.or.ddit.mvc.GridTemplateViewResolver;
import kr.or.ddit.vo.MemberVO;

@WebServlet("/member/memberInsert.do") //RESTful URL : 행위 포함 하지않는것
public class MemberInsertServlet extends HttpServlet {
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//		req.setAttribute("contents","/WEB-INF/views/member/memberForm.jsp" );
//		String view ="/WEB-INF/views/template.jsp";
//		req.getRequestDispatcher(view).forward(req, resp);
		String viewName ="/WEB-INF/views/member/memberForm.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
		new DelegatingViewResolver().viewResolve(viewName, req, resp);
	}
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
	
	}
}

String viewName ="member/memberForm"; : 로직컬 뷰 네임형태, 인터널 사용되며 템플릿 UI X

COMMENT ON COLUMN MEMBER.MEM_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEM_PASS IS '비밀번호';

=> 이런식으로 코멘트 달아주고 ..

코멘트 (0622수업코드에 있음)

SELECT *
FROM USER_COL_COMMENTS
WHERE TABLE_NAME ='MEMBER';

SELECT '<tr><th>'||
    NVL(COMMENTS, A.COLUMN_NAME)||'</th><td>'||
    '<input type="'||
    CASE WHEN A.DATA_TYPE IN('DATE','NUMBER')
        THEN LOWER(DATA_TYPE)
      WHEN A.DATA_TYPE ='VARCHAR2' OR A.DATA_TYPE ='CHAR'
            THEN 'text'
       ELSE 'text' END ||
       '"name="' || SNAKETOCAMEL(A.COLUMN_NAME) ||
       '" '|| DECODE(NULLABLE, 'N','required','')||
       '/></td></tr>'
FROM COLS A LEFT OUTER JOIN USER_COL_COMMENTS B
    ON(A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME)
WHERE A.TABLE_NAME ='MEMBER';

=> 코드 조각 저장하기

위에 db에서 만든것 폼에 적용하기 ctrl+shift+f하면 코드 정리가 됨

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<h4>회원 가입 양식</h4>
<form method="post"
	action="<%=request.getContextPath()%>/member/memberInsert.do"
	enctype="application/x-www-form-urlencoded">
	<table>
		<tr>
			<th>회원아이디</th>
			<td><input type="text" name="memId" required class="form-control"/></td>
		</tr>
		<tr>
			<th>비밀번호</th>
			<td><input type="text" name="memPass" required /></td>
		</tr>
		<tr>
			<th>회원명</th>
			<td><input type="text" name="memName" required /></td>
		</tr>
		<tr>
			<th>주민번호1</th>
			<td><input type="text" name="memRegno1" /></td>
		</tr>
		<tr>
			<th>주민번호2</th>
			<td><input type="text" name="memRegno2" /></td>
		</tr>
		<tr>
			<th>생일</th>
			<td><input type="date" name="memBir" required /></td>
		</tr>
		<tr>
			<th>우편번호</th>
			<td><input type="text" name="memZip" required /></td>
		</tr>
		<tr>
			<th>주소1</th>
			<td><input type="text" name="memAdd1" required /></td>
		</tr>
		<tr>
			<th>주소2</th>
			<td><input type="text" name="memAdd2" required /></td>
		</tr>
		<tr>
			<th>집전화번호</th>
			<td><input type="text" name="memHometel" /></td>
		</tr>
		<tr>
			<th>회사번호</th>
			<td><input type="text" name="memComtel" /></td>
		</tr>
		<tr>
			<th>휴대폰</th>
			<td><input type="text" name="memHp" required /></td>
		</tr>
		<tr>
			<th>이메일</th>
			<td><input type="text" name="memMail" required /></td>
		</tr>
		<tr>
			<th>직업</th>
			<td><input type="text" name="memJob" /></td>
		</tr>
		<tr>
			<th>취미</th>
			<td><input type="text" name="memLike" /></td>
		</tr>
		<tr>
			<th>기념일</th>
			<td><input type="text" name="memMemorial" /></td>
		</tr>
		<tr>
			<th>기념일자</th>
			<td><input type="date" name="memMemorialday" /></td>
		</tr>
		<tr>
			<th>마일리지</th>
			<td><input type="number" name="memMileage" /></td>
		</tr>
		<tr>
			<th>탈퇴여부</th>
			<td><input type="text" name="memDelete" /></td>
		</tr>
		<tr>
			<td colspan="2">
				<input type="submit" class="btn btn-primary" value="저장" />
				<input type="reset" class="btn btn-warning" value="취소" />
			</td>
		</tr>
	</table>
</form>

<MemberInsertServlet.java> 의 doPost다시 작업하기

필수데이터여부 데이터형태 데이터 길이 -> 데이터베이스 member테이블의 스키마 구조에 따라 결졍됨

총 9개의 낫널데이터 데이터 입력 여부 확인이 필요함

package kr.or.ddit.member.web;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Enumeration;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;

import kr.or.ddit.enumpkg.ServiceResult;
import kr.or.ddit.member.service.MemberService;
import kr.or.ddit.member.service.MemberServiceImpl;
import kr.or.ddit.mvc.DelegatingViewResolver;
import kr.or.ddit.mvc.GridTemplateViewResolver;
import kr.or.ddit.vo.MemberVO;


@WebServlet("/member/memberInsert.do") //RESTful URL : 행위 포함 하지않는것
public class MemberInsertServlet extends HttpServlet {
	MemberService service =new MemberServiceImpl();
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//		req.setAttribute("contents","/WEB-INF/views/member/memberForm.jsp" );
//		String view ="/WEB-INF/views/template.jsp";
//		req.getRequestDispatcher(view).forward(req, resp);
		String viewName ="/WEB-INF/views/member/memberForm.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
		new DelegatingViewResolver().viewResolve(viewName, req, resp);
	}
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		req.setCharacterEncoding("UTF-8");
		MemberVO member = new MemberVO();
		req.setAttribute("member", member); // 정상적 데이터 나중에 보내기 위해 사용
//		member.setMemId(req.getParameter("memId")); BeanUtils사용하자!
		
		try {
			BeanUtils.populate(member, req.getParameterMap());
		} catch (IllegalAccessException | InvocationTargetException e) {
			throw new ServletException(e);  //위에이미 에러 던지고있음 상황에 따라 다르다
		}
		Map<String,String> errors = new LinkedHashMap<>();
		req.setAttribute("errors", errors); //실패한게 뭔지도 알려줘야하니까 가지고가기위해
		boolean valid =validate(member,errors);
		String viewName =null;
		if(valid) { //검증 통과
			ServiceResult result =service.createMember(member);
			switch (result) {
			case PKDUPLICATED:
				req.setAttribute("message", "아이디 중복");
				viewName ="/WEB-INF/views/member/memberForm.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
				break;
			case FAIL:  //검증통과 등록하다가 문제, 클라이언트 잘못x 서버문제
				req.setAttribute("message", "서버의 문제로 등록을 못했어 다시해줘");
				viewName ="/WEB-INF/views/member/memberForm.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
				break;
			default:  //OK됨 그런데 forward구조이므로 redirect보낸다는 규칙만들자
				viewName ="redirect:/login/loginForm.jsp";
				break;
			}
		}else { //검증 실패 -> 정상인데이터가지고(memberVO안에존재)
			viewName ="/WEB-INF/views/member/memberForm.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
		}
		new DelegatingViewResolver().viewResolve(viewName, req, resp);
	}
	
	private boolean validate(MemberVO member, Map<String,String> errors) {
		boolean valid = true;
		if(StringUtils.isBlank(member.getMemId())) {
			errors.put("memId","회원아이디 누락");
			valid =false;
		}
		if(StringUtils.isBlank(member.getMemPass())) {
			errors.put("memPass","비밀번호 누락");
			valid =false;
		}
		return valid;
	}
}

viewName ="redirect:/login/loginForm.jsp"; 이렇게 사용하려고

<DelegatingViewResolver>수정

@Override
	public void viewResolve(String viewName, HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		if(viewName.startsWith("redirect:")) {
			viewName =viewName.substring("redirect:".length());
			response.sendRedirect(request.getContextPath()+viewName);
		}else {
			
			//forward 에서 사용
			ViewResolver finded = findViewResolver(viewName);
			finded.viewResolve(viewName, request, response);
		}
		
		
		
	}

<menberForm.jsp>

회원가입 양식 에서 추가

<%-- <%	
	MemberVO member =(MemberVO)request.getAttribute("member");
	if(member==null)
		member = new MemberVO(); //강제로 빈 member만들어주기
	Map<String,String> errors =(Map)request.getAttribute("errors");
		if(errors==null)
			errors = new LinkedHashMap<>();
%> --%>

<jsp:useBean id="member" class="kr.or.ddit.vo.MemberVO" scope="request"/>
<jsp:useBean id="errors" class="java.util.LinkedHashMap" scope="request"/>

=> 나중에 자바코드로 바뀔때 위에 코드처럼 바뀜

jsp:useBean 엑션 태그 : 자바 인스턴스를 준비한다. 보관소에서 자바 인스턴스를 꺼내거나 자바 인스턴스를 새로 만들어 보관소에 저장하는 코드를 생성한다.
(자바 인스턴스 = Java Bean)

<input type="text" name="memId"  class="form-control" value='<%=Objects.toString(member.getMemId(),"") %>'/>


이렇게 코드를 더 간단히 쓸 수 있음 
 value="${member.memPass }"
 
 최종 수정한 코드 
 
 <tr>
			<th>회원아이디</th>
			<td>
			<input type="text" name="memId"  class="form-control" value='<%=Objects.toString(member.getMemId(),"") %>'/>
				<span class="error"><%=errors.get("memId")%></span>
			</td>
		</tr>
		<tr>
			<th>비밀번호</th>
			<td><input type="text" name="memPass" value="${member.memPass }" />
				<sapn class="error">${errors['memPass']}</sapn>
			</td>		
		</tr>

<MemberInsertServlet>에서 수정


default:  //OK됨 그런데 forward구조이므로 redirect보낸다는 규칙만들자
req.getSession().setAttribute("message", "등록성공");
viewName ="redirect:/login/loginForm.jsp";
break;
}

=>추가

<loginForm.jsp> 등록성공이라는 메세지를 세션에담았잖아 그결과 여기에 출력하고 지우기

=>추가

미션 -임플로이 테이블 계층구조로 나타내기

HR 에서...

GRANT SELECT ON EMPLOYEES TO LAI; --select만 권한 주기
GRANT ALL ON EMPLOYEES TO LAI; --모든권한 주기

<권한 부여>

SELECT *
FROM HR.EMPLOYEES
WHERE MANAGER_ID='100';  --계급에 따라서 계층구조 형성중 , 트리뷰를 쓸 수 있다.

1. 스칼라 쿼리 맨위 select에 들어갈경우 레코드도 컬럼도 하나여야 한다./ 스칼라 데이터 : 그냥 값만 존재할때 / 백터데이터: [23,12] 방향과크기가존재하며 차원..

3. 중첩쿼리 where절에 들어갈 경우

-- 부하직원 cnt 구하기

SELECT A.*,
(SELECT COUNT(EMPLOYEE_ID)
            FROM EMPLOYEES B
            WHERE A.EMPLOYEE_ID =B.MANAGER_ID
)CHILD_CNT
FROM EMPLOYEES A

<보강>

중첩쿼리 :  scala query(Select),
           inline view(From),
           nested query(Where)
--1. 모든 부서의 정보 조회,
--   동시에 해당 부서에 소속된 인원수를 함께 조회.
SELECT  A.*, 
(
    SELECT  COUNT(EMPLOYEE_ID)
        FROM EMPLOYEES B
        WHERE A.DEPARTMENT_ID =B.DEPARTMENT_ID
)EMP_CNT
FROM DEPARTMENTS A
--2. 90 번 부서의 모든 정보 조회,
--        동시에 해당 부서에 소속된 사원의 이름, 사번, 연락처
-- FROM절안에 인라인 표현
SELECT A.*, B.*
FROM DEPARTMENTS A LEFT OUTER JOIN(
    SELECT EMPLOYEE_ID, PHONE_NUMBER,DEPARTMENT_ID
             , FIRST_NAME || ' '||LAST_NAME AS EMP_NAME
    FROM EMPLOYEES
) B ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID)
WHERE A.DEPARTMENT_ID =90; 
--3. 소속된 부서원이 한명도 없는 부서의 정보 조회.
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID NOT IN (
    SELECT DISTINCT NVL(DEPARTMENT_ID,0)
    FROM EMPLOYEES
);

<미션 해결 방법>

1. EmployeeVO 구조 다바꿔버리기

2. 어댑터 패턴 활용하기 트리에 맞는 돼지코..

Integer : WRAPPER클래스 반드시 값이 있어야 생성가능

=> Integer num = new Integer(34); 34값만 존재하기에 레퍼런스존재할 수 없어 랩핑을 하면 레퍼런스 생긴것임 그리고 ㅜnum에게 준것이다, 원래의 객체를 또다른 객체로 감싸서 본래의 객체 성격을 잃어버리게

Adapter pattern(WRAPPER pattern)

//부하 직원의 존재여부 , 폴더다라는건 누군가의 상관 폴더 아니면 말단임 adaptee.getChildCount()>0

최말단은 Lazy할필요없음

<leftMenu.jsp> 메뉴 추가

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <nav id="sidebarMenu" class="col-md-3 col-lg-2 d-md-block bg-light sidebar collapse">
      <div class="position-sticky pt-3">
        <ul class="nav flex-column">
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="#">
              <span data-feather="home"></span>
              Dashboard
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/member/memberList.do">
              <span data-feather="file"></span>
              		회원관리
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="#">
              <span data-feather="file"></span>
              		상품관리
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="#">
              <span data-feather="file"></span>
              		거래처관리
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="#">
              <span data-feather="file"></span>
              		자유게시판
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="#">
              <span data-feather="file"></span>
              		방명록
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/11/jdbdDesc.do">
              <span data-feather="file"></span>
              		DataBase Property
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/server/browsing.do">
              <span data-feather="file"></span>
              		Server File Browsing(Tree)
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/employee/employeeList.do">
              <span data-feather="file"></span>
              		Employee Organization(Tree)
            </a>
          </li>
        </ul>

        <h6 class="sidebar-heading d-flex justify-content-between align-items-center px-3 mt-4 mb-1 text-muted">
          <span>Model1 Service</span>
          <a class="link-secondary" href="#" aria-label="Add a new report">
            <span data-feather="plus-circle"></span>
          </a>
        </h6>
        <ul class="nav flex-column mb-2">
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/model1/service.do?command=FACTORIAL">
              <span data-feather="file-text"></span>
              Factorial
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="<%=request.getContextPath() %>/model1/service.do?command=CALENDAR">
              <span data-feather="file-text"></span>
              Calendar
            </a>
          </li>
         
        </ul>
      </div>
    </nav>

package kr.or.ddit.vo;

import java.io.Serializable;

public class EmployeeVO implements Serializable{
	private Integer employeeId;
	private String firstName;
	private String lastName;
	private String email;
	private String phoneNumber;
	private String hireDate;
	private String jobId;
	private Integer salary;
	private Integer commissionPct;
	private Integer managerId;
	private Integer departmentId;
	private String empName;
	private String retireDate;
	
	private int childCount;
	
	public int getChildCount() {
		return childCount;
	}
	public void setChildCount(int childCount) {
		this.childCount = childCount;
	}
	
	public Integer getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(Integer employeeId) {
		this.employeeId = employeeId;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhoneNumber() {
		return phoneNumber;
	}
	public void setPhoneNumber(String phoneNumber) {
		this.phoneNumber = phoneNumber;
	}
	public String getHireDate() {
		return hireDate;
	}
	public void setHireDate(String hireDate) {
		this.hireDate = hireDate;
	}
	public String getJobId() {
		return jobId;
	}
	public void setJobId(String jobId) {
		this.jobId = jobId;
	}
	public Integer getSalary() {
		return salary;
	}
	public void setSalary(Integer salary) {
		this.salary = salary;
	}
	public Integer getCommissionPct() {
		return commissionPct;
	}
	public void setCommissionPct(Integer commissionPct) {
		this.commissionPct = commissionPct;
	}
	public Integer getManagerId() {
		return managerId;
	}
	public void setManagerId(Integer managerId) {
		this.managerId = managerId;
	}
	public Integer getDepartmentId() {
		return departmentId;
	}
	public void setDepartmentId(Integer departmentId) {
		this.departmentId = departmentId;
	}
	public String getEmpName() {
		return empName;
	}
	public void setEmpName(String empName) {
		this.empName = empName;
	}
	public String getRetireDate() {
		return retireDate;
	}
	public void setRetireDate(String retireDate) {
		this.retireDate = retireDate;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((employeeId == null) ? 0 : employeeId.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		EmployeeVO other = (EmployeeVO) obj;
		if (employeeId == null) {
			if (other.employeeId != null)
				return false;
		} else if (!employeeId.equals(other.employeeId))
			return false;
		return true;
	}
	
	@Override
	public String toString() {
		return "EmployeeVO [employeeId=" + employeeId + ", firstName=" + firstName + ", lastName=" + lastName
				+ ", email=" + email + ", phoneNumber=" + phoneNumber + ", hireDate=" + hireDate + ", jobId=" + jobId
				+ ", salary=" + salary + ", commissionPct=" + commissionPct + ", managerId=" + managerId
				+ ", departmentId=" + departmentId + ", empName=" + empName + ", retireDate=" + retireDate + "]";
	}
	
	
}

FancyTreeNodeEmployeeAdapter

package kr.or.ddit.vo.fancytree;

import kr.or.ddit.vo.EmployeeVO;

public class FancyTreeNodeEmployeeAdapter implements FancyTreeNode<EmployeeVO>{
	
	private EmployeeVO adaptee;
	
	public  FancyTreeNodeEmployeeAdapter(EmployeeVO adaptee) {
		super();
		this.adaptee =adaptee;
	}
	@Override
	public String getKey() {
		return adaptee.getEmployeeId().toString();
	}

	@Override
	public String getTitle() {
		return String.format("%s %s", adaptee.getLastName(), adaptee.getFirstName());
	}

	@Override  //부하 직원의 존재여부 , 폴더다라는건 누군가의 상관 폴더 아니면 말단임
	public boolean isFolder() {
		return adaptee.getChildCount()>0;
	}

	@Override  //말단은 Lazy할필요없으며 폴더의 존재여부로..
	public boolean isLazy() {
		return isFolder();
	}

	@Override
	public boolean isExpanded() {
		return false;
	}

	@Override
	public EmployeeVO getAdaptee() {
		return adaptee;
	}
	
	@Override   //연봉으로 정렬
	public int compareTo(FancyTreeNode<EmployeeVO> o) {
		
		return -(adaptee.getSalary() - o.getAdaptee().getSalary());
		//내가 적으면 - 내가많으면 + 근데 많은 순서대로 할것이니까 -붙이기
	}

}

< interface EmployeeDAO>

package kr.or.ddit.employee.dao;

import java.util.List;

import kr.or.ddit.vo.EmployeeVO;

public interface EmployeeDAO {
	public List<EmployeeVO> selectEmployeeList(Integer managerId);
}

EmployeeDAOImpl

package kr.or.ddit.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.ddit.db.ConnectionFactory;
import kr.or.ddit.vo.EmployeeVO;

public class EmployeeDAOImpl implements EmployeeDAO {

	@Override
	public List<EmployeeVO> selectEmployeeList(Integer managerId) {
		StringBuffer sql = new StringBuffer();
                                                                                                 
		sql.append(" SELECT                                                                      ");
		sql.append("     EMPLOYEE_ID,    FIRST_NAME,    LAST_NAME,                               ");
		sql.append("     EMAIL,    PHONE_NUMBER,    TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') HIRE_DATE,  ");
		sql.append("     JOB_ID,    SALARY,    COMMISSION_PCT,                                   ");
		sql.append("     MANAGER_ID,    DEPARTMENT_ID,    EMP_NAME,                              ");
		sql.append("     RETIRE_DATE                                                             ");
		sql.append( " , (                                          ");
		sql.append( " 	    SELECT COUNT(EMPLOYEE_ID)              ");
		sql.append( " 	    FROM HR.EMPLOYEES B                    ");
		sql.append( " 	    WHERE A.EMPLOYEE_ID = B.MANAGER_ID     ");
		sql.append( " 	) CHILDCOUNT                               ");
		sql.append(" FROM    HR.EMPLOYEES  A ");
		if(managerId==null)
			sql.append(" WHERE MANAGER_ID IS NULL ");
		else
			sql.append(" WHERE MANAGER_ID = ? ");
		List<EmployeeVO> empList = new ArrayList<>();
		try (Connection conn = ConnectionFactory.getConnection();
				PreparedStatement stmt = conn.prepareStatement(sql.toString());) {

			if(managerId!=null)
				stmt.setInt(1, managerId);
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				EmployeeVO employee = new EmployeeVO();
				empList.add(employee);
				employee.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
				employee.setFirstName(rs.getString("FIRST_NAME"));
				employee.setLastName(rs.getString("LAST_NAME"));
				employee.setEmail(rs.getString("EMAIL"));
				employee.setPhoneNumber(rs.getString("PHONE_NUMBER"));
				employee.setHireDate(rs.getString("HIRE_DATE"));
				employee.setJobId(rs.getString("JOB_ID"));
				employee.setSalary(rs.getInt("SALARY"));
				employee.setCommissionPct(rs.getInt("COMMISSION_PCT"));
				employee.setManagerId(rs.getInt("MANAGER_ID"));
				employee.setDepartmentId(rs.getInt("DEPARTMENT_ID"));
				employee.setEmpName(rs.getString("EMP_NAME"));
				employee.setRetireDate(rs.getString("RETIRE_DATE"));
				
				//=========================================
				employee.setChildCount(rs.getInt("CHILDCOUNT"));
			}
			return empList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

-또다른 자식을 찾아야한다면 manager_id 넘어오겠지그럼 if,else따줘서

-if(managerId ==null) sql.append("where manager_id is null");

-else sql.append("where ,anager_id =?");

< view : employee: emplotList.jsp >

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<link href="//cdn.jsdelivr.net/npm/jquery.fancytree@2.27/dist/skin-win8/ui.fancytree.min.css" rel="stylesheet">
<script src="//cdn.jsdelivr.net/npm/jquery.fancytree@2.27/dist/jquery.fancytree-all-deps.min.js"></script>
<body>
<div id="tree"></div>
<table class="table-bordered col-8" id="displayArea">
	<tr><th>EMPLOYEE_ID</th><td id="employeeId"></td></tr>
	<tr><th>FIRST_NAME</th><td id="firstName"></td></tr>
	<tr><th>LAST_NAME</th><td id="lastName"></td></tr>
	<tr><th>EMAIL</th><td id="email"></td></tr>
	<tr><th>PHONE_NUMBER</th><td id="phoneNumber"></td></tr>
	<tr><th>HIRE_DATE</th><td id="hireDate"></td></tr>
	<tr><th>JOB_ID</th><td id="jobId"></td></tr>
	<tr><th>SALARY</th><td id="salary"></td></tr>
	<tr><th>COMMISSION_PCT</th><td id="commissionPct"></td></tr>
	<tr><th>MANAGER_ID</th><td id="managerId"></td></tr>
	<tr><th>DEPARTMENT_ID</th><td id="departmentId"></td></tr>
	<tr><th>EMP_NAME</th><td id="empName"></td></tr>
	<tr><th>RETIRE_DATE</th><td id="retireDate"></td></tr>
</table>
<script type="text/javascript">
	let displayArea = $("#displayArea");
	$("#tree").fancytree({
		extensions: ["childcounter", "wide"],
		childcounter: {
	        deep: true,
	        hideZeros: true,
	        hideExpanded: true
	    },
	    wide: {
	        // iconWidth: "32px",     // Adjust this if @fancy-icon-width != "16px"
        // iconSpacing: "6px", // Adjust this if @fancy-icon-spacing != "3px"
        // labelSpacing: "6px",   // Adjust this if padding between icon and label !=  "3px"
        // levelOfs: "32px"     // Adjust this if ul padding != "16px"
        },
        activate: function(event, data){
            var node = data.node;
            console.log(node.data.adaptee);
            let employee = node.data.adaptee;
            for(let prop in employee){
            	displayArea.find("#"+prop).html(employee[prop]);
            }
        },
        blur:function(){
        	displayArea.find("td").html("");
        },
		source: {
			url:"<%=request.getContextPath() %>/employee/employeeList.do"
		},
		lazyLoad:function(event, data){
			console.log(event);
			console.log(data);
			var node = data.node;
	      // Load child nodes via Ajax GET /getTreeData?mode=children&parent=1234
		      data.result = {
		        url: "<%=request.getContextPath() %>/employee/employeeList.do",
		        data: {mode: "children", managerId: node.key},
		        cache: false
		      };
		},postProcess: function(event, data) {
			console.log(data)
		  data.result = data.response.dataList;
		},
		loadChildren: function(event, data) {
	       data.node.updateCounters();
        }
	});
</script>

<EmployeeService>

package kr.or.ddit.employee.service;

import java.util.List;

import kr.or.ddit.vo.EmployeeVO;
import kr.or.ddit.vo.fancytree.FancyTreeNode;

public interface EmployeeService {
//	public List<EmployeeVO> retrieveEmployeeList();
	public List<FancyTreeNode<EmployeeVO>> retrieveEmployeeList(Integer managerId);
}

EmployeeServiceImpl

package kr.or.ddit.employee.service;

import java.util.List;
import java.util.stream.Collectors;

import kr.or.ddit.employee.dao.EmployeeDAO;
import kr.or.ddit.employee.dao.EmployeeDAOImpl;
import kr.or.ddit.vo.EmployeeVO;
import kr.or.ddit.vo.fancytree.FancyTreeNode;
import kr.or.ddit.vo.fancytree.FancyTreeNodeEmployeeAdapter;

public class EmployeeServiceImpl implements EmployeeService{
	EmployeeDAO empDAO = new EmployeeDAOImpl();
	
	@Override
	public List<FancyTreeNode<EmployeeVO>> retrieveEmployeeList(Integer managerId) {
		List<EmployeeVO> empList = empDAO.selectEmployeeList(managerId);
		return empList.stream()
				.map((emp)->new FancyTreeNodeEmployeeAdapter(emp))
				.collect(Collectors.toList());
	}
}

EmployeeListServlet

package kr.or.ddit.employee.web;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;

import kr.or.ddit.employee.service.EmployeeService;
import kr.or.ddit.employee.service.EmployeeServiceImpl;
import kr.or.ddit.mvc.DelegatingViewResolver;
import kr.or.ddit.mvc.GridTemplateViewResolver;
import kr.or.ddit.vo.EmployeeVO;
import kr.or.ddit.vo.fancytree.FancyTreeNode;

@WebServlet("/employee/employeeList.do")
public class EmployeeListServlet extends HttpServlet{
	EmployeeService service = new EmployeeServiceImpl();
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String accept = req.getHeader("Accept");
		if(StringUtils.containsIgnoreCase(accept, "json")) {
			String managerIdParam = req.getParameter("managerId");
			Integer managerId = null;
			if(StringUtils.isNotBlank(managerIdParam)) {
				managerId = new Integer(managerIdParam);
			}
			
			List<FancyTreeNode<EmployeeVO>> nodeList = service.retrieveEmployeeList(managerId);
			req.setAttribute("dataList", nodeList);
			
			// ========================
			req.getRequestDispatcher("/jsonView.do").forward(req, resp);
			// ========================
		}else {
			String viewName = "/WEB-INF/views/employee/employeeList.jsp"+GridTemplateViewResolver.GRIDSUFFIX;
			new DelegatingViewResolver().viewResolve(viewName, req, resp);
		}
	}
}

-if(StrubgUtils.isNotBlank(managerIdParam)부분

-파라미터있다면 매니저 아이디 있고 없다면 아이디 없고 if문 못들어가서 null나옴

728x90

'웹프로그래밍' 카테고리의 다른 글

tiles를 적용한 후 에러잡기 (0)	2022.06.25
EL, JSTL 프레임워크 -0624 (0)	2022.06.24
로그인처리, 템플릿맛보기-0622 (0)	2022.06.22
JDBC-22.06.21 (0)	2022.06.22
기본객체2, scope-0621 (0)	2022.06.21

ABOUT ME

하루복습 하루복습

'웹프로그래밍' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'웹프로그래밍' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바