Mybatis例子

1.批处理的操作，操作的时候需要设置连接信息带上参数：allowMultiQueries=true

<!--批量处理的操作:更新用户的属性内容-->
    <insert id="batchUpdateUserInfoData">
        <foreach collection="userIdList" item="userid" index="index" open="" close="" separator=";">
            UPDATE `${tableName}`
            SET
            <foreach item="value" index="key" collection="fieldObject.entrySet()" separator =",">
                `${key}` =
                <if test="value != null" >
                    #{value}
                </if>
                <if test="value == null" >
                    ''
                </if>
            </foreach>
            WHERE
            userid = #{userid}
        </foreach>
    </insert>

2.返回的JSONArray的结果集：

JSONArray selectPrizeGroupStatusByPrizeIdList(@Param("tableName") String tableName,@Param("prizeIdList") List prizeIdList);

xml的书写：

    <select id="selectPrizeGroupStatusByPrizeIdList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
          COUNT(*) as count,status,prize_id as prizeId
        FROM `${tableName}`
        WHERE
        1 = 1
        <if test="prizeIdList != null" >
          AND prize_id IN
            (
            <foreach collection="prizeIdList" item="onePrizeId" index="index" separator=",">
                ${onePrizeId}
            </foreach>
            )
        </if>
        GROUP BY status,prize_id
    </select>

 

 

 

3.List的查询操作：

<select id="xxDateToCount" resultType="com.alibaba.fastjson.JSONObject">
      SELECT
          `log_date` days,COUNT(id) numCount,type
      FROM `${tableName}`
      WHERE 1 =1
      <if test="typeList != null" >
          AND TYPE IN
          (
          <foreach collection="typeList" item="oneType" index="index" separator=",">
              ${oneType}
          </foreach>
          )
      </if>
      <if test="activityId != null" >
          AND activity_id = #{activityId,jdbcType=INTEGER}
      </if>
      <if test="channel != null and channel !=''" >
        AND channel = #{channel,jdbcType=VARCHAR}
      </if>
      <if test="startDate != null" >
        AND `log_date` <![CDATA[ >= ]]> #{startDate,jdbcType=VARCHAR}
      </if>
      <if test="endDate != null" >
        AND `log_date` <![CDATA[ <= ]]> #{endDate,jdbcType=VARCHAR}
      </if>
      GROUP BY days,TYPE
  </select>

相应的目标方法名称：

JSONArray xxDateToCount(@Param("tableName") String tableName,
                                              @Param("channel") String channel,
                                              @Param("startDate") Date startDate,
                                              @Param("endDate") Date endDate,
                                              @Param("activityId") Integer activityId,
                                              @Param("typeList") List<Integer> typeList);

4.batch批处理的操作：

<insert id ="insertMoreBatchDo">
    insert into `${tableName}`
    (
    `node_id`,`userid`,`num`,`status`,`custom_data`,`message`,
    <if test='type == "A"' >
      `os`,
    </if>
    <if test='type == "B"' >
      `fee`,
    </if>
    `created_at`,`updated_at`
    )
    values
    <foreach collection ="list" item="record" index= "index" separator =",">
      (
        #{record.nodeId,jdbcType=VARCHAR},
        #{record.userid,jdbcType=VARCHAR},
        #{record.num,jdbcType=VARCHAR},
        #{record.status,jdbcType=INTEGER},
        #{record.customData,jdbcType=LONGVARCHAR},
        #{record.message,jdbcType=VARCHAR},
        <if test='type == "A"' >
          <choose>
            <when test='record.os != null'>
              #{record.os,jdbcType=VARCHAR},
            </when>
            <otherwise>
              "",
            </otherwise>
          </choose>
        </if>
        <if test='type == "B"' >
          <choose>
            <when test='record.os != null'>
              #{record.fee,jdbcType=INTEGER},
            </when>
            <otherwise>
              '0',
            </otherwise>
          </choose>
        </if>
        #{record.createdAt,jdbcType=TIMESTAMP},
        #{record.updatedAt,jdbcType=TIMESTAMP}
      )
    </foreach >
  </insert>



Mapper方法：
int insertMoreBatchDo(@Param("tableName") String tableName, @Param("type") String type, @Param("list") List<Bean> list);

 

5.left join + 分页查询的操作( like 用bind标签来进行组拼)：

<resultMap id="prizeDTO" type="com.xxxx.PrizeDTO" extends="BaseResultMap" >
        <result column="stock" property="stock" jdbcType="INTEGER" />
        <result column="used" property="used" jdbcType="INTEGER" />
        <result column="owned" property="owned" jdbcType="BIT" />
</resultMap>

<select id="selectByList"  resultMap="prizeDTO">
        SELECT
          <include refid="prize_column" />
          ,
        d.app_id,d.merchant_id,d.used,d.stock,d.owned
        FROM prize_distributes d LEFT JOIN prizes p
        ON p.id=d.prize_id
        WHERE d.app_id=#{appId,jdbcType=INTEGER}
        AND d.merchant_id=#{merchantId,jdbcType=INTEGER}
        AND p.deleted_at is NULL
        <if test="status != null and status == 'valid'" >
            AND
            (p.from_date <![CDATA[ <= ]]>  now()
            AND p.to_date <![CDATA[ >= ]]> now()
            OR (p.from_date IS NULL AND p.to_date IS NULL))
        </if>
        <if test="status != null and status == 'expired'" >
            AND p.to_date <![CDATA[ < ]]> now()
        </if>
        <if test="owned != null" >
            AND d.owned = #{owned}
        </if>
        <if test="type != null and type !=''" >
            AND p.type = #{type}
        </if>
        <if test="name != null and name !=''" >
            <bind name="pattern" value="'%'+name+'%'"/>
            AND p.name LIKE  #{pattern}
        </if>
        ORDER BY p.created_at DESC
    </select>

使用pageHelper插件来进行操作:

 PageHelper.startPage(page, pageSize);
 List<PrizeDTO> list = prizeMapper.selectByList(appId, merchantId, owValue, type, status, name);