mybatis实战记录（

1.参数是一个对象

根据对传的属性作为where条件

List<Student> queryDetailDefault(Student student);

//对像结构
@data
public class Student{
 private<PP> attributeList；
}
@data
public class PP{
private string attributeName;
private string attributeValue;
}

//补充一个方法，驼峰转下划线
	/**
     * 将驼峰转换成下划线形式
     *
     * @param str
     * @return
     */
    public static String humpToLine(String str) {
        Pattern humpPattern = Pattern.compile("[A-Z]");
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

<select id="queryDetailDefault" resultMap="BaseResultMap" parameterType="Student">
         select *
    from table
    <where>
      is_deleted = 0
      <if test="attributeList != null  and attributeList.size >0">
        and
        <foreach collection="attributeList" item="item" separator="and" >
           ${item.attributeName} = #{item.attributeValue}
        </foreach>
      </if>
    </where>
  </select>

注意：mybatis还支持foreach嵌套，具体用法如下

<select id="queryCarModelDetailByAttributeList" resultMap="BaseResultMap" parameterType="PP">
    select *
    from table
    <where>
      status = 0
      <if test="vehicleAttributeList != null  and vehicleAttributeList.size >0">

        <foreach collection="vehicleAttributeList" item="item" separator="and" >
          and
          <if test="item.attributeValueList != null  and item.attributeValueList.size >0">
             ${item.dbName} in
            <foreach collection="item.attributeValueList" close=")" open="(" item="value" separator=",">
              #{value}
            </foreach>

          </if>>

        </foreach>
      </if>
    </where>
  </select>

2.传参是基本类型

示例：根据传的参数作为条件和出参

List<String> queryAttributeValues(@Param("list") List<PP> list,
                                  @Param("attributeName") String attributeName);

 <-- $表示字符串没有引号的 #有  where条件后面用#防止sql注入-->
 <select id="queryAttributeValues" resultType="java.lang.String">
    select distinct(${attributeName})
    from table
    <where>
      is_deleted = 0
      <if test="list != null  and list.size >0">
        and
        <foreach collection="list" item="item"  separator="and" >
           ${item.attributeName} = #{item.attributeValue}
        </foreach>
      </if>
    </where>
  </select>

3.高级用法，出参的对象有一个List的属性

java代码：

/**
 * @describe:
 * @author:zqm
 */
@Data
public class CategoryPo extends Serializable {
    private static final long serialVersionUID = 8316734300642006761L;

    private String level;
    private int levelId
    /**
    ** 详细
    **/
    private List<CategoryDetailPo> children;
}
@Data
public class CategoryDetailPo extends Po {

    private static final long serialVersionUID = -5031122553758553868L;
    /**
     * 位置名称
     */
    private String name;

    /**
     * 安装数量
     */
    private int num;

    /**
     * 状态 为0是，表示有数量
     */
    private int status;

    /**
     * 位置id
     */
    private int id;
}

//Mapper文件：

 * @describe:
 * @author:zqm
 */
public interface TdCategoryReadMapper {

    List<CategoryPo> queryThirdCategoryDetail();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.category.TdCategoryReadMapper" >



    <resultMap id="CategoryDetailMap" type="com.category.CategoryPo">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="levelId" />
        <result column="category_name" jdbcType="VARCHAR" property="level" />
        <!--collection 属性名表示集合名，column表示属性和上级的对应关系，可以有多个参数，需要{}包起来 -- >
       <collection property="children" select = "selectPosition" column="categoryId =id ">
       </collection>

    </resultMap>


    <resultMap id="positionDetail" type="com.CategoryDetailPo">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="position_name" jdbcType="VARCHAR" property="name" />
        <result column="position_amount_type" javaType="INTEGER" property="num" />
        <result column="position_amount" javaType="INTEGER" property="status"/>
<!--        <result column="unit" javaType="VARCHAR" property="num"/> -->

    </resultMap>

  <select id="queryThirdCategoryDetail" resultMap="CategoryDetailMap">
    select
    a.id,
    a.category_name
	from
	td_category a, td_category_position b
	where a.id = b.category_id and
	a.sort =3
	GROUP BY a.id
	ORDER BY b.sort
  </select>

   <select id= "selectPosition" resultMap="positionDetail">
    select
    id,
    position_name ,
    position_amount_type ,
	position_amount
-- 	b.unit as unit
   from td_category_position
   where category_id = #{categoryId}
   </select>


</mapper>

结果：

[
    {
        "children": [
            {
                "id": 1,
                "name": "前刹",
                "num": 0,
                "status": 2
            },
            {
                "id": 2,
                "name": "后刹",
                "num": 0,
                "status": 2
            }
        ],
        "level": "刹车片",
        "levelId": 1
    }
]

4.高级用法2，对象有list出参

已组id区分分组，相同的组合并到子集中去

@Getter
@Setter
public class Vo  implements java.io.Serializable  {

    private static final long serialVersionUID = -741817625697971466L;

    private Integer id;
    private Integer groupId;
    private List<DetaillVo> detailList;
}

@Data
public class DetaillVo implements java.io.Serializable {

    private static final long serialVersionUID = -6920934492324729614L;

   private Integer   id;
   private Integer   detailId;
   private Integer   attributeId;
   private String   attributeName;
   private Integer   attributeValueId;
   private String   attributeValue;


}

//mapper
List<Vo> getList(@Param("partAttributeList") List<PartAttributeIdSo> partAttributeList,
                                                   @Param("positionId") Integer positionId,
                                                   @Param("attributes") List<List<CommonAttribute>> attributes,
                                                   @Param("size")Integer size);

 <resultMap id="DetailMap" type="com.zqm.Vo">
    <result column="group_id" jdbcType="INTEGER" property="groupId"/>
    <result column="id" jdbcType="INTEGER" property="id" />
    <collection property="detailList" ofType="com.zqm,DetaillVo">
      <result column="detail_id" jdbcType="INTEGER" property="detailId" />
      <result column="attribute_id" jdbcType="INTEGER" property="attributeId" />
      <result column="attribute_name" jdbcType="VARCHAR" property="attributeName" />
      <result column="attribute_value_id" jdbcType="INTEGER" property="attributeValueId" />
      <result column="attribute_value" jdbcType="VARCHAR" property="attributeValue" />
    </collection>
  </resultMap>
 <select id="getList" resultMap="DetailMap" >
    select attribute_id ,
    attribute_name ,
    attribute_value_id ,
    attribute_value ,
    group_id
    from table1 
    join table2  on table1.detail_id = table2.detail_id
    where 1=1 
    <if test="partAttributeList != null and partAttributeList.size > 0">
     and (attribute_id,attribute_value_id) in
      <foreach collection="partAttributeList" item="item" separator="," open="(" close=")">

     <if test="item.attributeValueId != null and item.attributeValueId != '' and item.attributeId != null and item.attributeId != ''">
       (#{item.attributeId},#{item.attributeValueId})
     </if>

      </foreach>
    </if>
    and group_id in (....)
    group by table1.detail_id
    <if test="size > 0">
      having count(*) = #{size}
    </if>

  </select>```

`