스프링 웹 프로젝트 8

게시물 등록(Create)

• insert와 selectkey 사용
• 생성된 게시물의 번호를 사용하는지에 따른 구분
  insert만 처리되고 생성된 PK 값을 알 필요가 없는 경우
  insert문이 실행되고, 생성된 PK 값을 알아야 하는경우

※ MyBatis를 사용할 때 #을 생각해야함. #은 getter나 setter로 생각

insert

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;

public interface BoardMapper {

	List<BoardVO> getList();
	
	void insert(BoardVO boardVO); // 메서드 이름, 메서드 파라미터, 메서드 리턴 타입 순서로 생각을 함
	
}

<?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="org.zerock.mapper.BoardMapper">

	<select id="getList" resultType="org.zerock.domain.BoardVO">
		select * from tbl_board order by bno desc
	</select>

	<insert id="insert">
		insert into tbl_board(bno, title,content, writer)
		values(seq_board.nextval, #{title}, #{content}, #{writer}) <!-- #은 ?로 바뀌고 get메서드 -->
	</insert>


</mapper>

package org.zerock.mapper;

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.extern.log4j.Log4j;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class BoardMapperTest {
	
	@Autowired
	private BoardMapper boardMapper;
	
(...생략...)

	@Test
	public void testInsert() {
		log.info("=======================");
		BoardVO boardVO = new BoardVO();
		boardVO.setTitle("Title 테스트");
		boardVO.setContent("Content 테스트");
		boardVO.setWriter("Writer 테스트");
		
		boardMapper.insert(boardVO);
		
	}

}

 

selectkey

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;

public interface BoardMapper {

(...생략...)

	void insertSelectKey(BoardVO boardVO);
}

<?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="org.zerock.mapper.BoardMapper">

(...생략...)

	<insert id="insertSelectKey">
		<selectKey order="BEFORE" keyProperty="bno" resultType="long">
			select seq_board.nextval from dual
		</selectKey> <!-- 성능은 훨씬 안좋음 -->
		insert into tbl_board(bno, title,content, writer)
		values(#{bno}, #{title}, #{content}, #{writer})
	</insert>

</mapper>

package org.zerock.mapper;

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.extern.log4j.Log4j;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class BoardMapperTest {
	
(...생략...)

	@Test
	public void testInsertSelectKey() {
		BoardVO boardVO = new BoardVO();
		boardVO.setTitle("ISK_Title 테스트");
		boardVO.setContent("ISK_Content 테스트");
		boardVO.setWriter("ISK_Writer 테스트");
		boardMapper.insertSelectKey(boardVO);
		log.info("=======================");
		log.info("after insert selectkey " + boardVO.getBno());
	}

}

 

게시물의 조회(read)

• BoardMapper에  read관련된 메서드 추가
• BoardMapper.xml의 SQL 추가
• 테스트를 통한 확인

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;

public interface BoardMapper {

(...생략...)
	
	BoardVO read(Long bno);

}

<?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="org.zerock.mapper.BoardMapper">

(...생략...)

	<select id="read" resultType="org.zerock.domain.BoardVO"> <!-- Alias로 할 수도 있음 -->
		select * from tbl_board where bno = #{bno}
	</select>

</mapper>

package org.zerock.mapper;

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.extern.log4j.Log4j;
import oracle.net.aso.b;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class BoardMapperTest {
	
	@Autowired
	private BoardMapper boardMapper;
	
(...생략...)

	@Test
	public void read() {
		BoardVO boardVO = boardMapper.read(21L);
		log.info(boardVO);
	}

}

 

삭제

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;

public interface BoardMapper {

(...생략...)
	
	// 삭제 리턴 타입을 int로 하는 이유? 몇 건이 삭제되었는지 알아보기 위해 사용
	int delete(Long bno);

}

<?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="org.zerock.mapper.BoardMapper">

(...생략...)

	<delete id="delete">
		delete from tbl_board where bno = #{bno}
	</delete>

</mapper>

package org.zerock.mapper;

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.extern.log4j.Log4j;
import oracle.net.aso.b;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class BoardMapperTest {
	
	@Autowired
	private BoardMapper boardMapper;
	
(...생략...)

	@Test
	public void testDelete() {
		int count = boardMapper.delete(21L);
		log.info("count : " + count);
	}

}

 

수정

package org.zerock.mapper;

import java.util.List;

import org.zerock.domain.BoardVO;

public interface BoardMapper {

(...생략...)
	
	int update(BoardVO boardVO);

}

<?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="org.zerock.mapper.BoardMapper">

(...생략...)

	<update id="update">
		update tbl_board
		set title = #{title}, content = #{content}, writer = #{writer}, updatedate = sysdate
		where bno = #{bno} 
	</update>

</mapper>

package org.zerock.mapper;

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.extern.log4j.Log4j;
import oracle.net.aso.b;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class BoardMapperTest {
	
	@Autowired
	private BoardMapper boardMapper;
	
(...생략...)

	@Test
	public void testUpdate() {
		BoardVO boardVO = new BoardVO();
		boardVO.setBno(1L);
		boardVO.setTitle("Update Title");
		boardVO.setContent("Update Content");
		boardVO.setWriter("Update Writer");
		
		log.info("count " + boardMapper.update(boardVO));
	}

}

sql에서 update from으로 작성했을 때 java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name 에러가 발생함. update from 테이블명(X) update 테이블명(O)