myibatis的写法

!!!!!!!!!!!!!!!!!!!!!!!!特别注意地方（坑）!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

aa如果传的int型 下面的if条件是不会进入的，因为有 and aa !=''"  这个说明aa只能为string

<if test="aa!= null and aa !=''">
  and bb.aa1= #{aa}
</if>

1. 普通的 work_no = '001'

<if test="workNo!=null and workNo!=''">
      and work_no=#{workNo}
      </if>

2. 普通的 work_no in ( '001')

workNos可以为List<String> 或者String[]

<if test="workNos!=null and workNos.size()>0">
      and work_no in
<foreach item="item" index="index" collection="workNos" open="(" separator="," close=")">
#{item}
</foreach>
</if>

对应的SQL：work_no in ('001','002')

3. 普通的  and (  (work_no = ( '001') )   or    (work_no = ( '002') )    )

workList 为：List<Map<String,Object>> workList = new ArrayList<Map<String,Object>>();

<if test="workList !=null and workList.size()>0">

      and 
<foreach item="item" index="index" collection="works" open="(" separator="or" close=")" >

(work_no = #{item.workNo} and order_num =  #{item.orderNum})

//或者 work_no = #{item}

</foreach> 
</if>

对应的SQL：and  (  (work_no = '001' } and order_num =  '0' )  or (work_no = '002' and order_num =  '1' )  ) 

4.插入语法：

<insert id="insertSelective" parameterType="com.**.**.某某pojo类" >

    insert into tableName
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="creator != null" >
        creator,
      </if>
    </trim>

    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="creator != null" >
        #{creator,jdbcType=VARCHAR},
      </if>
    </trim>

    <selectKey resultType="java.lang.Long" keyProperty="id" >   //主键自增
      SELECT LAST_INSERT_ID()
    </selectKey>

  </insert>

5. update写法

<update id="updateByPrimaryKeySelective" parameterType="com.**.**.某某pojo类" >
    update tableName
    <set >
      <if test="creator != null" >
        creator = #{creator,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>

6. 包含写法

<mapper namespace="com.company.dal.mapper.modle.UserMapper" >
  <resultMap id="bbb" type="com.company.dal.domain.modle.User" >
    <id column="id" property="id" jdbcType="BIGINT" />

.....

  </resultMap>

<sql id="Base_Column_List" >    
    a.id  .....
  </sql>

<sql id="COMMON_CONDITION">
      <if test="status != null and status != ''">
       and a.status=#{status}
      </if>
      ........
  </sql>

<select id="aaa" resultMap="bbb" parameterType="java.util.Map" >

    select 
    <include refid="Base_Column_List" />
    from table_name a 
     where a.is_deleted='n'
    <include refid="COMMON_CONDITION" />
  </select>