MyBatis mapper.xml用法

假设有Product、Product Img表

public class Product {
    Long id;
    String name;
}

public class ProductImg {
    Long id;
    Long productId;
    String url;
}

1. 基本<resultMap>

   <resultMap id="BaseResultMap" type="com.zyd.shiro.persistence.beans.Product">
       <id column="id" jdbcType="INTEGER" property="id"/>
       <result column="name" jdbcType="VARCHAR" property="name"/>
   </resultMap>

2. Product中List<ProductImg>

   public class Product {
       Long id;
       String name;
       List<ProductImg> imgs;
   }

   <resultMap id="resultMapper1" type="com.zyd.shiro.persistence.beans.Product">
       <id column="id" jdbcType="INTEGER" property="id"/>
       <result column="name" jdbcType="VARCHAR" property="name"/>
   
       <collection property="imgs" column="img_id" javaType="ArrayList" ofType="java.lang.com.zyd.shiro.persistence.beans.ProductImg">
           <result property="id" jdbcType="BIGINT" column="img_id"/>
           <result property="url" jdbcType="VARCHAR" column="img_url"/>
       </collection>
   </resultMap>
   
   <!--对应的SQL-->
   <select id="xxx" resultMap="resultMapper1">
       select p.*,
              pi.id  img_id,
              pi.url img_url
       from product p
                left join product_img pi on p.id = pi.product_id
   </select>

3. Product中List<String>

   public class Product {
       Long id;
       String name;
       List<String> imgs;
   }

   <resultMap id="resultMapper1" type="com.zyd.shiro.persistence.beans.Product">
       <id column="id" jdbcType="INTEGER" property="id"/>
       <result column="name" jdbcType="VARCHAR" property="name"/>
   
       <collection property="imgs" ofType="java.lang.String" javaType="ArrayList">
           <result column="img_url" jdbcType="VARCHAR"/>
       </collection>
   </resultMap>
   
   <!--对应的SQL-->
   <select id="xxx" resultMap="resultMapper1">
       select p.*,
              pi.id  img_id,
              pi.url img_url
       from product p
                left join product_img pi on p.id = pi.product_id
   </select>

4. insert动态sql

   <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.zyd.shiro.persistence.beans.Product" useGeneratedKeys="true">
       insert into product
       <trim prefix="(" suffix=")" suffixOverrides=",">
           <if test="name != null">
               `name`,
           </if>
       </trim>
       <trim prefix="values (" suffix=")" suffixOverrides=",">
           <if test="name != null">
               #{name,jdbcType=VARCHAR},
           </if>
       </trim>
   </insert>

5. <if>中数组/集合的判断

   #集合
   <if test="arr != null and arr.size() > 0">
   #数组
   <if test="col != null and col .length > 0">

6. 使用<foreach>
   

   select * from product where name in
   <foreach collection="arr" item="item" open="(" separator="," close=")">
       #{item}
   </foreach>

7. 使用<trim>
    

   <if test="statusList != null and statusList.size() > 0">
       <trim prefix="(" suffix=")" prefixOverrides="and|or">
           <if test="statusList.contains('Indemand')">
               is_indemand = 1
           </if>
           <if test="statusList.contains('正在众筹')">
               or close_date > now()
           </if>
           <if test="statusList.contains('已结束')">
               or now() > close_date and is_indemand = 0
           </if>
       </trim>
   </if>