mybatis应用

#{}和${}的区别？
#将传入的数据都当成了一个字符串，会自动对传入的数据加一个双引号("")。
#方式能够防止sql注入。
$将传入的数据直接生成在sql中。
$方式无法防止sql注入。
$方式一般用于传入数据库对象，例如传入表格。

特殊符号

< 		&lt; 
<=      &lt;=
>       &gt; 
>=      &gt;=
&       &amp;
'       &apos;
"       &quot;

case where的使用

//part 为ispick为1时，过滤userid相同的值的统计
select count(DISTINCT userid) uv, count(DISTINCT userid, userid and CASE WHEN ispick=1 THEN userid END) part from ap_activityhallowmasstep where dictid =#{dictid} 

表中值为null

//如果表中的值可能为null，要去除值为a的话，必须使用ifnull函数
IFNULL(freeze_state,'') != 'a'

if标签值比较

<if test="ca.dealState=='1'.toString()"></if>
不能这样写：<if test="ca.dealState=='1'"></if>

如果你有类似于String str =“A”; 这样的写法时，你要小心了。
因为单引号内如果为单个字符时，OGNL将会识别为Java 中的 char类型，显然String 类型与char类型做==运算会返回false。

参数为数字类型（int）等

id=0时，无法进去if内
<if test="id!= null and id!= ''"></if>

id=0时，进去if内
<if test="id != null"></if>

对象是Number类型，当传值为0时会被解析成false，否则为true，浮点型0.00也是如此。
所以这里直接解析成false。只有String类型才需要判断是否 !=''，其他类型完全没有这个必要。

where标签
当遇到多个查询条件，使用where 1=1 可以很方便的解决我们的问题，但是这样很可能会造成非常大的性能损失，因为添加了 “where 1=1 ”的过滤条件之后，数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（即全表扫描） 以比较此行是否满足过滤条件，当表中的数据量较大时查询速度会非常慢；此外，还会存在SQL 注入的风险。

<!--正例-->
<select id="queryBookInfo" parameterType="com.tjt.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_rule_BookInfo t
<where>
<if test="title !=null and title !='' ">
 title = #{title} 
</if>
<if test="author !=null and author !='' "> 
 AND author = #{author}
</if>
</where> 
</select>

<!--反例-->
<select id="queryBookInfo" parameterType="com.tjt.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_rule_BookInfo t where 1=1
<if test="title !=null and title !='' ">
 AND title = #{title} 
</if> 
<if test="author !=null and author !='' ">
 AND author = #{author}
</if> 
</select>

动态sql

通过trim标签用()包裹，以及自动忽略尾部的逗号。

delete from admin where aid in #{aid} 相应的语句为：delete from admin where aid into(…)。foreach会将数组转为(aid,aid,aid)的形式。

select * from user

AND name=#{name}
AND gender=#{gender}
假如说name和gender的值都不为null的话打印的SQL为：select * from user where name = ‘xx’ and gender = ‘xx’

prefix：前缀　　　　　　
prefixoverride：去掉第一个and或者是or。
suffix：后缀
suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

resultMap 标签的collection 里为list集合，属性名为numberCases

<resultMap type="com.iflytek.atp.domain.DocumentTaskHallDO" id="documentInfo">
       <result column="id" property="id" jdbcType="VARCHAR" />
       <result column="orderNo" property="orderNo" jdbcType="VARCHAR" />
       <result column="name" property="name" jdbcType="VARCHAR" />
       <collection property="numberCases" ofType="com.iflytek.atp.domain.DocumentRecruiteCasesDO">
           <result property="id" column="l_id"/>
           <result property="aId" column="a_id"/>
           <result property="type" column="l_type"/>
       </collection>
   </resultMap>

SELECT
           b.id,
           b.order_no orderNo,
           b.name,
           l.id l_id,
           l.type l_type
       FROM t_recruit_link_info l 
       inner JOIN t_recruit_base_info b ON b.id = l.recruit_id 
       where b.id= #{baseId} 

批量更新

<update id="updateInterCloseByList">
        UPDATE t_inter_task t
        SET t.last_edit_user = #{orderDto.lastEditUser}
        WHERE 1=1 
        <if test="orderDto.orderNoList!=null">
            and t.order_no in
            <foreach collection="orderDto.orderNoList" index="index" item="orderId" open="(" separator="," close=")">
                #{orderId}
            </foreach>
        </if>
    </update>

<update id="updateInvoiceMoney" parameterType="java.util.List">
    update t_crm_order_invoice 
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="order_money =case" suffix="end,">  
            <foreach collection="list" item="item">  
                <if test="item.orderMoney!=null">  
                    when order_id=#{item.orderId} then #{item.orderMoney}  
                </if>  
            </foreach>  
        </trim>
        <trim prefix="invoice_money =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                 when order_id=#{item.orderId} then #{item.invoiceMoney}
            </foreach>
        </trim>
    </trim>
    where order_id in 
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.orderId}
    </foreach>
    and del_state ='0' 
</update>

<update id="updateInvoiceMoney" parameterType="java.util.List">
    update t_crm_order_invoice 
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="order_money =case" suffix="end,">  
            <foreach collection="list" item="item">  
                <if test="item.orderMoney!=null">  
                    when order_id=#{item.orderId} then #{item.orderMoney}  
                </if>  
            </foreach>  
        </trim>
        <trim prefix="invoice_money =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                 when order_id=#{item.orderId} then #{item.invoiceMoney}
            </foreach>
        </trim>
    </trim>
    where del_state ='0' and 
    <foreach collection="list" item="item" open="( " separator=") or (" close=" )">
        order_id = #{item.orderId} AND invoice_id = #{item.invoiceId}
    </foreach>
  </update>

批量插入

<insert id="saveRecruitApplication" parameterType="java.util.List" >
	insert into t_recruit (
		id, 
		user_id, 
		create_time
	 )values 
	<foreach collection ="list" item="reddemCode" index= "index" separator =",">
		(
			#{reddemCode.id}, 
			#{reddemCode.userId},
			now()
		)
        </foreach >
</insert>

association（一对一）和collection（一对多）

<resultMap type="com.iflytek.atp.domain.OrderDO" id="UserOrders">
    <result column="id" property="id" jdbcType="VARCHAR" />
    <result column="orderNo" property="orderNo" jdbcType="VARCHAR" />
    <result column="docName" property="docName" jdbcType="VARCHAR"/>
    <association property="orderFast" javaType="com.iflytek.atp.domain.OrderFastDO">
        <id property="id" column="f_id"/>
        <result property="orderNo" column="f_order_no"/>
        <result property="wordNum" column="f_word_num"/>
    </association>
    <collection property="orderDocumentList" ofType="com.iflytek.atp.domain.OrderDocumentDO">
        <id property="id" column="d_id"/>
        <result property="unitNum" column="d_unit_num"/>
        <result property="unit" column="d_unit"/>
        <collection property="formatList" ofType="com.iflytek.atp.domain.OrderDocumentFormatDO">
            <id property="id" column="d_df_id"/>
            <result property="attaName" column="d_df_atta_name"/>
            <result property="num" column="d_df_num"/>
        </collection>
    </collection>
</resultMap>

<select id="listUserOrders" resultMap="UserOrders">
  SELECT
      b.id id,
      b.order_no orderNo,
      b.doc_name docName,
      f.id f_id,
      f.order_no f_order_no,
      f.word_num f_word_num,
      d.id d_id,
      d.order_no d_order_no,
      d.unit_num d_unit_num,
      d.unit d_unit,
      df.id d_df_id,
      df.atta_name d_df_atta_name,
      df.num d_df_num
  FROM
      t_order_base b
  LEFT JOIN t_order_fast f ON b.order_no = f.order_no
  LEFT JOIN t_flow_node n ON b.node_code = n.code
  LEFT JOIN t_order_document d ON (b.order_no = d.p_order_no or b.order_no = d.order_no)
  LEFT JOIN t_order_document_format df ON df.order_no = d.order_no
  INNER JOIN (
      SELECT
          id
      FROM
          t_order_base
      <include refid="listUserOrdersCondition"/>
      ORDER BY
          create_time DESC
      LIMIT #{start,jdbcType=BIGINT},#{pageSize,jdbcType=BIGINT}
  ) page ON b.id = page.id  ORDER BY b.last_edit_time DESC
</select>

当查询的入参为多个，且包含list对象时，建议如下写法:

//dao查询入参
List<FastTaskHallDO> selectHallFast(@Param("accountId")String accountId, @Param("list")List<DocumentInterLanguageToDO> list);

//sql
SELECT 
h.id,DATE_ADD(b.pay_time,INTERVAL f.shortest_time MINUTE) AS finish_time_new ,f.note
FROM t_inter_hall_fast h
INNER JOIN t_order_fast f ON h.order_no = f.order_no AND f.del_state = '1'
INNER JOIN t_order_base b ON h.order_no = b.order_no AND b.del_state = '1'
WHERE h.receive_state = '0' AND h.del_state = '1' AND b.order_type = '1'
AND NOT EXISTS (
	AND t.create_user = #{accountId}
)
AND b.finish_time_new > NOW() 
AND (
	<foreach collection="list" index="index" item="item" separator=" or ">  
		(f.order_language =#{item.sourceLanguage} AND f.target_language =#{item.targetLanguage})
	</foreach>
)
ORDER BY b.finish_time_new DESC