<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<style>
.before{ // 왼쪽 화살표
display:inline-block; width:0; height:0; border-style:solid; border-width:7px;
border-color:transparent #7d1919 transparent transparent;
}
.after{ // 오른쪽 화살표
display:inline-block; width:0; height:0; border-style:solid; border-width:7px;
border-color:transparent transparent transparent #7d1919;
}
</style>
<title>고객번호 조회 및 출력</title>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
function fn_process(command){
// command 설정
$("#command").val(command);
var params = $("#frm").serialize();
var p_cust_id = $("#p_cust_id").val();
var i_cust_id = $("#cust_id").val();
var i_cust_name = $("#cust_name").val();
console.log("cust_id : " + p_cust_id);
console.log("command : " + command);
// 추가 시 값 초기화 후 종료
if(command == 'add'){
console.log("add로그");
$('#frm')[0].reset();
$('#cust_id').attr("readonly",false).attr("disabled",false) // cust_id 기본값 disabled -> 추가 누를시 활성화
return;
}
// 조회 시 validation + 널값 체크
if(command == 'search' && p_cust_id == ''){
$('#frm')[0].reset();
$('#cust_id').attr("readonly",true).attr("disabled",true) // 조회시 cust_id 활성화
alert("고객번호를 입력하세요");
return;
}
// 저장 시 validation
if(command == 'save' && i_cust_id && i_cust_name == ''){
alert("저장시 고객번호와 고객성명은 필수입니다.");
return;
}
// 삭제 시 validation
if(command == 'del' && i_cust_id == ''){
alert("삭제 고객번호는 필수입니다.");
return;
}
$.ajax({
type: "post",
async:false,
url:"${contextPath}/cus2",
dataType : "text",
data: params,
success: function (data, textStatus){
var jsonInfo = JSON.parse(data);
// dataType이 text일경우 parse를 해야하고, json일경우 아래문장만 써주면 됨.
// var jsonInfo = data;
if(command == 'search'){
if(jsonInfo.error.error_yn == 'Y'){
alert(jsonInfo.error.error_text);
return;
}
console.log("search로그" + jsonInfo.customer.cust_id);
$("#cust_id").val(jsonInfo.customer.cust_id);
$("#cust_name").val(jsonInfo.customer.cust_name);
$("#cust_address").val(jsonInfo.customer.cust_address);
$("#cust_state").val(jsonInfo.customer.cust_state);
$("#cust_zip").val(jsonInfo.customer.cust_zip);
$("#cust_country").val(jsonInfo.customer.cust_country);
$("#cust_contact").val(jsonInfo.customer.cust_contact);
$("#cust_email").val(jsonInfo.customer.cust_email);
for(var i in jsonInfo.states){
$("#states_cust").append("<option value='"+jsonInfo.states[i]+"'>"+jsonInfo.states[i]+"</option>");
}
for(var i in jsonInfo.countries){
$("#countries_cust").append("<option value='"+jsonInfo.countries[i]+"'>"+jsonInfo.countries[i]+"</option>");
}
}else if(command == 'save'){
console.log("save로그" + cust_id);
alert('저장되었습니다');
}else if(command == 'before' || command == 'after'){
// before after 값 받아서 조회
if(jsonInfo.error.error_yn == 'Y'){
alert(jsonInfo.error.error_text);
return;
}
console.log("bf로그" + jsonInfo.customer.cust_id);
$("#p_cust_id").val(jsonInfo.customer.cust_id); // p_cust_id 칸도 같아야 조회 가능
$("#cust_id").val(jsonInfo.customer.cust_id);
$("#cust_name").val(jsonInfo.customer.cust_name);
$("#cust_address").val(jsonInfo.customer.cust_address);
$("#cust_state").val(jsonInfo.customer.cust_state);
$("#cust_zip").val(jsonInfo.customer.cust_zip);
$("#cust_country").val(jsonInfo.customer.cust_country);
$("#cust_contact").val(jsonInfo.customer.cust_contact);
$("#cust_email").val(jsonInfo.customer.cust_email);
for(var i in jsonInfo.states){
$("#states_cust").append("<option value='"+jsonInfo.states[i]+"'>"+jsonInfo.states[i]+"</option>");
}
for(var i in jsonInfo.countries){
$("#countries_cust").append("<option value='"+jsonInfo.countries[i]+"'>"+jsonInfo.countries[i]+"</option>");
}
}
},
error:function(request,textStatus,error){
alert("code:"+request.status+"\n"+"message:"+request.responseText+"\n"+"error:"+error);
},
complete:function(data,textStatus){
alert("작업을완료 했습니다");
}
});
}
</script>
</head>
<body>
<form name="frm" id="frm">
고객번호<input type="text" id="p_cust_id" name="p_cust_id" >
<input type="hidden" id="command" name="command" />
<input type="button" name="search" id="search" value="조회" onClick="fn_process('search')"/>
<input type="button" id="add" value="추가" onClick="fn_process('add')" />
<input type="button" name="save" id="save" value="저장" onClick="fn_process('save')" />
<input type="button" name="del" id="del" value="삭제" onClick="fn_process('del')" /><br><br>
<table border=1 align=left>
<tr>
<td><span class="before" id="before" onClick="fn_process('before')"></span>고객번호<div class="after" id="after" onClick="fn_process('after')"></div></td>
// span div 둘다 onclick 가능
<td><input type="text" name="cust_id" id="cust_id" disabled /></td>
</tr>
<tr>
<td>고객이름</td>
<td><input type="text" name="cust_name" id="cust_name"></td>
</tr>
<tr>
<td>고객주소</td>
<td><input type="text" name="cust_address" id="cust_address"></td>
</tr>
<tr>
<td>고객주</td>
<td><input type="text" list="states_cust" name="cust_state" id="cust_state"><datalist id="states_cust"></datalist>
</td>
</tr>
<tr>
<td>고객우편번호</td>
<td><input type="text" name="cust_zip" id="cust_zip"></td>
</tr>
<tr>
<td>고객국가</td>
<td><input type="text" list="countries_cust" name="cust_country" id="cust_country"><datalist id="countries_cust"></datalist>
</td>
</tr>
<tr>
<td>고객담당자</td>
<td><input type="text" name="cust_contact" id="cust_contact"></td>
</tr>
<tr>
<td>고객메일주소</td>
<td><input type="text" name="cust_email" id="cust_email"></td>
</tr>
</table>
</form>
</body>
</html>
------------------------------
package day0903;
import java.io.IOException;
import java.io.PrintWriter;
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.json.simple.JSONArray;
import org.json.simple.JSONObject;
/**
* Servlet implementation class JsonServlet3
*/
@WebServlet("/cus2")
public class Customer extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doHandle(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doHandle(request, response);
}
protected void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
PrintWriter writer = response.getWriter();
JSONObject totaObject = new JSONObject();
JSONObject customerInfo = new JSONObject();
JSONObject error = new JSONObject();
CustomerDAO dao = new CustomerDAO();
CustomerVO vo = new CustomerVO();
String command = request.getParameter("command");
String input_id = request.getParameter("p_cust_id");
System.out.println("input_id: " + input_id);
System.out.println("command: "+ command);
String jsonInfo = null;
if (command.equals("before")) {
System.out.println("before메소드 로그");
input_id = dao.before(input_id);
System.out.println("before메소드 로그" + input_id);
command = "search"; // before메소드 통해 before의 cust_id 받은 후 search 메소드로 들어감
}
if (command.equals("after")) {
System.out.println("after메소드 로그");
input_id = dao.after(input_id);
System.out.println("after메소드 로그" + input_id);
command = "search"; // after메소드 통해 after의 cust_id 받은 후 search 메소드로 들어감
}
if(command.equals("search")) {
// before after 로직을 합쳐놔서 listCustomers 메소드와 널값 체크 를 search 안에 넣어놓음.
List customerList = dao.listCustomers(input_id);
if(customerList.size() == 0) {
System.out.println("널값체크 로그");
error.put("error_yn", "Y");
error.put("error_text", "존재하지 않습니다");
totaObject.put("error", error);
jsonInfo = totaObject.toJSONString();
System.out.print(jsonInfo);
writer.print(jsonInfo);
return;
}
System.out.println("서치메소드 로그");
JSONArray states = new JSONArray();
JSONArray countries = new JSONArray();
dao.listStates(states);
dao.listCountries(countries);
totaObject.put("states", states);
totaObject.put("countries", countries);
vo = (CustomerVO) customerList.get(0);
customerInfo.put("cust_id", vo.getCust_id());
customerInfo.put("cust_name", vo.getCust_name());
customerInfo.put("cust_address", vo.getCust_address());
customerInfo.put("cust_state", vo.getCust_state());
customerInfo.put("cust_zip", vo.getCust_zip());
customerInfo.put("cust_country", vo.getCust_country());
customerInfo.put("cust_contact", vo.getCust_contact());
customerInfo.put("cust_email", vo.getCust_email());
totaObject.put("customer", customerInfo);
} else if(command.equals("save")) {
// }else if("save".equals(command) || command == "save") {
System.out.println("저장메소드 로그");
String cust_id = request.getParameter("cust_id");
String cust_name = request.getParameter("cust_name");
String cust_address = request.getParameter("cust_address");
String cust_state = request.getParameter("cust_state");
String cust_zip = request.getParameter("cust_zip");
String cust_country = request.getParameter("cust_country");
String cust_contact = request.getParameter("cust_contact");
String cust_email = request.getParameter("cust_email");
dao.addCustomer(cust_id, cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact, cust_email);
}else if(command.equals("del")) {
System.out.println("삭제메소드 로그");
dao.delCustomer(input_id);
}
error.put("error_yn", "N");
totaObject.put("error", error);
jsonInfo = totaObject.toJSONString();
System.out.print(jsonInfo);
writer.print(jsonInfo);
}
}
------------------------------
package day0903;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.json.simple.JSONArray;
public class CustomerDAO {
private static final String driver= "oracle.jdbc.driver.OracleDriver";
private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String user ="scott";
private static final String pwd = "tiger";
private Connection con;
private PreparedStatement pstmt; // 실무에선 PreparedStatement를 더 많이씀.
public List listCustomers(String input_id)
{
List list = new ArrayList();
try
{
connDB(); // 네가지 정보로 데이터베이스를 연결
String query = "SELECT cust_id, cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact, cust_email "
+ "FROM customers "
+ "where cust_id = '" + input_id + "'";
System.out.println("preparedStatement : " + query);
pstmt = con.prepareStatement(query); // 파생된 SQL 재사용. preparedStatement 메소드에 sql문을 전달해 prepareStatement객체를 생성.
ResultSet rs = pstmt.executeQuery(); // sql문으로 회원 정보를 조회
while(rs.next())
{
// 조회한 레코드의 각 컬럼 값을 받아옴.
String cust_id = rs.getString("cust_id"); // " " 안에는 컬럼명이 와야함.!!!!!!!!!!!!!!
String cust_name =rs.getString("cust_name");
String cust_address =rs.getString("cust_address");
String cust_state =rs.getString("cust_state");
String cust_zip =rs.getString("cust_zip");
String cust_country =rs.getString("cust_country");
String cust_contact =rs.getString("cust_contact");
String cust_email =rs.getString("cust_email");
// 각 컬럼 값을 다시 MemberVO 객체의 속성에 설정.
CustomerVO vo = new CustomerVO();
vo.setCust_id(cust_id);
vo.setCust_name(cust_name);
vo.setCust_address(cust_address);
vo.setCust_state(cust_state);
vo.setCust_zip(cust_zip);
vo.setCust_country(cust_country);
vo.setCust_contact(cust_contact);
vo.setCust_email(cust_email);
list.add(vo); // 설정된 MemberVO 객체를 다시 ArrayList에 저장.
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e)
{
e.printStackTrace();
}
return list; // 조회한 레코드의 개수만큼 MemberVO객체를 저장한 ArrayList를 반환.
}
public String before(String input_id) // 이전값 찾는 메소드
{
try
{
connDB(); // 네가지 정보로 데이터베이스를 연결
String query = "select *" +
" from (select cust_id, cust_name, LAG(cust_id) OVER(ORDER BY cust_id) before from customers)" +
" where cust_id = '" + input_id + "'";
System.out.println("preparedStatement : " + query);
pstmt = con.prepareStatement(query); // 파생된 SQL 재사용. preparedStatement 메소드에 sql문을 전달해 prepareStatement객체를 생성.
ResultSet rs = pstmt.executeQuery(); // sql문으로 회원 정보를 조회
while(rs.next())
{
String cust_id = rs.getString("before");
CustomerVO vo = new CustomerVO();
vo.setCust_id(cust_id);
input_id = cust_id; // before 값을 input_id 에 넣은 후 리턴.
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e)
{
e.printStackTrace();
}
return input_id;
}
public String after(String input_id) // 이후값 찾는 메소드
{
try
{
connDB(); // 네가지 정보로 데이터베이스를 연결
String query = "select *" +
" from (select cust_id, cust_name, LEAD(cust_id) OVER(ORDER BY cust_id) after from customers)" +
" where cust_id = '" + input_id + "'";
System.out.println("preparedStatement : " + query);
pstmt = con.prepareStatement(query); // 파생된 SQL 재사용. preparedStatement 메소드에 sql문을 전달해 prepareStatement객체를 생성.
ResultSet rs = pstmt.executeQuery(); // sql문으로 회원 정보를 조회
while(rs.next())
{
String cust_id = rs.getString("after");
CustomerVO vo = new CustomerVO();
vo.setCust_id(cust_id);
input_id = cust_id; // after 값을 input_id 에 넣은 후 리턴.
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e)
{
e.printStackTrace();
}
return input_id;
}
public void addCustomer(String cust_id, String cust_name, String cust_address, String cust_state, String cust_zip, String cust_country, String cust_contact, String cust_email) // 고객추가 메소드
{
try
{
// 고객번호, 고객이름, 고객주소, 고객주, 고개우편번호, 고객국가, 고객담당자, 고객메일주소, 수정
// cust_id, cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact, cust_email
connDB();
String query = "insert into customers";
query += " (cust_id,cust_name,cust_address,cust_state,cust_zip,cust_country,cust_contact,cust_email)";
query += " values(?,?,?,?,?,?,?,?)"; // 순서대로 값을 assign 해야함.
System.out.println("prepareStatement: " + query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, cust_id);
pstmt.setString(2, cust_name);
pstmt.setString(3, cust_address);
pstmt.setString(4, cust_state);
pstmt.setString(5, cust_zip);
pstmt.setString(6, cust_country);
pstmt.setString(7, cust_contact);
pstmt.setString(8, cust_email);
pstmt.executeUpdate();
pstmt.close();
System.out.println("addCustomer실행");
} catch (Exception e)
{
e.printStackTrace();
}
}
void listCountries(JSONArray countriesArray){
try {
connDB();
String query=" SELECT DISTINCT TRIM(cust_country)"
+" FROM customers"
+" WHERE cust_country IS NOT NULL"
+" ORDER BY TRIM(cust_country) ASC";
this.pstmt=this.con.prepareStatement(query);
ResultSet rs=this.pstmt.executeQuery();
while(rs.next()) {
countriesArray.add(rs.getString("TRIM(cust_country)"));
}
rs.close();
this.pstmt.close();
this.con.close();
}catch(Exception e) {
e.printStackTrace();
}
}
void listStates(JSONArray statesArray){
try {
connDB();
String query=" SELECT DISTINCT TRIM(cust_state)"
+" FROM customers"
+" WHERE cust_state IS NOT NULL"
+" ORDER BY TRIM(cust_state) ASC";
this.pstmt=this.con.prepareStatement(query);
ResultSet rs=this.pstmt.executeQuery();
while(rs.next()) {
statesArray.add(rs.getString("TRIM(cust_state)"));
}
rs.close();
this.pstmt.close();
this.con.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void delCustomer(String input_id)
{
try
{
connDB();
String query = "delete from customers" + " where cust_id=? ";
System.out.println("prespareStatement: " + query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, input_id);
pstmt.executeUpdate();
pstmt.close();
} catch (Exception e)
{
e.printStackTrace();
}
}
private void connDB()
{
try
{
Class.forName(driver);
System.out.println("Oracle 드라이버 로딩 성공");
con = DriverManager.getConnection(url, user, pwd);
System.out.println("Connection 생성 성공");
} catch (Exception e)
{
e.printStackTrace();
}
}
}
------------------------------
package day0903;
public class CustomerVO {
private String cust_id;
private String cust_name;
private String cust_address;
private String cust_state;
private String cust_zip;
private String cust_country;
private String cust_contact;
private String cust_email;
public String getCust_id() {
return cust_id;
}
public void setCust_id(String cust_id) {
this.cust_id = cust_id;
}
public String getCust_name() {
return cust_name;
}
public void setCust_name(String cust_name) {
this.cust_name = cust_name;
}
public String getCust_address() {
return cust_address;
}
public void setCust_address(String cust_address) {
this.cust_address = cust_address;
}
public String getCust_state() {
return cust_state;
}
public void setCust_state(String cust_state) {
this.cust_state = cust_state;
}
public String getCust_zip() {
return cust_zip;
}
public void setCust_zip(String cust_zip) {
this.cust_zip = cust_zip;
}
public String getCust_country() {
return cust_country;
}
public void setCust_country(String cust_country) {
this.cust_country = cust_country;
}
public String getCust_contact() {
return cust_contact;
}
public void setCust_contact(String cust_contact) {
this.cust_contact = cust_contact;
}
public String getCust_email() {
return cust_email;
}
public void setCust_email(String cust_email) {
this.cust_email = cust_email;
}
}
