2020.06.17

[AJAX와 JSON을 이용하여 다단계 Category 구현]

1. 서버단
1) common > D.java 

package common;

/*
 * DB 접속 정보, 쿼리문, 테이블명, 컬럼명 등은
 * 별도로 관리하든지
 * XML, 초기화 파라미터 등에서 관리하는 것이 좋다.
 */
public class D {
	public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";  // JDBC 드라이버 클래스
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";  // DB 접속 URL
	public static final String USERID = "scott0316";  // DB 접속 계정 정보
	public static final String USERPW = "tiger0316";
	
	// 특정 depth 의 특정 parent 인 카테고리들 읽어오기
	public static final String SQL_CATEGORY_BY_DEPTH_N_PARENT 
			= "SELECT ca_uid, ca_name, ca_depth, ca_parent, ca_order " 
					+ "FROM test_category " 
					+ "WHERE ca_depth = ? AND ca_parent = ? "
					+ "ORDER BY ca_order ASC";

	// parent 값이 NULL 값일때
	public static final String SQL_CATEGORY_BY_DEPTH_N_PARENT_NULL 
			= "SELECT ca_uid, ca_name, ca_depth, ca_parent, ca_order " 
					+ "FROM test_category " 
					+ "WHERE ca_depth = ? AND ca_parent IS NULL "
					+ "ORDER BY ca_order ASC";
}

2) com.lec.beans

** AjaxCategoryList.java

package com.lec.beans;

import java.util.List;

import com.fasterxml.jackson.annotation.JsonProperty;

public class AjaxCategoryList {
	
	private int count;   			// 데이터 개수
	@JsonProperty("data")
	private List<CategoryDTO> list;	// 리스트
	private String message;			// 처리 메세지
	private String status;			// 처리 결과
	
	
	// 기본생성자
	public AjaxCategoryList() {}

	
	// getter, setter
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}

	public List<CategoryDTO> getList() {
		return list;
	}
	public void setList(List<CategoryDTO> list) {
		this.list = list;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	

} // end AjaxCategoryList()

 

** CategoryDTO.java 

package com.lec.beans;

public class CategoryDTO {
	private int uid;		// ca_uid
	private String name;	// ca_name
	private int depth;		// ca_depth
	private int parent;		// ca_parent
	private int order;		// ca_order
	
	// 기본생성자, 매개변수 생성자
	public CategoryDTO() {}

	public CategoryDTO(int uid, String name, int depth, int parent, int order) {
		super();
		this.uid = uid;
		this.name = name;
		this.depth = depth;
		this.parent = parent;
		this.order = order;
	}

	
	// getter, setter
	public int getUid() {
		return uid;
	}
	public void setUid(int uid) {
		this.uid = uid;
	}

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}

	public int getDepth() {
		return depth;
	}
	public void setDepth(int depth) {
		this.depth = depth;
	}

	public int getParent() {
		return parent;
	}
	public void setParent(int parent) {
		this.parent = parent;
	}

	public int getOrder() {
		return order;
	}
	public void setOrder(int order) {
		this.order = order;
	}
	

}

** CategoryDAO.java

package com.lec.beans;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import common.D;

public class CategoryDAO {
	Connection conn = null;
	Statement stmt = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// DAO 객체가 생성될때 Connection 도 생성된다.
	public CategoryDAO() {
		try {
			Class.forName(D.DRIVER);
			conn = DriverManager.getConnection(D.URL, D.USERID, D.USERPW);
			
		} catch(Exception e) {
			e.printStackTrace();
		}		
	}
	
	// DB 자원 반납 메소드,
	public void close() throws SQLException {
		if(rs != null) rs.close();
		if(pstmt != null) pstmt.close();
		if(stmt != null) stmt.close();
		if(conn != null) conn.close();
	} // end close()
	
	
	// ------------------------------------------------------------------------
	
	
	// ResultSet --> DTO 배열로 리턴
	public CategoryDTO[] createArray(ResultSet rs) throws SQLException {
		CategoryDTO[] arr = null;
		
		ArrayList<CategoryDTO> list = new ArrayList<CategoryDTO>();
		
		while(rs.next()) {
			int uid = rs.getInt("ca_uid");
			String name = rs.getString("ca_name");
			int depth = rs.getInt("ca_depth");
			int parent = rs.getInt("ca_parent");
			int order = rs.getInt("ca_order");
			
			
			CategoryDTO dto = new CategoryDTO(uid, name, depth, parent, order);
			list.add(dto);
			
		} // end while
		
		int size = list.size();
		
		if(size == 0) return null;
		
		arr = new CategoryDTO[size];
		list.toArray(arr);  // List -> 배열
		
		return arr;
	} // end createArray()
	
	// -----------------------------------------------------------------------------
	
	// depth와 parent로 카테고리 읽기  (order 오름차순 순서로), parent 값이 있을때
	public CategoryDTO[] selectByDepthAndParent(int depth, String parent) throws SQLException {
		CategoryDTO[] arr = null;
		
		parent = parent.trim();
		
		try {
			pstmt = conn.prepareStatement(D.SQL_CATEGORY_BY_DEPTH_N_PARENT);
			pstmt.setInt(1, depth);
			pstmt.setString(2, parent);
			rs = pstmt.executeQuery();
			
			arr = createArray(rs);
		} finally {
			close();
		}		
		
		return arr;
		
	} // end selectByDepthAndParent()
	
	// 카테고리 읽기  (order 오름차순 순서로), parent "", 0, null
	public CategoryDTO[] selectByDepthAndParent(int depth) throws SQLException {
		CategoryDTO[] arr = null;
		
		try {
			// IS NULL
			pstmt = conn.prepareStatement(D.SQL_CATEGORY_BY_DEPTH_N_PARENT_NULL);
			pstmt.setInt(1, depth);
			rs = pstmt.executeQuery();
			
			arr = createArray(rs);
		} finally {
			close();
		}		
		
		return arr;
		
	} // end selectByDepthAndParent()
	
}

 

3) com.controller > AjaxController 서블릿

package com.controller;

import java.io.IOException;
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 com.command.*;

@WebServlet("*.ajax")
public class AjaxController extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public AjaxController() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
		doAction(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
		doAction(request, response);
	}

	protected void doAction(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
		
		request.setCharacterEncoding("utf-8");
		
		// 어떠한 커맨드? --> 어떠한 로직을 수행할 것인지
		Command command = null;
		
		// URL로부터 URI, ContentPath, Command 분리
		String uri = request.getRequestURI();
		String conPath = request.getContextPath();
		String com = uri.substring(conPath.length());
		
		switch(com) {
		case "/cate_list.ajax":		// 목록 요청
			new CateListCommand().execute(request, response);
			new AjaxCategoryListCommand().execute(request, response);
			break;
		}
		
	} // end doAction()
	
} // end Controller

 

4) com.command
** Command 인터페이스

package com.command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public interface Command {
	void execute(HttpServletRequest request, HttpServletResponse response);
}

** AjaxCategoryListCommand.java

package com.command;

import java.io.IOException;
import java.util.Arrays;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.lec.beans.AjaxCategoryList;
import com.lec.beans.CategoryDTO;

public class AjaxCategoryListCommand implements Command {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		CategoryDTO[] dtoArr = (CategoryDTO[]) request.getAttribute("list");
		
		AjaxCategoryList list  = new AjaxCategoryList();	// response할 자바 객체
		
		list.setStatus((String)request.getAttribute("status"));
		list.setMessage((String)request.getAttribute("message"));
		
		if(dtoArr != null) {
			list.setCount(dtoArr.length);
			list.setList(Arrays.asList(dtoArr));
		} // end if
		
		// depth 가 2이상인데 parent 가 없거나 parent가 유효하지 않은 uid인 경우
		if(list.getCount() == 0) {
			list.setMessage("0개의 데이터");
		}
		
		
		// json 매핑
		ObjectMapper mapper = new ObjectMapper();
		
		try {
			String jsonStirng = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(list);
			response.setContentType("application/json; charset=utf-8");
			response.getWriter().write(jsonStirng);
		} catch (JsonProcessingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	} // end execute()

} // end Command

** CateListCommand.java

package com.command;

import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.lec.beans.CategoryDAO;
import com.lec.beans.CategoryDTO;

public class CateListCommand implements Command {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		CategoryDAO dao = new CategoryDAO();
		CategoryDTO[] arr = null;
		
		// ajax response 에 필요한 값들
		StringBuffer message = new StringBuffer();
		String status = "FAIL";   // 기본 FAIL
		
		int depth = Integer.parseInt(request.getParameter("depth"));
		String parent = request.getParameter("parent");
		
		try {
			// 카테고리 읽기
			// parent "", 0, null
			if(parent == null || parent.equals("") || parent.equals("0")) {
				arr = dao.selectByDepthAndParent(depth);
			} else {	// parent 값이 있을때
				arr = dao.selectByDepthAndParent(depth, parent);
			}
			
			if(arr == null) {
				message.append("[리스트할 데이터가 없습니다]");
			} else {
				status = "OK";
			}
			
		} catch(SQLException e) {
			message.append("[트랜잭션 에러:" + e.getMessage()+ "]");
		} // end try
		
		request.setAttribute("status", status);
		request.setAttribute("message", message.toString());
		
		request.setAttribute("list", arr);

	} // end execute()

} // end Command

 

2. 클라이언트단 

1) index.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/CSS/common.css"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="${pageContext.request.contextPath }/JS/category.js"></script>

<title>다단계 카테고리 - SELECT 하기</title>
</head>
<body>
<div class="cate" id="mycate">

	<span>
		<select id="depth_first" disabled></select>
	</span>
	
	<span>
		<select id="depth_second" disabled></select>
	</span>
	
	<span>
		<select id="depth_third" disabled></select>
	</span>
	
</div>

</body>
</html>

 

2) JS > category.js 

var depth = 1;
var parent = 0;

$(document).ready(function(){
	// 첫 로딩할때 보여지기 위해
	selectLoad(depth, parent);

	// 첫번째 select 값이 선택될때 하위 선택 박스 활성화..!!
	$("#depth_first").change(function(){
		window.depth = 2;
		window.parent = $("#depth_first option:selected").val();
		
		
		if(window.parent != 0) {
			selectLoad(depth, parent);
		} else {
			$("#depth_second").attr('disabled', true);
			$("#depth_second option").remove();
			$("#depth_third").attr('disabled', true);
			$("#depth_third option").remove();
		}
	});
	
	// 두번째 select 값이 선택될때 하위 선택 박스 활성화..!!
	$("#depth_second").change(function(){
		window.depth = 3;
		window.parent = $("#depth_second option:selected").val();
		
		if(window.parent != 0) {
			selectLoad(depth, parent);
		} else {
			$("#depth_third").attr('disabled', true);
			$("#depth_third option").remove();
		}
	});

});


function selectLoad(depth, parent){
	
	$.ajax({
		url : "cate_list.ajax?depth=" + depth + "&parent=" + parent
		, type : "GET"
		, cache : false
		, success : function(data, status){
			if(status == "success"){
				selectOption(data, depth, parent);
			}			
		}
	});
	
} // end selectLoad()

function selectOption(jsonObj, depth, parent){
	result = ""; 
	
	if(jsonObj.status == "OK"){
		
		var count = jsonObj.count;
		
		var i;
		var items = jsonObj.data;   // 배열
		result="<option value='0' selected>--선택하세요--</option>\n"
		for(i = 0; i < count; i++){
			result += "<option value='" + items[i].uid + "'>" + items[i].name + "</option>\n";
		} // end for
		
		if(depth == 1) {
			$("#depth_first").attr('disabled', false);
			$("#depth_first").html(result);
		}
		if(depth == 2) {
			$("#depth_second").attr('disabled', false);
			$("#depth_second").html(result);
		}
		if(depth == 3) {
			$("#depth_third").attr('disabled', false);
			$("#depth_third").html(result);
		}
		
		return true;
	} else {
		alert(jsonObj.message);
		return false;
	}
	return false;
} // end selectOption()

3) common.css

@charset "UTF-8";

#mycate span select {
	padding: 5px 10px;
	border-radius: 5px;
}

#mycate span select:disabled {
	padding: 5px 50px;
}

 

 

3. 이번 과제를 통해 배운점
1) 수업시간에 배운 AJAX와 Jackson을 이용하여 JSON 데이터로 스스로 변환
 : 변환하는 과정에서 NullPointException 에러가 많이 발생했는데 

   디버그 과정을 통해 자바와 JSON으로 어떻게 데이터에 담아야 하는지와

   담는 과정에서 왜 NullPointException이 발생했는지 배우고 느낄 수 있었다.

2) MVC 모델 2 사용
 : 게시판 배울때만 사용했지만 카테고리라는 전혀 새로운 주제로 MVC 모델 2를 적용해봄으로써

   해당 모델을 다른 주제로 응용해보는 경험을 했다.