迁移时间：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>
注意：
selectKey标签的keyProperty属性：查询出来的结果会被放到这个字段里面；必须指明返回的数据类型：resultClass；select ... 别名 from dual 别名可以任意取；
执行插入语句后，默认返回的是主键。
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"。
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！
作者：Marydon
