• ibatis基本语法


    <?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>
    
  • 相关阅读:
    暑假学习
    暑假学习
    暑假学习
    暑假学习
    暑假学习
    经验教训+总结
    NT 时刻
    联赛模拟测试 17
    联赛模拟测试 16
    联赛模拟测试 15
  • 原文地址:https://www.cnblogs.com/qq1988627/p/6606923.html
Copyright © 2020-2023  润新知