2012-6-1 select박스를 이용한 상품 조회하기[실습](product

프로그래밍/ajax

2012-6-1 select박스를 이용한 상품 조회하기[실습](product_category)

조은성 2012. 6. 1. 14:14

* 전체 소스

ajax_category.zip

* 설계도 및 테이블 및 소스 및 구현

product_category_logical.pdf

product_category_physical.pdf

category.sql

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="/ajax_category/controller?command=first_category">카테고리요청</a>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<c:if test="${requestScope.error_message!=null }">
실행 도중 오류가 발생했습니다.<br>
오류 내용 ${error_message }
</c:if>
<a href='/${initParam.context_root }/index.jsp'>메인페이지로 이동</a>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style type="text/css">
table, tr, td ,th{
border: 1px solid black;
border-collapse: collapse;
padding: 10px;
}
table{
width: 1000px;
}

</style>
<script type="text/javascript" src="ajax.js"></script>
<script type="text/javascript">

function selectRargeCategory() {
var sel = document.getElementById("first_category");
createXhr();
xhr.onreadystatechange = callback;
var queryString = "command=second_category&id="+ sel.options[sel.selectedIndex].value;
xhr.open("GET", "/ajax_category/controller?" + queryString, true);
xhr.send(null);

// alert(sel.options[sel.selectedIndex].value);

}

function callback() {
//서버로부터 응답이 왔다면 일한다.
if (xhr.readyState == 4) {//응답을 다 받은 경우
if (xhr.status == 200) {//응답코드가 200인 경우 - OK
var resTxt = xhr.responseText;//서버가 보낸 응답

var jobj = JSON.parse(resTxt);

var jobj = JSON.parse(resTxt);

var jobj = eval("(" + resTxt + ")");

   var thead = document.getElementById("thead");

   var childCnt = thead.children.length;
   for (var i = 0; i < childCnt; i++) {
    thead.removeChild(thead.firstChild);
   }


   var tr = document.createElement("tr");
   var td1 = document.createElement("th");
   var td2 = document.createElement("th");
   var td3 = document.createElement("th");
   var td4 = document.createElement("th");
   var td5 = document.createElement("th");

   tr.setAttribute("bgcolor", "yellow");

   td1.innerHTML = "상품ID";
   td2.innerHTML = "상품명";
   td3.innerHTML = "상품가격";
   td4.innerHTML = "상품MAKER";
   td5.innerHTML = "상품정보";

   tr.appendChild(td1);
   tr.appendChild(td2);
   tr.appendChild(td3);
   tr.appendChild(td4);
   tr.appendChild(td5);

   thead.appendChild(tr);
   var tbody = document.getElementById("tbody");

   var childCnt1 = tbody.children.length;
   for ( var i = 0; i < childCnt1; i++) {
    tbody.removeChild(tbody.firstChild);
   }

   if(jobj==""){
    var tr = document.createElement("tr");

    var td1 = document.createElement("td");
    td1.setAttribute("colspan", "5");
    td1.innerHTML= "데이터가 없습니다.";
    tr.appendChild(td1);
    tbody.appendChild(tr);
    return;
   }
   for ( var i = 0; i < jobj.length; i++) {
    var tr = document.createElement("tr");
    tr.setAttribute("align", "center");
    var td1 = document.createElement("td");
    td1.setAttribute("width", "100");
    var td2 = document.createElement("td");
    td2.setAttribute("width", "200");
    var td3 = document.createElement("td");
    td3.setAttribute("width", "100");
    var td4 = document.createElement("td");
    td4.setAttribute("width", "150");
    var td5 = document.createElement("td");
    td5.setAttribute("width", "400");
    td1.appendChild(document.createTextNode(jobj[i].productId));
    td2.appendChild(document.createTextNode(jobj[i].productName));
    td3.appendChild(document.createTextNode(jobj[i].productPrice));
    td4.appendChild(document.createTextNode(jobj[i].productMaker));
    td5.appendChild(document.createTextNode(jobj[i].productInfo));
    tr.appendChild(td1);
    tr.appendChild(td2);
    tr.appendChild(td3);
    tr.appendChild(td4);
    tr.appendChild(td5);

    tbody.appendChild(tr);
   }

  } else {
   alert("요청처리가 정상적으로 안되었습니다.\n" + xhr.status);
  }
}
}
function callback3(){
if (xhr.readyState == 4) {//응답을 다 받은 경우
  if (xhr.status == 200) {//응답코드가 200인 경우 - OK
   var resTxt = xhr.responseText;//서버가 보낸 응답
   var table1 = document.getElementById("table1");
   var jobj = eval("(" + resTxt + ")");
   var txt = "";
   alert(jobj);
   if(jobj==""){
    txt = txt+"<table border='1' width='950' cellspacing='0' cellpadding='10'>";
    txt = txt+"<tr bgcolor='yellow' align='center'>";
    txt = txt+"<td width='100'>상품ID</td>";
    txt = txt+"<td width='200'>상품명</td>";
    txt = txt+"<td width='100'>상품가격</td>";
    txt = txt+"<td width='150'>상품MAKER</td>";
    txt = txt+"<td width='400'>상품정보</td>";
    txt = txt+"</tr>";
    txt = txt+"<tr align='center'>";
    txt = txt+"<td colspan='5'>조회된 데이터가 없습니다.</td>";
    txt = txt+"</tr>";
    txt = txt+"</table>";

   }else{


   txt = txt+"<table border='1' width='950' cellspacing='0' cellpadding='10'>";
   txt = txt+"<tr bgcolor='yellow' align='center'>";
   txt = txt+"<td width='100'>상품ID</td>";
   txt = txt+"<td width='200'>상품명</td>";
   txt = txt+"<td width='100'>상품가격</td>";
   txt = txt+"<td width='150'>상품MAKER</td>";
   txt = txt+"<td width='400'>상품정보</td>";
   txt = txt+"</tr>";




   for(var i=0;i<jobj.length;i++){
   txt = txt+"<tr align='center'>";
   txt = txt+"<td>"+jobj[i].productId+"</td>";
   txt = txt+"<td>"+jobj[i].productName+"</td>";
   txt = txt+"<td>"+jobj[i].productPrice+"</td>";
   txt = txt+"<td>"+jobj[i].productMaker+"</td>";
   txt = txt+"<td>"+jobj[i].productInfo+"</td>";
   txt = txt+"</tr>";

   }
   txt = txt+"</table>";
   }
   table1.innerHTML = txt;
  }

}
}

</script>
</head>
<body>
대분류 : <select id="first_category" onchange="selectRargeCategory()">
<option>대분류</option>
<c:forEach items="${requestScope.firstCategoryList }" var="firstCategoryList">
<option value="${firstCategoryList.id }">${firstCategoryList.name }</option>
</c:forEach>
</select>
중분류 : <select id="second_category" onchange="getThird()"><option value="default">중분류</option></select>
소분류 : <select id="third_category" onchange="getProduct()" ><option value="default">소분류</option></select>
<p>
<table>
<thead id="thead"></thead>
<tbody id="tbody"></tbody>
</table>
<div id="table1"></div>
</body>
</html>

var xhr;
function createXhr(){
if(window.ActiveXObject){
xhr = new ActiveXObject("Microsoft.XMLHTTP");
}else{
xhr = new XMLHttpRequest();
}
}

DROP TABLE PRODUCT;
DROP TABLE THIRD_CATEGORY;
DROP TABLE SECOND_CATEGORY;
DROP TABLE FIRST_CATEGORY;
CREATE TABLE FIRST_CATEGORY(
FIRST_CATEGORY_ID VARCHAR2(20) PRIMARY KEY,
FIRST_CATEGORY_NAME VARCHAR2(100) NOT NULL
);

CREATE TABLE SECOND_CATEGORY(
SECOND_CATEGORY_ID VARCHAR2(20) PRIMARY KEY,
SECOND_CATEGORY_NAME VARCHAR2(100) NOT NULL,
FIRST_CATEGORY_ID VARCHAR2(20) NOT NULL,
CONSTRAINT FIRST_FK_SECOND FOREIGN KEY (FIRST_CATEGORY_ID) REFERENCES FIRST_CATEGORY(FIRST_CATEGORY_ID)
);

CREATE TABLE THIRD_CATEGORY(
THIRD_CATEGORY_ID VARCHAR2(20) PRIMARY KEY,
THIRD_CATEGORY_NAME VARCHAR2(100) NOT NULL,
SECOND_CATEGORY_ID VARCHAR2(20) NOT NULL,
CONSTRAINT SECOND_FK_THIRD FOREIGN KEY (SECOND_CATEGORY_ID) REFERENCES SECOND_CATEGORY(SECOND_CATEGORY_ID)
);

CREATE TABLE PRODUCT(
PRODUCT_ID VARCHAR2(20) PRIMARY KEY,
PRODUCT_NAME VARCHAR2(100) NOT NULL,
PRODUCT_PRICE NUMBER NOT NULL,
PRODUCT_MAKER VARCHAR2(50) NOT NULL,
PRODUCT_INFO VARCHAR2(4000) NOT NULL,
THIRD_CATEGORY_ID VARCHAR2(20) NOT NULL,
CONSTRAINT PRODUCT_FK_THIRD_CATEGORY FOREIGN KEY (THIRD_CATEGORY_ID) REFERENCES THIRD_CATEGORY(THIRD_CATEGORY_ID)
)
;

INSERT INTO FIRST_CATEGORY (FIRST_CATEGORY_ID, FIRST_CATEGORY_NAME) VALUES('F-1', '컴퓨터');
INSERT INTO FIRST_CATEGORY (FIRST_CATEGORY_ID, FIRST_CATEGORY_NAME) VALUES('F-2', '전자제품');
INSERT INTO FIRST_CATEGORY (FIRST_CATEGORY_ID, FIRST_CATEGORY_NAME) VALUES('F-3', '휴대용전자제품');

INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-1', '데스크탑', 'F-1' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-2', '노트북', 'F-1' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-3', '메모리', 'F-1' );

INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-4', 'TV', 'F-2' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-5', '냉장고', 'F-2' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-6', '세탁기', 'F-2' );

INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-7', '핸드폰', 'F-3' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-8', 'MP3', 'F-3' );
INSERT INTO SECOND_CATEGORY (SECOND_CATEGORY_ID, SECOND_CATEGORY_NAME, FIRST_CATEGORY_ID) VALUES ('S-9', 'PMP', 'F-3' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-1', '삼성', 'S-1' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-2', 'LG', 'S-1' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-3', 'HP', 'S-1' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-4', '레노버 ', 'S-2' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-5', '애플', 'S-2' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-6', '삼성 센스', 'S-2' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-7', 'LG X-NOTE', 'S-2' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-8', 'HP 파빌리온', 'S-2' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-9', '하이닉스', 'S-3' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-10', '삼성전자', 'S-3' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-11', 'EKMEMORY', 'S-3' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-12', '파브', 'S-4' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-13', '인피니아', 'S-4' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-14', '브라비아', 'S-4' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-15', '지펠', 'S-5' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-16', '디오스', 'S-5' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-17', '하우젠', 'S-6' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-18', '트롬', 'S-6' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-19', '클라쎄', 'S-6' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-20', 'SKT', 'S-7' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-21', 'KTF', 'S-7' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-22', 'LGT', 'S-7' );

INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-23', '코원', 'S-8' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-24', 'Apple', 'S-8' );
INSERT INTO THIRD_CATEGORY (THIRD_CATEGORY_ID, THIRD_CATEGORY_NAME, SECOND_CATEGORY_ID) VALUES ('T-25', '아이리버', 'S-8' );

INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-1', '매직스테이션 DM-C210-PA01S', 580000, '삼성전자', '인텔 / 펜티엄 / E5400 (2.7GHz) / 2GB / DDR3 / 320GB / DVD 레코더 / 슬림 / 인텔 / GMA X4500 / 기본 메모리 공유 / 1Gbps 유선랜 / 7 홈 프리미엄 / DVI / 멀티 리더기 / 모니터 미포함 / 색상: 블랙', 'T-1');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-2', '매직스테이션 DB-R130-PA01S', 545000, '삼성전자','인 텔 / 펜티엄 / E5300 (2.6GHz) / 2GB / DDR2 / 320 GB / DVD 레코더 / 슬림 / 엔비디아 / 지포스 7100 / 기본 메모리 공유 / 1Gbps 유선랜 / 7 홈 프리미엄 / 멀티 리더기 / 모니터 미포함 / 색상: 블랙', 'T-1');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-3', 'XPION Mini X30MT-ACA201', 480000, 'LG전자', '인텔 / 아톰 / 230 (1.6GHz) / 2GB / DDR2 / 250GB / 컴팩트 / 엔비디아 / ION / 기본 메모리 공유 / 100Mbps 유선랜 / Vista 홈 베이직 / 모니터 미포함 / 색상: 블랙', 'T-2');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-4', 'XPION S30NP-AR5701', 1370000, 'LG전자', '인 텔 / 코어i5 / i5-750 (2.66 GHz) / 3GB / DDR3 / 640GB / DVD 레코더 / 미니타워 / 엔비디아 / 지포스 9600 GT / 512MB / 1Gbps 유선랜 / 7 홈 프리미엄 / 멀티 리더기 / 모니터 미포함', 'T-2');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-5', 'SENS NT-R480-PS55A', 1381000, '삼성전자', '인 텔 / 코어i5 / i5-520M (2.4GHz) / LED 백라이트 / 14인치 / 1366x768 / 500GB / 4GB DDR3 / DVD레코더 / 7 홈프리미엄 / 엔비디아 / 지포스 GT 330M / 1GB GDDR3 / 2.3Kg / 6cell / 1Gbps 유선랜 / 802.11n 무선랜 / HDMI / D-SUB / 웹캠 / 블루투스 / e-SATA / 멀티 리더기 / 블록 키보드 / 색상: 레드', 'T-6');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-6', 'SENS NT-R530-JA42S', 711000, '삼성전자', '인 텔 / 펜티엄 / T4300 (2.1GHz) / LED 백라이트 / 15.6인치 / 1366x768 / 250GB / 2GB DDR3 / DVD레코더 / 7 홈프리미엄 / 인텔 / GMA 4500MHD / 시스템 메모리 공유 / 2.5Kg / 6cell / 100Mbps 유선랜 / 802.11n 무선랜 / HDMI / D-SUB / 웹캠 / 멀티 리더기 / 숫자 키패드 / 색상: 블랙', 'T-6');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-7', 'ThinkPad Edge E320 1298-RK9', 591000, '레노버', '인텔 / 코어i5-2세대 / i5-2450M (2.5GHz) / LED 백라이트 / 13.3인치 / 1366x768 / 320GB / 2GB / DDR3 / 옵션(선택사항) / OS 미포함 / AMD(ATI) / 라데온 HD6630 / 1GB DDR3 / 1.86Kg / 6cell / 1Gbps 유선랜 / 802.11n 무선랜 / 블루투스 3.0 / 웹캠 / HDMI / D-SUB / USB 2.0 / e-SATA / 블록 키보드 / 멀티 리더기 / 색상: 블랙 / 13.3인치 가성비 No.1 ', 'T-4');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-8', 'ThinkPad X220 4286-AL8', 1311000, '레노버', '인텔 / 코어i5-2세대 / i5-2520M (2.5GHz) / LED 백라이트 / 눈부심 방지 / 회전 LCD / 광시야각 / 12.5인치 / 1366x768 / 320GB / 4GB / DDR3 / 옵션(선택사항) / 7 프로페셔널 / 인텔 / HD Graphics / 시스템 메모리 공유 / 1.46Kg / 6cell / 802.11n 무선랜 / 1Gbps 유선랜 / 블루투스 3.0 / 웹캠 / 디스플레이 / D-SUB / USB 2.0 / 익스프레스 카드 / 지문 인식 / 방수 키보드 / 멀티 리더기 / IPS패널 / 색상: 블랙 ', 'T-4');
INSERT INTO PRODUCT (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_MAKER, PRODUCT_INFO, THIRD_CATEGORY_ID)VALUES ('P-9', 'ThinkPad X220 4286-PH3', 1611000, '레노버', '인텔 / 코어i7-2세대 / i7-2640M (2.8GHz) / 광시야각 / LED 백라이트 / 눈부심 방지 / 회전 LCD / 12.5인치 / 1366x768 / 320GB / 8GB / DDR3 / 옵션(선택사항) / 7 프로페셔널 / 인텔 / HD 3000 / 시스템 메모리 공유 / 1.46Kg / 6cell / 1Gbps 유선랜 / 802.11n 무선랜 / 블루투스 3.0 / 웹캠 / 디스플레이 / D-SUB / USB 2.0 / USB 3.0 / 지문 인식 / 방수 키보드 / 멀티 리더기 / IPS패널 / 색상: 블랙', 'T-4');

select * from PRODUCT;

select * from FIRST_CATEGORY;
select * from THIRD_CATEGORY where SECOND_CATEGORY_ID='S-1';
select * from SECOND_CATEGORY where FIRST_CATEGORY_ID='F-2';
select * from PRODUCT where THIRD_CATEGORY_ID='T-2';

package category.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;
//싱글톤 패턴
public class DBUtil {
private BasicDataSource dataSource;//객체 생성 -> 생성자
private static DBUtil instance = new DBUtil();
private DBUtil(){
  dataSource = new BasicDataSource();
  //프라퍼티 설정
  //1. 연결할 DB와 관련된 설정
  dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");//BasicDataSource를 사용하면 따로 드라이버 로딩을 해줄 필요가 없다. 바로 처리가능
  dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:XE");
  dataSource.setUsername("scott");
  dataSource.setPassword("tiger");
}
public static DBUtil getInstance(){
  return instance;
}
public BasicDataSource getDataSource(){
  return dataSource;
}
public void close(Connection conn, PreparedStatement pstmt)throws SQLException{
  if(pstmt!=null){
   pstmt.close();
  }
  if(conn!=null){
   conn.close();
  }
}
public void close(Connection conn, PreparedStatement pstmt, ResultSet rset)throws SQLException{
  if(rset!=null){
   rset.close();
  }
  if(pstmt!=null){
   pstmt.close();
  }
  if(conn!=null){
   conn.close();
  }
}
}

package category.model.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import category.model.dao.CategoryDAO;
//싱글톤으로 작성
public class CategoryService {
private CategoryDAO dao;
private static CategoryService instance = new CategoryService();

private CategoryService(){
  dao = CategoryDAO.getInstance();
}
public static CategoryService getInstance(){
  return instance;
}
public ArrayList<HashMap<String,String>> getFirstCategory() throws SQLException{
  return dao.selectFirstCategory();
}
public ArrayList<HashMap<String, String>> getAllSecondCategory(String id) throws SQLException {
  return dao.selectSecondCategory(id);
}
public ArrayList<HashMap<String, String>> getAllThirdCategory(String id) throws SQLException {
  return dao.getAllThirdCategory(id);
}
public ArrayList<HashMap<String,String>> getAllProductList(String id) throws SQLException {

  return dao.getAllProductList(id);
}
}

package category.model.dao;

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

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import category.dto.ProductDTO;
import category.util.DBUtil;
//싱글턴 패턴으로 만들기
public class CategoryDAO {
private DBUtil util;
private static CategoryDAO instance = new CategoryDAO();
private CategoryDAO(){
util = DBUtil.getInstance();
}
public static CategoryDAO getInstance(){
return instance;
}
//대분류 상품 카테고리 조회
public ArrayList<HashMap<String,String>> selectFirstCategory() throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "select first_category_id, first_category_name from first_category";

ArrayList<HashMap<String,String>> list_all = new ArrayList<HashMap<String,String>>();
try{
   BasicDataSource dataSource = util.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   rset = pstmt.executeQuery();
    while(rset.next()){
     HashMap<String,String> map = new HashMap<String, String>();
     map.put("id", rset.getString(1));
     map.put("name", rset.getString(2));
     list_all.add(map);
    }
}finally{
   util.close(conn, pstmt, rset);
}
return list_all;
}
public ArrayList<HashMap<String, String>> selectSecondCategory(String id) throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "select * from SECOND_CATEGORY where FIRST_CATEGORY_ID=?";

ArrayList<HashMap<String,String>> list_all = new ArrayList<HashMap<String,String>>();
try{
   BasicDataSource dataSource = util.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, id);
   rset = pstmt.executeQuery();
    while(rset.next()){
     HashMap<String,String> map = new HashMap<String, String>();
     map.put("id", rset.getString(1));
     map.put("name", rset.getString(2));
     list_all.add(map);
    }
}finally{
   util.close(conn, pstmt, rset);
}
return list_all;
}
public ArrayList<HashMap<String, String>> getAllThirdCategory(String id) throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "select * from THIRD_CATEGORY where SECOND_CATEGORY_ID=?";

ArrayList<HashMap<String,String>> list_all = new ArrayList<HashMap<String,String>>();
try{
   BasicDataSource dataSource = util.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, id);
   rset = pstmt.executeQuery();
    while(rset.next()){
     HashMap<String,String> map = new HashMap<String, String>();
     map.put("id", rset.getString(1));
     map.put("name", rset.getString(2));
     list_all.add(map);
    }
    System.out.println("list_all :"+list_all);
}finally{
   util.close(conn, pstmt, rset);
}
return list_all;
}
public ArrayList<ProductDTO> getAllProductList(String id) throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "select * from PRODUCT where THIRD_CATEGORY_ID=?";

ArrayList<ProductDTO> list_all = new ArrayList<ProductDTO>();
try{
   BasicDataSource dataSource = util.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, id);
   rset = pstmt.executeQuery();
    while(rset.next()){
     list_all.add(new ProductDTO(rset.getString(1), rset.getString(2), rset.getInt(3), rset.getString(4), rset.getString(5), rset.getString(6)));
    }
    System.out.println("list_all :"+list_all);
}finally{
   util.close(conn, pstmt, rset);
}
return list_all;
}

}

package category.dto;

public class ProductDTO {
private String productId;
private String productName;
private int productPrice;
private String productMaker;
private String productInfo;
private String thirdCategoryId;
public ProductDTO() {
  super();
}
public ProductDTO(String productId, String productName, int productPrice,
   String productMaker, String productInfo, String thirdCategoryId) {
  super();
  this.productId = productId;
  this.productName = productName;
  this.productPrice = productPrice;
  this.productMaker = productMaker;
  this.productInfo = productInfo;
  this.thirdCategoryId = thirdCategoryId;
}
public String getProductId() {
  return productId;
}
public void setProductId(String productId) {
  this.productId = productId;
}
public String getProductName() {
  return productName;
}
public void setProductName(String productName) {
  this.productName = productName;
}
public int getProductPrice() {
  return productPrice;
}
public void setProductPrice(int productPrice) {
  this.productPrice = productPrice;
}
public String getProductMaker() {
  return productMaker;
}
public void setProductMaker(String productMaker) {
  this.productMaker = productMaker;
}
public String getProductInfo() {
  return productInfo;
}
public void setProductInfo(String productInfo) {
  this.productInfo = productInfo;
}
public String getThirdCategoryId() {
  return thirdCategoryId;
}
public void setThirdCategoryId(String thirdCategoryId) {
  this.thirdCategoryId = thirdCategoryId;
}
@Override
public String toString() {
  return "ProductDTO [productId=" + productId + ", productName="
    + productName + ", productPrice=" + productPrice
    + ", productMaker=" + productMaker + ", productInfo="
    + productInfo + ", thirdCategoryId=" + thirdCategoryId + "]";
}
@Override
public int hashCode() {
  final int prime = 31;
  int result = 1;
  result = prime * result
    + ((productId == null) ? 0 : productId.hashCode());
  result = prime * result
    + ((productInfo == null) ? 0 : productInfo.hashCode());
  result = prime * result
    + ((productMaker == null) ? 0 : productMaker.hashCode());
  result = prime * result
    + ((productName == null) ? 0 : productName.hashCode());
  result = prime * result + productPrice;
  result = prime * result
    + ((thirdCategoryId == null) ? 0 : thirdCategoryId.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;
  ProductDTO other = (ProductDTO) obj;
  if (productId == null) {
   if (other.productId != null)
    return false;
  } else if (!productId.equals(other.productId))
   return false;
  if (productInfo == null) {
   if (other.productInfo != null)
    return false;
  } else if (!productInfo.equals(other.productInfo))
   return false;
  if (productMaker == null) {
   if (other.productMaker != null)
    return false;
  } else if (!productMaker.equals(other.productMaker))
   return false;
  if (productName == null) {
   if (other.productName != null)
    return false;
  } else if (!productName.equals(other.productName))
   return false;
  if (productPrice != other.productPrice)
   return false;
  if (thirdCategoryId == null) {
   if (other.thirdCategoryId != null)
    return false;
  } else if (!thirdCategoryId.equals(other.thirdCategoryId))
   return false;
  return true;
}

}

package category.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;

import category.dto.ProductDTO;
import category.model.service.CategoryService;

public class CategoryController extends HttpServlet {
private static final long serialVersionUID = 1L;

    public CategoryController() {
        super();
    }

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

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
response.setHeader("Cache-Control", "no-cache");
String command = request.getParameter("command");
if(command.equals("first_category")){
   getFirstCategory(request,response);//대분류 조회처리(ajax 응답처리가 아님, 일반 jsp로 보냄)
}else if(command.equals("second_category")){
   getSecondCategory(request, response);//중분류 조회처리
}else if(command.equals("third_category")){
   getThirdCategory(request, response);//소분류 조회처리
}else if(command.equals("get_product_list")){
   getProductList(request,response);//상품 목록 조회처리
}
}
protected void getFirstCategory(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
CategoryService service = CategoryService.getInstance();
String url = "";
try {
   ArrayList<HashMap<String,String>> firstCategoryList = service.getFirstCategory();
   System.out.println(firstCategoryList);
   request.setAttribute("firstCategoryList", firstCategoryList);
   url = "/category_list.jsp";

} catch (SQLException e) {
   e.printStackTrace();
   request.setAttribute("error_message", e.getMessage());
   url = "/erro.jsp";
}
RequestDispatcher requestDispatcher = request.getRequestDispatcher(url);
requestDispatcher.forward(request, response);
}
protected void getSecondCategory(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.요청파라미터 받기
String id = request.getParameter("id");
System.out.println(id);
//2. 비지니스 로직 처리
CategoryService cs = CategoryService.getInstance();

try {
   ArrayList<HashMap<String, String>> list = cs.getAllSecondCategory(id);
    //3. 응답 - cto -> json형태
     System.out.println("list : "+list);
     JSONArray jobj = new JSONArray(list);
     System.out.println("jobj : "+jobj);
     PrintWriter out = response.getWriter();
     out.println(jobj);
} catch (SQLException e) {
   //페이지가 바뀌지 않는 예외처리 기법
   throw new ServletException(e);
}

}
protected void getThirdCategory(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.요청파라미터 받기
    String id = request.getParameter("id");
    System.out.println(id);
    //2. 비지니스 로직 처리
    CategoryService cs = CategoryService.getInstance();

    try {
     ArrayList<HashMap<String, String>> list = cs.getAllThirdCategory(id);
      //3. 응답 - cto -> json형태
       System.out.println("list2 : "+list);
       JSONArray jobj = new JSONArray(list);
       System.out.println("jobj2 : "+jobj);
       PrintWriter out = response.getWriter();
       out.println(jobj);
    } catch (SQLException e) {
    throw new ServletException(e);
    }
}
protected void getProductList(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.요청파라미터 받기
String id = request.getParameter("id");
System.out.println(id);
//2. 비지니스 로직 처리
CategoryService cs = CategoryService.getInstance();

try {
   ArrayList<ProductDTO> list = cs.getAllProductList(id);
    //3. 응답 - cto -> json형태
     System.out.println("list2 : "+list);
     JSONArray jobj = new JSONArray(list);
     System.out.println("jobj2 : "+jobj);
     PrintWriter out = response.getWriter();
     out.println(jobj);
} catch (SQLException e) {
   throw new ServletException(e);
}
}
}

* 결과보기