IBATIS+ORACLE(一)

 

迁移时间：2017年6月1日15:55:17

Author:Marydon

　　（四）IBATIS + ORACLE

　　　　第一部分：基础篇

　　　　1.4.1.1 分页SQL　　

<!-- 开头 -->
<isNotNull prepend="" property="end">
    SELECT * FROM (
</isNotNull>

<!-- 结尾 -->
<isNotNull prepend="" property="end">
    <![CDATA[ WHERE ROWNUM<=#END#) WHERE ROWINDEX>=#START# ]]>
</isNotNull>

　　　　1.4.1.2 $column$

　　　　执行sql语句时，控制台输出的语句，在该位置不会以问号的形式出现，而是直接填充其对应的值　　　　

　　　　1.4.1.3 日期类比较大小

<isNotEmpty prepend="and" property="BEGINDATE">
    <![CDATA[
        T.SCHEDULE_DATE >= TO_DATE(#BEGINDATE#,'yyyy-MM-dd')
    ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="ENDDATE">
    <![CDATA[
        T.SCHEDULE_DATE <= TO_DATE(#ENDDATE#,'yyyy-MM-dd')
    ]]>
</isNotEmpty>

 　　　　1.4.1.4 从Oracle服务器获取当前系统日期

<select id="getSYSDATE" resultClass="java.lang.String">
    SELECT TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mm:ss') AS SYSTEMDATE FROM DUAL
</select>

　　　　　　dao层配置

/**
 * 获取Oracle服务器时间
 * @return 系统当前时间
 */
public String getSYSDATE() throws DaoException {
try {
        return (String)sqlMapClient.queryForObject("telemedicine.service.bo.remoteCons.schedule.getSYSDATE");
    } catch (SQLException e) {
        String err = "查询系统当前时间出错->出错原因:" + e.getMessage();
        log.error(err, e);
        throw new DaoException(err, e);
    }
}

　　　　1.4.1.5 增加

　　　　　　方式一：主键使用序列生成

<insert id="insertTSORGDIAITEM" parameterClass="map">
    <selectKey resultClass="java.math.BigDecimal" keyProperty="FID" >
      SELECT STSORGDIAITEM.NEXTVAL AS FID FROM DUAL
    </selectKey>
    INSERT INTO TSORGDIAITEM(FID,
                        FORGID,
                        FITEMCODE,
                        FITEMTYPE,
                        FCURCODE,
                        FCENCODE,
                        FITEMNAME,
                        FZJM,
                        FITEMSPEC,
                        FUNIT,
                        FPRICE)
     VALUES(#FID :NUMERIC#,
            #FORGID :NUMERIC#,
            UPPER(#FITEMCODE :VARCHAR#),
            #FITEMTYPE :NUMERIC#,
            #FCURCODE :VARCHAR#,
            #FCENCODE :VARCHAR#,
            UPPER(#FITEMNAME :VARCHAR#),
            UPPER(#FZJM :VARCHAR#),
            #FITEMSPEC :VARCHAR#,
            #FUNIT :VARCHAR#,
            #FPRICE :NUMERIC#)
</insert>

　　　　　方式二：主键采用sys_guid()生成

<insert id="insertCONSULT_DEPENT_DOCTOR_INFO" parameterClass="map">
    <selectKey keyProperty="id" resultClass="java.lang.String">
        select sys_guid() as id from dual
    </selectKey>
    INSERT INTO CONSULT_DEPENT_DOCTOR_INFO(DEPENT_DOCTOR_INFO_ID,CONSULT_APPLY_ID,CONSULT_DEPENT_ID,CONSULT_DEPENT,CONSULT_DOCTOR_ID,CONSULT_DOCTOR,DOCTOR_PHONE)
    VALUES(#id#,#CONSULT_APPLY_ID#,#CONSULT_DEPENT_ID#,#CONSULT_DEPENT#,#CONSULT_DOCTOR_ID#,#CONSULT_DOCTOR#,#DOCTOR_PHONE#)
</insert>

 　　　　注意：

　　　　　　　a.selectKey标签的keyProperty属性：查询出来的结果会被放到这个字段里面；必须指明返回的数据类型：resultClass；select ... 别名 from dual 别名可以任意取； 

　　　　　　　b.执行插入语句后，默认返回的是主键。　

　　　　1.4.1.6 WHERE条件

<!-- 通用查询条件 -->
<sql id="DIRECTORY_ERROR_WHERE">
    <dynamic prepend=""> <!-- 或prepend="and" -->
        <isNotEmpty prepend="and" property="FORGID">
            T.FORGID = #FORGID#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="FICDCODE">
            (UPPER(T.FICDCODE) LIKE UPPER(#FICDCODE# || '%') OR T.FICDCODE LIKE #FICDCODE# || '%')
        </isNotEmpty>
        <isEqual prepend="and" property="FERRORSTATUS" compareValue="0">
            T.FCENCODE IS NULL
        </isEqual>
        <isEqual prepend="and" property="FERRORSTATUS" compareValue="1">
            T.FITEMNAME !=T1.FITEMNAME
        </isEqual>
        <isNotEmpty prepend="and" property="FRCODE">
            T.FRCODE = '$FRCODE$'
        </isNotEmpty>
    </dynamic>
</sql>

　　　 1.4.1.7 更新

　　　 方式一

<update id="updateTSORGDIAITEM" parameterClass="map">
    UPDATE TSORGDIAITEM  SET
        FID=#FID:NUMERIC#,
        FORGID=#FORGID:NUMERIC#,
        FITEMCODE=#FITEMCODE:VARCHAR#,
        FITEMTYPE=#FITEMTYPE:NUMERIC#,
        FCURCODE=#FCURCODE:VARCHAR#,
        FCENCODE=#FCENCODE:VARCHAR#,
        FITEMNAME=#FITEMNAME:VARCHAR#,
        FZJM=#FZJM:VARCHAR#,
        FITEMSPEC=#FITEMSPEC:VARCHAR#,
    WHERE FID =#FID# 
</update>

　　　　方式二

<update id="updateCONSULT_SCHEDULE" parameterClass="map">
    UPDATE CONSULT_SCHEDULE  SET
            ORG_CODE = #ORG_CODE#,
            DEPENT_ID = #DEPENT_ID#,
            DEPENT_NAME = #DEPENT_NAME#,
            DOCTOR_ID = #DOCTOR_ID#,
            DOCTOR_NAME = #DOCTOR_NAME#,
            DOCTOR_PHONE = #DOCTOR_PHONE#,
            SCHEDULE_DATE = TO_DATE(#SCHEDULE_DATE#,'YYYY-MM-DD'),
            WEEK_TXT = #WEEK_TXT#,
            WB_TYPE = #WB_TYPE#,
            CLOSE_TZ = #CLOSE_TZ#,
            REPLACE_TZ = #REPLACE_TZ#,
            DOCTOR_ID_TZ = #DOCTOR_ID_TZ#,
            DOCTOR_NAME_TZ = #DOCTOR_NAME_TZ#,
            REMARK = #REMARK#
    WHERE CONSULT_SCHEDULE_ID = #CONSULT_SCHEDULE_ID#
</update>

　　　　UpdateTime--2017年8月8日09:30:01

<update id="updateTDOCTORORGDynamic" parameterClass="map">
    UPDATE TDOCTORORG
      <dynamic prepend="SET">
        <isNotNull prepend="," property="FDOCTORCODE"> 
             FDOCTORCODE = #FDOCTORCODE#      
        </isNotNull>
        <isNotNull prepend="," property="FDOCTORCODE_CENTER"> 
             FDOCTORCODE_CENTER = #FDOCTORCODE_CENTER#      
        </isNotNull>
        <isNotNull prepend="," property="FDOCTORNAME"> 
             FDOCTORNAME = #FDOCTORNAME#      
        </isNotNull>
        <isNotNull prepend="," property="FZJM"> 
             FZJM = #FZJM#      
        </isNotNull>
        <isNotNull prepend="," property="FDEPTCODE"> 
             FDEPTCODE = #FDEPTCODE#      
        </isNotNull>
        <isNotNull prepend="," property="FDOCTORPHONE"> 
             FDOCTORPHONE = #FDOCTORPHONE#      
        </isNotNull>
        <isNotNull prepend="," property="FJOBTITLE"> 
             FJOBTITLE = #FJOBTITLE#      
        </isNotNull>
        <isNotNull prepend="," property="FREMARK"> 
             FREMARK = #FREMARK#      
        </isNotNull>
      </dynamic>
    WHERE
    FID = #FID#
</update>

　　　　这种动态修改字段的方式需要注意的是：要修改的字段是否允许为空，如果允许用isNotNull；不允许用isNotEmpty　　　　 　　

　　　　1.4.1.8 删除

　　　　方式一

<!-- 方法一 -->
<delete id="delTDICTICDCODE" parameterClass="map">
    DELETE FROM TDICTICDCODE
    <dynamic prepend="WHERE">
        <isNotEmpty prepend="and" property="FICDCODE">
            FICDCODE=#FICDCODE#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="FICDNAME">
            FICDNAME=#FICDNAME#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="FZJM">
            FZJM=#FZJM#
        <isNotEmpty prepend="and" property="FCLASSID">
            FCLASSID=#FCLASSID#
        </isNotEmpty>
    </dynamic>
</delete>

　　　　方式二

<!-- 方法二 -->
<delete id="delTSORGDIAITEM" parameterClass="map">
    DELETE FROM TSORGDIAITEM T WHERE 1=1
    <include refid="TSORGDIAITEM_WHERE"/>
</delete>

　　　　1.4.1.9 计数

<!-- 查询总数 -->
<select id="getTSORGDIAITEMCount" parameterClass="map" resultClass="java.lang.Integer" cacheModel="cacheTSORGDIAITEM">
    SELECT COUNT(1) FROM TSORGDIAITEM T,
            (SELECT FITEMCODE,FITEMNAME,FITEMTYPE,FSTATUS FROM TDICTCHARGEITEM TT1
                 WHERE TT1.FRCODE = substr(#FRCODE#,0,2)||'0000'
                   AND NOT EXISTS (SELECT T.FITEMCODE FROM TDICTCHARGEITEM T
                             WHERE (T.FRCODE = #FRCODE# OR T.FRCODE = substr(#FRCODE#,0,4)||'00')
                       AND T.FITEMCODE = TT1.FITEMCODE)
          UNION
          SELECT FITEMCODE,FITEMNAME,FITEMTYPE,FSTATUS FROM TDICTCHARGEITEM TT3 WHERE TT3.FRCODE =#FRCODE#
          ) T1 
    WHERE UPPER(T.FCENCODE)=upper(T1.FITEMCODE(+)) 
    <isEqual prepend="AND" property="Xm_isdzlx" compareValue="1">
        T.FITEMTYPE = T1.FITEMTYPE(+)
    </isEqual> 
    <include refid="TSORGDIAITEM_WHERE"/>
</select>

　　　　1.4.1.10 分页查询

<!-- 分页查询 -->
<select id="getTSORGDIAITEM" parameterClass="map" remapResults="true"  resultClass="java.util.HashMap" cacheModel="cacheTSORGDIAITEM">
    <isNotNull prepend="" property="END">
        SELECT F.* FROM (
    </isNotNull>
        SELECT E.*,ROWNUM ROWNO,                   
            (SELECT T2.FCLASSNAME FROM TDICTDIAITEMTYPE T2 WHERE T2.FCLASSCODE = E.FPLVCODE AND T2.FTYPE = E.FITEMTYPE) AS FCLASSNAME,
            (SELECT T4.FFEENAME FROM TSORGFEECLASS T4 
                WHERE T4.FFEECODE = E.FFEETYPE  AND T4.FORGID = E.FORGID AND    T4.FRCODE = E.FRCODE
            ) AS FFEENAME
         <isNotEmpty property="FORGLEVEL">
             <!--村级 是否可报-->
             <isEqual property="FORGLEVEL" compareValue="1" prepend=",">
                decode(nvl(E.FVILLLEVELLIMIT,0),'1','是','否') as LEVELLIMIT
             </isEqual>
             <!--乡镇级是否可报 -->
             <isEqual property="FORGLEVEL" compareValue="2" prepend=",">
                decode(nvl(E.FTOWNLEVELLIMIT,0),'1','是','否') as LEVELLIMIT
             </isEqual>
             <!--县级是否可报 -->
             <isEqual property="FORGLEVEL" compareValue="3" prepend=",">
                decode(nvl(E.FCOUNLEVELLIMIT,0),'1','是','否') as LEVELLIMIT
             </isEqual>
             <!--市级是否可报 -->
             <isEqual property="FORGLEVEL" compareValue="4" prepend=",">
                decode(nvl(E.FPROVLEVELLIMIT,0),'1','是','否') as LEVELLIMIT
             </isEqual>
              <!--省级是否可报 -->
             <isEqual property="FORGLEVEL" compareValue="5" prepend=",">
                decode(nvl(E.FPROVLEVELLIMIT,0),'1','是','否') as LEVELLIMIT
             </isEqual>
        </isNotEmpty> 
            FROM (
                SELECT T.FID,T.FORGID,T.FITEMCODE,T.FITEMTYPE,decode(T.FITEMTYPE,'1','诊疗项目','药品项目') FITEMTYPENAME,
                    T.FCURCODE, T.FCENCODE,T.FCENCODE_TEM,T.FITEMNAME,T.FZJM,T.FITEMSPEC,T.FUNIT,
                    T.FPRICE,T.FFEETYPE,T.FPLVCODE,T.FHISCODE,T.FSTATUS,  
                    (CASE WHEN T.FFORMS IS NULL OR UPPER(T.FFORMS)='NULL' THEN '' ELSE T.FFORMS END) FFORMS,
                    (CASE WHEN T1.FFORMS IS NULL OR UPPER(T1.FFORMS) = 'NULL' THEN '' ELSE T1.FFORMS END) FFORMS_TDICT,
                    T.FRCODE,LENGTH(T.FITEMNAME) AS FITEMNAMELENGTH,
                    T1.FVILLLEVELLIMIT,T1.FTOWNLEVELLIMIT,T1.FCOUNLEVELLIMIT,T1.FPROVLEVELLIMIT, 
                    T1.FITEMNAME AS FCENNAME,T1.FCOMPUTERATIO,
                    T.FAUDITER,TO_CHAR(T.FAUDDATE,'YYYY-MM-DD') FAUDDATE,
                    T1.FDRUGTYPE,
               (select fdictname from tdictcode where ftypeid(+)='45' and fdictcode(+)=T1.FDRUGTYPE) AS FDRUGTYPENAME
                FROM TSORGDIAITEM T,
                    (SELECT FITEMCODE,FITEMNAME,FITEMTYPE,FCOMPUTERATIO,FCOUNLEVELLIMIT,FTOWNLEVELLIMIT,
                            FVILLLEVELLIMIT,FPROVLEVELLIMIT,FFORMS,FSTATUS,FDRUGTYPE
                        FROM TDICTCHARGEITEM TT1
                     WHERE TT1.FRCODE = substr(#FRCODE#,0,2)||'0000'
                       AND NOT EXISTS (SELECT T.FITEMCODE FROM TDICTCHARGEITEM T
                                 WHERE (T.FRCODE = #FRCODE# OR T.FRCODE = substr(#FRCODE#,0,4)||'00')
                                   AND T.FITEMCODE = TT1.FITEMCODE)
                    UNION
                    SELECT FITEMCODE,FITEMNAME,FITEMTYPE,FCOMPUTERATIO,FCOUNLEVELLIMIT,FTOWNLEVELLIMIT,
                        FVILLLEVELLIMIT,FPROVLEVELLIMIT,FFORMS,FSTATUS,FDRUGTYPE
                     FROM TDICTCHARGEITEM TT3 WHERE TT3.FRCODE =#FRCODE#
                ) T1 
            WHERE UPPER(T.FCENCODE)=UPPER(T1.FITEMCODE(+)) 
            <isEqual prepend="AND" property="Xm_isdzlx" compareValue="1">
               T.FITEMTYPE = T1.FITEMTYPE(+)
            </isEqual>
            <include refid="TSORGDIAITEM_WHERE"/>
            ORDER BY FITEMNAMELENGTH,T.FITEMCODE
        ) E
    <isNotNull prepend="" property="END">
        <![CDATA[ WHERE rownum <=#END# ) F WHERE F.ROWNO >=#START#]]>
    </isNotNull>
</select>

　　　　注意：　　　　　　

　　　　　　a.增，删，改这3个操作无需指明返回的数据类型，执行SQL语句，会默认返回影响行数：Integer；

　　　　　　b.如果将Map作为查询的返回数据类型，必须指定resultClass="java.util.HashMap"。