iBatis是一个半自动的工具,其优点之一就在于能够生成动态的SQL语句,利用这一特点可以实现对数据的多条件自由组合查询。笔者就自己的一些应用心得略作总结,与大家共享。
iBatis中使用bean与数据库中的字段映射,这样可以直接操作bean来获得数据。在做查询的时候,我利用了SQLMap中的一些特性来生成SQL,这样能够将bean中的绝大部分属性作为可能的组合条件,对数据库进行检索。
感觉很好。请见下面的例子:
<select id="getRecords" parameterClass="Bean" resultClass="Bean" cacheModel="product-cache">
select
ID as id,
PROJECT_ID as projectId,
MAIN_ROAD as mainRoad,
CROSS_ROAD1 as crossRoad1,
CROSS_ROAD2 as crossRoad2,
LENGTH as length,
LOCATION as location,
HOLE as hole,
LEAD_UNIT as leadUnit,
PROPOSE_UNIT as proposeUnit,
cast(year(LAUNCH_TIME) as char(4))+'-'+cast(replicate('0',2-len(month(LAUNCH_TIME))) + cast(month(LAUNCH_TIME) as char(2)) as char(2))+'-'+cast(replicate('0',2-len(day(LAUNCH_TIME))) + cast(day(LAUNCH_TIME) as char(2)) as char(2)) as launchTime,
cast(year(APPLY_TIME) as char(4))+'-'+cast(replicate('0',2-len(month(APPLY_TIME))) + cast(month(APPLY_TIME) as char(2)) as char(2))+'-'+cast(replicate('0',2-len(day(APPLY_TIME))) + cast(day(APPLY_TIME) as char(2)) as char(2)) as applyTime,
cast(year(EVA_TIME) as char(4))+'-'+cast(replicate('0',2-len(month(EVA_TIME))) + cast(month(EVA_TIME) as char(2)) as char(2))+'-'+cast(replicate('0',2-len(day(EVA_TIME))) + cast(day(EVA_TIME) as char(2)) as char(2)) as evaTime,
EVA_REMARK as evaRemark,
STATUS as status,
PRJ_REMARK as prjRemark,
CHA_REMARK as chaRemark
from
PIPE_BUSINESS
<!-- 添加动态查询部分-->
<dynamic prepend="WHERE">
<isNotNull property="id">
<isGreaterThan prepend=" and " property="id" compareValue="0">ID = #id#</isGreaterThan> </isNotNull>
<isNotEmpty prepend=" and " property="projectId">PROJECT_ID like #projectId#</isNotEmpty> <isNotEmpty prepend=" and " property="mainRoad">MAIN_ROAD like #mainRoad#</isNotEmpty <isNotEmpty prepend=" and " property="crossRoad1">CROSS_ROAD1 like #crossRoad1#</isNotEmpty>
<isNotEmpty prepend=" and " property="crossRoad2">CROSS_ROAD2 like #crossRoad2#</isNotEmpty>
<isNotNull property="length">
<isGreaterThan prepend=" and " property="length" compareValue="0">LENGTH between #length#-100 and #length#+100</isGreaterThan>
</isNotNull>
<isNotEmpty prepend=" and " property="location">LOCATION like #location#</isNotEmpty>
<isNotNull property="hole">
<isGreaterThan prepend=" and " property="hole" compareValue="0">HOLE = #hole#</isGreaterThan>
</isNotNull>
<isNotEmpty prepend=" and " property="leadUnit">LEAD_UNIT like '%$leadUnit$%'</isNotEmpty>
<isNotEmpty prepend=" and " property="proposeUnit">PROPOSE_UNIT like '%$proposeUnit$%'</isNotEmpty>
<isNotEmpty prepend=" and " property="launchTime">year(LAUNCH_TIME) = year(cast(#launchTime# as datetime)) and month(LAUNCH_TIME) = month(cast(#launchTime# as datetime))</isNotEmpty>
<isNotEmpty prepend=" and " property="applyTime">year(APPLY_TIME) = year(cast(#applyTime# as datetime)) and month(APPLY_TIME) between month(cast(#applyTime# as datetime))-3 and month(cast(#applyTime# as datetime)) ></isNotEmpty>
<isNotEmpty prepend=" and " property="evaTime"> year(EVA_TIME) = year(cast(#evaTime# as datetime)) and month(EVA_TIME) = month(cast(#evaTime# as datetime))</isNotEmpty><!-
<isNotEmpty prepend=" and " property="evaRemark">EVA_REMARK like '%$evaRemark$%'</isNotEmpty>
<isNotEmpty prepend=" and " property="status">STATUS like #status#</isNotEmpty>
<isNotEmpty prepend=" and " property="prjRemark">PRJ_REMARK like '%$prjRemark$%'</isNotEmpty>
<isNotEmpty prepend=" and " property="chaRemark">CHA_REMARK like '%$chaRemark$%'</isNotEmpty>
</dynamic>
order by id
</select>