일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
Tags
- angular2
- full text indexing
- 다운캐스팅
- Random
- 전체텍스트
- while
- 추상클래스
- IBatis procedure
- 전자정부
- Login with OAuth Authentication
- 업캐스팅
- 페이징
- Full text
- 가변인자
- 로또
- 스프링
- 상속
- 야구게임
- 전체
- 다형성
- jquery
- 25가지 효율적인 sql작성법
- 형변환
- 단축키
- 상속예제
- 자바
- 자바 야구게임
- 이클립스
- Validations
Archives
- Today
- Total
nalaolla
SqlSession + MyBatis + 프로시져 호출하여 우편번호 가져오기 본문
728x90
반응형
Spring + SqlSession + MyBatis + 프로시져 호출하여 우편번호 가져오기
우편번호 검색하여 해당 결과리스트 가져오는 단순한 프로세스이다..
혹 까먹을까봐 블로그에 남겨놓기로 하자..
우선 Bean설정부터..
root_context.xml 설정
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:mvc="http://www.springframework.org/schema/mvc"
- xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
- http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
- <!-- Root Context: defines shared resources visible to all other web components -->
- <!-- Root Context: defines shared resources visible to all other web components -->
- <bean id="jacksonMessageConverter"
- class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"></bean>
- <bean
- class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
- <property name="messageConverters">
- <list>
- <ref bean="jacksonMessageConverter" />
- </list>
- </property>
- </bean>
- <bean id="multipartResolver"
- class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
- <property name="maxUploadSize" value="-1" /> <!-- -1 이면 크기제한 없음 -->
- </bean>
- <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
- <property name="locations">
- <value>classpath:jdbc/oracle.properties</value>
- </property>
- </bean>
- <!--1. pom.xml commons-dbcp.jar -->
- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
- <property name="driverClassName" value="${jdbc.DRIVER_NAME}" />
- <property name="url" value="${jdbc.URL}" />
- <property name="username" value="${jdbc.USER_ID}" />
- <property name="password" value="${jdbc.USER_PWD}" />
- </bean>
- <!-- 3.mybatis -->
- <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="dataSource" />
- <!-- <property name="mapperLocations" value="classpath:jdbc/sqlMapper_*.xml"
- /> -->
- <property name="mapperLocations">
- <list>
- <value>classpath:jdbc/sqlMapper_Member.xml</value>
- <value>classpath:jdbc/sqlMapper_Common.xml</value>
- </list>
- </property>
- </bean>
- <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
- <constructor-arg ref="sqlSessionFactory" />
- </bean>
- <!-- transaction 처리 insert, update, delete, etc -->
- <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- <property name="dataSource" ref="dataSource" />
- </bean>
- <!-- ////////////////////// 타일즈 관련 설정 //////////////////////// -->
- <!-- 설정파일 지정하기 -->
- <bean id="tilesConfig"
- class="org.springframework.web.servlet.view.tiles2.TilesConfigurer">
- <property name="definitions">
- <list>
- <value>/WEB-INF/tiles/tiles_index.xml</value>
- <value>/WEB-INF/tiles/tiles_sub.xml</value>
- <value>/WEB-INF/tiles/tiles_template.xml</value>
- </list>
- </property>
- </bean>
- <!-- 뷰를 타일즈로 지정하기 -->
- <bean class="org.springframework.web.servlet.view.UrlBasedViewResolver">
- <property name="order" value="0" />
- <property name="viewClass"
- value="org.springframework.web.servlet.view.tiles2.TilesView" />
- <property name="viewNames" value=".*" />
- </bean>
- <bean id="ServletPath" class="abc.co.kr.member.controller.GetServletPath"></bean>
- </beans>
CommonService.java
- package nzin.co.kr.common.model;
- import java.util.Map;
- import org.springframework.stereotype.Service;
- @Service
- public interface CommonService {
- public Map<String, Object> zip_list(ZipcodeDTO dto, int page, int pagesize);
- }
CommonServiceImpl.java
- package nzin.co.kr.common.model;
- import java.util.Map;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- @Service
- public class CommonServiceImpl implements CommonService {
- CommonDAO commonDAO;
- public CommonDAO getCommonDAO() {
- return commonDAO;
- }
- @Autowired
- public void setCommonDAO(CommonDAO commonDAO) {
- this.commonDAO = commonDAO;
- }
- @Override
- public Map<String, Object> zip_list(ZipcodeDTO dto, int page, int pagesize) {
- // TODO Auto-generated method stub
- Map<String, Object> list = commonDAO.zip_list(dto, page, pagesize);
- return list;
- }
- }
CommonDAO.java
- package nzin.co.kr.common.model;
- import java.util.Map;
- public interface CommonDAO {
- public Map<String, Object> zip_list(ZipcodeDTO dto, int page, int pagesize);
- }
CommonDAOImpl.java
- package nzin.co.kr.common.model;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.ibatis.session.SqlSession;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Repository;
- @Repository
- public class CommonDAOImpl implements CommonDAO {
- SqlSession sqlSession;
- public SqlSession getSqlSession() {
- return sqlSession;
- }
- @Autowired
- public void setSqlSession(SqlSession sqlSession) {
- this.sqlSession = sqlSession;
- }
- @Override
- public Map<String, Object> zip_list(ZipcodeDTO dto, int page, int pagesize) {
- // TODO Auto-generated method stub
- System.out.println("dao zip_list : " + page);
- System.out.println("dao zip_listsize : " + pagesize);
- System.out.println("dao bname : " + dto.getB_name());
- List<ZipcodeDTO> list = new ArrayList<ZipcodeDTO>();
- Map<String, Object> map = new HashMap<String, Object>();
- map.put("d_name", dto.getB_name());
- map.put("page", String.valueOf(page));
- map.put("pagesize", String.valueOf(pagesize));
- sqlSession.selectOne("zip_select", map);
- list = (List<ZipcodeDTO>) map.get("PLIST1");
- map.put("result_list", map.get("PLIST1"));
- map.put("result_cnt", map.get("PLIST2"));
- return map;
- }
- }
sqlMapper_Common.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper
- PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="nzin.co.kr.common.model">
- <resultMap id="zipcodeVOMap" type="nzin.co.kr.common.model.ZipcodeDTO">
- <result property="zipcode" column="zipcode"/>
- <result property="sido" column="sido"/>
- <result property="sigungu" column="sigungu"/>
- <result property="myon" column="myon"/>
- <result property="b_name" column="b_name"/>
- <result property="road_name" column="road_name"/>
- <result property="building_no" column="building_no"/>
- <result property="building_no_sub" column="building_no_sub"/>
- <result property="sigungu_building_name" column="sigungu_building_name"/>
- </resultMap>
- <select id="zip_select" statementType="CALLABLE" resultType="nzin.co.kr.common.model.ZipcodeDTO" parameterType="hashMap">
- {CALL
- UP_ZIPCODE_SEARCH(
- #{d_name, mode=IN }
- ,#{PLIST1, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=zipcodeVOMap}
- ,#{PLIST2, mode=OUT, jdbcType=INTEGER }
- ,#{page, mode=IN }
- ,#{pagesize, mode=IN }
- )
- }
- </select>
- </mapper>
CommonController.java
- package nzin.co.kr.common.controller;
- import java.util.Map;
- import javax.servlet.http.HttpServletRequest;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Controller;
- import org.springframework.ui.Model;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.web.bind.annotation.RequestMethod;
- import nzin.co.kr.common.model.CommonService;
- import nzin.co.kr.common.model.ZipcodeDTO;
- /**
- * Handles requests for the application home page.
- */
- @Controller
- public class CommonController {
- private static final Logger logger = LoggerFactory.getLogger(CommonController.class);
- /**
- * Simply selects the home view to render by returning its name.
- */
- CommonService cService;
- public CommonService getcService() {
- return cService;
- }
- @Autowired
- public void setcService(CommonService cService) {
- this.cService = cService;
- }
- @RequestMapping(value = {"/popup/pop_zipsearch.do"}, method = RequestMethod.GET)
- public String pop_zipsearch(Model model, HttpServletRequest request) {
- logger.info("pop_zipsearch()");
- return "/popup/pop_zipsearch";
- }
- @RequestMapping(value = {"/popup/pop_zipsearch_list.do"}, method = RequestMethod.GET)
- public String pop_zipsearch_list(Model model, String d_name, String page, String pagesize) {
- logger.info("pop_zipsearch_list()");
- String pages = page;
- String listsize = pagesize;
- String b_name;
- b_name= d_name;
- if (pages == null) {
- pages = "1";
- }
- if (listsize == null) {
- listsize = "10";
- }
- if (b_name != null && b_name != "") {
- ZipcodeDTO dto = new ZipcodeDTO();
- dto.setB_name(b_name);
- Map<String, Object> result = cService.zip_list(dto, Integer.parseInt(pages), Integer.parseInt(listsize));
- //List<ZipcodeDTO> list = dao.list(dto, Integer.parseInt(pages), Integer.parseInt(listsize));
- //System.out.println("size : " + list.size());
- model.addAttribute("ziplist", result.get("result_list"));
- if(Integer.parseInt(result.get("result_cnt").toString()) > 0) {
- model.addAttribute("resultCount", result.get("result_cnt"));
- PagingNavi pNavi = new PagingNavi(Integer.parseInt(result.get("result_cnt").toString()), Integer.parseInt(listsize), 10,
- Integer.parseInt(pages));
- model.addAttribute("pn", pNavi);
- } else {
- model.addAttribute("resultCount", 0);
- }
- }
- logger.info("pages : " + pages);
- logger.info("listsize : " + listsize);
- logger.info("d_name : " + b_name);
- return "/popup/pop_zipsearch";
- }
- }
PagingNavi.java
- package nzin.co.kr.common.controller;
- public class PagingNavi {
- private int totalRow; // 총 레코드수
- private int totalPage; // 전체페이지
- private int currentPage; // 현재페이지
- private int pageGroup; // 페이지그룹
- private int listSize; // 페이지별 리스트갯수
- private int pageSize; // 페이지갯수..[1][2][3]..일반적으로 10개
- private boolean firstGo;
- private boolean lastGo;
- private boolean nextGo;
- private boolean prevGo;
- private int firstPage;
- private int nextPage;
- private int prevPage;
- private int lastPage;
- private int[] pageNavi;
- public PagingNavi(int totalRow, int listSize, int pageSize, int currentPage) {
- // 전체레코드수, 리스트갯수, 페이지갯수, 현재페이지
- this.totalRow = totalRow;
- this.listSize = listSize;
- this.pageSize = pageSize;
- this.currentPage = currentPage;
- if (currentPage == 0)
- currentPage = 1;
- int startNum;
- int endNum;
- int mod = totalRow % listSize;
- if (mod > 0) {
- totalPage = (totalRow / listSize) + 1;
- } else {
- totalPage = (totalRow / listSize);
- }
- pageGroup = (totalPage / pageSize) + 1; // 전체 페이지 그룹 (1~10 : 1그룹, 11~20:2그룹)
- int currentPageGroup = ((currentPage - 1) / pageSize) + 1; // 현재 페이지 그룹
- // System.out.println(currentPageGroup);
- firstGo = (currentPage <= pageSize) ? false : true; // 첫페이지 이동 설정(현재페이지가 1그룹안에 있으면 false)
- prevGo = (currentPageGroup == 1) ? false : true; // 이전페이지 이동(현재페이지가 1그룹에 있으면 false)
- nextGo = (currentPageGroup == pageGroup) ? false : true; // 다음페이지 이동(현재페이지가 마지막 그룹에 속해있으면 false)
- // lastGo = (currentPage >= pageSize && currentPageGroup == pageGroup) ? false : true; // 라스트페이지 이동 설정
- lastGo = (currentPageGroup == pageGroup) ? false : true; // 라스트페이지 이동 설정
- firstPage = 1; // 첫페이지값
- prevPage = (currentPageGroup - 1) * pageSize - (pageSize-1); // 이전페이지값
- nextPage = (currentPageGroup + 1) * pageSize - (pageSize-1); // 다음페이지값
- lastPage = totalPage; // 마지막페이지값
- // System.out.println(lastGo);
- startNum = (currentPage <= pageSize) ? 1 : ((currentPage - 1) / pageSize) * pageSize + 1; // 페이지 리스트 첫번째배열값
- endNum = (currentPageGroup < pageGroup) ? (((currentPage - 1) + pageSize) / pageSize) * pageSize : totalPage; // 페이지 리스트 마지막 배열값
- // System.out.println(lastNum);
- pageNavi = new int[endNum - startNum + 1];
- for (int i = 0; i < pageNavi.length; i++) {
- pageNavi[i] = startNum + i;
- }
- }
- public void getNavi() {
- setPrevGo(true);
- }
- public boolean isPrevGo() {
- return prevGo;
- }
- public void setPrevGo(boolean prevGo) {
- this.prevGo = prevGo;
- }
- public int getTotalRow() {
- return totalRow;
- }
- public void setTotalRow(int totalRow) {
- this.totalRow = totalRow;
- }
- public int getTotalPage() {
- return totalPage;
- }
- public void setTotalPage(int totalPage) {
- this.totalPage = totalPage;
- }
- public int getCurrentPage() {
- return currentPage;
- }
- public void setCurrentPage(int currentPage) {
- this.currentPage = currentPage;
- }
- public int getPageGroup() {
- return pageGroup;
- }
- public void setPageGroup(int pageGroup) {
- this.pageGroup = pageGroup;
- }
- public boolean isFirstGo() {
- return firstGo;
- }
- public void setFirstGo(boolean firstGo) {
- this.firstGo = firstGo;
- }
- public boolean isLastGo() {
- return lastGo;
- }
- public void setLastGo(boolean lastGo) {
- this.lastGo = lastGo;
- }
- public boolean isNextGo() {
- return nextGo;
- }
- public void setNextGo(boolean nextGo) {
- this.nextGo = nextGo;
- }
- public int[] getPageNavi() {
- return pageNavi;
- }
- public void setPageNavi(int[] pageNavi) {
- this.pageNavi = pageNavi;
- }
- public int getListSize() {
- return listSize;
- }
- public void setListSize(int listSize) {
- this.listSize = listSize;
- }
- public int getPageSize() {
- return pageSize;
- }
- public void setPageSize(int pageSize) {
- this.pageSize = pageSize;
- }
- public int getNextPage() {
- return nextPage;
- }
- public void setNextPage(int nextPage) {
- this.nextPage = nextPage;
- }
- public int getPrevPage() {
- return prevPage;
- }
- public void setPrevPage(int prevPage) {
- this.prevPage = prevPage;
- }
- public int getLastPage() {
- return lastPage;
- }
- public void setLastPage(int lastPage) {
- this.lastPage = lastPage;
- }
- public int getFirstPage() {
- return firstPage;
- }
- }
pop_zipsearch.jsp
- <%@page import="nzin.co.kr.common.controller.PagingNavi"%>
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
- <%
- // PagingNavi pNavi = (PagingNavi)request.getAttribute("pn");
- // out.print(pNavi.getNextPage());
- %>
- <!DOCTYPE html>
- <html>
- <head>
- <meta charset="UTF-8">
- <title>Insert title here</title>
- <link href="/resources/css/common.css" rel="stylesheet">
- <!-- common script group -->
- <jsp:include page="/resources/include/script.jsp" flush="false" />
- <!-- common script group -->
- <script language="javascript">
- function ValueSelect(zip, addr_dong, addr_road, build_name) {
- // opener.document.getElementById("zipcode1_dong").value=zip1;
- $("#zipcode",opener.document).val(zip);
- $("#address_dong",opener.document).val(addr_dong);
- $("#address_road",opener.document).val(addr_road);
- $("#addr_detail_dong",opener.document).val(build_name);
- $("#addr_detail_road",opener.document).val(build_name);
- self.close();
- }
- </script>
- </head>
- <body>
- <h1 style="color:#ffffff">ZIPCODE SEARCH</h1>
- <br> result : ${resultCount }
- <form name="zip_frm" method="get" action="/popup/pop_zipsearch_list.do">
- <table cellspacing="0" cellpadding="0" style="width: 100%;">
- <tr>
- <td colspan="2">
- <div class="content-form-header-wrap content-form-header-wrap-blue">검색어를
- 입력해주세요</div>
- </td>
- </tr>
- <tr style="height:45px;">
- <td class="content-form-field-name">동/건물명 입력</td>
- <td><input type="text" name="d_name" id="d_name" value="수택동" class="content-form-field-input">
- <input type="submit" value="search" id="sbutton"></td>
- </tr>
- <tr>
- <td>우편번호</td>
- <td>동주소</td>
- </tr>
- <c:forEach var="zlist" items="${ziplist}">
- <tr>
- <td>${zlist.zipcode}</td>
- <td><a href="javascript:void(0);"
- onclick="ValueSelect('${zlist.zipcode}', '${zlist.sido} ${zlist.sigungu} ${zlist.myon} ${zlist.road_name} ${zlist.building_no} ${zlist.building_no_sub}', '${zlist.sido} ${zlist.sigungu} ${zlist.myon} ${zlist.road_name} ${zlist.building_no} ${zlist.building_no_sub}', '${zlist.sigungu_building_name }');">${zlist.sido}
- ${zlist.sigungu} ${zlist.myon} ${zlist.road_name}
- ${zlist.building_no} ${zlist.building_no_sub}
- ${zlist.sigungu_building_name }</a></td>
- </tr>
- </c:forEach>
- </table>
- </form>
- <c:if test="${pn.firstGo == true }">
- <a href="/popup/pop_zipsearch_list.do?page=${pn.firstPage }&pagesize=${pn.listSize}&d_name=${param.d_name}">처음</a>
- </c:if>
- <c:if test="${pn.prevGo == true }">
- <a href="/popup/pop_zipsearch_list.do?page=${pn.prevPage }&pagesize=${pn.listSize}&d_name=${param.d_name}">이전</a>
- </c:if>
- <c:forEach var="plist" items="${pn.pageNavi }">
- <a href="/popup/pop_zipsearch_list.do?page=${plist }&pagesize=${pn.listSize}&d_name=${param.d_name}"><c:choose><c:when test="${pn.currentPage == plist}"><b>[${plist }]</b></c:when><c:otherwise>${plist }</c:otherwise></c:choose></a>
- </c:forEach>
- <c:if test="${pn.nextGo == true }">
- <a href="/popup/pop_zipsearch_list.do?page=${pn.nextPage }&pagesize=${pn.listSize}&d_name=${param.d_name}">다음</a>
- </c:if>
- <c:if test="${pn.lastGo == true }">
- <a href="/popup/pop_zipsearch_list.do?page=${pn.lastPage }&pagesize=${pn.listSize}&d_name=${param.d_name}">마지막</a>
- </c:if>
- </body>
- </html>
여기부터는 오라클 설정
1. 패키지 생성
- CREATE OR REPLACE PACKAGE TYPES AS --> 패키지명은 "TYPES"
- TYPE cursorType IS REF CURSOR; --> 패키지에 생성된 커서타입 REF CURSOR
- END TYPES;
2. 프로시저 생성
- CREATE OR REPLACE PROCEDURE UP_ZIPCODE_SEARCH (
- PSEARCH_FIELD IN VARCHAR2, --SEARCH FIELD
- PLIST1 OUT TYPES.CURSORTYPE, --> 첫째 select값을 PLIST1 커서에 담는다.
- PLIST2 OUT NUMBER, --> 두번째 전체 count값을 PLIST2에 담는다.
- PAGE IN NUMBER,
- PAGESIZE IN NUMBER
- )
- AS
- BEGIN
- --LIST1
- OPEN PLIST1 FOR --> 커서를 오픈하고 ";"까지의 select값을 담는다.
- --SELECT zipcode, sido, sigungu, b_name FROM ZIPCODE_NEW WHERE B_NAME LIKE ''||PSEARCH_FIELD||'%' AND ROWNUM < 10;
- SELECT * FROM (
- SELECT ROWNUM RNUM, zipcode, sido, sigungu, myon, b_name, road_name, building_no, building_no_sub, sigungu_building_name FROM (
- SELECT zipcode, sido, sigungu, myon, b_name, road_name, building_no, building_no_sub, sigungu_building_name FROM ZIPCODE_NEW
- WHERE B_NAME LIKE ''||PSEARCH_FIELD||'%' OR ROAD_NAME LIKE ''||PSEARCH_FIELD||'%'
- )
- ) WHERE RNUM BETWEEN ((PAGE-1) * 10 + 1) AND (PAGE * PAGESIZE) ;
- --LIST2
- SELECT count(zipcode) INTO PLIST2 FROM ZIPCODE_NEW WHERE B_NAME LIKE ''||PSEARCH_FIELD||'%' OR ROAD_NAME LIKE ''||PSEARCH_FIELD||'%' ;
- END UP_ZIPCODE_SEARCH;
728x90
반응형
'SPRING' 카테고리의 다른 글
웹 소켓(spring을 이용한 채팅) (0) | 2016.04.26 |
---|---|
테스트 주도 개발(TDD-Test Driven Development) (0) | 2016.03.30 |
AOP-Aspect Oriented Programming (0) | 2016.03.25 |
인터셉터(Interceptor) 사용하기 (0) | 2016.03.18 |
log4j 사용 및 적용방법 (0) | 2016.03.17 |