用iBatis实现多条件自由组合查询

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>