카테고리 없음
4. Spring create(insert) 처리
Handy Smurf
2020. 9. 8. 18:30
MyBatis는 내부적으로 JDBC의 PreparedStatement를 활용한 파라미터를 처리하는 '?'에 대한 치환은 #{속성}을 이용해서 처리한다.
예시
<?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">
<!-- association : 1:N에서 N쪽의 정보를 조회할 때 1쪽의 정보도 함께 읽을 것임 -->
<mapper namespace="www.study.com.bullutineBoard.mapper.PostMapper">
<resultMap id="boardResultMap"
type="www.study.com.bullutineBoard.model.BoardVO">
<id property="id" column="id" />
<result property="name" column="name" />
</resultMap>
<resultMap id="replyResultMap"
type="www.study.com.bullutineBoard.model.ReplyVO">
<id property="hierarchyId" column="hierarchy_id" />
<result property="content" column="content" />
<!-- 다른 Mapper.xml에서 정의된 resultMap을 활용하는 방법 -->
<association property="writer" columnPrefix="m_" resultMap="www.study.com.party.mapper.PartyMapper.partyResultMap"/>
<!-- 구분자 -->
<discriminator javaType="String" column="post_type">
<case value="POST" resultMap="postResultMap" />
</discriminator>
</resultMap>
<resultMap id="postResultMap"
type="www.study.com.bullutineBoard.model.PostVO" extends="replyResultMap">
<result property="title" column="title" />
<association property="board" columnPrefix="b_" resultMap="boardResultMap"/>
</resultMap>
<select id="getAll" resultMap="replyResultMap">
select p.*, m.id m_id, m.name m_name, m.birth_date m_birth_date, m.party_type m_party_type, m.gender m_gender, m.sales_tot m_sales_tot
from t_post p
left outer join t_party m on p.writer_id = m.id
</select>
<!-- Composite Pattern에 따른 개발 방법 -->
<select id="getAllReply" resultMap="replyResultMap">
select p.*, m.id m_id, m.name m_name, m.birth_date m_birth_date, m.party_type m_party_type, m.gender m_gender, m.sales_tot m_sales_tot
from t_post p
left outer join t_party m on p.writer_id = m.id
where hierarchy_id like '1%'
</select>
<insert id="insertPost">
<selectKey keyProperty="hierarchyId" order="BEFORE" resultType="String">
select to62(seq4post.nextval) from dual
</selectKey>
insert into t_post (hierarchy_id, bb_id, writer_id, post_type, title, content)
values(#{hierarchyId}, #{board.id}, #{writer.id}, 'POST', #{title}, #{content})
</insert>
<insert id="insertReply">
<selectKey keyProperty="hierarchyId" order="BEFORE" resultType="String">
select #{parent.hierarchyId} || #{hidDeli} || to62(seq4post.nextval) from dual
</selectKey>
insert into t_post(hierarchy_id, writer_id, content)
values(#{hierarchyId}, #{writer.id}, #{content})
</insert>
</mapper>tbl_board 테이블은 PK 칼럼으로 bno를 이용하고 시퀀스를 이용해서 자동으로 데이터가 추가될 때 번호가 만들어지는 방식을 사용. 이처럼 자동으로 PK 값이 정해지는 경우에는 다음과 같은 2가지 방식으로 처리할 수 있다.
- insert만 처리되고 생성된 PK값을 알 필요가 없는 경우
- insert문이 실행되고 생성된 PK값을 알아야 하는 경우
org.zerock.mapper.BoardMapper인터페이스
1. public void insert(BoardVO board);
2. public void insertSelectKey(BoardVO board); 추가 작성
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();
public void insert(BoardVO board);
public void insertSelectKey(BoardVO board);
}
BoardMapper.xml 추가 작성
<!-- public void insert(BoardVO board);
public void insertSelectKey(BoardVO board);-->
1. <insert id="insert">
insert into tbl_board (bno, title, content, writer)
values(seq_board.nextval, #{title}, #{content}, #{writer})
</insert>
2. <insert id="insertSelectKey">
<selectKey keyProperty="bno" order="BEFORE" resultType="long">
select seq_board.nextval from dual </selectKey>
insert into tbl_board (bno, title, content, writer)
values(#{bno}, #{title}, #{content}, #{writer})
</insert>
</mapper>
<?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>
<!-- public void insert(BoardVO board);
public void insertSelectKey(BoardVO board);-->
<insert id="insert">
insert into tbl_board (bno, title, content, writer)
values(seq_board.nextval, #{title}, #{content}, #{writer})
</insert>
<insert id="insertSelectKey">
<selectKey keyProperty="bno" order="BEFORE" resultType="long">
select seq_board.nextval from dual </selectKey>
insert into tbl_board (bno, title, content, writer)
values(#{bno}, #{title}, #{content}, #{writer})
</insert>
</mapper>
BoardMapperTests 클래스 일부 작성
@Test
public void testInsert() {
BoardVO board = new BoardVO();
board.setTitle("새로 작성하는 글");
board.setContent("새로 작성하는 내용");
board.setWriter("newbie");
mapper.insert(board);
log.info(board);
}테스트 결과
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 18:23:23 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.zerock.mapper.BoardMapperTests - BoardVO(bno=null, title=새로 작성하는 글, content=새로 작성하는 내용, writer=newbie, regdate=null, updateDate=null)
INFO : org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@3dd4520b: startup date [Tue Sep 08 18:23:23 KST 2020]; root of context hierarchy
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.
@SelectKey를 이용한 경우
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=23, title=새로 작성하는 글, content=새로 작성하는 내용, writer=newbie, regdate=Tue Sep 08 18:23:24 KST 2020, updateDate=Tue Sep 08 18:23:24 KST 2020)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=null, title=새로 작성하는 글 select key, content=새로 작성하는 내용 select key, writer=newbie, regdate=null, updateDate=null)
INFO : org.zerock.mapper.BoardMapperTests - BoardVO(bno=null, title=새로 작성하는 글, content=새로 작성하는 내용, writer=newbie, regdate=null, updateDate=null)
INFO : org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@3dd4520b: startup date [Tue Sep 08 18:31:51 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 seq_board.nextval from dual과 같은 쿼리가 먼저 실행되고 여기서 생성된 결과를 이용해 bno 값으로 처리 되는 것을 볼 수 있다.