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>
?