smartERPLite
Vue DocumentAppearance
Mapper
Mapper는 mybatis의 sql 실행에 사용된다. Mapper interface와 Mapper.xml파일 두개로 구성한다.MyBaptise플러그인이 있으면 메소드에 마우스를 올리고 Ctrl키를 누르면 Mapper interface와 Mapper.xml을 바로 이동할 수 있다.
Mapper interface
- Mapper interface내의 메소드명은 mapper.xml에서 각
select,insert,update,delete태그의id속성과 동일하게 작성한다. - 메소드가 select, insert, update, delete인지 구분하기 쉽도록 메소드 이름은 각 태그이름으로 시작하도록 한다.
package com.vmerp.sample.form_grid;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.vmerp.business.common.model.CmmFile;
import com.vmerp.sample.model.FormGrid;
@Mapper
public interface FormGridMapper {
List<FormGrid> selectList(FormGridParam param);
int insert(FormGrid formGrid);
int update(FormGrid formGrid);
int delete(FormGrid formGrid);
List<CmmFile> selectFormGridCmmFileList(FormGridParam param);
List<FormGrid> selectUploadList(FormGridParam param);
}
Mapper XML
/smart-lite/src/main/resources/mappers/sample/FormGridMapper.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.vmerp.sample.form_grid.FormGridMapper">
<select id="selectList" resultType="com.vmerp.sample.model.FormGrid">
/* com.vmerp.sample.formGrid.FormGridMapper.xml | selectList */
SELECT
A.ID_PK
, A.CORP_CD
, A.NORMAL_TEXT
, A.REQUIRE_TEXT
, A.DATE_DATE
, A.DATE_DATE_1
, A.DATE_DATE_2
, A.DATE_YEAR
, A.DATE_MONTH
, A.MASK_TEL
, A.MASK_ZIP
, A.MASK_EMAIL
, A.USE_YN_COMBO
, A.COM_CODE_COMBO
, A.COM_CODE_CHECKBOX
, A.COM_CODE_RADIO
, A.COM_CODE_POPUP_ID
, A.COM_CODE_POPUP_NAME
, A.COM_CODE_POPUP_ETC1
, A.COM_CODE_POPUP_ETC2
, A.NORMAL_CODE_COMBO
, A.INSERT_USER_ID
, A.INSERT_DATETIME
, A.UPDATE_USER_ID
, A.UPDATE_DATETIME
, A.USE_YN_CHECK
, A.USE_YN_CHECK2
, A.MASK_JUMIN
, A.MASK_BIZ
, A.MASK_CODE_N3
, A.MASK_CODE_N35
, A.MASK_CODE_A5
, A.MASK_NUMBER
, A.MASK_NUMBER_POINT
, A.MASK_PERCENT
, A.MASK_PERCENT_MINUS
, A.SYSTEM_CD
, A.MODULE_CD
, A.ACCT_CD
, (SELECT ACCT_NM FROM FIM_ACCT FA WHERE FA.ACCT_CD = A.ACCT_CD AND FA.CORP_CD = #{corpCd} ) AS ACCT_NM
FROM FORM_SEARCH_GRID A
<where>
<!-- a.NORMAL_TEXT LIKE '111%' -->
<include refid="selectFormGridWhere"></include>
</where>
ORDER BY A.INSERT_DATETIME DESC
</select>
<!-- 본 쿼리의 조회조건과 동일하게 첨부파일도 조회하려고 where 절을 분래 -->
<sql id="selectFormGridWhere">
AND A.CORP_CD = #{corpCd}
AND ( A.ID_PK NOT LIKE 'PR%' AND A.ID_PK NOT LIKE 'CH%' )
<if test="idPk != null and idPk != ''"> AND A.ID_PK = #{idPk}</if>
<if test="idPk == null or idPk == ''">
<if test="normalText != null and normalText != ''"> AND A.NORMAL_TEXT LIKE '%'|| #{normalText} || '%'</if>
<if test="requireText != null and requireText != ''"> AND A.REQUIRE_TEXT LIKE '%'|| #{requireText} || '%'</if>
<if test="dateDate != null "> AND A.DATE_DATE = #{dateDate} </if>
<if test="dateDate1 != null "> AND #{dateDate1} <= A.DATE_DATE</if>
<if test="dateDate2 != null "> AND A.DATE_DATE < #{dateDate2} + 1</if>
<if test="maskTel != null and maskTel != ''"> AND A.MASK_TEL = #{maskTel}</if>
<if test="maskZip != null and maskZip != ''"> AND A.MASK_ZIP = #{maskZip}</if>
<if test="maskEmail != null and maskEmail != ''"> AND A.MASK_EMAIL = #{maskEmail}</if>
<if test="useYnCombo != null and useYnCombo != ''"> AND A.USE_YN_COMBO = #{useYnCombo}</if>
<if test="comCodeCombo != null and comCodeCombo != ''"> AND A.COM_CODE_COMBO = #{comCodeCombo}</if>
<if test="comCodeCheckbox != null and comCodeCheckbox.size() > 0">
AND A.COM_CODE_CHECKBOX <foreach collection="comCodeCheckbox" item="item" index="index" open="IN (" separator="," close=")">#{item}</foreach>
</if>
<if test="comCodeRadio != null and comCodeRadio != ''"> AND A.COM_CODE_RADIO = #{comCodeRadio}</if>
<if test="comCodePopupId != null and comCodePopupId != ''"> AND A.COM_CODE_POPUP_ID = #{comCodePopupId}</if>
<if test="comCodePopupEtc1 != null and comCodePopupEtc1 != ''"> AND A.COM_CODE_POPUP_ETC1 = #{comCodePopupEtc1}</if>
<if test="comCodePopupEtc2 != null and comCodePopupEtc2 != ''"> AND A.COM_CODE_POPUP_ETC2 = #{comCodePopupEtc2}</if>
<if test="normalCodeCombo != null and normalCodeCombo != ''"> AND A.NORMAL_CODE_COMBO = #{normalCodeCombo}</if>
<if test="useYnCheck != null and useYnCheck != ''"> AND A.USE_YN_CHECK = #{useYnCheck}</if>
<if test="maskJumin != null and maskJumin != ''"> AND A.MASK_JUMIN = #{maskJumin}</if>
<if test="maskBiz != null and maskBiz != ''"> AND A.MASK_BIZ = #{maskBiz}</if>
<if test="maskCodeN3 != null and maskCodeN3 != ''"> AND A.MASK_CODE_N3 = #{maskCodeN3}</if>
<if test="maskCodeN35 != null and maskCodeN35 != ''"> AND A.MASK_CODE_N35 = #{maskCodeN35}</if>
<if test="maskCodeA5 != null and maskCodeA5 != ''"> AND A.MASK_CODE_A5 = #{maskCodeA5}</if>
<if test="maskNumber != null and maskNumber != ''"> AND A.MASK_NUMBER = #{maskNumber}</if>
<if test="maskNumberPoint != null and maskNumberPoint != ''"> AND A.MASK_NUMBER_POINT = #{maskNumberPoint}</if>
<if test="maskPercent != null and maskPercent != ''"> AND A.MASK_PERCENT = #{maskPercent}</if>
<if test="maskPercentMinus != null and maskPercentMinus != ''"> AND A.MASK_PERCENT_MINUS = #{maskPercentMinus}</if>
<if test="systemCd != null and systemCd != ''"> AND A.SYSTEM_CD = #{systemCd}</if>
<if test="moduleCd != null and moduleCd != ''"> AND A.MODULE_CD = #{moduleCd}</if>
<if test="acctCd != null and acctCd != ''"> AND A.ACCT_CD = #{acctCd}</if>
<if test="useYnCheck2 != null and useYnCheck2 != ''"> AND A.USE_YN_CHECK2 = #{useYnCheck2}</if>
<if test="dateYear != null and dateYear != ''"> AND A.DATE_YEAR = #{dateYear}</if>
<if test="dateMonth != null and dateMonth != ''"> AND A.DATE_MONTH = #{dateMonth}</if>
</if>
</sql>
<!-- 조회조건에 해당하는 업무별 첨부파일 목록 조회 쿼리. -->
<select id="selectFormGridCmmFileList" resultType="com.vmerp.business.common.model.CmmFile">
<!-- FROM CMM_FILE CF -->
<include refid="com.vmerp.business.common.file.FileMapper.selectCmmFileSql"><property name="FILE_DIV" value="SAMPLE"/></include>
AND EXISTS( SELECT 'CMM_FILE'
FROM FORM_SEARCH_GRID A
<where> CF.REL_KEY1 = A.ID_PK <!-- 업무테일블과 첨부파일 테이블을 연결해주는 pk 컬럼을 join -->
<!-- 본 쿼리의 조회조건과 동일하게 -->
<include refid="selectFormGridWhere"></include>
</where>
)
<include refid="com.vmerp.business.common.file.FileMapper.selectCmmFileOrderBySql"/>
</select>
<select id="selectUploadList" resultType="com.vmerp.sample.model.FormGrid">
/* com.vmerp.sample.formGrid.FormGridMapper.xml | selectUploadList */
SELECT A.CORP_CD
, A.ID_PK
, A.NORMAL_TEXT
, COUNT(*) AS FILE_COUNT
, MIN(A.INSERT_DATETIME) AS INSERT_DATETIME
FROM FORM_SEARCH_GRID A,
CMM_FILE B
WHERE A.CORP_CD = B.CORP_CD
AND A.ID_PK = B.REL_KEY1
AND A.CORP_CD = #{corpCd}
GROUP BY A.CORP_CD, A.ID_PK, A.NORMAL_TEXT
ORDER BY INSERT_DATETIME DESC
</select>
<insert id="insert">
/* FromGridMapper.xml | insert by cha */
INSERT INTO FORM_SEARCH_GRID (
ID_PK /*아이디기본키*/
, NORMAL_TEXT /*일반텍스트*/
, REQUIRE_TEXT /*필수입력텍스트*/
, DATE_DATE /*날짜데이트*/
, DATE_DATE_1 /*날짜데이트기간1*/
, DATE_DATE_2 /*날짜데이트기간2*/
, MASK_TEL /*마스킹_전화번호1*/
, MASK_TEL_2 /*마스킹_전화번호2*/
, MASK_TEL_3 /*마스킹_전화번호3*/
, MASK_ZIP /*마스킹_우편번호*/
, MASK_EMAIL /*마스킹_이메일*/
, USE_YN_COMBO /*사용여부콤보*/
, COM_CODE_COMBO /*공통코드콤보*/
, COM_CODE_CHECKBOX /*공통코드체크박스*/
, COM_CODE_RADIO /*공통코드라디오*/
, COM_CODE_POPUP_ID /*공통코드팝업코드*/
, COM_CODE_POPUP_NAME /*공통코드팝업코드명*/
, COM_CODE_POPUP_ETC1 /*공통코드팝업기타1*/
, COM_CODE_POPUP_ETC2 /*공통코드팝업기타2*/
, NORMAL_CODE_COMBO /*일반코드콤*/
, INSERT_USER_ID /*등록자id*/
, INSERT_DATETIME /*등록일시*/
, UPDATE_USER_ID /*수정자id*/
, UPDATE_DATETIME /*수정일시*/
, USE_YN_CHECK /*사용여부체크*/
, MASK_JUMIN /*주민번호*/
, MASK_BIZ /*사업자번호*/
, MASK_CODE_N3 /*숫자형 코드 3자리*/
, MASK_CODE_N35 /*숫자형 코드 3~5자리*/
, MASK_CODE_A5 /*문자숫자코드5자리*/
, MASK_NUMBER /*숫자*/
, MASK_NUMBER_POINT /*숫자 소수점 4자리*/
, MASK_PERCENT /*퍼센트*/
, MASK_PERCENT_MINUS /*-퍼센트*/
, SYSTEM_CD /*시스템코드*/
, MODULE_CD /*모듈코드*/
, ACCT_CD /*계정코드*/
, USE_YN_CHECK2 /*사용여부체크2.*/
, DATE_YEAR /*연도*/
, DATE_MONTH /*연월*/
, CORP_CD /*회사코드*/
) VALUES (
#{idPk} /*아이디기본키*/
, #{normalText} /*일반텍스트*/
, #{requireText} /*필수입력텍스트*/
, #{dateDate} /*날짜데이트*/
, #{dateDate1} /*날짜데이트기간1*/
, #{dateDate2} /*날짜데이트기간2*/
, #{maskTel} /*마스킹_전화번호1*/
, #{maskTel2} /*마스킹_전화번호2*/
, #{maskTel3} /*마스킹_전화번호3*/
, #{maskZip} /*마스킹_우편번호*/
, #{maskEmail} /*마스킹_이메일*/
, #{useYnCombo} /*사용여부콤보*/
, #{comCodeCombo} /*공통코드콤보*/
, #{comCodeCheckbox} /*공통코드체크박스*/
, #{comCodeRadio} /*공통코드라디오*/
, #{comCodePopupId} /*공통코드팝업코드*/
, #{comCodePopupName} /*공통코드팝업코드명*/
, #{comCodePopupEtc1} /*공통코드팝업기타1*/
, #{comCodePopupEtc2} /*공통코드팝업기타2*/
, #{normalCodeCombo} /*일반코드콤*/
, #{baseUserId} /*등록자id*/
, CURRENT_TIMESTAMP /*등록일시*/
, #{baseUserId} /*수정자id*/
, CURRENT_TIMESTAMP /*수정일시*/
, #{useYnCheck} /*사용여부체크*/
, #{maskJumin} /*주민번호*/
, #{maskBiz} /*사업자번호*/
, #{maskCodeN3} /*숫자형 코드 3자리*/
, #{maskCodeN35} /*숫자형 코드 3~5자리*/
, #{maskCodeA5} /*문자숫자코드5자리*/
, #{maskNumber} /*숫자*/
, #{maskNumberPoint} /*숫자 소수점 4자리*/
, #{maskPercent} /*퍼센트*/
, #{maskPercentMinus} /*-퍼센트*/
, #{systemCd} /*시스템코드*/
, #{moduleCd} /*모듈코드*/
, #{acctCd} /*계정코드*/
, #{useYnCheck2} /*사용여부체크2.*/
, #{dateYear} /*연도*/
, #{dateMonth} /*연월*/
, #{corpCd} /*회사코드*/
)
</insert>
<update id="update">
/* com.vmerp.sample.FormGrid.FormGridMapper.xml | update */
UPDATE FORM_SEARCH_GRID
SET
, NORMAL_TEXT = #{normalText} /*일반텍스트*/
, REQUIRE_TEXT = #{requireText} /*필수입력텍스트*/
, DATE_TEXT = #{dateText} /*날짜텍스트*/
, DATE_TEXT_1 = #{dateText1} /*날짜텍스트기간1*/
, DATE_TEXT_2 = #{dateText2} /*날짜텍스트기간2*/
, DATE_DATE = #{dateDate} /*날짜데이트*/
, DATE_DATE_1 = #{dateDate1} /*날짜데이트기간1*/
, DATE_DATE_2 = #{dateDate2} /*날짜데이트기간2*/
, MASK_TEL = #{maskTel} /*마스킹_전화번호1*/
, MASK_TEL_2 = #{maskTel2} /*마스킹_전화번호2*/
, MASK_TEL_3 = #{maskTel3} /*마스킹_전화번호3*/
, MASK_ZIP = #{maskZip} /*마스킹_우편번호*/
, MASK_EMAIL = #{maskEmail} /*마스킹_이메일*/
, USE_YN_COMBO = #{useYnCombo} /*사용여부콤보*/
, COM_CODE_COMBO = #{comCodeCombo} /*공통코드콤보*/
, COM_CODE_CHECKBOX = #{comCodeCheckbox} /*공통코드체크박스*/
, COM_CODE_RADIO = #{comCodeRadio} /*공통코드라디오*/
, COM_CODE_POPUP_ID = #{comCodePopupId} /*공통코드팝업코드*/
, COM_CODE_POPUP_NAME = #{comCodePopupName} /*공통코드팝업코드명*/
, COM_CODE_POPUP_ETC1 = #{comCodePopupEtc1} /*공통코드팝업기타1*/
, COM_CODE_POPUP_ETC2 = #{comCodePopupEtc2} /*공통코드팝업기타2*/
, NORMAL_CODE_COMBO = #{normalCodeCombo} /*일반코드콤*/
, UPDATE_USER_ID = #{baseUserId} /*수정자id*/
, UPDATE_DATETIME = CURRENT_TIMESTAMP /*수정일시*/
, USE_YN_CHECK = #{useYnCheck} /*사용여부체크*/
, MASK_JUMIN = #{maskJumin} /*주민번호*/
, MASK_BIZ = #{maskBiz} /*사업자번호*/
, MASK_CODE_N3 = #{maskCodeN3} /*숫자형 코드 3자리*/
, MASK_CODE_N35 = #{maskCodeN35} /*숫자형 코드 3~5자리*/
, MASK_CODE_A5 = #{maskCodeA5} /*문자숫자코드5자리*/
, MASK_NUMBER = #{maskNumber} /*숫자*/
, MASK_NUMBER_POINT = #{maskNumberPoint} /*숫자 소수점 4자리*/
, MASK_PERCENT = #{maskPercent} /*퍼센트*/
, MASK_PERCENT_MINUS = #{maskPercentMinus} /*-퍼센트*/
, SYSTEM_CD = #{systemCd} /*시스템코드*/
, MODULE_CD = #{moduleCd} /*모듈코드*/
, ACCT_CD = #{acctCd} /*계정코드*/
, USE_YN_CHECK2 = #{useYnCheck2} /*사용여부체크2.*/
, DATE_YEAR = #{dateYear} /*연도*/
, DATE_MONTH = #{dateMonth} /*연월*/
, CORP_CD = #{corpCd} /*회사코드*/
WHERE ID_PK = #{idPk}
AND CORP_CD = #{corpCd}
</update>
<delete id="delete">
/* com.vmerp.sample.FormGrid.FormGridMapper.xml | delete */
DELETE FORM_SEARCH_GRID
WHERE ID_PK = #{idPk}
AND CORP_CD = #{corpCd}
</delete>
</mapper>