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="com.keyware.kd.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="com.keyware.kd.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 &lt;= #{endTime,jdbcType=TIMESTAMP}//小于 and o.create_time &gt;= #{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>