ibatIS中sqlMap常用写法+ ibatIS中的模糊拼接

ibatIS中模糊查询，需要拼接sqlMap文件(即TDepartment.xml)

如下:

<select id="queryMap"  parameterClass="com.hanpeng.base.phone.model.TDepartment" 
	resultClass="java.util.HashMap">
	<include refid="paginationStart"/>
	SELECT
			 DEP_NUM  as depNum ,			
			 DEP_NAME  as depName ,			
			 DEP_INFO  as depInfo ,			
			CREATE_DATE  as createDate ,			
			 EMPLOYEE_ID  as employeeId ,			
			 STATE  as state 			
	FROM  T_DEPARTMENT 
	<dynamic prepend="WHERE">
			<isNotEmpty prepend=" AND " property="depNum">
			DEP_NUM =  #depNum# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depName">
			DEP_NAME like  '%'||#depName#||'%' 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depInfo">
			DEP_INFO =  #depInfo# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="createDate">
			CREATE_DATE = #createDate# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="employeeId">
			EMPLOYEE_ID =  #employeeId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="state">
			STATE =  #state# 		</isNotEmpty>
		</dynamic>
	<include refid="paginationEnd"/>
</select>

 

 

 

==========================================================================================

 

常用sqlMap写法为：

<?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="TDepartment">
	<select id="load" parameterClass="java.lang.String" 
	resultClass="com.hanpeng.base.phone.model.TDepartment">
		SELECT 
			DEP_NUM as depNum ,			
			DEP_NAME as depName ,			
			DEP_INFO as depInfo ,			
			CREATE_DATE as createDate ,			
			EMPLOYEE_ID as employeeId ,			
			STATE as state 			
		FROM T_DEPARTMENT
		WHERE
			DEP_NUM=#depNum# 	</select>
	
	<select id="queryModel"  parameterClass="com.hanpeng.base.phone.model.TDepartment" 
	resultClass="com.hanpeng.base.phone.model.TDepartment">
	SELECT
			 DEP_NUM  as depNum ,			
			 DEP_NAME  as depName ,			
			 DEP_INFO  as depInfo ,			
			CREATE_DATE  as createDate ,			
			 EMPLOYEE_ID  as employeeId ,			
			 STATE  as state 			
	FROM  T_DEPARTMENT 
	<dynamic prepend="WHERE">
			<isNotEmpty prepend=" AND " property="depNum">
			DEP_NUM =  #depNum# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depName">
			DEP_NAME =  #depName# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depInfo">
			DEP_INFO =  #depInfo# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="createDate">
			CREATE_DATE = #createDate# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="employeeId">
			EMPLOYEE_ID =  #employeeId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="state">
			STATE =  #state# 		</isNotEmpty>
		</dynamic>
	</select>


<select id="queryMap"  parameterClass="com.hanpeng.base.phone.model.TDepartment" 
	resultClass="java.util.HashMap">
	<include refid="paginationStart"/>
	SELECT
			 DEP_NUM  as depNum ,			
			 DEP_NAME  as depName ,			
			 DEP_INFO  as depInfo ,			
			CREATE_DATE  as createDate ,			
			 EMPLOYEE_ID  as employeeId ,			
			 STATE  as state 			
	FROM  T_DEPARTMENT 
	<dynamic prepend="WHERE">
			<isNotEmpty prepend=" AND " property="depNum">
			DEP_NUM =  #depNum# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depName">
			DEP_NAME like  '%'||#depName#||'%' 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="depInfo">
			DEP_INFO =  #depInfo# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="createDate">
			CREATE_DATE = #createDate# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="employeeId">
			EMPLOYEE_ID =  #employeeId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="state">
			STATE =  #state# 		</isNotEmpty>
		</dynamic>
	<include refid="paginationEnd"/>
</select>



	<select id="count"  parameterClass="com.hanpeng.base.phone.model.TDepartment" 
	resultClass="java.lang.Integer">
		SELECT count(*) 
		FROM T_DEPARTMENT 
		<dynamic prepend="WHERE">
					<isNotEmpty prepend=" AND " property="depNum">
						DEP_NUM =  #depNum# 			</isNotEmpty>
					<isNotEmpty prepend=" AND " property="depName">
						DEP_NAME like  '%'||#depName#||'%' 	</isNotEmpty>
					<isNotEmpty prepend=" AND " property="depInfo">
						DEP_INFO =  #depInfo# 			</isNotEmpty>
					<isNotEmpty prepend=" AND " property="createDate">
						CREATE_DATE = #createDate# 			</isNotEmpty>
					<isNotEmpty prepend=" AND " property="employeeId">
						EMPLOYEE_ID =  #employeeId# 			</isNotEmpty>
					<isNotEmpty prepend=" AND " property="state">
						STATE =  #state# 			</isNotEmpty>
				</dynamic>
	</select>
		
	<insert id="insert" parameterClass="com.hanpeng.base.phone.model.TDepartment">
		INSERT INTO T_DEPARTMENT ( 
			DEP_NUM ,			
			DEP_NAME ,			
			DEP_INFO ,			
			CREATE_DATE ,			
			EMPLOYEE_ID ,			
			STATE 			
		) VALUES (
			#depNum:VARCHAR# ,				
			#depName:VARCHAR# ,				
			#depInfo:VARCHAR# ,				
			#createDate:DATE# ,				
			#employeeId:VARCHAR# ,				
			#state:VARCHAR# 				
		)
	</insert>

	<delete id="delete" parameterClass="com.hanpeng.base.phone.model.TDepartment">
		DELETE FROM T_DEPARTMENT
		WHERE
			DEP_NUM=#depNum# 				
	</delete>

	<update id="update" parameterClass="com.hanpeng.base.phone.model.TDepartment">
		UPDATE T_DEPARTMENT	
		SET
			<isNotNull prepend=" " property="depNum">
				DEP_NUM = #depNum# 
			</isNotNull>
			<isNotNull prepend="," property="depName">
				DEP_NAME = #depName# 
			</isNotNull>		
			<isNotNull prepend="," property="depInfo">
				DEP_INFO = #depInfo# 
			</isNotNull>		
			<isNotNull prepend="," property="createDate">
				CREATE_DATE = #createDate:DATE# 
			</isNotNull>		
			<isNotNull prepend="," property="employeeId">
				EMPLOYEE_ID = #employeeId# 
			</isNotNull>		
			<isNotNull prepend="," property="state">
				STATE = #state# 
			</isNotNull>		
		WHERE
			DEP_NUM=#depNum# 				
	</update>

</sqlMap>