웹프로그래밍
회원등록, DB -22.06.23
AIN99
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>
VO
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