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