【mybatis】mybatis中放置IN查询拼接sql过长，IN查询进行分批次查询的处理

需要使用的切割list集合的工具类，链接：https://www.cnblogs.com/sxdcgaq8080/p/9376947.html

处理逻辑，原本的一个LIst，进行切割，循环进行mybatis查询即可

举个例子：

mapper.xml中方法查询如下：

 <select

            id="dealFindByNameAndMobile"

            parameterType="com.pisen.cloud.luna.ms.dealer.base.mapper.bean.DealerBean"

            resultType="com.pisen.cloud.luna.ms.dealer.base.domain.Dealer">

        SELECT

        dea.id id,

        dea.uid uid,

        dea.enabled_flag enabledFlag,

        dea.delete_flag deleteFlag,

        dea.tenement_id tenementId,

        dea.parent_id parentId,

        dea.name name,

        dea.type type,

        dea.bar_code barCode,

        dea.outer_code outerCode,

        dea.outer_id outerId,

        dea.mne_code  mneCode,

        dea.address address,

        dea.address_xy addressXy,

        dea.business_area businessArea,

        dea.business_area_xy businessAreaXy

        FROM

        dealer AS dea

        LEFT JOIN  (SELECT a.* FROM contact AS a where  a.main_contact = ${@com.pisen.cloud.luna.ms.dealer.base.domain.Contact@IS_MAIN} AND a.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO} AND a.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}) con ON dea.uid = con.dealer_id

        WHERE

        dea.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO}

        AND

        dea.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}

        <if test="name != null and name != '' ">

            AND

            dea.name LIKE '%' #{name} '%'

        </if>

        AND

        dea.uid IN

        <foreach collection="uidList" open="(" close=")" separator="," item="item" index="index">

            #{item}

        </foreach>

对应mapper.java中方法也是同名方法：

List<Dealer> dealFindByNameAndMobile(DealerBean bean);

这个DealerBean呢则是为了查询专门封装的一个java类

import java.util.List;

public class DealerBean {

    private  String name;

    private String mobile;

    private List<String> uidList;

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getMobile() {

        return mobile;

    }

    public void setMobile(String mobile) {

        this.mobile = mobile;

    }

    public List<String> getUidList() {

        return uidList;

    }

    public void setUidList(List<String> uidList) {

        this.uidList = uidList;

    }

}

在serviceImpl中调用这个mapper的方法处，进行的分批次切割处理：【此处调用的工具类的方法，请查看上面链接处】

@Override

    public List<Dealer> findDealerList(DealerBean bean) {

        List<String> list = bean.getUidList();

        List<List<String>> result = ListUtils.splitListBycapacity(list,100);

        List<Dealer> allDealer = new ArrayList<>();

        for (List<String> strings : result) {

            bean.setUidList(strings);

            allDealer.addAll(dealerMapper.dealFindByNameAndMobile(bean));

        }

        return allDealer;

    }

这样即可完成IN在mybatis中的分批次查询

巴特西

【mybatis】mybatis中放置IN查询拼接sql过长，IN查询进行分批次查询的处理

最新文章

热门文章