전자정부 프레임워크3- select, delete

프로젝트는 egov_dept를 이어서 한다.

insert는 전자정부 프레임워크2를 참고!

1.select 전체 목록 조회

sql.xml부터

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<!-- <sqlMap namespace="Sample"> -->
<!-- 고유한 이름, 안에서 사용하진 않는다 -->
<sqlMap namespace="Dept">

   <typeAlias  alias="egovMap" type="egovframework.rte.psl.dataaccess.util.EgovMap"/>
   <!-- type="" : 리턴타입 alias="deptVO" 로 받아서 처리 -->
   <typeAlias  alias="deptVO" type="egovframework.example.sample.service.DeptVO"/>


<!-- 	deptDAO.insertDept : dao에서 호출되는 이름과 매칭 -->
   <insert id="deptDAO.insertDept">
<!--    		##: dao에서 넘어오는 vo -->
<!--    		"deptDAO.insertDept", vo -->
      <![CDATA[
         INSERT INTO dept 
            (deptno
            ,dname
            ,loc )
         VALUES ( 
         	 #deptno#
            ,#dname#
            ,#loc# )
      ]]>
   </insert>
	
		
	 <select id="deptDAO.selectDept" resultClass="deptVO">
        <![CDATA[
            SELECT
                deptno, dname, loc
            FROM dept 
        ]]>
    </select>

</sqlMap>

dao

package egovframework.example.sample.service.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import egovframework.example.sample.service.DeptVO;
import egovframework.rte.psl.dataaccess.EgovAbstractDAO;

//@Repository("deptDAO"): 스프링 컨테이너가 관리하는 빈(스프링이 관리하는 객체)
@Repository("deptDAO")
public class DeptDAO extends EgovAbstractDAO {
	
	public String inserDept(DeptVO vo) throws Exception {
		return (String) insert("deptDAO.insertDept", vo);
	}

	public List<?> selectDeptList(DeptVO vo) throws Exception {
		      return list("deptDAO.selectDept", vo);
		}

}

service

package egovframework.example.sample.service;

import java.util.List;

public interface DeptService {
//	접근 지정자 없으므로 default임
	String insertDept(DeptVO vo) throws Exception;
	List<?> selectDeptList(DeptVO vo) throws Exception;

}

impl

package egovframework.example.sample.service.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import egovframework.example.sample.service.DeptService;
import egovframework.example.sample.service.DeptVO;
import egovframework.example.sample.service.EgovSampleService;
import egovframework.example.sample.service.SampleDefaultVO;
import egovframework.rte.fdl.cmmn.EgovAbstractServiceImpl;


//@Service("deptService"): 컨트롤러에서 deptService로 연결
@Service("deptService")
public class DeptServiceImpl extends EgovAbstractServiceImpl implements DeptService {

//	@Resource(name = "deptDAO") : deptDAO 이름으로 dao 사용(ibatis방식, mybatis는 매퍼)
	@Resource(name = "deptDAO")
	private DeptDAO deptDAO;
	
	@Override
	public String insertDept(DeptVO vo) throws Exception {
		return deptDAO.inserDept(vo);
	}
	@Override
	public List<?> selectDeptList(DeptVO vo) throws Exception {
		return deptDAO.selectDeptList(vo);
	}
}

controller

package egovframework.example.sample.web;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import egovframework.example.sample.service.DeptService;
import egovframework.example.sample.service.DeptVO;
import egovframework.example.sample.service.EgovSampleService;
import egovframework.example.sample.service.SampleDefaultVO;
import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
public class DeptController {
	
//	@Resource(name = "deptService") : DeptServiceImpl에 @Service로 연결
	@Resource(name = "deptService")
	private DeptService deptService;
	
	@RequestMapping(value = "/deptWrite.do")
	public String deptWrite() {
		return "dept/deptWrite";
	}
	
	@RequestMapping(value = "/deptWriteSave.do")
//	public String deptWriteSave(String deptno, String dname, String loc) {
	public String deptWriteSave(DeptVO vo) throws Exception {
//		log.info("부서번호 : "+deptno);
//		log.info("부서이름 : "+dname);
		log.info("부서번호 : "+vo.getDeptno());
		log.info("부서이름 : "+vo.getDname());
		log.info("부서위치 : "+vo.getLoc());
		
		String result = deptService.insertDept(vo);
		log.info("@# result ===>"+ result);
		
		return "";
	}
	
	@RequestMapping(value = "/deptList.do")
//	ModelMap : 전자정부 프레임워크는 model의 이름이 다르다
	public String selectDeptList(DeptVO vo, ModelMap model) throws Exception {
		
		List<?> list = deptService.selectDeptList(vo);
		log.info("@# list=>"+list);
		model.addAttribute("resultList", list);
		
		return "dept/deptList";
	}

}

deptList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
	<style type="text/css">
		table{
			width: 400px;
			border-collapse: collapse;
		}
		th, td{
			border: 1px solid #cccccc;
			padding: 5px;
		}
	</style>
</head>
<body>
	<table>
		<caption>부서목록</caption>
		<tr>
			<th>부서번호</th>
			<th>부서이름</th>
			<th>부서위치</th>
		</tr>
		<c:forEach var="result" items="${resultList}" varStatus="status">
            <tr>
<!--            c:out으로 써도되고 안써도 됨(초기 출력방식) -->
				<td>${result.deptno}</td>
				<td>${result.dname}</td>
				<td>${result.loc}</td>
            </tr>
        </c:forEach>
	</table>
</body>
</html>

collapse : 테이블의 셀 경계가 병합되고 겹치지 않게 됨

 

http://localhost:8181/egov_dept/deptList.do

 

내가 자주 틀리는 것 

1. alias는 중복되면 안됨 . 저길 sampleVO그대로 뒀다가 sample_sql의 alias와 중복되어 빈이 생성이 인되는 오류 겪음

1. mybatis의 resultType처럼 ibatis는 resultMap, resultClass등으로 반환 타입을 표시함. 반환타입과 일치하지 않으면 오류발생

 

null 파싱 오류

null값있는걸 불러올때 vo 컬럼 타입을 Integer로  불러오거나, 쿼리에서 nvl로 처리하지않으면 오류가 뜬다.

그럴때

우리가 만든 vo말고 위에서 정의되어있는 egovmap을 쓰면 알아서 처리해줌

아니면 null 들어올 값에 다 nvl처리

 

1-1. select 상세보기

jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
	<style type="text/css">
		table{
			width: 400px;
			border-collapse: collapse;
		}
		th, td{
			border: 1px solid #cccccc;
			padding: 5px;
		}
	</style>
</head>
<body>
	<table>
		<caption>부서목록</caption>
		<tr>
			<th>부서번호</th>
			<th>부서이름</th>
			<th>부서위치</th>
		</tr>
		<c:forEach var="result" items="${resultList}" varStatus="status">
            <tr>
<!--            c:out으로 써도되고 안써도 됨(초기 출력방식) -->
				<td>${result.deptno}</td>
				<td>
					<a href="deptDetail.do?deptno=${result.deptno}">${result.dname}
				</td>
				<td>${result.loc}</td>
            </tr>
        </c:forEach>
	</table>
</body>
</html>

dept xml

	 <select id="deptDAO.selectDeptDetail" resultClass="deptVO">
        <![CDATA[
            SELECT
                deptno, dname, loc
            FROM dept 
            WHERE deptno=#deptno#
        ]]>
    </select>

dao.java

	public DeptVO selectDeptDetail(int deptno) throws Exception {
		return (DeptVO)select("deptDAO.selectDeptDetail", deptno);
	}

service

DeptVO selectDeptDetail(int deptno) throws Exception;

serviceimpl

@Override
	public DeptVO selectDeptDetail(int deptno) throws Exception {
		return deptDAO.selectDeptDetail(deptno);
	}

controller

	@RequestMapping(value = "/deptDetail.do")
//	ModelMap : 전자정부 프레임워크는 model의 이름이 다르다
	public String selectDeptDetail(int deptno, ModelMap model) throws Exception {
		
		DeptVO vo = deptService.selectDeptDetail(deptno);
		log.info("@# vo=>"+vo);
		model.addAttribute("deptVO", vo);
		
		return "dept/deptDetail";
	}

deptDetail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
	<style type="text/css">
		table{
			width: 400px;
			border-collapse: collapse;
		}
		th, td{
			border: 1px solid #cccccc;
			padding: 5px;
		}
	</style>
</head>
<body>
	<table>
			<tr>
				<th>부서번호</th>
				<td>${deptVO.deptno}</td>
			</tr>
			<tr>
				<th>부서이름</th>
				<td>${deptVO.dname}</td>
			</tr>
			<tr>
				<th>부서위치</th>
				<td>${deptVO.loc}</td>
			</tr>
			<tr align="center">
				<td colspan="2">
					<button type="button">삭제</button>
					<button type="button">수정</button>
				</td>
			</tr>
		</table>
</body>
</html>

2.delete

jsp

<button type="button" onclick="location='deptDelete.do?deptno=${deptVO.deptno}'">삭제</button>

dept sql.xml

  <delete id="deptDAO.deleteDept">
		<![CDATA[
			DELETE FROM dept 
			WHERE deptno=#deptno#
		]]>
	</delete>

dao.java

	public void deleteDept(int deptno) throws Exception {
		delete("deptDAO.deleteDept", deptno);
	}

service

void deleteDept(int deptno) throws Exception;

impl

	@Override
	public void deleteDept(int deptno) throws Exception {
		deptDAO.deleteDept(deptno);
	}

controller

	@RequestMapping("/deptDelete.do")
	public String deleteDept(int deptno) throws Exception {
		deptService.deleteDept(deptno);
//		forward: 전자정부 프레임워크에서의 redirect
		return "forward:/deptList.do";
	}