16일차 우편번호검색(json)
SELECT ZIPCODE, SIDO, GUGUN, DONG, RI, BLDG, BUNJI, SEQ FROM ZIPTB ;
제약조건:
PK_ZIPTB Primary_Key
SYS_C004141 Check "ZIPCODE" IS NOT NULL
SYS_C004142 Check "SIDO" IS NOT NULL
SYS_C004143 Check "GUGUN" IS NOT NULL
SYS_C004144 Check "DONG" IS NOT NULL
SYS_C004145 Check "SEQ" IS NOT NULL
데이터
ZIPCODE, SIDO, GUGUN, DONG, RI, BLDG, BUNJI, SEQ
135-806 서울 강남구 개포1동 경남아파트 1
135-807 서울 강남구 개포1동 우성3차아파트 (1∼6동) 2
135-806 서울 강남구 개포1동 우성9차아파트 (901∼902동) 3
135-770 서울 강남구 개포1동 주공아파트 (1∼16동) 4
135-805 서울 강남구 개포1동 주공아파트 (17∼40동) 5
135-966 서울 강남구 개포1동 주공아파트 (41∼85동) 6
135-807 서울 강남구 개포1동 주공아파트 (86∼103동) 7
135-805 서울 강남구 개포1동 주공아파트 (104∼125동) 8
135-807 서울 강남구 개포1동 현대1차아파트 (101∼106동) 9
135-805 서울 강남구 개포1동 565 10
135-806 서울 강남구 개포1동 649∼651 11
135-807 서울 강남구 개포1동 652∼653 12
135-810 서울 강남구 개포1동 660 13
135-241 서울 강남구 개포1동 14
135-800 서울 강남구 개포2동 우성8차아파트 (801∼803동) 15
SimpleZip.jsp
<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>SimpleZip</title>
<script type="text/javascript" src="../../js/jquery-1.7.2.js"></script>
<script type="text/javascript">
$(function(){
$("#zipBtn").click(function(){
win =window.open("zipSc2.jsp","우편번호","scrollbars=yes, width=450, height=300, resizable=yes, menubar=no, top=150, left=260");
return true;
});
});
</script>
</head>
<body>
<table border=1>
<form name="zipform" action="save.jsp" method="post">
<tr><td>성명</td><td><input type="text" name="irum" size=12></td></tr>
<tr><td>우편번호</td><td><input type=text name="zip1" size=3 readonly> -
<input type=text name="zip2" size=3 readonly>
<input type="button" name="zip" value="우편번호검색" id="zipBtn"></td></tr>
<tr><td>주소</td><td><input type=text name="address" size=30></td></tr>
<tr><td>전화번호</td>
<td> <input type=text name=tel1 size=5>-<input type=text name=tel2 size=5>-<input type=text name=tel3 size=5></td>
</tr>
<tr><td><input type=submit value=Save> <input type=reset value=Reset> </td></tr>
</table>
</form>
</body>
</html>
zipSc2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<meta http-equiv="Content-Type" content="text/plain; charset=UTF-8" />
<title>우편 검색</title>
<link rel="stylesheet" type="text/css" href="<%=request.getContextPath() %>/css/main.css" />
<script type="text/javascript" src="../../js/jquery-1.7.2.js"></script>
<script type="text/javascript">
/* function parent(d){
alert(d);
var v = val.value;
if(!opener) return;
} */
function myClick(){
alert(눌렀다);
/* var tb = document.getElementById("tb");
alert(tb.rows[1].cells[4].innerText);
*/
};
$(function(){
$("#list tr").live("click",function(){
var zipsp=$(this,"td").eq(0).text();
//alert(zipsp);
var zipsp2 = zipsp.split("-");
var zipfirst=zipsp2[0];
var zipsp3=zipsp2[1].split(":");
//alert(zipsp3[0]);
var ziplast=zipsp3[0];
var addfinal = zipsp3[1];
opener.document.zipform.zip1.value=zipfirst;
opener.document.zipform.zip2.value=ziplast;
opener.document.zipform.address.value=addfinal;
self.closed();
});
$("#zipBtnDB").click(function(){
//alert("눌렀따");
var dong=$.trim($("#zipDBSC").val());
if(dong==null||dong==""){
alert("동이름을 입력해주세요.");
return;
}
$.ajax({
"url" : "zipSc3.jsp",
"type" : "get",
"dataType": "json",
"data" : {"dong":dong},
"success" : function(data){
var str = "<table border='1' bordercolor='blue'>";
str +="<tr >";
$.each(data,function(i,v){
str +="<td>"+v.zipcode +":</td>";
str +="<td>"+v.sido +" ";
str +=v.gugun +" ";
str +=v.dong +" ";
str +=v.ri +" ";
str +=v.bldg +" ";
str +=v.bunji +" ";
str +="</td></tr>";
});
str +="</table>";
$("#list").html(str);
},
"error" : function(info, xhr){
if(info.readyState == '4'){
alert('문제가 발생했습니다.\n상태코드 : ' + info.status+ '\n\n' + info.responseText);
}
else{
alert('문제가 발생했습니다.\n잠시후 다시 시도해 주세요.\n 상태코드 : ' +info.status);
}
}
});
/* $.getJSON(
"zipSc3.jsp",
"lowAdd="+lowAdd,
function(data){
alert(data);
var str = "<table border='1' bordercolor='blue'>";
$.each(data,function(i,v){
str +="<tr><td>"+(i+1)+"번</td> ";
str +="<td>"+v.zipcode +" </td>";
str +="<td>"+v.sido +" ";
str +=v.gugun +" ";
if(v.ri !=null)str +=+v.ri +" ";
if(v.bldg !=null)str +=+v.bldg +" ";
if(v.bunji!=null)str +=+v.bunji +" ";
str +="</td></tr>";
});
str +="</table>";
$("#showZip").html(str);
}
); */
});
});
</script>
</head>
<body>
<table>
<tr>
<td>동검색</td><td><input type="text" id="zipDBSC" size="8" /> ex) '대흥동'
<input type="button" id="zipBtnDB" value="검색"/></td>
</tr>
</table>
<div id="list" class="zipList">
</div>
</body>
</html>
zipSc3.jsp
<%@page import="kr.or.ddit.db.*"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/plain; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8");
String dong =request.getParameter("dong");
%>
<%
Connection conn= null;
PreparedStatement psmt= null;
ResultSet rs = null;
try{
conn = ConnectionProvider.getConnection();
psmt = conn.prepareStatement("SELECT ZIPCODE, SIDO, GUGUN, DONG, RI, BLDG, BUNJI, SEQ FROM ZIPTB where dong like ?");
psmt.setString(1, "%"+dong+"%");
rs= psmt.executeQuery();
%>
[
<%while(rs.next()){
if(rs.getRow()>1){
out.print(",");
}
%>
{
"zipcode" : "<%=Util.toJS(rs.getString("zipcode")) %>",
"sido" : "<%=Util.toJS(rs.getString("sido"))%>",
"gugun" : "<%=Util.toJS(rs.getString("gugun"))%>",
"dong" : "<%=Util.toJS(rs.getString("dong"))%>",
"ri" : "<%=Util.toJS(rs.getString("ri"))%>",
"bldg" : "<%=Util.toJS(rs.getString("bldg"))%>",
"bunji" : "<%=Util.toJS(rs.getString("bunji"))%>"
}
<%
}%>
]
<%
}catch(SQLException e){
e.printStackTrace();
}finally{
if(rs!=null) try{rs.close();}catch(Exception e){}
if(psmt!=null) try{psmt.close();}catch(Exception e){}
if(conn!=null) try{conn.close();}catch(Exception e){}
}
%>
Util.java (데이터 포멧)
package kr.or.ddit.db;
public class Util {
public static String toJS(String str){
if(str==null) return "";
return str.replace("\\", "\\\\")
.replace("\"","\\\"")
.replace("\'","\\\'")
.replace("\r\n","\\n")
.replace("\n","\\n");
}
}
이 글은 스프링노트에서 작성되었습니다.