mybatis实现DAO，MapperXML语法

mybatis实现DAO，MapperXML语法

(1)声明一个sql，可以根据ID嵌入其他sql

<sql id="Base_Column_List" >
    id, 
    user_id, 
    user_name, 
    password, 
    depart_id, 
    user_creation_time, 
    user_pwd_modif_time, 
    user_status, 
    user_lock, 
    user_pwd_error, 
    user_pwd_error_date,
    exit_time,password_old,
    post, email, office_telephone, 
    user_number, id_card, mobile_phone, 
    sex, home_phone,logout,init_pwd_time,sort ,logout_time
  </sql>

（2）使用include通过Id引用了（1）中声明的sql，另外注意like之后的语法格式like ‘%’||#{userName}||’%’

<select id="selectBySearch" parameterType="java.lang.String"  resultMap="BaseResultMap"  >
     select 
    <include refid="Base_Column_List" />
     from USER USER
     where (user_name like '%'||#{userName}||'%' or user_id like '%'||#{userName}||'%') 
     and "user_status" = '0'
 </select>

（3）使用if标签控制需要传输的参数，如果参数不为null或空，才以此为条件插叙

<select id="selectDepartUser" resultMap="BaseResultMap">
   select id, user_id, user_name, 
      depart_id
    from USER 
    where  user_status =  '0'
    <if test="_parameter != null and _parameter !=''" >  
         and depart_id=#{_parameter,jdbcType=VARCHAR}  
    </if>  
  </select>

（4）使用foreach处理一个列表性的参数

  <update id="updateLogout" parameterType="repository.entity.user.User">
    update USER
    set logout ='1'
    where id in
    <foreach item="item" index="index" collection="list"   
           open="(" separator="," close=")">  
            #{item}  
     </foreach>
  </update>

collection 属性为list或者array的名称，方法参数里面要加注解
（5）使用了trim标签，给id和user_id用括号包起来，最后一个去掉逗号
prefix : 给包裹的sql语句加上前缀.
suffix : 给包裹的sql语句加上后缀.
prefixOverrides: 如果包裹的sql语句是空语句（经常出现在 if 判断为否的情况下）,取消指定的前缀，如where.
suffixOverrides: 如果包裹的sql语句是空语句（经常出现在 if 判断为否的情况下）,取消指定的后缀，如and | or. 逗号等

<insert id="insertSelective" parameterType="repository.entity.user.User" >
    insert into USER USER
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

(6)大于、小于、等于、模糊查询

and o.create_time <= #{endTime,jdbcType=TIMESTAMP}//小于
 and o.create_time >= #{startTime,jdbcType=TIMESTAMP}//大于
and i.status_flag <![CDATA[ <> ]]> 'F'//不等于
<if test="dutyGroupName != null and dutyGroupName != '' " >
    AND DUTY_GROUP_NAME like '%'||#{dutyGroupName}||'%'    //模糊查询
</if>

(7)if里面的字符串比较

<if test="projectStatus != null and projectStatus != '' and projectStatus != '0'.toString()">
        and PROJECT_STATUS = #{projectStatus,jdbcType=VARCHAR}
</if>
<if test='projectStatus == "0"'>
    and PROJECT_STATUS != '0'
</if>