Mybatis②

1. 조인

1)  조인 테이블 모델 

: 두 테이블 값 모두 변수로 넣어준다. (외래키는 1개만)

package com.oracle.oBootMybatis01.model;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class EmpDept {

	//Emp 용도
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private String hiredate;
	private int sal;
	private int comm;
	private int deptno;			//외래키(DEPT 같이 사용)
	
	//Dept 용도
	private String dname;
	private String loc;
}

2) Controller -> Service -> Dao -> xml 

//Controller
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		EmpDept empDept = null;
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		return "listEmpDept";
	}
//Service
	@Override
	public List<EmpDept> listEmpDept() {
		List<EmpDept> listEmpDept = ed.listEmpDept();
		return listEmpDept;
	}

//Dao
    @Override
	public List<EmpDept> listEmpDept() {
		List<EmpDept> listEmpDept = null;
		try {
			listEmpDept = session.selectList("mjListEmpDept");
		} catch (Exception e) {
			e.getMessage();
		}
		return listEmpDept;
	}

<select id="mjListEmpDept" resultType="EmpDept">
		select 	 e.empno, e.ename, e.job, d.deptno, d.loc
		from 	 emp e, dept d
		where  	 e.deptno = d.deptno
		order by empno
</select>

 

2. 검색

<form action="listKeyword">
    <input type="hidden" name="currentPage" value="${pg.currentPage }">
    <select name="search">
        <option value="s_all">전체조회</option>
        <option value="s_job">업무조회</option>
        <option value="s_ename">이름조회</option>
    </select>

    <input type="text" 	name="keyword" 	placeholder="keyword를 입력하세요">
    <button type="submit">keyword검색</button>
</form>

* 검색조건 넣기 위해 Emp model에 search/keyword 넣어둠(테이블에 넣는 것 x. 조회에만 사용)

 

- Controller : 검색어에 해당 되는 게시글 수 구하기/게시글 List 구하기

@RequestMapping(value = "listKeyword")
	public String listKeyword(Emp emp, String currentPage, Model model) {
		
		int total = es.totalKeyword(emp);
		
		Paging pg = new Paging(total, currentPage);
		emp.setStart(pg.getStart());
		emp.setEnd(pg.getEnd());
		List<Emp> emps = es.listEmpKeyword(emp);
		model.addAttribute("listEmp", emps);
		model.addAttribute("pg", pg);
		model.addAttribute("total", total);
		model.addAttribute("keyword", emp.getKeyword());
		return "listKeyword";
	}

 

-  % keyword % : 키워드가 들어간 값 모두 조회 

<select id="mjEmpTotalKeyword" parameterType="Emp" resultType="int">
		select count(*) from emp
			<choose>
				<when test="search == 's_all'">
					where ( ename like '%' || #{keyword} || '%'
					   or     job like '%' || #{keyword} || '%'
					   )
				</when>
				<when test="search == 's_job'">
					where job like '%' || #{keyword} || '%'
				</when>
				<when test="search == 's_ename'">
 					where ename like '%' || #{keyword} || '%'
 				</when>
 				<otherwise>
 					where job like '%'
 				</otherwise>	
			</choose>
	</select>
	
	<select id="mjListEmpKeyword" parameterType="Emp" resultType="Emp">
		select *
		from   (
				select rownum rn, a.*
				from   (select * from emp
						<choose>
			 				<when test="search == 's_all'">
			 					where ( ename like '%' || #{keyword} || '%'
			 					   or	  job like '%' || #{keyword} || '%'
			 						)
			 				</when>
			 				<when test="search == 's_job'">
			 					where job like '%' || #{keyword} || '%'
			 				</when>	
			 				<when test="search == 's_ename'">
			 					where ename like '%' || #{keyword} || '%'
			 				</when>	
			 				<otherwise>
			 					where (	 	ename like '%'
			 							or	job   like '%' )
			 				</otherwise>
			 			</choose>
						order by empno) a
					
				)
		where  rn between #{start} and #{end}
	</select>