반응형
//값을 여러개 선택하여 검색하는 경우에는 hidden태그와 Param으로 전달받아 함께 검색해야 유지된다.
히든 과? Param으로 받아 삼항연산자 사용해야 동기로 값이 유지!
<<<car 조회>>>>
//car_mapper.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">
<!-- Query문을 정의
ns(namespace) : XML내에서 중복된 id를 사용할 수 있도록 구분하는 것.
(패키지와 비슷한 용도)
-->
<mapper namespace="kr.co.sist.car">
<!-- Car resultMap 설정 : setter method명이 컬럼명과 다른경우에 주의 -->
<resultMap type="kr.co.sist.exam.domain.CarModel" id="carModelResult">
<result column="car_img" property="carImg"/>
<result column="maker" property="maker"/>
<result column="model" property="model"/>
<result column="car_year" property="carYear"/>
<result column="price" property="price"/>
<result column="car_option" property="carOption"/>
</resultMap>
<select id="selectCarCountry" resultType="String" parameterType="String">
select maker
from car_country
where country = #{ country }
</select>
<select id="selectCarMaker" resultType="String" parameterType="String">
select model
from car_maker
where maker = #{ maker }
</select>
<select id="selectCarModel" resultMap="carModelResult" parameterType="String">
select cmo.car_img, cma.maker, cmo.model, cmo.car_year, cmo.price, cmo.car_option
from car_maker cma, car_model cmo
where (cmo.model = cma.model) and cmo.model = #{ model }
</select>
</mapper>
//CarModel.java
package kr.co.sist.exam.domain;
public class CarModel {
private String carImg,maker,model,carYear,price,carOption;
public String getCarImg() {
return carImg;
}
public void setCarImg(String carImg) {
this.carImg = carImg;
}
public String getMaker() {
return maker;
}
public void setMaker(String maker) {
this.maker = maker;
}
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}
public String getCarYear() {
return carYear;
}
public void setCarYear(String carYear) {
this.carYear = carYear;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getCarOption() {
return carOption;
}
public void setCarOption(String carOption) {
this.carOption = carOption;
}
}
//CarDAO.java
package kr.co.sist.exam.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import kr.co.sist.exam.domain.CarModel;
public class CarDAO {
public List<String> selectMaker(String country){
List<String> list=null;
//4.Handler얻기
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("selectCarCountry", country);
ss.close();
return list;
}//selectMaker
public List<String> selectModel(String maker){
List<String> list=null;
//4.Handler얻기
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("selectCarMaker", maker);
ss.close();
return list;
}//selectModel
public List<CarModel> selectDetailModel(String model){
List<CarModel> list=null;
//4.Handler얻기
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("selectCarModel", model);
ss.close();
return list;
}//selectDetailModel
public static void main(String[] args) {
CarDAO md=new CarDAO();
//md.selectMaker("국산");
//md.selectModel("BMW");
md.selectDetailModel("아반테");
}//main
}//class
//CarService.java
package kr.co.sist.exam.service;
import java.util.List;
import kr.co.sist.exam.dao.CarDAO;
import kr.co.sist.exam.domain.CarModel;
public class CarService {
private CarDAO cd;
public CarService() {
cd=new CarDAO();
}//CarService
public List<String> searchMaker(String country){
List<String> list=null;
//CarDAO cd=new CarDAO();
list=cd.selectMaker(country);
return list;
}//searchMaker
public List<String> searchModel(String maker){
List<String> list=null;
list=cd.selectModel(maker);
return list;
}//searchModel
public List<CarModel> searchDetailModel(String model){
List<CarModel> list=null;
list=cd.selectDetailModel(model);
return list;
}//searchDetailModel
}//class
//car.jsp
<%@page import="kr.co.sist.exam.domain.CarModel"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.sist.exam.service.CarService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String country=request.getParameter("car_country");
String maker=request.getParameter("car_maker");
String model=request.getParameter("car_model");
String selectFlag=request.getParameter("car_flag");//submit이 되는 <form>에서 어떤 폼 컨트롤이 이벤트를 발생시켰는지 식별하기 위해서 사용하는것
CarService cs=new CarService();
if("1".equals(selectFlag)){//제조국에서 이벤트가 발생 했을 때
List<String> list=cs.searchMaker(country); //제조국에 따른 제조사를 조회
pageContext.setAttribute("makerList", list);
}//end if
if("2".equals(selectFlag)){//제조사에서 이벤트가 발생 했을 때
List<String> list=cs.searchMaker(country);
pageContext.setAttribute("makerList", list);
//제조사에 따른 모델명을 조회
List<String> modelList=cs.searchModel(maker);
pageContext.setAttribute("modelList", modelList);
}//end if
if("3".equals(selectFlag)){//모델에서 이벤트가 발생했을 때
List<String> list=cs.searchMaker(country);
pageContext.setAttribute("makerList", list);
//제조사에 따른 모델명을 조회
List<String> modelList=cs.searchModel(maker);
pageContext.setAttribute("modelList", modelList);
//모델에 따른 세부 차량 조회
List<CarModel> carList=cs.searchDetailModel(model);
pageContext.setAttribute("carList", carList);
//동기식의 가장 큰 문제 여러번 붙여야 한다.
}//end if
%>
<script type="text/javascript">
//CDN : Content delivery network 콘텐츠 전송 네트워크
$(function(){
$("#car_country").change(function(){
if($("#car_country").val() !="none"){
$("#car_flag").val("1");
$("#frm").submit();
}//end if
});//change
$("#car_maker").change(function(){
if($("#car_maker").val() !="none"){
$("#car_flag").val("2");
$("#frm").submit();
}//end if
});//change
$("#car_model").change(function(){
if($("#car_model").val() !="none"){
$("#car_flag").val("3");
}//end if
});//change
$("#btn").click(function(){
if($("#car_flag").val()=="3"){
$("#frm").submit();
}//end if
});//click
});//ready
</script>
<div id="carHeader" style="margin-top: 20px; ">
<form name="frm" action="main.jsp" method="get" id="frm">
<input type="hidden" name="page" value="day0410/car"/>
<input type="hidden" name="car_flag" id="car_flag"/>
<select name="car_country" id="car_country">
<option value="none">----제조국----</option>
<option value="국산"${param.car_country eq '국산'?"selected='selected'":"" }>국산</option>
<option value="수입"${param.car_country eq '수입'?"selected='selected'":""}>수입</option>
</select>
<select name="car_maker" id="car_maker">
<option value="none">----제조사----</option>
<c:if test="${not empty makerList }">
<c:forEach var="maker" items="${makerList}">
<option value="${maker}"${param.car_maker eq maker?"selected='selected'":"" }><c:out value="${maker}"/></option>
</c:forEach>
</c:if>
</select>
<select name="car_model" id="car_model">
<option value="none">----모델명----</option>
<c:if test="${not empty modelList}">
<c:forEach var="model" items="${modelList}">
<option value="${model}"${param.car_model eq model?"selected='selected'":"" }><c:out value="${model}"/></option>
</c:forEach>
</c:if>
</select>
<input type="button" value="조회" id="btn" class="btn"/>
</form>
</div><br/>
<div id="carSearchView">
<c:if test="${param.car_flag eq '3'}">
<table style="border-top: 2px solid #333; border-bottom: 1px solid #333; ">
<tr>
<th width="80" style="border-bottom: 1px solid #333;">이미지</th>
<th width="400" style="border-bottom: 1px solid #333;">차량정보</th>
<th width="150" style="border-bottom: 1px solid #333;">연식</th>
</tr>
<c:if test="${empty carList}">
<tr>
<td colspan="3" style="text-align: center">등록된 차량이 존재하지 않습니다.</td>
</tr>
</c:if>
<c:forEach var="car" items="${carList}">
<tr>
<td><img src="http://localhost:8080/mybatis_prj/day0409/images/${car.carImg}" style="width:80px; height: 50px"/></td>
<td>
<div>
<c:out value="${car.maker}"/><c:out value="${car.model}"/>
</div>
<div>
<c:out value="${car.price}"/>만원
</div>
<div>
<c:out value="${car.carOption}"/>
</div>
</td>
<td>
<c:out value="${car.carYear}"/>년식
</td>
</tr>
</c:forEach>
</table>
</c:if>
</div>
//다이나믹은 vo
*dynamic Query
-
<if> )
-
는 단일if -조건에 맞을때에만 쿼리를 실행해야 할 때.
-
<choose> )
-
는 여러개의 조건을 비교할 때
-
속성이 없고
-
<choose>
-
<when test="조건식">
-
조건에 맞을때 실행될 쿼리문
-
</when>
-
...반복
-
<otherwise>
-
모든 조건이 맞지않을 때 실행될 쿼리문
-
</otherwise>
-
</choose>
//dynamic_choose.jsp
<%@page import="kr.co.sist.exam.domain.Dynamicif"%>
<%@page import="kr.co.sist.exam.vo.DeptnoVO"%>
<%@page import="kr.co.sist.exam.service.MyBatisService1"%>
<%@page import="kr.co.sist.exam.domain.Emp"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.sist.exam.dao.MyBatisDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
MyBatisService1 mbs=new MyBatisService1();
String deptno=request.getParameter("deptno");
if(deptno!=null){
List<Dynamicif> list=mbs.dynamicChoose(new DeptnoVO(Integer.parseInt(deptno)));
pageContext.setAttribute("empList", list);
}//end if
%>
<form name="frm" action="main.jsp">
<input type="hidden" name="page" value="day0410/dynamic_choose"/>
<label>부서번호 입력</label>
<input type="text" name="deptno" class="inputBox"/>
<input type="submit" value="사원 조회" class="btn"/><br/>
*10~30번 부서의 사원을 조회. 없는 부서번호가 입력되면 모든 부서사원을 조회
</form>
<div>
<c:if test="${not empty param.deptno}">
<div>
${param.deptno}번 부서 사원 조회 결과
</div>
<table border="1">
<tr>
<th width="40">번호</th>
<th width="80">사원번호</th>
<th width="120">사원명</th>
<th width="80">직무</th>
<th width="80">부서번호</th>
<th width="150">입사일</th>
</tr>
<c:if test="${empty empList}">
<tr>
<td colspan="6" align="center">
입력하신 부서는 부서가 존재하지 않거나 사원이 없습니다.
</td>
</tr>
</c:if>
<c:forEach var="emp" items="${empList}">
<c:set var="i" value="${i+1}"/>
<tr>
<td><c:out value="${i}"/></td>
<td><c:out value="${emp.empno}"/></td>
<td><c:out value="${emp.ename}"/></td>
<td><c:out value="${emp.job}"/></td>
<td><c:out value="${emp.deptno}"/></td>
<td><c:out value="${emp.hiredate}"/></td>
</tr>
</c:forEach>
</table>
</c:if>
</div>
-
<forEach> )
-
//는 속성이 위와 좀 다르다 => 반복! (쿼리문이 동적일때 반복 시킬 일??=>있긴 있 ㅏㄷ...) 조건 검색..
-
배열이나 List에 들어있는 값을 반복 시켜야 할 때
-
//where 회사명 in(,,,)
-
in <forEach collection="배열|List명" item="객체명" open="시작문자(" close="끝문자)" separator="값구문하는 문자">
-
${getter명}
-
</forEach>
|
ex)
select 컬럼명,,,
from 테이블명
where 컬럼명 in
<forEach open="("
collection="List를 반환하는 getter명(nameList)" item="객체명" separator="," close=")"> //getNameList가 nameList에 들어온다.
#{} ///객체명이 들어온다.
</forEach>
|
class Test{
private List<String> nameList;
public List<String> getNameList(){
} |
//dynamic_foreach.jsp
<%@page import="kr.co.sist.exam.domain.Car"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.sist.exam.service.MyBatisService1"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
MyBatisService1 mbs =new MyBatisService1();
List<Car> carList=mbs.joinSubquery();
pageContext.setAttribute("carList", carList);
%>
<div>
<form action="main.jsp" method="get">
<input type="hidden" name="page" value="day0410/dynamic_foreach"/>
<c:set var="maker" value="현대,기아,삼성,쌍용,BMW,BENZ,AUDI"/>
제조사 선택 :
<c:forTokens items="${maker}" delims="," var="maker">
<input type="checkbox" name="maker" value="${maker}"/>
<c:out value="${maker}" escapeXml="false"/>
</c:forTokens>
<input type="submit" value="조회" class="btn"/>
</form>
</div>
<table style="border-top: 2px solid #333; border-bottom: 1px solid #333; ">
<tr>
<th width="80" style="border-bottom: 1px solid #333;">이미지</th>
<th width="400" style="border-bottom: 1px solid #333;">차량정보</th>
<th width="150" style="border-bottom: 1px solid #333;">등록일</th>
</tr>
<c:if test="${empty carList}">
<tr>
<td colspan="3" style="text-align: center">등록된 차량이 존재하지 않습니다.</td>
</tr>
</c:if>
<c:forEach var="car" items="${carList}">
<tr>
<td><img src="http://localhost:8080/mybatis_prj/day0409/images/${car.img}" style="width:80px; height: 50px"/></td>
<td>
<div>
<c:out value="${car.maker}"/><c:out value="${car.model}"/>
</div>
<div>
<c:out value="${car.carYear}"/>년식 <c:out value="${car.price}"/>
</div>
<div>
<c:out value="${car.carOption}"/>
</div>
</td>
<td>
<c:out value="${car.hiredate}"/>
</td>
</tr>
</c:forEach>
</table>
//CarVO.java
package kr.co.sist.exam.vo;
import java.util.List;
public class CarVO {
private List<String> makerList;
public CarVO(List<String> makerList) {
this.makerList = makerList;
}
public List<String> getMakerList() {
return makerList;
}
}
//dynamic_foreach.jsp
<%@page import="kr.co.sist.exam.domain.Car"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.sist.exam.service.MyBatisService1"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String[] makerArr=request.getParameterValues("maker");
MyBatisService1 mbs =new MyBatisService1();
List<Car> carList=mbs.dynamicForeach(makerArr);
pageContext.setAttribute("carList", carList);
%>
<div style="margin-top: 20px">
<form action="main.jsp" method="get">
<input type="hidden" name="page" value="day0410/dynamic_foreach"/>
<c:set var="maker" value="현대,기아,삼성,쌍용,BMW,BENZ,AUDI"/>
제조사 선택 :
<c:forTokens items="${maker}" delims="," var="maker">
<input type="checkbox" name="maker" value="${maker}"/>
<c:out value="${maker}" escapeXml="false"/>
</c:forTokens>
<input type="submit" value="조회" class="btn"/>
</form>
</div>
<div style="margin-top: 20px">
<table style="border-top: 2px solid #333; border-bottom: 1px solid #333; ">
<tr>
<th width="80" style="border-bottom: 1px solid #333;">이미지</th>
<th width="400" style="border-bottom: 1px solid #333;">차량정보</th>
<th width="150" style="border-bottom: 1px solid #333;">등록일</th>
</tr>
<c:if test="${empty carList}">
<tr>
<td colspan="3" style="text-align: center">등록된 차량이 존재하지 않습니다.</td>
</tr>
</c:if>
<c:forEach var="car" items="${carList}">
<tr>
<td><img src="http://localhost:8080/mybatis_prj/day0409/images/${car.img}" style="width:80px; height: 50px"/></td>
<td>
<div>
<c:out value="${car.maker}"/><c:out value="${car.model}"/>
</div>
<div>
<c:out value="${car.carYear}"/>년식 <c:out value="${car.price}"/>
</div>
<div>
<c:out value="${car.carOption}"/>
</div>
</td>
<td>
<c:out value="${car.hiredate}"/>
</td>
</tr>
</c:forEach>
</table>
</div>
*procedure 사용
create or replace procedure 프로시저명{
매개변수 in 데이터형, 매개변수 in 데이터형,
매개변수 out 데이터형, 매개변수 out 데이터형)
is
begin
end;
/
--반환형이 없다! 대신 in/out
create or replace procedure insertDept{
deptno in number|dept.deptno%type, dname in varchar2,
loc out varchar2, cnt out number)
is
begin
:cnt=sql%rowCount; --:cnt가 out 을 타고 나간다
end;
/
-
<select>노드를 가지고 호출한다.
-
<select id="" parameterType="VO(getter-입력되어 사용할 값과 저장할 값을 모두 가지고 있어야 함)" statementType="CALLABLE">
-
//VO는 int deptno, cnt, String dname, loc를 가져야 한다.
-
{
-
call 프로시저명(insertDept)(#{getter명, mode=IN} | OUT, jdbcType=DB에서 사용하는 데이터형, javaType="Java에서 저장할 데이터형}
-
call insert_dept(#{deptno, mode=IN},
-
#{dname,mode=IN},
-
#{loc, mode=IN},
-
#{cnt,mode=out, jdbcType=NUMERIC, JavaType=int})
-
}
-
</select>
-
T VO tv=new TVO(10,"개발",서울,0);
-
SqlSession.selectOne("id", tv);//여러행을 반환해도 프로시져 호출해 List가 아니라 vo가 list를 가진다.
-
tv.getCnt() //추가된 함수 cnt parameter에 대한 값을 얻음.
//TestProcVO.java
package kr.co.sist.exam.vo;
/**
* Procedure에 사용되는 VO는 In Parameter 값과 Out Parameter 값을 모두 가지고 있어야 한다.
* @author owner
*/
public class TestProcVO {
private int empno, sal, rowCnt;
private String ename, job, msg;
public TestProcVO(int empno, int sal, int rowCnt, String ename, String job, String msg) {
this.empno = empno;
this.sal = sal;
this.rowCnt = rowCnt;
this.ename = ename;
this.job = job;
this.msg = msg;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getRowCnt() {
return rowCnt;
}
public void setRowCnt(int rowCnt) {
this.rowCnt = rowCnt;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}//class
//insert_procedure.jsp
<%@page import="kr.co.sist.exam.vo.TestProcVO"%>
<%@page import="kr.co.sist.exam.domain.Car"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.sist.exam.service.MyBatisService1"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String empno=request.getParameter("empno");
String ename=request.getParameter("ename");
String sal=request.getParameter("sal");
String job=request.getParameter("job");
if(empno!=null){
MyBatisService1 mbs =new MyBatisService1();
TestProcVO tp=new TestProcVO(Integer.parseInt(empno),Integer.parseInt(sal),0,ename,job,"" );
tp=mbs.insertProcedure(tp);
pageContext.setAttribute("tp", tp);
}//end if
%>
<div style="margin-top: 20px">
<form action="main.jsp" method="get">
<input type="hidden" name="page" value="day0410/insert_procedure"/>
사원번호 :
<input type="text" name="empno" class="inputBox"/><br/>
사원명 :
<input type="text" name="ename" class="inputBox"/><br/>
직급 :
<input type="text" name="job" class="inputBox"/><br/>
연봉 :
<input type="text" name="sal" class="inputBox"/><br/>
<input type="submit" value="사원추가" class="btn"/>
</form>
</div>
<div style="margin-top: 20px">
<c:if test="${not empty param.empno}">
<c:out value="${tp.rowCnt}"/>건 수행 되었습니다.<br/>
<c:out value="${tp.msg}"/>
</c:if>
</div>
//main_menu.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- smartmenu 시작 -->
<!-- SmartMenus core CSS (required) -->
<link href="http://localhost:8080/mybatis_prj/common/smartmenu/css/sm-core-css.css" rel="stylesheet" type="text/css" />
<!-- "sm-blue" menu theme (optional, you can use your own CSS, too) -->
<link href="http://localhost:8080/mybatis_prj/common/smartmenu/css/sm-simple/sm-simple.css" rel="stylesheet" type="text/css" />
<!-- jQuery -->
<!-- <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script> -->
<!-- SmartMenus jQuery plugin -->
<script type="text/javascript" src="http://localhost:8080/mybatis_prj/common/smartmenu/jquery.smartmenus.js"></script>
<!-- SmartMenus jQuery init -->
<script type="text/javascript">
$(function() {
$('#main-menu').smartmenus({
subMenusSubOffsetX: 1,
subMenusSubOffsetY: -8
});
});
</script>
<!-- smartmenu 끝 -->
<nav id="main-nav">
<!-- Sample menu definition -->
<ul id="main-menu" class="sm sm-simple">
<li><a href="#void">홈으로</a></li>
<li><a href="#void">쿼리 실행</a>
<ul>
<li><a href="#void">1일차</a>
<ul>
<li><a href="main.jsp?page=day0404/single_column">컬럼하나에 레코드 하나</a></li>
<li><a href="main.jsp?page=day0404/multi_column">컬럼여러개에 레코드 하나</a></li>
<li><a href="main.jsp?page=day0404/multi_row">컬럼하나에 레코드 여러개</a></li>
</ul>
</li>
<li><a href="#void">2일차</a>
<ul>
<li><a href="main.jsp?page=day0405/multi_column_row">컬럼여러개에 레코드 여러개</a></li>
<li><a href="main.jsp?page=day0405/multi_param">where절의 값이 여러개</a></li>
<li><a href="main.jsp?page=day0405/lessthan">< 의 비교</a></li>
<li><a href="main.jsp?page=day0405/greaterthan">> 의 비교</a></li>
<li><a href="main.jsp?page=day0405/like">like</a></li>
</ul>
</li>
<li><a href="#void">3일차</a>
<ul>
<li><a href="main.jsp?page=day0408/subquery">subquery</a></li>
<li><a href="main.jsp?page=day0408/union">union</a></li>
<li><a href="main.jsp?page=day0408/join">join</a></li>
</ul>
</li>
<li><a href="#void">4일차</a>
<ul>
<li><a href="main.jsp?page=day0409/join_subquery">join+subquery</a></li>
<li><a href="main.jsp?page=day0409/dynamic_table">테이블명이 동적일 떄</a></li>
<li><a href="main.jsp?page=day0409/dynamic_if">dynamic if</a></li>
</ul>
</li>
<li><a href="#void">5일차</a>
<ul>
<li><a href="main.jsp?page=day0410/dynamic_choose">dynamic choose</a></li>
<li><a href="main.jsp?page=day0410/dynamic_foreach">dynamic foreach</a></li>
<li><a href="main.jsp?page=day0410/insert_procedure">insert procedure</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="main.jsp?page=day0410/car">동기방식 차량조회</a></li>
</ul>
</nav>
//MyBatisDAO1.java
package kr.co.sist.exam.dao;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import kr.co.sist.exam.domain.Car;
import kr.co.sist.exam.domain.Demp;
import kr.co.sist.exam.domain.DiaryList;
import kr.co.sist.exam.domain.Dynamicif;
import kr.co.sist.exam.domain.Emp;
import kr.co.sist.exam.domain.EmpJoin;
import kr.co.sist.exam.domain.Union;
import kr.co.sist.exam.domain.Zipcode;
import kr.co.sist.exam.vo.CarVO;
import kr.co.sist.exam.vo.DeptnoVO;
import kr.co.sist.exam.vo.DiaryListParamVO;
import kr.co.sist.exam.vo.EmpVO;
import kr.co.sist.exam.vo.TestProcVO;
import kr.co.sist.exam.vo.TnameVO;
public class MyBatisDAO1 {
public List<Emp> multiParam(EmpVO ev){
List<Emp> list=null;
//4.Handler얻기
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("multiParam",ev);
ss.close();
return list;
}//multiParam
public List<Emp> lessThan(int sal){
List<Emp> list=null;
//4.
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("lessThan",sal);
ss.close();
return list;
}//lessThan
public List<Emp> greaterThan(int sal){
List<Emp> list=null;
//4.
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("greaterThan",sal);
ss.close();
return list;
}//greaterThan
public List<Zipcode> like(String dong){
List<Zipcode> list=null;
//4.
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("like",dong);
ss.close();
return list;
}//like
public int diaryTotalCount() {
int cnt=0;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
cnt=ss.selectOne("diaryCnt");
return cnt;
}//diaryTotalCount
public List<DiaryList> subquery(DiaryListParamVO dlpvo){
List<DiaryList> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("subquery",dlpvo);
return list;
}//subquery
public List<Union> union(){
List<Union> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("union");
return list;
}//union
public List<EmpJoin> join(int mgr){
List<EmpJoin> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("join",mgr);
return list;
}//join
public List<Car> joinSubquery(){
List<Car> list=null;
SqlSession ss= MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("joinSubquery");
return list;
}//joinSubquery
public List<Demp> dynamicTable(TnameVO tvo){
List<Demp> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("dtable",tvo);
return list;
}//dynamicTable
public List<Dynamicif> dynamicIf(DeptnoVO dvo){
List<Dynamicif> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("dynamicif", dvo);
return list;
}//dynamicIf
public List<Dynamicif> dynamicChoose(DeptnoVO dvo){
List<Dynamicif> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("kr.co.sist.exam2.dynamicChoose", dvo);
return list;
}//dynamicIf
public List<Car> dynamicForeach(CarVO cv){
List<Car> list=null;
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
list=ss.selectList("kr.co.sist.exam2.dynamicForeach", cv);
return list;
}//dynamicForeach
public TestProcVO insertProc(TestProcVO tpvo) {
SqlSession ss=MyBatisDAO.getInstance().getSessionFactory().openSession();
System.out.println("-----"+tpvo.getMsg());
ss.selectOne("insertProcedure",tpvo);
System.out.println("-----"+tpvo.getMsg());
return tpvo;
}//insertProc
public static void main(String[] args) {
MyBatisDAO1 md=new MyBatisDAO1();
//md.multiParam(new EmpVO(30, "SALESMAN"));
//md.lessThan(3000);
//md.greaterThan(3000);
//md.like("상도동");
//md.diaryTotalCount();
//md.union();
//md.join(7698);
//md.joinSubquery();
//md.dynamicTable(new TnameVO("cp_emp2"));
//md.dynamicIf(13);//Error
//md.dynamicIf(new DeptnoVO(20));
//md.dynamicChoose(new DeptnoVO(100));
//List<String> list=null;
/*list=new ArrayList<String>();
list.add("현대");
list.add("기아");
list.add("BMW");
list.add("삼성");*/
//md.dynamicForeach(new CarVO(list));
TestProcVO tpvo=new TestProcVO(1111, 3000, 0, "김희철", "대리", "");
md.insertProc(tpvo);
}//main
}//class
//MyBatisService1.java
package kr.co.sist.exam.service;
import java.util.ArrayList;
import java.util.List;
import kr.co.sist.exam.dao.MyBatisDAO1;
import kr.co.sist.exam.domain.Car;
import kr.co.sist.exam.domain.Demp;
import kr.co.sist.exam.domain.DiaryList;
import kr.co.sist.exam.domain.Dynamicif;
import kr.co.sist.exam.domain.Emp;
import kr.co.sist.exam.domain.EmpJoin;
import kr.co.sist.exam.domain.Union;
import kr.co.sist.exam.domain.Zipcode;
import kr.co.sist.exam.vo.CarVO;
import kr.co.sist.exam.vo.DeptnoVO;
import kr.co.sist.exam.vo.DiaryListParamVO;
import kr.co.sist.exam.vo.EmpVO;
import kr.co.sist.exam.vo.TestProcVO;
import kr.co.sist.exam.vo.TnameVO;
public class MyBatisService1 {
public List<Emp> multiParam(EmpVO ev){
List<Emp> list=null;
MyBatisDAO1 mb_dao1=new MyBatisDAO1();
list=mb_dao1.multiParam(ev);
return list;
}//multiParam
public List<Emp> lessThan(int sal){
List<Emp> list=null;
if(sal<0) {
sal=-sal;
}//end if
MyBatisDAO1 md_dao1=new MyBatisDAO1();
list=md_dao1.lessThan(sal);
return list;
}//lessThan
public List<Emp> greaterThan(int sal){
List<Emp> list=null;
if(sal > 10000) {
sal=10000;
}//end if
MyBatisDAO1 md_dao1=new MyBatisDAO1();
list=md_dao1.greaterThan(sal);
return list;
}//greaterThan
public List<Zipcode> like(String dong){
List<Zipcode> list=null;
MyBatisDAO1 md_dao1=new MyBatisDAO1();
list=md_dao1.like(dong);
return list;
}//like
////////////////////게시판의 리스트를 얻어오자//////////////////////////////////////
//게시판의 총 갯수
public int totalCount() {
int cnt=0;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
cnt=mb_dao.diaryTotalCount();
return cnt;
}//totalCount
//한화면에 보여줄 게시물의 갯수
public int pageScale() {
return 10;
}//pageScale
//총 페이지 수
public int totalPage(int totalCount, int pageScale) {
int totalPage=0;
totalPage=(int)Math.ceil((double)totalCount/pageScale);
return totalPage;
}//totalPage
//시작번호
public int startNum(String currentPage, int pageScale) {
int startNum=1;
if(currentPage !=null) {
int tempPage=Integer.parseInt(currentPage);
startNum =tempPage*pageScale-pageScale+1;
}//end if
return startNum;
}//startNum
//끝번호
public int endNum(int startNum, int pageScale) {
return startNum+pageScale-1;
}//endNum
public List<DiaryList> diaryList(DiaryListParamVO dlp_vo){
List<DiaryList> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.subquery(dlp_vo);
return list;
}//diaryList
public List<Union> union(){
List<Union> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.union();
return list;
}//union
public List<EmpJoin> join(int mgr){
List<EmpJoin> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.join(mgr);
return list;
}//join
public List<Car> joinSubquery(){
List<Car> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.joinSubquery();
//카 옵션이 25자 이상이라면 24자까지 보여주고 "..."으로 처리
for(Car car:list) {
if(car.getCarOption().length()>25) {
car.setCarOption(car.getCarOption().substring(0, 24)+"...");
}//end if
}//end for
return list;
}//joinSubquery
public List<Demp> dynamicTable(TnameVO tv){
List<Demp> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.dynamicTable(tv);
return list;
}//dynamicTable
public List<Dynamicif> dynamicIf(DeptnoVO dv){
List<Dynamicif> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.dynamicIf(dv);
return list;
}//dynamicIf
public List<Dynamicif> dynamicChoose(DeptnoVO dv){
List<Dynamicif> list=null;
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.dynamicChoose(dv);
return list;
}//dynamicChoose
public List<Car> dynamicForeach(String[] makerArr){
List<Car> list=null;
//입력되는 배열의 값이 존재한다면 리스트에 추가
List<String> makerList=null;
if(makerArr!=null) {
makerList=new ArrayList<String>();
for(String temp: makerArr) {
makerList.add(temp);
}//end for
}//end if
CarVO cv=new CarVO(makerList);
MyBatisDAO1 mb_dao=new MyBatisDAO1();
list=mb_dao.dynamicForeach(cv);
return list;
}//dynamicForeach
public TestProcVO insertProcedure(TestProcVO tpvo) {
MyBatisDAO1 mb_dao=new MyBatisDAO1();
tpvo=mb_dao.insertProc(tpvo);
return tpvo;
}//insertProcedure
}//class
//exam_mapper2.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="kr.co.sist.exam2">
<!-- Diary List에 대한 Mapper 설정 -->
<resultMap type="kr.co.sist.exam.domain.DiaryList" id="dlResult">
<result column="num" property="num"/>
<result column="subject" property="subject"/>
<result column="writer" property="writer"/>
<result column="evt_date" property="evt_date"/>
<result column="w_date" property="w_date"/>
</resultMap>
<!-- Union에서 사용되는 resultMap -->
<resultMap type="kr.co.sist.exam.domain.Union" id="unionResult">
<result column="empno" property="empno"/>
<result column="sal" property="sal"/>
<result column="deptno" property="deptno"/>
<result column="ename" property="ename"/>
<result column="job" property="job"/>
<result column="hiredate" property="hiredate"/>
<result column="retired" property="retired"/>
</resultMap>
<!-- Join resultMap -->
<resultMap type="kr.co.sist.exam.domain.EmpJoin" id="empResult">
<result column="empno" property="empno"/>
<result column="ename" property="ename"/>
<result column="sal" property="sal"/>
<result column="hiredate" property="hiredate"/>
<result column="deptno" property="deptno"/>
<result column="dname" property="dname"/>
<result column="loc" property="loc"/>
</resultMap>
<!-- Car resultMap: 컬럼명과 setter method명이 다른경우에 주의하여(명시에 주의) 사용!!!=>같게 만드는게 훨씬 편하다. -->
<resultMap type="kr.co.sist.exam.domain.Car" id="carResult">
<result column="car_img" property="img"/>
<result column="maker" property="maker"/>
<result column="model" property="model"/>
<result column="car_year" property="carYear"/>
<result column="price" property="price"/>
<result column="car_option" property="carOption"/>
</resultMap>
<select id="diaryCnt" resultType="int">
select count(*) cnt from diary
</select>
<!-- subquery의 조회
diary 테이블에서 가장 마지막 글에서 부터 시작번호와 끝번호 사이의 글을 조회
-->
<select id="subquery" resultMap="dlResult" parameterType="kr.co.sist.exam.vo.DiaryListParamVO">
select r_num,num,subject,writer, e_year||'-'||e_month||'-'||e_day evt_date,
to_char(w_date,'yyyy-mm-dd hh24:mi') w_date
from (select num,subject,writer,e_year,e_month,e_day, w_date, row_number() over(order by w_date desc) r_num
from diary)
where r_num between #{startNum} and #{endNum}
</select>
<!-- union의 조회 : 퇴사한 사원과 재직중인 사원을 구분하여 조회-->
<select id="union" resultMap="unionResult" >
select empno,ename,job,sal,to_char(hiredate,'yyyy-mm-dd') hiredate,deptno,retired
from union_test
where retired='N'
union all
select empno,ename,job,sal,to_char(hiredate,'yyyy-mm-dd') hiredate,deptno,retired
from union_test
where retired='Y'
</select>
<!-- join의 조회 : 매니저 번호를 입력받아 매니저가 관리하는 사원들의 사원번호,사원명,연봉,입사일,부서번호,부서명,위치를 조회 -->
<select id="join" resultMap="empResult" parameterType="Integer">
select e.empno,e.ename,e.sal,e.hiredate,d.deptno,d.dname,d.loc
from emp e, dept d
where e.deptno=d.deptno and e.mgr=#{mgr}
</select>
<!-- join+subquery 의 조회
차량이미지, 제조사, 모델명, 연식, 가격, 옵션을 조회 하세요.
가장 마지막에 입력된 차량부터 10건만 조회.(무조건)
-->
<select id="joinSubquery" resultMap="carResult">
select car_img, maker,model, car_year, price, car_option, to_char(hiredate,'yyyy-mm-dd hh24:mi') hiredate
from (select car_img, maker,model, car_year, price, car_option, hiredate,
row_number() over(order by hiredate desc) r_num
from (select cmo.car_img,cma.maker,cmo.model,cmo.car_year,cmo.price,cmo.car_option,cmo.hiredate
from car_maker cma, car_model cmo
where cmo.model=cma.model))
where r_num between 1 and 10
</select>
<!-- 컬럼명 또는 테이블명이 동적일 때의 조회
//#은 사용할 수 없다
반드시 parameterType이 VO라고 하고 ${getter}를 사용해야 한다.
컬럼이 동일형으로 하나가 조회되는 경우에는 동적으로 사용 할 수 있으나
컬럼이 여러개가 조회되는 경우에는 동적으로 사용하기 어렵다.
테이블 명(EMP,CP_EMP2)이 외부에서 입력되고, 동일 컬럼이 조회되는 경우
조회 컬럼 : EMPNO,ENAME,HIREDATE,SAL
from 에 #이 들어가면 from ? 으로 인식하여 조회되지 않는다
-->
<resultMap type="kr.co.sist.exam.domain.Demp" id="dynamicTable">
<result column="empno" property="empno"/>
<result column="ename" property="ename"/>
<result column="hiredate" property="hiredate"/>
<result column="sal" property="sal"/>
</resultMap>
<select id="dtable" resultMap="dynamicTable" parameterType="kr.co.sist.exam.vo.TnameVO">
select empno,ename,hiredate,sal
from ${tname}
</select>
<!-- dynamic query -OGNL : where,if,choose,foreach 반드시 VO
if의 사용:
EMP테이블에서 부서번호가 10~19사이면 10번부서사원 검색하고,
20~29번 사이면 20번 부서 사원 검색하고, 30~39번 사이면 30번 부서 조회
그렇지 않으면 전체 사원 조회
-->
<resultMap type="kr.co.sist.exam.domain.Dynamicif" id="ifResult">
<result column="empno" property="empno"/>
<result column="deptno" property="deptno"/>
<result column="ename" property="ename"/>
<result column="job" property="job"/>
<result column="hiredate" property="hiredate"/>
</resultMap>
<select id="dynamicif" resultMap="ifResult" parameterType="kr.co.sist.exam.vo.DeptnoVO">
select empno, ename, job, deptno, to_char(hiredate,'yyyy-mm-dd') hiredate
from emp
<where>
<if test="deptno >= 10 and deptno < 20">
deptno=10
</if>
<if test="deptno >= 20 and deptno < 30">
deptno=20
</if>
<if test="deptno >= 30 and deptno < 40">
deptno=30
</if>
</where>
</select>
<!-- 숙제- 제조국|제조사|모델명 조회 -모델1 -->
<!-- dynamic choose :
EMP테이블에서 부서번호가 10~19사이면 10번부서사원 검색하고,
20~29번 사이면 20번 부서 사원 검색하고, 그렇지 않으면 30번 부서 조회
-->
<select id="dynamicChoose" resultMap="ifResult" parameterType="kr.co.sist.exam.vo.DeptnoVO">
<!-- test??parameterType??에 들어가는건 반드시 getter -->
select empno, ename, job, deptno, to_char(hiredate,'yyyy-mm-dd') hiredate
from emp
<where>
<choose>
<when test="deptno >=10 and deptno < 20">
deptno=10
</when>
<when test="deptno >=20 and deptno < 30">
deptno=20
<!-- deptno in(20,30)도 가능 -->
</when>
<otherwise>
deptno=30
</otherwise>
</choose>
</where>
</select>
<!-- dynamic forEach :
제조가사 조회되면 제조사별 차량을 조회
-->
<select id="dynamicForeach" resultMap="carResult" parameterType="kr.co.sist.exam.vo.CarVO">
select car_img, maker,model, car_year, price, car_option, to_char(hiredate,'yyyy-mm-dd hh24:mi') hiredate
from (select car_img, maker,model, car_year, price, car_option, hiredate,
row_number() over(order by hiredate desc) r_num
from (select cmo.car_img,cma.maker,cmo.model,cmo.car_year,cmo.price,cmo.car_option,cmo.hiredate
from car_maker cma, car_model cmo
where cmo.model=cma.model
<if test="makerList != null">
and cma.maker in
<foreach collection="makerList" item="maker" open="("
close=")" separator=",">
#{maker}
</foreach>
</if>
))
</select>
<!-- procedure 호출 -->
<!-- insert : insert_test_proc(10000,'공선의',3000,'사원',:m,:c)
프로시저를 호출할 때 in parameter 값과 out parameter 값은 모두 VO 에 함께 들어가 있어야 한다.
in parameter => #{getter, mode=IN}
단일 데이터 형일 때
out parameter => #{setter, mode=OUT, jdbcType= , javaType= }
-->
<select id="insertProcedure" parameterType="kr.co.sist.exam.vo.TestProcVO"
statementType="CALLABLE">
{
call insert_test_proc(#{empno, mode=IN}, #{ename, mode=IN}, #{sal, mode=IN}, #{job, mode=IN},
#{msg, mode=OUT, jdbcType=VARCHAR, javaType=String},
#{rowCnt, mode=OUT, jdbcType=NUMERIC, javaType=int}
)
}
</select>
<!-- cursor -->
</mapper>
반응형
'국비지원학원 > MyBatis' 카테고리의 다른 글
| 116일차-Procedure사용 (0) | 2019.04.20 |
|---|---|
| 114일차-Dynamic Query조회 (0) | 2019.04.19 |
| 113일차-지도 마무리/조회 마무리 (0) | 2019.04.19 |
| 112일차-MyBatis (0) | 2019.04.17 |
| 111일차-MyBatis (0) | 2019.04.17 |