select元素查询例子

最新推荐文章于 2021-11-28 10:31:21 发布

hqmln

最新推荐文章于 2021-11-28 10:31:21 发布

阅读量189

点赞数

分类专栏： ibatis 文章标签： java

本文链接：https://blog.csdn.net/hqmln/article/details/84717890

版权

ibatis 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

动态查询

<select id="getAllResource" parameterClass="map"
		resultClass="java.util.HashMap">
		SELECT fpr2.name as parenTEST_name,
         fpr1.id,
         fpr1.name,
         fpr1.layout,
         nvl(fpr1.parenTEST_id, 0) parenTEST_id,
         fpr1.level_num
      FROM TEST_permission_resource fpr1
      left join TEST_permission_resource fpr2 on fpr1.parenTEST_id = fpr2.id
     where (fpr1.name like '%$search_content$%' or fpr1.layout like '%$search_content$%' or fpr2.name like '%$search_content$%')
       and fpr1.status = 0

		   <dynamic prepend="and">
		   	<isEqual compareValue="1" property="searchType">
		   		fpr1.parenTEST_id is null
		   	</isEqual>
		   		<isEqual compareValue="2" property="searchType">
		   		fpr1.parenTEST_id is not null
		   	</isEqual>
		   </dynamic>
		 order by fpr2.name asc,fpr1.level_num desc

	</select>

isNotEmpty、isNotEmpty使用

<select id="queryAllCreditExtension" resultClass="java.util.HashMap">
	 	<![CDATA[
	 		SELECT T.CUCES_ID CUCES_ID,
			       T.PROTOCOL_CODE PROTOCOL_CODE,
			       T.STATUS STATUS,
			       T.CREDIT_VERSION CREDIT_VERSION,
			       T1.CUST_NAME CUST_NAME,
			       T2.NAME CUST_FLAG,
			       T3.NAME CUST_DIF,
			       T.PRE_MONEY PRE_MONEY,
			       T.CREDIT_BALANCE CREDIT_BALANCE,
				   TO_CHAR(T.CREDIT_DATE_START,'YYYY-MM-DD') CREDIT_DATE_START,
                   TO_CHAR(T.CREDIT_DATE_END,'YYYY-MM-DD') CREDIT_DATE_END,
			       T.IS_LOOP_CREDIT IS_LOOP_CREDIT,
			       T.CREATOR CREATOR,
			       T4.NAME CREATE_NAME,
             	   TO_CHAR(T.CREATE_DATE,'YYYY-MM-DD') CREATE_DATE
			  FROM TEST_CUST_CREDITEXTENSION T
			  LEFT JOIN TEST_CUST_CUSTOMER T1 ON (T.CUST_ID = T1.CUST_ID AND
			                                  T1.IS_DELETE = 0)
			  LEFT JOIN TEST_SYS_LINKAGECONFIG T2 ON (T2.ID = T.CUST_FLAG)
			  LEFT JOIN TEST_SYS_LINKAGECONFIG T3 ON (T3.ID = T.CUST_DIF)
			  LEFT JOIN TEST_USER_USER T4 ON T4.ID = T.CREATOR
			 WHERE T.IS_DELETE = 0

	 	]]>
	 	<isNotEmpty property="searchReport" prepend=" ">
	 		<![CDATA[
	 			AND ( CUST_NAME LIKE '%$searchReport$%'
	 			OR T.PROTOCOL_CODE LIKE '%$searchReport$%')
	 		]]>
	 	</isNotEmpty>
	 	<isEmpty prepend=" " property="ORDERTYPE">
	 		ORDER BY CREATE_DATE DESC ,PROTOCOL_CODE DESC, CREDIT_VERSION DESC
	 	</isEmpty>
	 	<isNotEmpty prepend=" " property="ORDERTYPE">
	 		ORDER BY $ORDERTYPE$ DESC 
	 	</isNotEmpty>
	 </select>

使用resultMap自定义返回类型

 <resultMap class="hashmap" id="groupCreditDetailMap" groupBy="CUGD_ID">
	     	<result property="CUGD_ID" column="CUGD_ID"/>
	     	<result property="CUCES_ID" column="CUCES_ID"/>
	     	<result property="CUST_FLAG_" column="CUST_FLAG_"/>
	     	<result property="CUST_DIF_" column="CUST_DIF_"/>
	     	<result property="CUST_ID_" column="CUST_ID_"/>
	     	<result property="CREDIT_MONEY_" column="CREDIT_MONEY_"/>
	     	<result property="MONEY_TYPE_" column="MONEY_TYPE_"/>
	     	<result property="START_DATE_" column="START_DATE_"/>
	     	<result property="END_DATE_" column="END_DATE_"/>
	     	<result property="CUST_RELATION_" column="CUST_RELATION_"/>
	     	<result property="CUGD_REMARK_" column="CUGD_REMARK_"/>
	     	<result property="DIF_LIST" resultMap="creditExtension.custDifMap" javaType="java.util.List" />
	     </resultMap>
	     <resultMap class="hashmap" id="custDifMap">
			<result property="CODE" column="CODE" />
			<result property="FLAG" column="FLAG" />
		</resultMap>
	     <select id="queryGroupCreditDetail" parameterClass="map" resultMap="groupCreditDetailMap">
	     	<![CDATA[
	     		SELECT  CUGD_ID ,
				        CUCES_ID ,
				        CUST_FLAG_ ,
				        CUST_DIF_ ,
				        CUST_ID_ ,
				        CREDIT_MONEY_ ,
				        MONEY_TYPE_ ,
						TO_CHAR(START_DATE_, 'YYYY-MM-DD') AS START_DATE_,
						TO_CHAR(END_DATE_, 'YYYY-MM-DD') AS END_DATE_,
				        CUST_RELATION_ ,
				        CUGD_REMARK_ ,
				        T2.ID CODE ,
				        T2.NAME FLAG
				FROM    TEST_CUST_GROUPCREDITDETAIL T1
				        LEFT JOIN TEST_SYS_LINKAGECONFIG T2 ON T1.CUST_FLAG_ = T2.P_ID
				WHERE   T1.IS_DELETE = 0
				        AND T1.CUCES_ID = #CUCES_ID#
				        ORDER BY T1.CREATE_DATE 
	     	]]>
	     </select>

适应java bean开始

<resultMap class="com.test.bean.ApplyBean" id="appyMap">
		<result property="ID" column="ID"/>
		<result property="CUST_ID" column="CUST_ID"/>
		<result property="DEPT_ID" column="DEPT_ID"/>
		<result property="VENDEE_ID" column="VENDEE_ID"/>
		<result property="PROP_ID" column="PROP_ID"/>
		<result property="STATUS" column="STATUS"/>
		<result property="duePayBeanList" select="duePay.getTransferDuePayByFKId" column="ID"/>
		<result property="otherFeesBeanList" select="otherFee.getBeanListByFKId" column="ID"/>
		<result property="fileBeanList" select="file.getFileByFKId" column="ID"/>
	</resultMap>
	<select id="getApplyId" parameterClass="map" resultMap="appyMap">
		SELECT 
			T.ID,
			T.TRANS_CODE,
			T.CUST_ID,
			T.DEPT_ID,
			T.PROP_ID,
			T.VENDEE_ID,
			T.STATUS
		FROM Test T 
		WHERE T.STATUS=0 
			AND T.ID=#ID#
	</select>