Spring 06 (23.04.13)

Jane·2023년 4월 13일

IT 수업 정리

목록 보기

104/124

1. 게시판 페이징

1-1. DB 글 추가하기

DB : 페이징 쿼리 (MySQL, Oracle 12 이상 - limit // Oracle 11g 이하는 복잡해진다)
https://luckyguystory.tistory.com/92

SELECT * FROM (
     SELECT ROWNUM AS RNUM, A.* FROM (
          {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
    ) A WHERE ROWNUM <= {범위까지}
) WHERE RNUM > {범위부터};

SELECT * FROM (
	SELECT ROWNUM AS RNUM, A.* FROM (
		SELECT *
			FROM mvc_board 
		order by bGroup desc, bStep asc   
	) A WHERE ROWNUM <= #{pageNum} * #{amount}
) WHERE RNUM > (#{pageNum}-1) * #{amount}

테스트 글을 1000개 작성하기

begin
  for i in 1..1000 loop
   insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, 'test' , '테스트', '테스트', 0, mvc_board_seq.currval, 0, 0);
  end loop;
end;

commit;

1-2. 글 페이징하기

select * from mvc_board order by bid;
bid 번호로 접근하기?
- select * from mvc_board where bid > 0 and bid<32 order by bid ;
- 글은 지워지므로, bid 순서로 자르기가 쉽지 않다.
rownum을 사용하기 (순서 매기기, 번호 붙여주기)
- select rownum, emp.* from emp;
- select rownum, emp.* from emp where rownum<=10;
- select * from mvc_board where rownum <= 10;
- select rownum rn, bid, bname, btitle from mvc_board;
- select rownum rn, bid, bname, btitle from mvc_board where rownum <= 10; (정상 출력)
- select rownum rn, bid, bname, btitle from mvc_board where rownum > 10 and rownum <=20; (출력 안됨)

먼저 오라클 쿼리의 순서를 익혀야 한다

FROM/WHERE 절이 먼저 처리됩니다.

ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.

SELECT가 적용됩니다.

GROUP BY 조건이 적용됩니다.

HAVING이 적용됩니다.

ORDER BY 조건이 적용됩니다.

이유는?
rownum은 당연히 1이 할당 될 것이다.
select 하기전 아래가 먼저 실행한다.
rownum = 1 > 10
-> 여기서 부터 조건에 맞지 않으므로 아무것도 뿌리지 못한다.
한마디로 where 절부터 실행되므로 여기에서 무효화가 되는 것이다.
이러한 이유로 SQL 작성시 ROWNUM 조건은 반드시 1이 포함되어야 한다.

서브 쿼리를 활용하여 출력한다.

SELECT * FROM (
	SELECT ROWNUM AS RNUM, A.* FROM (
		select rownum rn, bid, bname, btitle from mvc_board
	) A where rownum <= 20
) WHERE RNUM > 10;

1-3. 페이징 코드 작성

Criteria.java

package edu.global.ex.page;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@ToString
@Getter
@Setter
public class Criteria {
	// 페이징 처리를 위해선 페이지 번호와 한 페이지당 몇 개의 데이터를 보여줄 것인지 결정되어야만 함.
	private int pageNum; // 페이지 번호
	private int amount; // 한 페이지당 몇 개의 데이터를 보여줄 것인가?

	public Criteria() {
		this(1, 10);// 기본값 1페이지 10개로 지정
	}

	public Criteria(int pageNum, int amount) {
		this.pageNum = pageNum;
		this.amount = amount;
	}

}

PageVO.java

package edu.global.ex.page;

import org.springframework.web.util.UriComponents;
import org.springframework.web.util.UriComponentsBuilder;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@ToString
@Getter
@Setter
public class PageVO {
	// 페이징 처리할 때 필요한 정보들
	private int startPage;// 화면에 보여지는 페이지 시작번호
	private int endPage;// 화면에 보여지는 끝번호
	private boolean prev, next;// 이전과 다음으로 이동 가능한 링크 표시

	private int total;// 전체 게시판 글 수
	private Criteria cri;

	public PageVO(Criteria cri, int total) {
		this.cri = cri;
		this.total = total; // 전체 데이타 수

		this.endPage = (int) (Math.ceil(cri.getPageNum() / 10.0)) * 10;
		this.startPage = this.endPage - 9;

		// Total을 통한 endPage의 재계산
		// 10개씩 보여주는 경우, 전체 데이터 수가 80개라고 가정하면 끝번호는 10이 아닌 8이 됨
		int realEnd = (int) (Math.ceil((total * 1.0) / cri.getAmount()));

		if (realEnd <= this.endPage) {
			this.endPage = realEnd;
		}

		// 시작번호가 1보다 큰경우 존재
		this.prev = this.startPage > 1; // <<
		// realEnd가 끝번호(endPage)보다 큰 경우에만 존재
		this.next = this.endPage < realEnd; // >>

	}

	//
	public String makeQuery(int page) {
		UriComponents uriComponentsBuilder = UriComponentsBuilder.newInstance().queryParam("pageNum", page) // pageNum = 3
				.queryParam("amount", cri.getAmount()) // pageNum=3&amount=10
				.build(); // ?pageNum=3&amount=10
		return uriComponentsBuilder.toUriString(); // ?pageNum=3&amount=10 리턴
	}

}

ceil : 올림함수
Math.ceil(.95); // 1
Math.ceil(4); // 4
Math.ceil(7.004); // 8
Math.ceil(-0.95); // -0
Math.ceil(-4); // -4
Math.ceil(-7.004); // -7

endPage는 현재의 페이지 번호를 기준으로 계산함.
현재 페이지가 3일 경우 : Math.ceil(3/10) x 10 = 10
현재 페이지가 1일 경우 : Math.ceil(1/10) x 10 = 10
현재 페이지가 10일 경우 : Math.ceil(10/10) x 10 = 10
현재 페이지가 11일 경우 : Math.ceil(11/10) x 10 = 20 (1.1을 올림함)
현재 페이지가 20일 경우 : Math.ceil(20/10) x 10 = 20
현재 페이지가 21일 경우 : Math.ceil(21/10) x 10 = 30

BoardController.java (list2 추가)

	@GetMapping("/list2")
	public String list2(Criteria cri, Model model) {
		log.info("list2() ..");
		log.info("list2() Criteria " + cri);

		model.addAttribute("boards", boardService.getList(cri));

		int total = boardService.getTotal();
		log.info("total" + total);

		model.addAttribute("pageMaker", new PageVO(cri, total));

		return "/board/list2";
	}

BoardService.java

package edu.global.ex.service;

import java.util.List;

import edu.global.ex.page.Criteria;
import edu.global.ex.vo.BoardVO;

public interface BoardService {
	public List<BoardVO> getList();

	public BoardVO get(int bid);

	public int modify(BoardVO boardVO);

	public int delete(int bid);

	public int register(BoardVO boardVO);

	public void registerReply(BoardVO boardVO);

	public int getTotal();

	public List<BoardVO> getList(Criteria cri);

}

BoardServiceImpl.java (함수 추가)

	@Override
	public int getTotal() {
		log.info("getTotal()..");
		return mapper.getTotalCount();
	}

	@Override
	public List<BoardVO> getList(Criteria cri) {
		log.info("getList(cri)..");
		return mapper.getListWithPaging(cri);
	}

BoardMapper.java

package edu.global.ex.mapper;

import java.util.List;
import org.apache.ibatis.annotations.Mapper;

import edu.global.ex.page.Criteria;
import edu.global.ex.vo.BoardVO;

@Mapper
public interface BoardMapper {
	public List<BoardVO> getList();

	public BoardVO read(int bid);

	public int update(BoardVO board);

	public int remove(int bid);

	public int insert(BoardVO board);

	public void updateShape(BoardVO board);

	public void insertReply(BoardVO board);

	public int getTotalCount();

	public List<BoardVO> getListWithPaging(Criteria cri);
}

list2.jsp 작성

2. EMP 페이징하기

page 패키지 복사/붙여넣기 하고 진행

EmpController.java

package edu.global.ex.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import edu.global.ex.page.Criteria;
import edu.global.ex.page.PageVO;
import edu.global.ex.service.EmpService;
import edu.global.ex.vo.EmpVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Controller
@RequestMapping("/emp/*")
@RequiredArgsConstructor
public class EmpController {

	@Autowired
	private EmpService empService;

	@GetMapping("/list")
	public String view_list(Model model) {
		log.info("view_list()...");

		model.addAttribute("emps", empService.getList());
		return "/emp/emp_list";
	}

	@GetMapping("/emp_view")
	public String emp_view(EmpVO empVO, Model model) {
		log.info("emp_view()...");

		int empno = empVO.getEmpno();

		empVO = empService.get(empno);

		model.addAttribute("emp_view", empVO);

		return "/emp/emp_view";
	}
	
	@GetMapping("/list2")
	public String list2(Criteria cri, Model model) {
		log.info("view_list2()...");

		model.addAttribute("emps", empService.getList(cri));
		
		int total = empService.getTotal();
		log.info("total" + total);

		model.addAttribute("pageMaker", new PageVO(cri, total));
		return "/emp/list2";
	}

}

EmpService.java

package edu.global.ex.service;

import java.util.List;

import edu.global.ex.page.Criteria;
import edu.global.ex.vo.EmpVO;

public interface EmpService {
	public List<EmpVO> getList();
	public EmpVO get(int bno);
	public List<EmpVO> getList(Criteria cri);
	public int getTotal();
}

EmpServiceImpl.java (함수 오버라이드하여 추가)

	@Override
	public List<EmpVO> getList(Criteria cri) {
		log.info("getList(cri)..");
		return mapper.getListWithPaging(cri);
	}

	@Override
	public int getTotal() {
		log.info("getTotal()..");
		return mapper.getTotalCount();
	}

EmpMapper.java

package edu.global.ex.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import edu.global.ex.page.Criteria;
import edu.global.ex.vo.EmpVO;

@Mapper
public interface EmpMapper {
	public List<EmpVO> getList();

	public EmpVO read(int bid);

	public List<EmpVO> getListWithPaging(Criteria cri);

	public int getTotalCount();
}

EmpMapper.xml (쿼리 삽입한 태그 추가)

	<select id="getTotalCount" resultType="int">
     <![CDATA[
      select count(*) from emp
   ]]>
	</select>

	<select id="getListWithPaging"
		resultType="edu.global.ex.vo.EmpVO">
      <![CDATA[
        SELECT * FROM (
              SELECT ROWNUM AS RNUM, A.* FROM (
                       SELECT
                             *
                         FROM
                             emp
                           
             ) A WHERE ROWNUM <= #{pageNum} * #{amount}
         ) WHERE RNUM > (#{pageNum}-1) * #{amount}
      ]]>
	</select>

3. MyBatis를 이용한 조인 처리

3-1. SQL로 관계도 확인

https://kindloveit.tistory.com/21

select * from emp, dept where emp.deptno = dept.deptno;

3-2. Dept 만들기

application.properties

#server port number
server.port = 8282

#datasource (oracle)
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=scott
spring.datasource.password=tiger


#xml location
mybatis.mapper-locations=classpath:mappers/**/*.xml
mybatis.type-aliases-package=edu.global.ex

#### jsp 
spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp

EmpDeptVO.java

package edu.global.ex.vo;

import java.util.Date;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class EmpDeptVO {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private int sal;
	private int comm;
	private int deptno;
	private String dname;
	private String loc;
}

DeptMapper.java

package edu.global.ex.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import edu.global.ex.vo.EmpDeptVO;

@Mapper
public interface DeptMapper {
	public List<EmpDeptVO> getEmpDeptOneVOList();
}

DeptMapper.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="edu.global.ex.mapper.DeptMapper">
	<select id="getEmpDeptOneVOList" resultType="EmpDeptVO">
	<![CDATA[
      select * from emp,dept where emp.deptno = dept.deptno
   ]]>

	</select>

</mapper>

EmpService.java

package edu.global.ex.service;

import java.util.List;

import edu.global.ex.page.Criteria;
import edu.global.ex.vo.EmpDeptVO;
import edu.global.ex.vo.EmpVO;

public interface EmpService {
	public List<EmpVO> getList();
	public EmpVO get(int bno);
	public List<EmpVO> getList(Criteria cri);
	public int getTotal();
	public List<EmpDeptVO> getEmpDeptOneVOList();
}

EmpServiceImpl.java

	@Autowired
	private final DeptMapper deptMapper;

	@Override
	public List<EmpDeptVO> getEmpDeptOneVOList() {
		
		return deptMapper.getEmpDeptOneVOList();
	}

EmpController.java

	@GetMapping("/dept1")
	public String dept1(Model model) {
		log.info("view_dept1()...");
		System.out.println(empService.getEmpDeptOneVOList());
		
		return "/emp/list2";
	}

@ToString
[EmpDeptVO(empno=7839, ename=KING, job=PRESIDENT, mgr=0, hiredate=Tue Nov 17 00:00:00 KST 1981, sal=5000, comm=0, deptno=10, dname=ACCOUNTING, loc=NEW YORK),
EmpDeptVO(empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=Tue Jun 09 00:00:00 KST 1981, sal=2450, comm=0, deptno=10, dname=ACCOUNTING, loc=NEW YORK),
EmpDeptVO(empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=Sat Jan 23 00:00:00 KST 1982, sal=1300, comm=0, deptno=10, dname=ACCOUNTING, loc=NEW YORK),
EmpDeptVO(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 KST 1981, sal=3000, comm=0, deptno=20, dname=RESEARCH, loc=DALLAS),
EmpDeptVO(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 KST 1980, sal=800, comm=0, deptno=20, dname=RESEARCH, loc=DALLAS),
EmpDeptVO(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 KST 1981, sal=2975, comm=0, deptno=20, dname=RESEARCH, loc=DALLAS),
EmpDeptVO(empno=7900, ename=JAMES, job=CLERK, mgr=7698, hiredate=Thu Dec 03 00:00:00 KST 1981, sal=950, comm=0, deptno=30, dname=SALES, loc=CHICAGO),
EmpDeptVO(empno=7844, ename=TURNER, job=SALESMAN, mgr=7698, hiredate=Tue Sep 08 00:00:00 KST 1981, sal=1500, comm=0, deptno=30, dname=SALES, loc=CHICAGO),
EmpDeptVO(empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=Mon Sep 28 00:00:00 KST 1981, sal=1250, comm=1400, deptno=30, dname=SALES, loc=CHICAGO),
EmpDeptVO(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 KST 1981, sal=1250, comm=500, deptno=30, dname=SALES, loc=CHICAGO),
EmpDeptVO(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 KST 1981, sal=1600, comm=300, deptno=30, dname=SALES, loc=CHICAGO),
EmpDeptVO(empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=Fri May 01 00:00:00 KST 1981, sal=2850, comm=0, deptno=30, dname=SALES, loc=CHICAGO)]

3-3. 1:N 처리하기

DEPT 1개에 EMP 여러 개 (1:N 관계)

DeptVO.java

package edu.global.ex.vo;

import java.util.List;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class DeptVO {
	private int deptno;
	private String dname;
	private String loc;
	
	List<EmpVO> empList;
}