IBATIS+ORACLE(一)

it2025-04-27 9

迁移时间：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="">  <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"。　　

转载于:https://www.cnblogs.com/Marydon20170307/p/6929380.html

相关资源：myibatis 生成oracle 对应映射文件

最新回复(0)