Mybatis xml 写法

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- 

namespace：必须与对应的接口全类名一致

id:必须与对应接口的某个对应的方法名一致

-->

<mapper namespace="com.soft.dao.customer.CustomerMapper">

 

<!-- 查询返回值对应map -->

<resultMap id="userMap" type="Customer">

<result column="customerId" property="customerId" jdbcType="INTEGER" />

<result column="LoginName" property="loginName" jdbcType="VARCHAR" />

<result column="Uimage" property="uImage" jdbcType="VARCHAR" />

<result column="Pwd" property="pwd" jdbcType="VARCHAR" />

<result column="NikeName" property="nikeName" jdbcType="VARCHAR" />

<result column="Mobile" property="mobile" jdbcType="VARCHAR" />

<result column="qq" property="qq" jdbcType="VARCHAR" />

<result column="MaxOnline" property="maxOnline" jdbcType="INTEGER" />

<result column="regtimestr" property="regTimeStr" jdbcType="VARCHAR" />

<result column="RegFrom" property="regFrom" jdbcType="VARCHAR" />

<result column="Email" property="email" jdbcType="VARCHAR" />

<result column="GroupId" property="groupId" jdbcType="INTEGER" />

<result column="GroupName" property="groupName" jdbcType="VARCHAR" />

<result column="account" property="account" jdbcType="VARCHAR" />

<result column="tranche" property="tranche" jdbcType="VARCHAR" />

<result column="custBution" property="custBution" jdbcType="VARCHAR" />

<result column="fromCus" property="fromCus" jdbcType="VARCHAR" />

<result column="orgCode" property="orgCode" jdbcType="VARCHAR" />

<result column="customerKey" property="customerKey" jdbcType="VARCHAR" />

<result column="expiryDateStr" property="expiryDateStr" jdbcType="VARCHAR"/>

<result column="dateInterval" property="dateInterval" jdbcType="INTEGER"/>

<result column="effDay" property="effDay" jdbcType="INTEGER"/>

</resultMap>

 

<!-- 查询返回值对应map -->

<resultMap id="userAdjustMap" type="Customer">

<result column="customerId" property="customerId" jdbcType="INTEGER" />

<result column="GroupName" property="groupName" jdbcType="VARCHAR" />

<result column="account" property="account" jdbcType="VARCHAR" />

<result column="tranche" property="tranche" jdbcType="VARCHAR" />

<result column="custBution" property="custBution" jdbcType="VARCHAR" />

</resultMap>

 

<!-- 查询的列方便多个方法共用 -->

<sql id="Base_Column_List">

c.customerId,c.LoginName,c.Uimage,c.Pwd,c.NikeName,c.Mobile,c.qq,c.MaxOnline,date_format(c.regtime,'%Y-%m-%d %H:%i:%s') regtimestr,c.RegFrom,c.Email,c.GroupId,g.GroupName,c.account,c.tranche,c.custBution,

c.fromCus,c.orgCode,c.customerKey,date_format(c.expiryDate,'%Y-%m-%d') expiryDateStr,dateInterval,effDay

</sql>

 

<sql id="Base_Column_List_Adjust">

c.customerId,g.GroupName,c.account,c.tranche,c.custBution

</sql>

 

<!-- where 条件方便多个方法共用 -->

<sql id="Base_Where_Clause">

<where>

<trim prefixOverrides="and">

   <!-- 机构编码 -->

<if test="orgCode !=null">

and c.orgCode = #{orgCode}

</if>

   <!-- 账号 -->

<if test="loginName !=null">

and c.LoginName like CONCAT("%",#{loginName},"%")

</if>

<!-- 昵称 -->

<if test="nikeName !=null">

and c.NikeName like CONCAT("%",#{nikeName},"%")

</if>

<!-- 手机 -->

<if test="mobile !=null">

and c.Mobile like CONCAT("%",#{mobile},"%")

</if>

<!-- qq -->

<if test="qq !=null">

and c.qq like CONCAT("%",#{qq},"%")

</if>

<!-- 微信 -->

<if test="wx !=null">

and c.wx like CONCAT("%",#{wx},"%")

</if>

<!-- 注册开始日期 -->

<if test="regBeginDate !=null">

  <![CDATA[

  and Date(c.RegTime) >= #{regBeginDate}

]]>

</if>

<!-- 注册结束日期 -->

<if test="regEndDate !=null">

 <![CDATA[

  and Date(c.RegTime) <= #{regEndDate}

 ]]>

</if> 

<!-- 角色 -->

<if test="groupId !=0 ">

   and c.GroupId=#{groupId}

</if>

<!-- 用户类型 -->

<if test="type !=0 ">

   and g.GroupType=#{type}

</if>

<!-- 来源 -->

<if test="regFrom !=null"> 

   and LOWER(c.RegFrom)=#{regFrom}

</if>

<!-- 实盘账号 -->

<if test="account !=null">

and c.account like CONCAT("%",#{account},"%")

</if>

<!-- 组别 -->

<if test="tranche !=null">

and c.tranche like CONCAT("%",#{tranche},"%")

</if>

<!-- 员工归属 -->

<if test="custBution !=null">

and c.custBution like CONCAT("%",#{custBution},"%")

</if>

and c.State=1

</trim> 

</where>

</sql>

 

<!-- where 条件方便多个方法共用 -->

<sql id="Base_Where_Clause_Adjust">

<where>

<trim prefixOverrides="and">

 

<!-- 机构 -->

<if test="orgCode !=null">

and c.orgCode =#{orgCode}

</if>

<!-- ID -->

<if test="customerId !=null">

and c.customerId like CONCAT("%",#{customerId},"%")

</if>

<!-- 实盘账号 -->

<if test="account !=null">

and c.account like CONCAT("%",#{account},"%")

</if>

<!-- 组别 -->

<if test="tranche !=null">

and c.tranche like CONCAT("%",#{tranche},"%")

</if>

<!-- 员工归属 -->

<if test="custBution !=null">

and c.custBution like CONCAT("%",#{custBution},"%")

</if>

and c.State=1  and g.GroupType=1

</trim> 

</where>

</sql>

 

<!-- 分页查询 -->

<select id="queryCustomerByParam" resultMap="userMap" parameterType="UserCondition">

select 

<include refid="Base_Column_List" />

from cus_customer c left join cus_group g on g.GroupId=c.GroupId 

<include refid="Base_Where_Clause" />

<if test="sort != null"> 

<![CDATA[

  order by ${sort} desc

]]>

</if>

<if test="pageStart != null and pageSize != null and pageSize > 0">

limit #{pageStart}, #{pageSize}

</if>

</select>

 

<!-- 分页查询用户角色调整 -->

<select id="queryCustomerByParamAdjust" resultMap="userAdjustMap" parameterType="CusRoleAdjustCondition">

select 

<include refid="Base_Column_List_Adjust" />

from cus_customer c left join cus_group g on g.GroupId=c.GroupId 

<include refid="Base_Where_Clause_Adjust" />

<if test="sort != null"> 

<![CDATA[

  order by ${sort} desc

]]>

</if>

<if test="pageStart != null and pageSize != null">

limit #{pageStart}, #{pageSize}

</if>

</select>

 

<!-- 统计数量 -->

<select id="countRecord" resultType="java.lang.Integer" parameterType="UserCondition">

select count(1) total 

from cus_customer c left join cus_group g on g.GroupId=c.GroupId 

<include refid="Base_Where_Clause" /> 

</select>

 

<!-- 统计数量用户角色调整 -->

<select id="countRecordAdjust" resultType="java.lang.Integer" parameterType="CusRoleAdjustCondition">

select count(1) total 

from cus_customer c left join cus_group g on g.GroupId=c.GroupId 

<include refid="Base_Where_Clause_Adjust" /> 

</select>

 

<!-- 根据Id查询信息 -->

<select id="queryCustomerById" resultMap="userMap" parameterType="java.lang.Integer" >

select

<include refid="Base_Column_List" />

from cus_customer c inner join cus_group g on g.GroupId=c.GroupId  

where customerId=#{customerId}

</select>

 

<!-- 查询全部供导出使用 -->

<select id="findAllCustomer" resultMap="userMap" parameterType="UserCondition" >

select 

<include refid="Base_Column_List" />

from cus_customer c left join cus_group g on g.GroupId=c.GroupId

   <include refid="Base_Where_Clause" />

</select>

 

<!-- 没有查询条件的导出 -->

<select id="queryNoCondition" resultMap="userMap"  parameterType="java.lang.Integer">

select 

<include refid="Base_Column_List" />

from cus_customer c left join cus_group g on g.GroupId=c.GroupId

   order by c.RegTime desc limit ${record}

</select>

 

<!-- 新增用户信息 -->

<insert id="saveCustomer" keyProperty="customerId"  useGeneratedKeys="true" parameterType="Customer">

insert into

cus_customer(LoginName,Pwd,Mobile,GroupId,NikeName,qq,RegTime,Email,MaxOnline,

State,Uimage,RegFrom,CreateId,CreateTime,account,tranche,custBution,fromCus,orgCode,customerKey)

values(#{loginName},#{pwd},#{mobile},#{groupId},#{nikeName},#{qq},#{regTime},#{email},#{maxOnline},

#{state},#{uImage},#{regFrom},#{createId},#{createTime},

#{account},#{tranche},#{custBution},#{fromCus},#{orgCode},#{customerKey})

</insert>

 

<!-- 修改用户信息 -->

<update id="updateCustomer" parameterType="Customer">

update cus_customer set GroupId=#{groupId},Uimage=#{uImage},Pwd=#{pwd},NikeName=#{nikeName},

qq=#{qq},Email=#{email},Mobile=#{mobile},MaxOnline=#{maxOnline},account=#{account},tranche=#{tranche},custBution=#{custBution},

expiryDate=#{expiryDate},dateInterval=#{dateInterval},effDay=#{effDay}

where customerId=#{customerId}

</update>

 

<!-- 根据主键id查询账户信息 -->

<select id="queryCustomerLogin" resultType="java.lang.String" >

 select IFNULL(LoginName,NikeName) as loginname from cus_customer where customerId in

  <foreach item="item" index="index" collection="array" open="(" separator="," close=")">

           #{item}

       </foreach>

</select>

 

<!-- 调整用户角色 -->

<update id="updateCustomerRole">

 update cus_customer set GroupId=#{groupId} where customerId in

  <foreach item="item" index="index" collection="accountArr" open="(" separator="," close=")">

           #{item}

       </foreach>

</update>

 

<!-- 屏蔽用户信息 -->

<update id="deleteCustomerById" >

   update cus_customer set State=#{state} where customerId in

         <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">

             #{item}

         </foreach>

</update>

 

<!-- 修改用户角色调整 -->

<update id="updateCusRoleAdjust" parameterType="Customer">

   update cus_customer set tranche=#{tranche},custBution=#{custBution} where customerId=#{customerId}

</update>

 

<!-- 查询用户账号是否存在 -->

<select id="queryCustomerByLoginName" resultType="java.lang.Integer" parameterType="java.util.Map">

  select count(1) total from cus_customer where  LoginName=#{loginName} and orgCode=#{orgCode}

</select>

 

<!-- 查询手机号码是否存在 -->

<select id="queryCustomerByMobile" resultType="java.lang.Integer" parameterType="java.util.Map">

   select count(1) total from cus_customer where  Mobile=#{mobile} and orgCode=#{orgCode}

</select>

 

<!-- 来源下拉框值 -->

<select id="queryCustomerRegFrom"  resultType="java.lang.String">

  select DISTINCT LOWER(RegFrom) from cus_customer where State=1  and RegFrom is not NULL and RegFrom!=''

</select>

 

<!-- 清空临时表 -->

    <delete id="deleteTmpCustomer">

       delete from tmp_customer;

    </delete>

    

<!-- 批量新增到临时表(导入) -->

<insert id="insertBatch" parameterType="java.util.List"> 

    insert into tmp_customer(LoginName,Pwd,Uimage,NikeName,Mobile,Email,RegTime,GroupId,RegFrom,MaxOnline,State,CreateId,CreateTime,Money)

    values  

    <foreach collection="list" item="obj" index="index" separator="," >  

       (#{obj.loginName},#{obj.pwd},#{obj.uImage},#{obj.nikeName},#{obj.mobile},#{obj.email},#{obj.regTime},#{obj.groupId},#{obj.regFrom},#{obj.maxOnline},

        #{obj.state},#{obj.createId},#{obj.createTime},#{obj.money})

    </foreach>

    </insert> 

    

    <!-- 插入新增的账号(导入) -->

    <insert id="saveCustomerByTmp">

    insert into cus_customer(LoginName,Pwd,Uimage,NikeName,Mobile,Email,RegTime,GroupId,RegFrom,MaxOnline,State,CreateId,CreateTime,Money)

select * from tmp_customer t 

where NOT EXISTS 

(SELECT 1 from  cus_customer c where c.LoginName=t.LoginName or c.Mobile=t.Mobile)

    </insert>

    

     <!-- 向历史表加入数据(新增用户时调用) -->

<insert id="saveCustHistoryByAdd"  parameterType="java.lang.Integer">

     insert into cus_costomer_up_history(CustomerId,SgroupId,EgroupId,ChangeDate,CreateId,CreateTime) 

select c.customerId,0,c.GroupId,NOW(),c.CreateId,NOW() from cus_customer c

     where c.customerId =#{customerId}

    </insert>

    

    <!-- 向历史表加入数据(修改用户时,若角色有变化) -->

     <insert id="saveCustHistoryByEdit" >

     insert into cus_costomer_up_history(CustomerId,SgroupId,EgroupId,ChangeDate,CreateId,CreateTime) 

select c.customerId,c.GroupId,#{groupId},NOW(),#{userId},NOW() from cus_customer c

     where c.customerId =#{customerId}

    </insert>

     

    <!-- 向历史表加入数据-->

    <insert id="saveCustomerHistory" >

     insert into cus_costomer_up_history(CustomerId,SgroupId,EgroupId,ChangeDate,CreateId,CreateTime) 

select c.customerId,c.GroupId,#{groupId},NOW(),#{userId},NOW() from cus_customer c

     where c.customerId in

  <foreach item="item" index="index" collection="accountArr" open="(" separator="," close=")">

           #{item}

       </foreach>

     and c.GroupId!=#{groupId}

    </insert>

    

    <!-- 查询 -->

    <select id="queryCustPartByParam" resultType="CustPartInfo" parameterType="SingleBehaviorInfoCondition">

     SELECT t.customerId as custId,t.Mobile as mobile,g.GroupId as groupId,g.GroupName as groupName from cus_customer t

INNER JOIN cus_group g on t.GroupId=g.GroupId  where 1=1 

<if test="loginName !=0">

and t.LoginName=#{loginName}

</if>

<if test="mobile !=0">

and t.Mobile=#{mobile}

</if>

<if test="custId !=0">

and t.customerId=#{custId}

</if>

    </select>

    

    <!-- 查询实盘账号数量 -->

    <select id="checkAcount" resultType="java.lang.Integer" parameterType="java.lang.String">

      select count(1) total from cus_customer where account=#{account} 

    </select>

    

    <!-- 根据手机号码查询用户信息 -->

    <select id="queryCustByMobile" resultType="Customer" parameterType="java.lang.String">

      select c.customerId,c.LoginName,c.NikeName,c.Mobile from cus_customer c where c.Mobile=#{mobile}

    </select>

    

    <!-- 用户账号过期自动变为注册用户 -->

    <update id="updateCustoverdue" >

    update cus_customer c set expiryDate=NULL,dateInterval=NULL,effDay=NULL,GroupId=(

SELECT g.GroupId from cus_group g 

where c.orgCode=g.OrgCode 

and g.groupname='注册用户'

)  where expiryDate is not null and datediff(expiryDate,now())=0

    </update>

    

    <!-- 根据customerKey查询用户信息 -->

    <select id="queryCustByCustomerKey" resultType="Customer" parameterType="java.lang.String">

      select c.customerId,c.LoginName,c.NikeName,c.Mobile from cus_customer c where c.customerKey=#{customerKey}

    </select>

    

<!-- 调用统一注册接口之后——修改用户其余信息 -->

<update id="updateCustomerOtherInfo" parameterType="Customer">

update cus_customer set LoginName=#{loginName},Pwd=#{pwd},Uimage=#{uImage},NikeName=#{nikeName},qq=#{qq},Email=#{email},

Mobile=#{mobile},GroupId=#{groupId},RegFrom=#{regFrom},MaxOnline=#{maxOnline},account=#{account},tranche=#{tranche},custBution=#{custBution},CreateId=#{createId},

expiryDate=#{expiryDate},dateInterval=#{dateInterval},effDay=#{effDay}

where customerId=#{customerId}

</update>

 

   <!-- 用户角色降级 -->

   <update id="updateCustexpiryDate">

    update cus_customer set expiryDate=NULL,dateInterval=NULL,effDay=NULL,GroupId=#{groupId} where customerId in

  <foreach item="item" index="index" collection="accountArr" open="(" separator="," close=")">

           #{item}

       </foreach>

   </update>

   

   <!-- 用户降级 -->

   <update id="updateCustomerExpiry" parameterType="Customer">

update cus_customer set GroupId=#{groupId},Uimage=#{uImage},Pwd=#{pwd},NikeName=#{nikeName},

qq=#{qq},Email=#{email},Mobile=#{mobile},MaxOnline=#{maxOnline},account=#{account},tranche=#{tranche},custBution=#{custBution},

expiryDate=NULL,dateInterval=NULL,effDay=NULL

where customerId=#{customerId}

   </update>

    

</mapper>

 

 

 

一个牛人的博客

 

https://github.com/abel533/Mapper