假设有Product、Product Img表
public class Product {
Long id;
String name;
}
public class ProductImg {
Long id;
Long productId;
String url;
}
- 基本<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>
- 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>
-
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>
-
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>
-
<if>中数组/集合的判断
#集合 <if test="arr != null and arr.size() > 0"> #数组 <if test="col != null and col .length > 0">
-
使用<foreach>
select * from product where name in <foreach collection="arr" item="item" open="(" separator="," close=")"> #{item} </foreach>
- 使用<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>