3. 영속 / 비지니스 계층의 CRUD 구현

• 테이블의 칼럼 구조를 반영하는 VO 클래스 작성
• Mybatis의 Mapper 인터페이스의 작성 / xml 처리
• 작성한 Mapper 인터페이스의 테스트

 

VO 클래스 작성

 

src/main/java > org.zerock.domain 패키지를 생성하고 BoardVO 클래스를 정의 

 

 

 

package org.zerock.domain;

import java.util.Date;

import lombok.Data;

@Data
public class BoardVO {

	private Long bno; //BNO	NUMBER(10,0)
	private String title; //TITLE	VARCHAR2(200 BYTE)
	private String content; //CONTENT	VARCHAR2(2000 BYTE)
	private String writer; //WRITER	VARCHAR2(50 BYTE)
	private Date regdate; //REGDATE	DATE
	private Date updateDate; //UPDATEDATE	DATE
}

 

 

@data :  getter/ setter, toString()

 

Mapper 인터페이스와  Mapper XML

 

root-context.xml에서 org.zerock.mapper 패키지를 스캔하도록 설정 

<mybatis-spring:scan base-package="org.zerock.mapper"/>	

 

 

 

org.zerock.mapper 패키지 작성. BoardMapper 인터페이스를 추가

 

 

BoardMapper(인터페이스)

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;
import org.apache.ibatis.annotations.Select;

public interface BoardMapper {

		@Select("select * from tbl_board where bno > 0")
		public List<BoardVO> getList();
}

@Select("select * from tbl_board where bno > 0") //전체 리스트를 가지고 온다.

 

 

 

BoardMapper 인터페이스 테스트 

src/test/java > org.zerock.mapper패키지 작성. BoardMapperTests 클래스 추가

 

package org.zerock.mapper;

import static org.junit.Assert.*;

import java.util.List;

import org.apache.ibatis.annotations.Select;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.zerock.domain.BoardVO;

import lombok.Setter;
import lombok.extern.log4j.Log4j;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
//C:\Users\SeonMin Lee\Desktop\Eunchans\ex02\src\main\webapp\WEB-INF\spring\root-context.xml (Root_context 위치)
@Log4j
public class BoardMapperTests {

	@Setter(onMethod_ = @Autowired)
	private BoardMapper mapper; //interface mapper
	
	@Test
	public void testGetList() { //@Select("select * from tbl_board where bno > 0")
		                        //  public List<BoardVO> getList(); <-이름 가져오기
		
		mapper.getList().forEach(board -> log.info(board));
	}

}

 테스트 완료 후 Mapper XML 생성

 

src/main/resources > org/zerock/mapper 경로에 BoardMapper.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">

<![CDATA[
  
  ]]> XML 에서 부등호를 사용하기 위해 사용

 

BoardMapper.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">
  
  
  
  <!-- 
  
  package org.zerock.mapper;
		@Select("select * from tbl_board where bno > 0") //전체 리스트를 가지고 온다.
		public List<BoardVO> getList();
}
 
 	package org.zerock.domain; 
 	public class BoardVO; 
  	private Long bno; //BNO	NUMBER(10,0)
	private String title; //TITLE	VARCHAR2(200 BYTE)
	private String content; //CONTENT	VARCHAR2(2000 BYTE)
	private String writer; //WRITER	VARCHAR2(50 BYTE)
	private Date regdate; //REGDATE	DATE
	private Date updateDate; //UPDATEDATE	DATE  -->
  <mapper namespace="org.zerock.mapper.BoardMapper"> <!-- board mapper 주소 -->
  
  <select id="getList" resultType="org.zerock.domain.BoardVO"> <!-- mapper의 함수명 / vo경로 -->
  <![CDATA[
  select * from tbl_board where bno > 0
  ]]>
  
  </select>
  </mapper>

org.zerock.mapper.BoardMapper 인터페이스 주석 처리하기

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;
import org.apache.ibatis.annotations.Select;

public interface BoardMapper {

//		@Select("select * from tbl_board where bno > 0") //전체 리스트를 가지고 온다. 2.setter 주석처리
		public List<BoardVO> getList();
}

 

테스트 결과

INFO : org.springframework.test.context.support.DefaultTestContextBootstrapper - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener]
INFO : org.springframework.test.context.support.DefaultTestContextBootstrapper - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@7085bdee, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@1ce92674, org.springframework.test.context.support.DependencyInjectionTestExecutionListener@5700d6b1, org.springframework.test.context.support.DirtiesContextTestExecutionListener@6fd02e5, org.springframework.test.context.transaction.TransactionalTestExecutionListener@5bcab519, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@e45f292]
INFO : org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:src/main/webapp/WEB-INF/spring/root-context.xml]
INFO : org.springframework.context.support.GenericApplicationContext - Refreshing org.springframework.context.support.GenericApplicationContext@3dd4520b: startup date [Tue Sep 08 17:17:58 KST 2020]; root of context hierarchy
INFO : org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor - JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=2, title=테스트 제목, content=테스트 내용, writer=user00, regdate=Wed Aug 26 22:14:28 KST 2020, updateDate=Wed Aug 26 22:14:28 KST 2020)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=3, title=테스트 제목, content=테스트 내용, writer=user00, regdate=Wed Aug 26 22:14:29 KST 2020, updateDate=Wed Aug 26 22:14:29 KST 2020)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=4, title=테스트 제목, content=테스트 내용, writer=user00, regdate=Wed Aug 26 22:14:30 KST 2020, updateDate=Wed Aug 26 22:14:30 KST 2020)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=5, title=테스트 제목, content=테스트 내용, writer=user00, regdate=Wed Aug 26 22:14:32 KST 2020, updateDate=Wed Aug 26 22:14:32 KST 2020)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=6, title=테스트 제목, content=테스트 내용, writer=user00, regdate=Wed Aug 26 22:14:32 KST 2020, updateDate=Wed Aug 26 22:14:32 KST 2020)
INFO : org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@3dd4520b: startup date [Tue Sep 08 17:17:58 KST 2020]; root of context hierarchy
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.

 

 

오라클

**select ename, sal, rownum from
(select ename, sal, rownum from emp order by sal desc) where rownum <= 5;  급여가 높은 사람 5명 데이터 가져오기

mysql

**select ename, sal from emp order by sal desc limit5;

**select ename, sal from emp order by sal desc limit5;

 

급여가 많은 순으로 6-10까지 출력

select ename, sal, rownum, rn from
(select ename, sal, rownum rn from (select * from emp order by sal desc) 
  where rn rownum between 6 and 10;

 

**select * from 
(select a.*, rownum rn from

(select * from emp order by sal desc) a)  
  where rn rownum between 6 and 10;

 

**select * from
(select a.*, rownum rn from
(select * from board order by num desc) a)
where rn between #{startRow} and #{endRow}