多表关联查询 CategoryVO是中包含SubCategoryVO的集合

因为 CategoryVO中属性值包含集合，定义resultmap中使用到了collection
1.实体类定义

public class CategoryVO {private Integer id;private String name;// 三级分类 VO Listprivate List<SubCategoryVO> subCatList;}

2.对应xml

    <resultMap id="myCategoryVO" type="com.luyuze.pojo.vo.CategoryVO"><id column="id" property="id" /><result column="name" property="name" /><collection property="subCatList" ofType="com.luyuze.pojo.vo.SubCategoryVO"><id column="subId" property="subId" /><result column="subName" property="subName" /></collection></resultMap><select id="getSubCatList" resultMap="myCategoryVO" parameterType="int">selectf.id as id,f.name as name,c.id as subId,c.name as subName,fromcategory fleft joincategory conf.id = c.father_idwheref.father_id = #{rootCatId}</select>

resultmap中入参是map

    <select id="getSixNewItemsLazy" resultMap="myNewItemsVO" parameterType="Map">SELECTf.id as rootCatId,f.`name` as rootCatName,FROMcategory fWHEREf.type = 1ANDi.root_cat_id = #{paramsMap.rootCatId}</select>

存在有些where条件可有可无情况

定义resultmap中使用到了if

    <select id="queryItemComments" parameterType="Map" resultType="com.luyuze.pojo.vo.ItemCommentVO" >SELECTic.comment_level as commentLevel,ic.content as content,FROMitems_comments icWHEREic.item_id = #{paramsMap.itemId}<if test="paramsMap.level != null and paramsMap.level != ''">AND ic.comment_level = #{paramsMap.level}</if></select>

存在sql主体是一致的，where排序条件有多种情况之间选择其一

比如说商品列表查询，可以通过价格多少排序，可以通过订单量多少排序…

    <select id="searchItems" parameterType="Map" resultType="com.luyuze.pojo.vo.SearchItemsVO">SELECTi.id as itemId,i.item_name as itemName,FROMitems iWHEREii.is_main = 1ORDER BY<choose><when test="paramsMap.sort == &quot;c&quot;">i.sell_counts DESC</when><when test="paramsMap.sort == &quot;p&quot;">i.priceDiscount ASC</when><otherwise>i.item_name ASC</otherwise></choose></select>