ibatie 語法 彙總

1、基本語法

<?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="User">
    	<typeAlias alias="page" type="byd.core.Page"/>
    	<typeAlias alias="user" type="byd.entity.User"/>
    	<parameterMap id="userMap" class="user">
    		<parameter property="userName" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="passWord" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="emplNo" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="emplName" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="phoneNum" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="email" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="deptName" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="dutyName" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="remark" javaType="string" jdbcType="VARCHAR"/>
    		<parameter property="companyName" javaType="string" jdbcType="VARCHAR"/>
    	</parameterMap>
    	<sql id="byUserIdCondition">
    		<![CDATA[
    			id = #id:INTEGER#
    		]]>
    	</sql>
    	<!-- 新增使用者 -->
    	<insert id="save" parameterMap="userMap">
    		<![CDATA[
    			INSERT INTO PLAN_USER (id,userName,passWord,emplNo,emplName,phoneNum,email,deptName,dutyName,remark,companyName)
    			VALUES (PLAN_USER_LOG_S.nextval,?,?,?,?,?,?,?,?,?,?)
    		]]>
    		<selectKey resultClass="java.lang.Integer">
			<![CDATA[
				SELECT  PLAN_USER_LOG_S.currval
				AS id FROM dual
			]]>
		</selectKey>
		</insert>
		<!-- 刪除使用者 -->
		<delete id="deleteById" parameterClass="user">
			<![CDATA[
				delete PLAN_USER
			]]>
			<dynamic prepend="WHERE">
				<include refid="byUserIdCondition"/>
			</dynamic>
		</delete>
    	<!-- 修改使用者 -->
    	<update id="update" parameterClass="user">
		<![CDATA[
			update PLAN_USER
		]]>
		<dynamic prepend="SET">
			<isNotNull property="userName" prepend=",">
				<![CDATA[
					userName = #userName#
				]]>
			</isNotNull>
			<isNotNull property="passWord" prepend=",">
				<![CDATA[
					passWord = #passWord#
				]]>
			</isNotNull>
			<isNotNull property="emplNo" prepend=",">
				<![CDATA[
					emplNo = #emplNo#
				]]>
			</isNotNull>
			<isNotNull property="emplName" prepend=",">
				<![CDATA[
					emplName = #emplName#
				]]> 
			</isNotNull>
			<isNotNull property="phoneNum" prepend=",">
				<![CDATA[
					phoneNum = #phoneNum#
				]]>
			</isNotNull>
			<isNotNull property="deptName" prepend=",">
				<![CDATA[
					deptName = #deptName#
				]]>
			</isNotNull>
			<isNotNull property="dutyName" prepend=",">
				<![CDATA[
					dutyName = #dutyName#
				]]>
			</isNotNull>
			<isNotNull property="remark" prepend=",">
				<![CDATA[
					remark = #remark#
				]]>
			</isNotNull>
			<isNotNull property="email" prepend=",">
				<![CDATA[
					email= #email#
				]]>
			</isNotNull>
			<isNotNull property="companyName" prepend=",">
				<![CDATA[
					companyName = #companyName#
				]]>
			</isNotNull>
		</dynamic>
		<dynamic prepend="WHERE">
			<include refid="byUserIdCondition" />
		</dynamic>
	</update>
	<!-- 分頁查詢 -->
	<select id="findByPage" parameterClass="page" resultClass="user">
		<![CDATA[
			SELECT id,userName,passWord,emplNo,emplName,phoneNum,email,deptName,dutyName,remark,companyName,state FROM (SELECT a.*,ROWNUM nm
			FROM (SELECT u.id, u.username, u.passWord, u.emplname, u.emplno, u.companyname,u.phoneNum,
                   u.deptname, u.dutyname,u.remark, u.email,l.state
                  FROM PLAN_user u LEFT JOIN TIME_logout l ON u.id =l.userid
				WHERE (
					('$objCondition$' is null or upper(u.userName) LIKE upper('%$objCondition$%'))
					OR('$objCondition$' is null or upper(u.phoneNum) LIKE upper('%$objCondition$%'))
					OR('$objCondition$' is null or upper(u.email) LIKE upper('%$objCondition$%'))
					OR('$objCondition$' is null or upper(u.companyName) LIKE upper('%$objCondition$%'))
					OR('$objCondition$' is null or upper(u.deptName) LIKE upper('%$objCondition$%'))
					OR('$objCondition$' is null or upper(u.dutyName) LIKE upper('%$objCondition$%')))
					ORDER BY id DESC) a
                    WHERE ($start$=0 OR ROWNUM <= DECODE ($start$, 1, $limit$, $start$ + $limit$ - 1)))
          WHERE ($start$=0 OR nm >= $start$)
		]]>
	</select>
	<select id="findByCount" parameterClass="page" resultClass="int">
		<![CDATA[
		SELECT COUNT(*) FROM PLAN_USER
		WHERE (
				('$objCondition$' is null or upper(userName) LIKE upper('%$objCondition$%'))
				OR('$objCondition$' is null or upper(phoneNum) LIKE upper('%$objCondition$%'))
				OR('$objCondition$' is null or upper(email) LIKE upper('%$objCondition$%'))
				OR('$objCondition$' is null or upper(companyName) LIKE upper('%$objCondition$%'))
				OR('$objCondition$' is null or upper(deptName) LIKE upper('%$objCondition$%'))
				OR('$objCondition$' is null or upper(dutyName) LIKE upper('%$objCondition$%')))
		]]>
	</select>
	<!-- 使用者登入 -->
	<select id="login" parameterClass="user" resultClass="user">
		<![CDATA[
			SELECT * FROM PLAN_USER u
					WHERE userName = #userName# and passWord = #passWord# and u.id not in(SELECT userId FROM TIME_LOGOUT)
		]]>
	</select>
	<!-- 查詢所有使用者姓名 -->
	<select id="findUser" resultClass="user">
		<![CDATA[
			SELECT emplName FROM PLAN_USER  
		]]>
	</select>
  </sqlMap>

傳入