oracle 使用ibatis动态多条件组合模糊查询
链接地址：http://kevin12.iteye.com/blog/1953571
最近将流程项目数据库由mysql数据库改成oracle数据库时，发现oracle的ibatis配置文件和mysql的ibatis的模糊查询不同，下面记录一下，以备后用。
如果是mysql的模糊查询用：
<isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like concat('%',#IM_NAME#,'%')</isNotEmpty><isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty>或者<isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%$IM_NAME$%'</isNotEmpty>
<?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="IMenu"> <typeAlias alias="iMenu" type="com.sense.workflow.model.IMenu" /> <resultMap id="codeNodeMapping" column="IM_ID" /> <result property="IM_PARENT_ID" column="IM_PARENT_ID" /> <result property="IM_LEVEL" column="IM_LEVEL" /> <result property="IM_PATH" column="IM_PATH" /> <result property="IM_NAME" column="IM_NAME" /> <result property="IM_DESCRIPTION" column="IM_DESCRIPTION" /> <result property="IM_URL" column="IM_URL" /> <result property="IM_STATUS_CODE" column="IM_STATUS_CODE" /> <result property="IM_ORDER" column="IM_ORDER" /> <result property="IM_CREATE_DATETIME" column="IM_CREATE_DATETIME" /> <result property="IM_LAST_UPDATE_DATETIME" column="IM_LAST_UPDATE_DATETIME" /> <result property="IM_LAST_UPDATE_OPERATOR_ID" column="IM_LAST_UPDATE_OPERATOR_ID" /> <result property="IM_VERSION" column="IM_VERSION" /> <result property="IM_VERSION_DATE" column="IM_VERSION_DATE" /> <result property="IM_DATA_STATUS" column="IM_DATA_STATUS" /> <result property="IM_IS_ACTIVITY" column="IM_IS_ACTIVITY" /> </resultMap> <insert id="IMenu_insert" parameterparameter" property="IM_PARENT_ID"> IM_PARENT_ID = #IM_PARENT_ID# </isNotNull> <isNotNull prepend="," property="IM_LEVEL"> IM_LEVEL = #IM_LEVEL# </isNotNull> <isNotNull prepend="," property="IM_PATH"> IM_PATH = #IM_PATH# </isNotNull> <isNotNull prepend="," property="IM_NAME"> IM_NAME = #IM_NAME# </isNotNull> <isNotNull prepend="," property="IM_DESCRIPTION"> IM_DESCRIPTION = #IM_DESCRIPTION# </isNotNull> <isNotNull prepend="," property="IM_URL"> IM_URL = #IM_URL# </isNotNull> <isNotNull prepend="," property="IM_STATUS_CODE"> IM_STATUS_CODE = #IM_STATUS_CODE# </isNotNull> <isNotNull prepend="," property="IM_ORDER"> IM_ORDER = #IM_ORDER# </isNotNull> <isNotNull prepend="," property="IM_CREATE_DATETIME"> IM_CREATE_DATETIME = #IM_CREATE_DATETIME# </isNotNull> <isNotNull prepend="," property="IM_LAST_UPDATE_DATETIME"> IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME# </isNotNull> <isNotNull prepend="," property="IM_LAST_UPDATE_OPERATOR_ID"> IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID# </isNotNull> <isNotNull prepend="," property="IM_VERSION"> IM_VERSION = #IM_VERSION# </isNotNull> <isNotNull prepend="," property="IM_VERSION_DATE"> IM_VERSION_DATE = #IM_VERSION_DATE# </isNotNull> <isNotNull prepend="," property="IM_DATA_STATUS"> IM_DATA_STATUS = #IM_DATA_STATUS# </isNotNull> <isNotNull prepend="," property="IM_IS_ACTIVITY"> IM_IS_ACTIVITY = #IM_IS_ACTIVITY# </isNotNull> </dynamic> where IM_ID=#IM_ID# </update> <update id="IMenu_delete" parameterparameterresultMap="codeNodeMapping"> select IM_ID,IM_PARENT_ID,IM_LEVEL,IM_PATH,IM_NAME,IM_DESCRIPTION,IM_URL,IM_STATUS_CODE,IM_ORDER,IM_CREATE_DATETIME,IM_LAST_UPDATE_DATETIME,IM_LAST_UPDATE_OPERATOR_ID,IM_VERSION,IM_VERSION_DATE,IM_DATA_STATUS,IM_IS_ACTIVITY from TB_I_MENU a where IM_ID=#value# </select> <select id="IMenu_getList" parameterresultMap="codeNodeMapping"> select IM_ID,IM_PARENT_ID,IM_LEVEL,IM_PATH,IM_NAME,IM_DESCRIPTION,IM_URL,IM_STATUS_CODE,IM_ORDER,IM_CREATE_DATETIME,IM_LAST_UPDATE_DATETIME,IM_LAST_UPDATE_OPERATOR_ID,IM_VERSION,IM_VERSION_DATE,IM_DATA_STATUS,IM_IS_ACTIVITY from TB_I_MENU a <dynamic prepend="WHERE "> <isNotEmpty prepend="AND" property="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> order by IM_CREATE_DATETIME desc </select> <select id="IMenu_getListForPage" parameterresultMap="codeNodeMapping"> select IM_ID,IM_PARENT_ID,IM_LEVEL,IM_PATH,IM_NAME,IM_DESCRIPTION,IM_URL,IM_STATUS_CODE,IM_ORDER,IM_CREATE_DATETIME,IM_LAST_UPDATE_DATETIME,IM_LAST_UPDATE_OPERATOR_ID,IM_VERSION,IM_VERSION_DATE,IM_DATA_STATUS,IM_IS_ACTIVITY from TB_I_MENU a <dynamic prepend="WHERE "> <isNotEmpty prepend="AND" property="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> <dynamic prepend="ORDER BY"> <isNotEmpty property="orderSql"> $orderSql$ </isNotEmpty> </dynamic> <dynamic prepend=" LIMIT "> <isNotEmpty property="startRow"> #startRow# </isNotEmpty> <isNotEmpty property="endRow"> ,#endRow# </isNotEmpty> </dynamic> </select> <select id="IMenu_getCount" parameterresultproperty="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> </select> <select id="IMenu_getListForPage_Time_Order" parameterresultMap="codeNodeMapping"> select IM_ID,IM_PARENT_ID,IM_LEVEL,IM_PATH,IM_NAME,IM_DESCRIPTION,IM_URL,IM_STATUS_CODE,IM_ORDER,IM_CREATE_DATETIME,IM_LAST_UPDATE_DATETIME,IM_LAST_UPDATE_OPERATOR_ID,IM_VERSION,IM_VERSION_DATE,IM_DATA_STATUS,IM_IS_ACTIVITY from TB_I_MENU a <dynamic prepend="WHERE "> <isNotEmpty prepend="AND" property="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> <isNotEmpty prepend="AND" property="filterStartTime"> <![CDATA[a.CREATE_DATETIME >= #filterStartTime#]]> </isNotEmpty> <isNotEmpty prepend="AND" property="filterEndTime"> <![CDATA[ a.CREATE_DATETIME <= #filterEndTime# ]]> </isNotEmpty> <dynamic prepend="ORDER BY"> <isNotEmpty property="orderSql"> $orderSql$ </isNotEmpty> </dynamic> <dynamic prepend=" LIMIT "> <isNotEmpty property="startRow"> #startRow# </isNotEmpty> <isNotEmpty property="endRow"> ,#endRow# </isNotEmpty> </dynamic> </select> <select id="IMenu_getCount_Time_Order" parameterresultproperty="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> <isNotEmpty prepend="AND" property="filterStartTime"> <![CDATA[a.CREATE_DATETIME >= #filterStartTime#]]> </isNotEmpty> <isNotEmpty prepend="AND" property="filterEndTime"> <![CDATA[ a.CREATE_DATETIME <= #filterEndTime# ]]> </isNotEmpty> </select> <select id="IMenu_getLastVersion" parameterresultproperty="IM_ID"> a.IM_ID = #IM_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PARENT_ID"> a.IM_PARENT_ID = #IM_PARENT_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LEVEL"> a.IM_LEVEL = #IM_LEVEL#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_PATH"> a.IM_PATH like '%'||#IM_PATH#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_NAME"> a.IM_NAME like '%'||#IM_NAME#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DESCRIPTION"> a.IM_DESCRIPTION like '%'||#IM_DESCRIPTION#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_URL"> a.IM_URL like '%'||#IM_URL#||'%'</isNotEmpty> <isNotEmpty prepend="AND" property="IM_STATUS_CODE"> a.IM_STATUS_CODE = #IM_STATUS_CODE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_ORDER"> a.IM_ORDER = #IM_ORDER#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_CREATE_DATETIME"> a.IM_CREATE_DATETIME = #IM_CREATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_DATETIME"> a.IM_LAST_UPDATE_DATETIME = #IM_LAST_UPDATE_DATETIME#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_LAST_UPDATE_OPERATOR_ID"> a.IM_LAST_UPDATE_OPERATOR_ID = #IM_LAST_UPDATE_OPERATOR_ID#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION"> a.IM_VERSION = #IM_VERSION#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_VERSION_DATE"> a.IM_VERSION_DATE = #IM_VERSION_DATE#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_DATA_STATUS"> a.IM_DATA_STATUS = #IM_DATA_STATUS#</isNotEmpty> <isNotEmpty prepend="AND" property="IM_IS_ACTIVITY"> a.IM_IS_ACTIVITY = #IM_IS_ACTIVITY#</isNotEmpty> </dynamic> </select></sqlMap>