mybatis 及 mysql 部分操作（模糊查询，批量插入）

1、模糊查询

 <select id="list" parameterType="com.SysDictTypeAReq"
            resultType="com.SysDictTypeAResp">
         select t.id as id,
               t.create_time as createTime,
               t.delete_time as deleteTime,
               t.mark as mark,
               t.versionnub as versionnub,
               t.last_modified_time as lastModifiedTime,
               t.creator_id as creatorId,
               t.creator_name as creatorName,
               t.editor_id as editorId,
               t.editor_name as editorName,
               t.dict_code as dictCode,
               t.dict_name as dictName,
               t.parent_code as parentCode,
               t.edit_flag as editFlag,
               t.dict_type as dictType,
               t.dict_remark as dictRemark
        from sys_dict_type t
        where t.mark != 0
        <if test="dictType != null and dictType !=''" >
            and  t.dict_type = #{dictType}
        </if>
        <if test="dictCode != null and dictCode !=''" >
            and t.dict_code like concat('%', #{dictCode},'%')
        </if>
        <if test="dictName != null and dictName !=''" >
            and t.dict_name like concat('%', #{dictName},'%')
        </if>
        <if test="dictRemark != null and dictRemark !=''" >
            and t.dict_remark like concat('%', #{dictRemark},'%')
        </if>
        order by t.create_time desc
    </select>

2、批量插入

    <insert id="insertBatch">
        <foreach collection="list" item="entity" separator=";">
            insert into supplier_info
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="entity.creatorId!=null">creator_id,</if>
                <if test="entity.creatorName!=null">creator_name,</if>
                <if test="entity.creatorId!=null">editor_id,</if>
                <if test="entity.creatorName!=null">editor_name,</if>
                <if test="entity.supplierName!=null">supplier_name,</if>
                <if test="entity.supplierCode!=null">supplier_code,</if>
                <if test="entity.province!=null">province,</if>
                <if test="entity.city!=null">city,</if>
            </trim>
            values
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="entity.creatorId!=null">#{entity.creatorId},</if>
                <if test="entity.creatorName!=null">#{entity.creatorName},</if>
                <if test="entity.creatorId!=null">#{entity.creatorId},</if>
                <if test="entity.creatorName!=null">#{entity.creatorName},</if>
                <if test="entity.supplierName!=null">#{entity.supplierName},</if>
                <if test="entity.supplierCode!=null">#{entity.supplierCode},</if>
                <if test="entity.province!=null">#{entity.province},</if>
                <if test="entity.city!=null">#{entity.city},</if>
            </trim>
        </foreach>
    </insert>

3、批量更新

<update id="updateBatch">
    <foreach collection="list" item="entity" separator=";">
        update supplier_info
        <trim prefix="set" suffixOverrides=",">
            editor_id=#{entity.creatorId},
            editor_name=#{entity.creatorName},
            <if test="entity.supplierName!=null">supplier_name=#{entity.supplierName},</if>
            <if test="entity.province!=null">province=#{entity.province},</if>
            <if test="entity.city!=null">city=#{entity.city},</if>
        </trim>
    </foreach>
</update>

3、分页查询 pageHelper pageInfo

方式一：PageHelper.startPage(request.getPage(), request.getSize()).doSelectPageInfo(）

/**
 * 分页查询打印机信息列表
 * @param request
 * @return
 */
public CommonListPageAResp<PrinterInfoListAResp> listPrinterInfo(PrinterInfoListAReq request) {
    CommonListPageAResp<PrinterInfoListAResp> commonListPageAResp = new CommonListPageAResp<>();
    PageInfo<PrinterInfoListAResp> pageInfo = PageHelper.startPage(request.getPage(), request.getSize()).doSelectPageInfo(() -> {
        printerInfoDao.listPrinterInfo(request);
    });
    commonListPageAResp.setPage(request.getPage());
    commonListPageAResp.setSize(request.getSize());
    commonListPageAResp.setCount((int)pageInfo.getTotal());
    commonListPageAResp.setItems(pageInfo.getList());
    return commonListPageAResp;
}

方式二：PageHelper + PageInfo
PageHelper.startPage(req.getPage(), req.getSize());
PageInfo pageInfo = new PageInfo<>(list);

  public CommonListPageAResp<SupplierInfoListResp> supplierInfoListGet(SupplierInfoQueryAReq req) {
        if (!org.springframework.util.ObjectUtils.isEmpty(req.getPage()) && !org.springframework.util.ObjectUtils.isEmpty(req.getSize())) {
            PageHelper.startPage(req.getPage(), req.getSize());
        }
        List<SupplierInfoListResp> list = supplierInfoDao.getSupplierInfoByQuery(req);
        PageInfo<SupplierInfoListResp> pageInfo = new PageInfo<>(list);
        pageInfo.setTotal(pageInfo.getTotal());
        pageInfo.setList(list);
        pageInfo.setPageSize(req.getSize());
        pageInfo.setPageNum(req.getPage());
        return PageUtils.resultPage(pageInfo);
    }

4、批量查询

<select id="getSupplierInfoByCodeList"
        resultType="com.SupplierInfoListResp">
    select
    s.supplier_code as supplierCode,
    s.supplier_name as supplierName,
    s.province,
    s.city,
    s.creator_id as creatorId,
    s.creator_name as creatorName,
    s.editor_id as editorId,
    s.editor_name as editorName,
    s.create_time as createTime,
    s.last_modified_time as lastModifiedTime
    FROM
    supplier_info s
    where
    s.supplier_code in
      <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
         #{item}
      </foreach>
    order by create_time desc
</select>

5、连表更新

<update id="updateBoxNo">
    UPDATE shipment_box sb
        left join shipment_box_detail sbd
        on sbd.box_no = sb.box_no
    SET sb.box_no  = #{newBoxNo},
        sbd.box_no = #{newBoxNo},
        sb.editor_id=#{req.operatorId},
        sb.editor_name=#{req.operatorName},
        sbd.editor_id=sb.editor_id,
        sbd.editor_name=sb.editor_name
    WHERE sb.box_no = #{req.boxNo}
      and sb.mark > 0
</update>

6、截取带字母的字符串 数字部分最大值

SX00012321

<select id="getMaxBoxNo" resultType="java.lang.Integer">
    SELECT IFNULL(right(max(box_no), 5), 0)
    FROM shipment_box
    WHERE box_no LIKE concat(
            '%', #{boxNoPrev}, '%')
</select>