< 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
'SpringBoot' 카테고리의 다른 글
윈도우 카프카 - Kafka 를 Springboot 에서 간단히 써보자 (1) | 2023.12.12 |
---|---|
MySQL + Springboot JPA + logback (1) | 2023.12.12 |
SpringBoot - SSR , SCR (개념 간략) (0) | 2023.10.04 |
SpringBoot - Controller 에서 자주 사용되는 어노테이션 (0) | 2023.10.04 |
SpringBoot - 시작 (0) | 2023.10.04 |