postgresqlのSQL例文、mybatis

salute

TbJsjJd020sgsDao
/** * 作業所対応状況テーブルを登録します. * * @param value 登録データ */public void insert(final TbJsjJd020sgs value);

TbJsjJd020sgsDao.xml
<insert id="insert" parameterType="jp.co.taisei.jsj.bean.TbJsjJd020sgs">INSERT INTO TB_JSJ_JD020SGS (  KANRI_NO, SAIGAI_KANRI_NO, CMP_CD, CMP_NM, SITEN_CD, SITEN_NM, SITEN_RNM, SORT, DOKEN_CD, DOKEN_NM, SISETU_NO, SISETU_NM, ADDRESS, ADDRESS_DATE, METRO_CD, METRO_NM, AREA_CD, AREA_NM, SAGYO_NM, SAGYO_NNM, JUYO_CD, JUYO_NM, JUYO_RNM, SIJI_TAIO_CD, SIJI_TAIO_NM, SHOCHO_NM, ADD_NM, RENSAKI, ANPI_CD, ANPI_NM, HISAI_JOKYO, HISAI_JOKYO_DATE, KAHUKA_CD, KAHUKA_NM, YOIN_KAKUHO_FLG, YOIN_KAKUHO_DATE, YOSEI_UMU_FLG, YOSEI_KEIRO_CD, YOSEI_KEIRO_NM, YOSEI_KEIRO_RNM, YOSEI_NAIYO, YOSEI_NAIYO_DATE, HUKKYU_TAIOU, HUKKYU_TAIOU_DATE, SITEN_SIJI, SITEN_SIJI_DATE, SITEN_SIJI_NM, HON_SIJI, HON_SIJI_DATE, HON_SIJI_NM, FOLD_PATH, ADD_ID, ADD_DATE, UPD_ID, UPD_DATE, DEL_FLG) VALUES (  #{kanriNo}, #{saigaiKanriNo}, #{cmpCd}, (SELECT CMP_NM FROM TB_JSJ_JN001CMP WHERE CMP_CD = #{cmpCd}), #{sitenCd}, (SELECT SITEN_NM FROM TB_JSJ_JN002STN WHERE CMP_CD = #{cmpCd} AND SITEN_CD = #{sitenCd}), (SELECT SITEN_RNM FROM TB_JSJ_JN002STN WHERE CMP_CD = #{cmpCd} AND SITEN_CD = #{sitenCd}), #{sort}, #{dokenCd}, (SELECT DOKEN_NM FROM TB_JSJ_JN003DKN WHERE DOKEN_CD = #{dokenCd}), #{sisetuNo}, (SELECT SISETU_NM FROM TB_JSJ_JM002SST WHERE SISETU_NO = #{sisetuNo}), #{address}, #{addressDate}, #{metroCd}, #{metroNm}, #{areaCd}, (SELECT AREA_NM FROM TB_JSJ_JN010ARE WHERE AREA_CD = #{areaCd}), #{sagyoNm}, #{sagyoNnm}, #{juyoCd}, #{juyoNm}, (SELECT JUYO_RNM FROM TB_JSJ_JN014JYD WHERE JUYO_CD = #{juyoCd}), #{sijiTaioCd}, #{sijiTaioNm}, #{shochoNm}, #{addNm}, #{rensaki}, #{anpiCd}, #{anpiNm}, #{hisaiJokyo}, #{hisaiJokyoDate}, #{kahukaCd}, #{kahukaNm}, #{yoinKakuhoFlg}, #{yoinKakuhoDate}, #{yoseiUmuFlg}, #{yoseiKeiroCd}, #{yoseiKeiroNm}, (SELECT YOSEI_KEIRO_RNM FROM TB_JSJ_JN012SYK WHERE YOSEI_KEIRO_CD = #{yoseiKeiroCd}), #{yoseiNaiyo}, #{yoseiNaiyoDate}, #{hukkyuTaiou}, #{hukkyuTaiouDate}, #{sitenSiji}, #{sitenSijiDate}, #{sitenSijiNm}, #{honSiji}, #{honSijiDate}, #{honSijiNm}, #{foldPath}, #{addId}, current_timestamp, #{addId}, current_timestamp, '0')</insert>
mybatis 以下的resultMap可以不加，只要在mybatis-config.xml中配置一下
<resultMap type="liming.student.manager.data.model.PhotoEntity" id="photoMapper_resultMap_photoEntity"><id property="photoId"column="PHOTO_ID" javaType="String" jdbcType="VARCHAR" /><result property="photoData"column="PHOTO_DATA" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /><result property="fileName" column="FILE_NAME" javaType="String" jdbcType="VARCHAR" /></resultMap><select id="getPhotoEntityByPhotoId" resultMap="photoMapper_resultMap_photoEntity">SELECT PHOTO_ID,       PHOTO_DATA,       FILE_NAME  FROM PHOTO_TBL WHERE PHOTO_ID = #{photoId, jdbcType=VARCHAR}</select>

mybatis-config.xml<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><setting name="mapUnderscoreToCamelCase" value="true" /></settings></configuration>

SQL例文
<!-- 作業所対応状況一覧CSV用にテーブルを検索 --><select id="selectListAtRC001"parameterType="jp.co.taisei.jsj.bean.DPJ02001Bean"resultType="java.util.TreeMap">SELECT#{sitenNm} AS A001,COALESCE(A.KANRI_NO,'') AS A002,COALESCE(A.SAIGAI_KANRI_NO,'') AS A003,COALESCE(B.TAISHO_SAIGAI,'') AS A004,COALESCE(A.CMP_CD,'') AS A005,COALESCE(A.CMP_NM,'') AS A006,COALESCE(A.SITEN_CD,'') AS A007,<if test="(cmpCd == null or cmpCd == '') and (sitenCd == null or sitenCd == '')">COALESCE(CASE WHEN A.CMP_CD = '001' THEN A.SITEN_NM ELSE C.CMP_RNM END,'') AS A008,COALESCE(CASE WHEN A.CMP_CD = '001' THEN A.SITEN_RNM ELSE C.CMP_RNM END,'') AS A009,</if><if test="(cmpCd != null and cmpCd != '') or (sitenCd != null and sitenCd != '')">COALESCE(A.SITEN_NM,'') AS A008,COALESCE(A.SITEN_RNM,'') AS A009,</if>COALESCE(CAST(A.SORT AS VARCHAR),'') AS A010,COALESCE(A.DOKEN_CD,'') AS A011,COALESCE(A.DOKEN_NM,'') AS A012,COALESCE(B.SAISHU_CD,'') AS A013,COALESCE(B.SAISHU_NM,'') AS A014,COALESCE(A.SISETU_NO,'') AS A015,COALESCE(A.SISETU_NM,'') AS A016,COALESCE(A.ADDRESS,'') AS A017,COALESCE(TO_CHAR(A.ADDRESS_DATE,'YYYY/MM/DD HH:MI'),'') AS A018,COALESCE(A.METRO_CD,'') AS A019,COALESCE(A.METRO_NM,'') AS A020,COALESCE(A.AREA_CD,'') AS A021,COALESCE(A.AREA_NM,'') AS A022,COALESCE(A.SAGYO_NM,'') AS A023,COALESCE(A.SAGYO_NNM,'') AS A024,COALESCE(A.JUYO_CD,'') AS A025,COALESCE(A.JUYO_NM,'') AS A026,COALESCE(A.JUYO_RNM,'') AS A027,COALESCE(A.SIJI_TAIO_CD,'') AS A028,COALESCE(A.SIJI_TAIO_NM,'') AS A029,COALESCE(A.SHOCHO_NM,'') AS A030,COALESCE(A.ADD_NM,'') AS A031,COALESCE(A.RENSAKI,'') AS A032,COALESCE(A.ANPI_CD,'') AS A033,COALESCE(A.ANPI_NM,'') AS A034,COALESCE(A.HISAI_JOKYO,'') AS A035,COALESCE(TO_CHAR(A.HISAI_JOKYO_DATE,'YYYY/MM/DD HH:MI'),'') AS A036,COALESCE(A.KAHUKA_CD,'') AS A037,COALESCE(A.KAHUKA_NM,'') AS A038,COALESCE(A.YOIN_KAKUHO_FLG,'') AS A039,COALESCE(TO_CHAR(A.YOIN_KAKUHO_DATE,'YYYY/MM/DD HH:MI'),'') AS A040,COALESCE(A.YOSEI_UMU_FLG,'') AS A041,COALESCE(A.YOSEI_KEIRO_CD,'') AS A042,COALESCE(A.YOSEI_KEIRO_NM,'') AS A043,COALESCE(A.YOSEI_KEIRO_RNM,'') AS A044,COALESCE(A.YOSEI_NAIYO,'') AS A045,COALESCE(TO_CHAR(A.YOSEI_NAIYO_DATE,'YYYY/MM/DD HH:MI'),'') AS A046,COALESCE(A.HUKKYU_TAIOU,'') AS A047,COALESCE(TO_CHAR(A.HUKKYU_TAIOU_DATE,'YYYY/MM/DD HH:MI'),'') AS A048,COALESCE(A.SITEN_SIJI,'') AS A049,COALESCE(TO_CHAR(A.SITEN_SIJI_DATE,'YYYY/MM/DD HH:MI'),'') AS A050,COALESCE(A.SITEN_SIJI_NM,'') AS A051,COALESCE(A.HON_SIJI,'') AS A052,COALESCE(TO_CHAR(A.HON_SIJI_DATE,'YYYY/MM/DD HH:MI'),'') AS A053,COALESCE(A.HON_SIJI_NM,'') AS A054,COALESCE(A.FOLD_PATH,'') AS A055,COALESCE(TO_CHAR(A.ADD_DATE,'YYYY/MM/DD HH:MI:SS'),'') AS A056,COALESCE(TO_CHAR(A.UPD_DATE,'YYYY/MM/DD HH:MI:SS'),'') AS A057FROM    TB_JSJ_JD020SGS A    LEFT JOIN TB_JSJ_JD000SGK B ON    A.SAIGAI_KANRI_NO = B.SAIGAI_KANRI_NO<if test="(cmpCd == null or cmpCd == '') and (sitenCd == null or sitenCd == '')">    LEFT JOIN TB_JSJ_JN001CMP C ON    A.CMP_CD = C.CMP_CD</if>WHERE    A.DEL_FLG = '0' AND    A.DOKEN_CD = #{dokenCd} AND    A.SAIGAI_KANRI_NO = #{saigaiKanriNo}<if test="empFlg == false">    AND A.CMP_CD IN    <foreach collection="cmpCds" item="cmpCdsItem" open="(" close=")" separator=",">    #{cmpCdsItem}    </foreach></if><if test="cmpCd != null and cmpCd != ''">    AND A.CMP_CD = #{cmpCd}</if><if test="sitenCd != null and sitenCd != ''">    AND A.SITEN_CD = #{sitenCd}</if><if test="sijiTaioCd != null and sijiTaioCd.length != 0">    AND A.SIJI_TAIO_CD IN    <foreach collection="sijiTaioCd" item="sijiTaioCdItem" open="(" close=")" separator=",">#{sijiTaioCdItem}    </foreach></if><if test="metroCd != null and metroCd != ''">    AND A.METRO_CD = #{metroCd}</if><if test="juyoCd != null and juyoCd.length != 0">    AND A.JUYO_CD IN    <foreach collection="juyoCd" item="juyoCdItem" open="(" close=")" separator=",">    #{juyoCdItem}    </foreach></if><if test="hisaiFlg == true">    AND A.JUYO_CD IN ('01', '02')</if><if test="areaCd != null and areaCd != ''">    AND A.AREA_CD = #{areaCd}</if><if test="shochoNm != null and shochoNm != ''">    AND A.SHOCHO_NM LIKE '%${shochoNm}%'</if><if test="rensaki != null and rensaki != ''">    AND A.RENSAKI LIKE '%${rensaki}%'</if><if test="updDateFrom != null and updDateFrom != ''">    AND TO_CHAR(A.UPD_DATE, 'YYYY-MM-DD') >= #{updDateFrom}</if><if test="updDateTo != null and updDateTo != ''">    AND TO_CHAR(A.UPD_DATE, 'YYYY-MM-DD') <= #{updDateTo}</if><if test="searchStr != null and searchStr != ''">    AND    (        A.SAGYO_NM LIKE '%${searchStr}%' OR        A.SHOCHO_NM LIKE '%${searchStr}%' OR        A.RENSAKI LIKE '%${searchStr}%' OR        A.HISAI_JOKYO LIKE '%${searchStr}%' OR        A.HUKKYU_TAIOU LIKE '%${searchStr}%' OR        A.SITEN_SIJI_NM LIKE '%${searchStr}%' OR        A.SITEN_SIJI LIKE '%${searchStr}%' OR        A.HON_SIJI_NM LIKE '%${searchStr}%' OR        A.HON_SIJI LIKE '%${searchStr}%'    )</if>ORDER BY    SAGYO_NNM</select>