ibatis的ＤＭＬ的写法（五，六，八章综合）

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd ">
<sqlMap namespace="adminuser">

 <typeAlias alias="adminuser"
  type="com.hzmc.capaa.domain.persistence.trustuser.AdminUser" />

 <parameterMap id="addAdminUser"
  class ="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property ="username" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="password" jdbcType="varchar"
   javaType="string" />
  <parameter property="enabled" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="roles" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="expiryDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="lockDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="changePasswordDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="accessLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />
  <parameter property="failureLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />
  <parameter property="realUser" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="contact" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 

 < parameterMap id="CheckupdateAdminUser"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="rowId" jdbcType="varchar"
   javaType="java.lang.String" />
 </ parameterMap>

 < parameterMap id="AdminUser_chackIsSame"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="password" jdbcType="varchar"
   javaType="java.lang.String" />
 </ parameterMap>

 <parameterMap id="AdminUser_updatePassword"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="password" jdbcType="varchar"
   javaType="string" />
  <parameter property="changePasswordAccessNumber"
   jdbcType="NUMBER" javaType="java.lang.Integer" />
  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />

 </parameterMap>

 

 <parameterMap id="AdminUser_updateAccess"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <parameter property="changePasswordAccessNumber"
   jdbcType="NUMBER" javaType="java.lang.Integer" />
  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap>

 

 <parameterMap id="AdminUser_updateLoginAccess"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 

 <parameterMap id="updateAdminUser"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="password" jdbcType="varchar"
   javaType="string" />
  <parameter property="enabled" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="roles" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="expiryDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="lockDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="changePasswordDate" jdbcType="Date"
   javaType="java.util.Date" />

  <parameter property="accessLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />
  <parameter property="failureLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />

  <parameter property="realUser" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="contact" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="rowId" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 <parameterMap id="updateAdminUser_notUpdatePassword"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="username" jdbcType="varchar"
   javaType="java.lang.String" />

  <parameter property="enabled" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="roles" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="expiryDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="lockDate" jdbcType="Date"
   javaType="java.util.Date" />
  <parameter property="changePasswordDate" jdbcType="Date"
   javaType="java.util.Date" />

  <parameter property="accessLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />
  <parameter property="failureLimited" jdbcType="NUMBER"
   javaType="java.lang.Integer" />

  <parameter property="realUser" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="contact" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="rowId" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 <parameterMap id="AdminUser_showDetail"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">

  <parameter property="rowId" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 <parameterMap id="AdminUser_searchParm"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <parameter property="searchParameter.username"
   jdbcType="varchar" javaType="java.lang.String" />
  <parameter property="realUser" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="status" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="roles" jdbcType="varchar"
   javaType="java.lang.String" />
  <parameter property="contact" jdbcType="varchar"
   javaType="java.lang.String" />
 </parameterMap >

 

 <resultMap id="select"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <result property="username" column="username" />
  <result property="password" column="password" />
  <result property="status" column="account_status" />
  <result property="roles" column="granted_role" />
  <result property="realUser" column="realuser" />
  <result property="contact" column="contact" />
  <result property="created" column="created" />
  <result property="expiryDate" column="expiry_date" />
  <result property="lockDate" column="lock_date" />
  <result property="changePasswordDate" column="change_date" />
  <result property="rowId" column="rowid" />

 </resultMap>

 <resultMap id="AdminUser_resultList"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <result property="username" column="username" />
  <result property="password" column="password" />
  <result property="status" column="account_status" />
  <result property="active" column="account_active" />
  <result property="enabled" column="enabled" />
  <result property="roles" column="granted_role" />
  <result property="created" column="created" />
  <result property="expiryDate" column="expiry_date" />
  <result property="lockDate" column="lock_date" />
  <result property="changePasswordDate" column="change_date" />
  <result property="realUser" column="realuser" />
  <result property="contact" column="contact" />
  <result property="rowId" column="rowid_" />

 </resultMap>

 <resultMap id="AdminUser_resultLists"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <result property="username" column="username" />
  <result property="password" column="password" />
  <result property="status" column="account_status" />
  <result property="active" column="account_active" />
  <result property="enabled" column="enabled" />
  <result property="roles" column="granted_role" />
  <result property="created" column="created" />
  <result property="expiryDate" column="expiry_date" />
  <result property="lockDate" column="lock_date" />
  <result property="changePasswordDate" column="change_date" />

  <result property="accessLimited" column="access_limited" />
  <result property="failureLimited" column="failure_limited" />
  <result property="realUser" column="realuser" />
  <result property="contact" column="contact" />
  <result property="rowId" column="rowid_" />

 </resultMap>

 <resultMap id="AdminUser_password"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <result property="username" column="username" />
  <result property="password" column="password" />
  <result property="status" column="account_status" />
  <result property="active" column="account_active" />

  <result property="enabled" column="enabled" />
  <result property="roles" column="granted_role" />
  <result property="created" column="created" />
  <result property="expiryDate" column="expiry_date" />

  <result property="lockDate" column="lock_date" />
  <result property="changePasswordDate" column="change_date" />
  <result property="changePasswordAccessNumber"
   column="change_acesses" />
  <result property="accessLimited" column="access_limited" />

  <result property="lockFailure" column="lock_failure" />
  <result property="failureLimited" column="failure_limited" />
  <result property="realUser" column="realuser" />
  <result property="contact" column="contact" />

  <result property="rowId" column="rowid_" />

  </resultMap>

 <cacheModel id="trustUserCache" type="MEMORY" >
  <flushOnExecute statement="AdminUser_updatePassword" />
  <flushOnExecute statement="AdminUser_updateAcesses" />
  <flushOnExecute statement="AdminUser_updateLoginAcesses" />
  <flushOnExecute statement="AdminUser_addAdminUser" />
  <flushOnExecute statement="AdminUser_updateAdminUser" />
  <flushInterval hours="2" />
  <property name="size" value="50"/>
 </cacheModel>

 

 <resultMap id="AdminUser_password"
  class="com.hzmc.capaa.domain.persistence.trustuser.AdminUser">
  <result property="username" column="username" />
  <result property="password" column="password" />
 </resultMap>
 
 <select id="AdminUser_checkIsSame"
  parameterMap="AdminUser_chackIsSame"  resultMap="AdminUser_password">
  select username,password from mc$$lg_adminUser where username=?
  and password=?
 </select>
 
 <update id="AdminUser_updatePassword"
  parameterMap="AdminUser_updatePassword">
  update mc$$lg_adminuser set password=?,change_acesses=? where
  username=?
 </update>

 <update id="AdminUser_updateAcesses"
  parameterMap="AdminUser_updateAccess">
  update mc$$lg_adminuser set change_acesses=? where username=?
 </update>

 <update id="AdminUser_updateLoginAcesses"
  parameterMap="AdminUser_updateLoginAccess">
  update mc$$lg_adminuser set lock_failure=lock_failure+1 where
  username=?
 </update>

 <select id="AdminUser_passwordCheck" parameterClass="string"
  resultMap="AdminUser_password">
  select username,password ,account_status,account_active,enabled,
  granted_role, created ,expiry_date, lock_date, change_date ,
  change_acesses,access_limited,lock_failure,failure_limited,
  realuser ,contact,rowid rowid_ from mc$$lg_adminuser where
  username=#username#
 </select>

 

 <select id="AdminUser_getAdminUser"
  resultMap="AdminUser_resultLists" >
  select rownum rownum_,rowid rowid_,row_.* from (select * from
  mc$$lg_adminuser )row_ where rowid=#rowId#
 </select>

 <!-- 模糊查询不能用#,#在是用prepareStatement的?插入参数,$是文本替换 -->

 <insert id="AdminUser_addAdminUser" parameterMap="addAdminUser">
  insert into mc$$lg_adminuser
  (username,password,enabled,GRANTED_ROLE,EXPIRY_DATE,LOCK_DATE,CHANGE_DATE,access_limited,failure_limited,realUser,contact)values(?,?,?,?,?,?,?,?,?,?,?)
 </insert>

 <update id="AdminUser_updateAdminUser"
  parameterMap="updateAdminUser">
  update mc$$lg_adminuser set
  username=?,password=?,enabled=?,granted_role=?,expiry_date=?,lock_date=?,change_date=?,access_limited=?,failure_limited=?,realuser=?,contact=?
  where rowid=?
 </update>

 <update id="AdminUser_updateAdminUserAndNotUpdatePassword"
  parameterMap="updateAdminUser_notUpdatePassword">
  update mc$$lg_adminuser set
  username=?,enabled=?,granted_role=?,expiry_date=?,lock_date=?,change_date=?,access_limited=?,failure_limited=?,realuser=?,contact=?
  where rowid=?
 </update>

 <delete id="AdminUser_delete">
  delete from mc$$lg_adminuser where rowid=#rowId#
 </delete>

 

 <select id="AdminUser_isExists" parameterClass="string"
  resultMap="select" cacheModel="trustUserCache">
  select username,password ,account_status, granted_role, realuser
  ,contact, created ,expiry_date, lock_date, change_date ,rowid
  from mc$$lg_adminuser where username=#username#
 </select>

 <select id="AdminUser_UpdateExists"
  parameterMap="CheckupdateAdminUser"  resultMap="select">
  select username,password ,account_status, granted_role, realuser
  ,contact, created ,expiry_date, lock_date, change_date ,rowid
  from mc$$lg_adminuser where username=? and rowid!=?
 </select>

 

 <select id="AdminUser_list" resultMap="select">
  select
  username,password,account_status,granted_role,realuser,contact,created,expiry_date,lock_date,change_date,change_acesses,rowid
  from mc$$lg_adminuser
 </select>

 

 <!-- 以下是新加的分页的查询统计方法 测试用-->
 <select id="AdminUser_countUser" parameterClass="com.hzmc.common.domain.Page"
  resultClass="int">

  select count(*) from mc$$lg_adminuser

  <dynamic prepend="WHERE">
   <isNotEmpty prepend="AND" property="searchParameters.username">
    upper(username) like upper('%$searchParameters.username$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.realUser">
    upper(realuser) like upper('%$searchParameters.realUser$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.status">
    upper(account_status) like upper('%$searchParameters.status$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.roles">
    upper(granted_role) like upper('%$searchParameters.roles$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.contact">
    upper(contact) like upper('%$searchParameters.contact$%')
   </isNotEmpty>
  </dynamic>

 </select>

 <select id="AdminUser_searchResults"
  parameterClass="com.hzmc.common.domain.Page"
  resultMap="AdminUser_resultList">
  select * from (select rowid rowid_,rownum rownum_,row_.* from
  (select * from mc$$lg_adminuser
  <dynamic prepend="WHERE">
  <isNotEmpty prepend="AND" property="searchParameters.username">
    upper(username) like upper('%$searchParameters.username$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.realUser">
    upper(realuser) like upper('%$searchParameters.realUser$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.status">
    upper(account_status) like upper('%$searchParameters.status$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.roles">
    upper(granted_role) like upper('%$searchParameters.roles$%')
   </isNotEmpty>
   <isNotEmpty prepend="AND" property="searchParameters.contact">
    upper(contact) like upper('%$searchParameters.contact$%')
   </isNotEmpty>
  </dynamic>
  )row_)
  <isEqual prepend="WHERE" property="paged" compareValue="true">
   rownum_ between #startNumber# and #endNumber#
  </isEqual>
  order by created desc
 </select>
 
 <select id="AdminUser_Details" parameterMap="AdminUser_showDetail"
  resultMap="AdminUser_resultList">
       select * from (select rowid rowid_,rownum rownum_,row_.* from
      (select * from mc$$lg_adminuser )row_)where rowid_=? desc
 </select>
 
 <select id="Adminuser_getRowId" parameterClass="string" resultClass="string">
     select ROWID from MC$$LG_ADMINUSER where USERNAME=#value#
 </select>
</sqlMap>