SpringBoot - MyBatis (RestController)

< model package >

common 패키지

https://tinylittlelife.tistory.com/219

 

vo 패키지

Emp (EmpVo)

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Emp  extends BaseTimeEntity {
    private Long eno;
    private String ename;
    private String job;
    private Long manager;
    private String hiredate;
    private Long salary;
    private Long commission;
    private Long dno;
}

 

< dao package >

EmpDao

@Mapper
public interface EmpDao {

    /**
     * 전체 조회 (Like 조회)
     *
     * @param ename
     * @param pageReq
     * @return
     */
    public List<Emp> findEmpByEname(@Param("ename") String ename, PageReq pageReq);

    /**
     * ename의 개수 (Like 씀)
     *
     * @param ename
     * @return
     */
    long countByEname(String ename);

    /**
     * 상세 조회
     */
    Optional<Emp> findEmpById(long eno);

    /**
     * 저장 요청
     */
    int insert(Emp emp);

    /**
     * 수정 요청
     */
    int update(Emp emp);

    /**
     * 삭제 요청시 먼저 있는지 없는지 판단
     */
    long existEmpById(long eno);

    /**
     * 삭제 요청
     */
    long deleteById(long eno);

    /**
     * 다이나믹 SQL
     */
    List<Emp> findByEmpAllDynamic(
            @Param("ename") String ename,
            @Param("job") String job,
            @Param("manager") long manager,
            @Param("hiredate") String hiredate,
            @Param("salary") long salary,
            @Param("commission") long commission,
            @Param("dno") long dno,
            PageReq pageReq
    );

    /**
     * 페이징 처리를 위한 다이나믹 SQL
     */
    long countByEmpAllDynamic(@Param("ename") String ename,
                              @Param("job") String job,
                              @Param("manager") long manager,
                              @Param("hiredate") String hiredate,
                              @Param("salary") long salary,
                              @Param("commission") long commission,
                              @Param("dno") long dno);
}

 

< resources/mappers/.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="com.example.mybatisexam.dao.EmpDao">
    <select id="findEmpByEname" parameterType="PageReq" resultType="Emp">
        SELECT ENO , ENAME , JOB , MANAGER ,HIREDATE, SALARY , COMMISSION , DNO , INSERT_TIME, UPDATE_TIME
        FROM TB_EMP
        WHERE ENAME LIKE '%'|| #{ename} ||'%'
--         OFFSET 건너뛰어라 ROWS FETCH FIRST 행만큼만 띄워라 ROWS ONLY
        OFFSET #{pageReq.page} * #{pageReq.size} ROWS FETCH FIRST #{pageReq.size} ROWS ONLY
    </select>

    <select id="countByEname" parameterType="String" resultType="long">
        SELECT COUNT(eno) FROM TB_EMP
        WHERE ENAME LIKE '%'|| #{ename} ||'%'
    </select>

    <select id="findEmpById" parameterType="long" resultType="Emp">
        SELECT ENO , ENAME , JOB , MANAGER ,HIREDATE, SALARY , COMMISSION , DNO <include refid="common.selectCol"></include>
        FROM TB_EMP
        WHERE ENO = #{eno}
    </select>

    <insert id="insert" parameterType="Emp">
        INSERT INTO TB_EMP (ENO,
                            ENAME,
                            JOB,
                            MANAGER,
                            HIREDATE,
                            SALARY,
                            COMMISSION,
                            DNO,
                            INSERT_TIME)
        VALUES (SQ_EMP.NEXTVAL,
                #{ename},
                #{job},
                #{manager},
                #{hiredate},
                #{salary},
                #{commission},
                #{dno},
                #{insertTime})
    </insert>
    <update id="update" parameterType="Emp">
        UPDATE TB_EMP
        SET ENAME = #{ename}, JOB =#{job}, MANAGER = #{manager}, HIREDATE = #{hiredate}, SALARY = #{salary}, COMMISSION = #{commission}, DNO = #{dno}, UPDATE_TIME = #{updateTime}
        WHERE ENO = #{eno}
    </update>

<!--  삭제 요청 전에 있는지 없는지 확인  -->
    <select id="existEmpById" parameterType="long" resultType="long">
        SELECT COUNT(ENO) FROM TB_EMP
        WHERE ENO = #{eno}
    </select>

<!--  삭제 요청  -->
    <delete id="deleteById" parameterType="long">
        DELETE FROM TB_EMP
        WHERE ENO = #{eno}
    </delete>

<!--  다이나믹 sql  -->
    <select id="findByEmpAllDynamic" parameterType="PageReq" resultType="Emp" >
        SELECT ENO , ENAME , JOB , MANAGER ,HIREDATE, SALARY , COMMISSION , DNO , INSERT_TIME, UPDATE_TIME
        FROM TB_EMP
        WHERE 1 = 1
        <if test="ename != '' ">
            AND ENAME LIKE '%' || #{ename} ||'%'
        </if>
        <if test="job != '' ">
            AND JOB LIKE '%' || #{job} ||'%'
        </if>
        <if test="manager != -1 ">
            AND MANAGER LIKE '%' || #{manager} ||'%'
        </if>
        <if test="hiredate != '' ">
            AND HIREDATE LIKE '%' || #{hiredate} ||'%'
        </if>
        <if test="salary != -1 ">
            AND SALARY LIKE '%' || #{salary} ||'%'
        </if>
        <if test="commission != -1 ">
            AND COMMISSION LIKE '%' || #{commission} ||'%'
        </if>
        <if test="dno != -1 ">
            AND DNO LIKE '%' || #{dno} ||'%'
        </if>
    </select>

<!-- 페이징 처리를 위한 다이나믹 sql -->
    <select id="countByEmpAllDynamic" resultType="long">
        SELECT COUNT(ENO) FROM TB_EMP
        WHERE 1 = 1
        <if test="ename != '' ">
            AND ENAME LIKE '%' || #{ename} ||'%'
        </if>
        <if test="job != '' ">
            AND JOB LIKE '%' || #{job} ||'%'
        </if>
        <if test="manager != -1 ">
            AND MANAGER LIKE '%' || #{manager} ||'%'
        </if>
        <if test="hiredate != '' ">
            AND HIREDATE LIKE '%' || #{hiredate} ||'%'
        </if>
        <if test="salary != -1 ">
            AND SALARY LIKE '%' || #{salary} ||'%'
        </if>
        <if test="commission != -1 ">
            AND COMMISSION LIKE '%' || #{commission} ||'%'
        </if>
        <if test="dno != -1 ">
            AND DNO LIKE '%' || #{dno} ||'%'
        </if>
    </select>
</mapper>

 

< service package >

@Service
@Slf4j
public class EmpService {

    @Autowired
    EmpDao empDao;

    public PageRes<Emp> findEmpAll(String ename , PageReq pageReq) {
        List<Emp> list = empDao.findEmpByEname(ename, pageReq);
        long totalCount = empDao.countByEname(ename);
        PageRes<Emp> pageRes = new PageRes<>(list, pageReq.getPage() , totalCount , pageReq.getSize());
        return pageRes;
    }

    /**
     * 상세 조회
     */
    public Optional<Emp> findEmpById(long eno) {
        Optional<Emp> optionalEmp = empDao.findEmpById(eno);
        return optionalEmp;
    }

    /**
     * 저장 요청
     */
    public int save(Emp emp) {
        int queryResult = -1; // 저장된 건수를 위한 변수
        try {
//            TODO : 기본키 (dno) 가 없으면 insert
            if(emp.getEno() == null) {
                queryResult = empDao.insert(emp);
            } else {
//            TODO : 기본키 (dno) 가 있으면 update
                queryResult = empDao.update(emp);
            }
            queryResult = empDao.insert(emp);
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
        return queryResult;
    }


        /**
         * 삭제 요청
         */
        public boolean deleteById(long eno) {
            try {
                long existEmp = empDao.existEmpById(eno);
                if(existEmp > 0) {
                    empDao.deleteById(eno);
                    return true;
                }
            } catch (Exception e) {
                log.debug(e.getMessage());
            }
            return false;
        }

    /**
     * 다이나믹 SQL 문
     */
    public PageRes<Emp> findEmpAllDynamic(String ename , String job , long manager , String hiredate , long salary , long commission , long dno , PageReq pageReq) {
        //        TODO : Dynamic SQL 조회 (like 됨)
        List<Emp> list = empDao.findByEmpAllDynamic(ename,job,manager,hiredate,salary,commission,dno,pageReq);
//        TODO: 페이징 처리 로직
//         1) 총 테이블 개수
        long totalCount = empDao.countByEmpAllDynamic(ename,job,manager,hiredate,salary,commission,dno);
//        TODO : 생성자 페이지 결과 객체 (PageRes)
        PageRes pageRes = new PageRes(
                list,              // 검색 결과(부서) 배열
                pageReq.getPage(), // 현재 페이지 번호
                totalCount,       // 총 테이블 건수
                pageReq.getSize());// 1페이지당 개수
        return pageRes;
    }
}

 

< controller package >

EmpController < 1 >

RestController

@RestController
@Slf4j
@RequestMapping("/exam02")
public class Emp02Controller {
    @Autowired
    EmpService empService;
//    TODO : 연습 1) 부서 클래스를 참고하여
//                사원에 전체조회(페이징처리) 와 상세조회 기능을 추가하세요
//    Emp02Controller
//    url : 전체조회 : /emp
//          상세조회 : /emp/{eno}

    /**
     * 전체 조회 + Like 검색
     */
    @GetMapping("/emp")
    public ResponseEntity<Object> findEmpAll(@RequestParam(defaultValue = "") String ename,
                                             @RequestParam(defaultValue = "0") int page ,
                                             @RequestParam(defaultValue = "3") int size) {
        try {
            PageReq pageReq = new PageReq(page,size);
            PageRes<Emp> pageRes = empService.findEmpAll(ename,pageReq);
            if(pageRes.isEmpty() == false) {
                return new ResponseEntity<>(pageRes, HttpStatus.ACCEPTED);
            } else {
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /**
     * 상세 조회
     */
    @GetMapping("/emp/{eno}")
    public ResponseEntity<Object> findEmpById(@PathVariable long eno) {
        try {
            Optional<Emp> optionalEmp = empService.findEmpById(eno);
            if(optionalEmp.isEmpty() == false) {
                return new ResponseEntity<>(optionalEmp.get(), HttpStatus.ACCEPTED);
            } else {
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /**
     *  저장하기
     */
    @PostMapping("/emp")
    public ResponseEntity<Object> saveEmp(@RequestBody Emp emp) {
        try {
        int count = empService.save(emp);
        return new ResponseEntity<>(count, HttpStatus.OK);
        } catch (Exception e) {
         return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /**
     * 수정 하기
     */
    @PutMapping("/emp/{eno}")
    public ResponseEntity<Object> updateEmp(@RequestBody Emp emp , @PathVariable long eno) {
        try {
            int count = empService.save(emp);
            return new ResponseEntity<>(count, HttpStatus.OK);
        } catch (Exception e) {
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /**
     * 삭제 하기
     */
    @DeleteMapping("/emp/deletion/{eno}")
    public ResponseEntity<Object> deleteEmp(@PathVariable long eno) {
        try {
            boolean result = empService.deleteById(eno);
            if(result == true) {
                return new ResponseEntity<>(HttpStatus.ACCEPTED);
            } else {
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

//    다이나믹 sql
    @GetMapping("/emp/dynamic")
    public ResponseEntity<Object> findAllEmpDynamic (
        @RequestParam(defaultValue = "") String ename ,
         @RequestParam(defaultValue = "") String job,
        @RequestParam(defaultValue = "-1") long manager,
        @RequestParam(defaultValue = "") String hiredate ,
        @RequestParam(defaultValue = "-1") long salary ,
        @RequestParam(defaultValue = "-1") long commission,
        @RequestParam(defaultValue = "-1") long dno ,
        @RequestParam(defaultValue = "0") int page ,
        @RequestParam(defaultValue = "3") int size
    ) {
        try {
            PageReq pageReq = new PageReq(page, size);

//      todo: dynamic 조회 함수 호출
            PageRes<Emp> pageRes
                    = empService.findEmpAllDynamic(ename, job, manager,hiredate,salary,commission,dno,
                    pageReq);

//      todo: 정보전달( 부서배열, 페이징정보 )
//        자료구조 (키, 값) : Map
            Map<String, Object> response = new HashMap<>();

            response.put("emp", pageRes.getContent()); // 사원배열
            response.put("currentPage", pageRes.getNumber()); // 현재 페이지 번호
            response.put("totalItems", pageRes.getTotalElements()); // 전체 테이블 건수
            response.put("totalPages", pageRes.getTotalPages()); // 전체 페이지 개수

            if(pageRes.isEmpty() == false) {
//                todo: 조회 성공
                return new ResponseEntity<>(response, HttpStatus.OK);
            } else {
//                todo: 0건 조회 (실패)
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);

        }
    }
} // end of class