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

发布时间:2016-12-7 18:38:26 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"用iBatis实现多条件自由组合查询",主要涉及到用iBatis实现多条件自由组合查询方面的内容,对于用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>

上一篇:IIS+PHP+MySQL+Zend Optimizer+GD库+phpMyAdmin安装配置[完整修正实用版]
下一篇:学习ADO.NET

相关文章

相关评论