mysql多条件批量查询，且数据量大分批查询

一，查询入参

@Data
public class ReqSupplierOrderCycleVO implements Serializable {@ApiModelProperty("供应商编码")private String supplierCode;@ApiModelProperty("地点编码")private String locationCode;@ApiModelProperty("大类编码")private String bigCategoryCode;
}

二，分批处理

service接口及对参数分批查询200条，查询一次

R<List<ResSupplierOrderCycleVO>> getListParameterOrderCycleByVOs(List<ReqSupplierOrderCycleVO> vos);
分批
List<List<ParameterOrderCycleDto>> batches = Lists.partition(queryDtos, BizConstant.PROD_BATCH_SIZE);
List<ParameterOrderCycle> queryResult = parameterOrderCycleMapper.selectListParameterOrderCycle(batches);

mapper接口

List<ParameterOrderCycle> selectListParameterOrderCycle(@Param("dtos") List<List<ParameterOrderCycleDto>> dtos);

mapper实现

 <select id="selectListParameterOrderCycle" parameterType="java.util.List" resultMap="BaseResultMap"><foreach item="item" index="index" collection="dtos" separator="UNION ALL">(select<include refid="Base_Column_List"/>from parameter_order_cycle<where>(location_code, supplier_code, big_category_code) IN<foreach item="vo" index="index" collection="item" open="(" separator="," close=")">(#{vo.locationCode,jdbcType=VARCHAR}, #{vo.supplierCode,jdbcType=VARCHAR}, #{vo.bigCategoryCode,jdbcType=VARCHAR})</foreach>and submit_status = 1</where>)</foreach></select>

三，批量不分批实现

方式1

mapper实现

 (select<include refid="Base_Column_List"/>from parameter_order_cycle<where>(location_code, supplier_code, big_category_code) IN<foreach item="vo" index="index" collection="item" open="(" separator="," close=")">(#{vo.locationCode,jdbcType=VARCHAR}, #{vo.supplierCode,jdbcType=VARCHAR}, #{vo.bigCategoryCode,jdbcType=VARCHAR})</foreach>and submit_status = 1</where>)

sql打印

select*fromparameter_order_cycle WHERE(location_code, supplier_code, big_category_code) IN (('W001', '200174', '') , ('W001', '200291', '') , ('W001', '200174', '1237') , ('W001', '200291', '1230') ) and submit_status = 1 
)

方式2

List<ParameterProductShopSpace> selectByProductAndShopCode(@Param("condition") Collection<ParameterProductShopSpace> condition);

mapper实现

  <select id="selectByProductAndShopCode" resultType="com.yonghui.yh.rme.srm.ordercenter.dao.entity.ParameterProductShopSpace">SELECT * FROMparameter_product_shop_space<where><foreach collection="condition" separator="or" item="item">(shop_code = #{item.shopCode} and product_code= #{item.productCode})</foreach></where></select>

sql打印

 SELECT* FROMparameter_product_shop_space WHERE(shop_code = '9010' and product_code= '7') or (shop_code = '9010' a

 

 

创作打卡挑战赛赢取流量/现金/CSDN周边激励大奖