ibatis多条件查询
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="APPLICATION_EXT" >
<resultMap id="ApplicationList" class="com.hkairport.tcaap.model.appl.Application" >
<!--
WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
-->
<result column="REF_NO" property="refNo" jdbcType="VARCHAR" />
<result column="STATUS" property="status" jdbcType="VARCHAR" />
<result column="ROOM_CODE" property="roomCode" jdbcType="VARCHAR" />
<result column="ROOM_DATE" property="roomDate" jdbcType="TIMESTAMP" />
<result column="REJECTED_REASON" property="rejectedReason" jdbcType="VARCHAR" />
<result column="CREATED" property="created" jdbcType="TIMESTAMP" />
<result column="CREATED_BY" property="createdBy" jdbcType="VARCHAR" />
<result column="LAST_MODIFIED" property="lastModified" jdbcType="TIMESTAMP" />
<result column="LAST_MODIFIED_BY" property="lastModifiedBy" jdbcType="VARCHAR" />
<result column="REVISION" property="revision" jdbcType="DECIMAL" />
</resultMap>
<select id="getApplicationList"
resultMap="ApplicationList"
parameterClass="com.hkairport.tcaap.model.ext.ApplicationExt">
SELECT APP.REF_NO,APP.STATUS,APP.ROOM_CODE,APP.ROOM_DATE,APP.REJECTED_REASON,APP.CREATED,APP.CREATED_BY,APP.LAST_MODIFIED,APP.LAST_MODIFIED_BY,APP.REVISION FROM APPLICATION APP JOIN ROOM_CODE RC ON APP.ROOM_CODE=RC.CODE
<dynamic prepend="where">
<isNotEmpty prepend="and" property="refNo">
upper(APP.REF_NO) like upper('%'||ltrim(rtrim(#refNo:VARCHAR#))||'%')
</isNotEmpty>
<isNotEmpty prepend="and" property="description">
upper(RC.DESCRIPTION)like upper('%'||ltrim(rtrim(#description:VARCHAR#))||'%')
</isNotEmpty>
<isNotNull prepend="and" property="dateFrom">
to_date(to_char(APP.ROOM_DATE,'yyyyMMdd'),'yyyyMMdd') >= #dateFrom:TIMESTAMP#
</isNotNull>
<isNotNull prepend="and" property="dateTo">
to_date(to_char(APP.ROOM_DATE,'yyyyMMdd'),'yyyyMMdd') <= #dateTo:TIMESTAMP#
</isNotNull>
<isNotEmpty prepend="and" property="status">
APP.STATUS = #status:VARCHAR#
</isNotEmpty>
</dynamic>
order by APP.REF_NO DESC
</select>
<select id="getMaxNum"
resultClass="com.hkairport.tcaap.model.appl.Application"
parameterClass="com.hkairport.tcaap.model.appl.Application">
SELECT MAX(REF_NO) refNo FROM APPLICATION
<dynamic prepend="where">
<isNotEmpty prepend="and" property="refNo">
upper(REF_NO)like upper(ltrim(rtrim(#refNo:VARCHAR#))||'%')
</isNotEmpty>
</dynamic>
</select>
</sqlMap>
在这里要注意,虽然我们查出来了MAX(REF_NO),并放到了 com.hkairport.tcaap.model.appl.Application这个类当中,但是它并不知具体放到哪个属性