CRUD-SQL

前置

insertSelective和upsertSelective使用姿势

• 新增：insertSelective和upsertSelective

1、功能都是和手写sql的insert语句一样，都可以新增数据：未赋值的字段列，会使用default值赋值

普通的insert，未赋值的列如果是not null default ‘0’，则会报错

2、不同：如果表有唯一键，前者如果表中有了一条相同的数据，则插入报错，唯一键冲突；

后者，则选择的是on duplication key update即有相同的数据，则赋值了的字段也会被更新，未赋值的字段保持原值不变

• 更新：updateByExampleSelective（等效人为写update sql） 和 upsertSelective

1、前者就是update where xxx语句，是更新语句。二者都可以更新字段，更新都是未赋值的字段不更新，仅更新赋值的字段

手写sql，有两种方式

1、在AutoMapper的子接口Mapper下，通过注解的形式

2、在AutoMapper的子接口Mapper下，通过xml的形式。只不过需要自己创建一个对应的xml【xml中前置内容，可参考AutoGeneratorMapper.xml中前置内容】

<?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 // 省略
</mapper>

一、增

当导入的数据不存在时则进行添加，有则更新

https://www.cnblogs.com/better-farther-world2099/articles/11737376.html

  @Insert("insert into lock_max_stock\n" +"    (id, net_poi_id, sale_date, sku_id, lock_type, status, rdc_id)\n" +"    values\n" +"    (#{id,jdbcType=BIGINT}, #{netPoiId,jdbcType=BIGINT}, #{saleDate,jdbcType=VARCHAR}, #{skuId,jdbcType=BIGINT}, \n" +"    #{lockType,jdbcType=INTEGER}, #{status,jdbcType=INTEGER}, #{rdcId,jdbcType=BIGINT})\n" +"    on duplicate key update \n" +"    status = #{status,jdbcType=INTEGER} \n")int upsertByKey(LockMaxStockDO lockMaxStockDO);

1.1 唯一键，先查，后插

        //唯一键poiId+containerFlowNo+pickingTaskNo校验List<ContainerPickingTask> existContainerPickingTasks = containerPickingTaskRepository.queryRelatedPickingTaskList(poiId, containerFlowNo, pickingTaskNo);if (null != existContainerPickingTasks && existContainerPickingTasks.size() > 0) {log.warn("已存在容器与拣货任务绑定关系，无需重复绑定，containerCode={}, containerFlowNo={}, pickingTaskNo={}.",containerCode, containerFlowNo, pickingTaskNo);throw new BusinessException(BUSINESS_ERROR, "已存在拣货任务与容器绑定");}
//没有再insertreturn containerPickingTaskRepository.insertContainerPickingTask(newContainerPickingTask);

幂等键

Optional<RefundSupplierMessage> messageOptional = refundSupplierMessageRepository.queryRefundSupplierMessage(message.getPoiId(), message.getRefundBillNo(), message.getMessageType());if (messageOptional.isPresent()) {// 如果已经发送过消息，则不用再次发送log.info("sendSupplierMessage...已经发送过消息，message:{}", messageString);} else {// 如果没有发送过消息，则发送try {rdcNotifySupplierStatusProducer.send(messageString, message.getPoiId(), DimType.RDC, message.getSupplierId());refundSupplierMessageRepository.insertRefundSupplierMessage(buildRefundSupplierMessage(message));} catch (Exception e) {}}

1.2 批量插

1.2.1 批次一200、批次二200、批次三200，有一条数据写入失败了

批次一200、批次二200、批次三200
场景1：不catch异常public void batchInsert(List<TestTableDO> DOList) {if (CollectionUtils.isEmpty(DOList)) {return;}Lists.partition(DOList, 200).forEach(partitionData -> {this.testTableAutoGeneratorMapper.batchInsert(partitionData);});}
1、同一个批次，插入多条数据时，是原子性的，一条失败，这个批次200条都失败，不会写入表中  
2、不同批次之间，没有原子性，报错的批次 以及 其后面的批次，都不会写入。报错之前的批次数据可正常插入
批次一失败，批次一中200条数据都不会写入。且后续批次二、三都不会执行
批次一成功，会插入200条数据，批次二中有失败，则批次二全部写入失败，批次三不会执行补充：现在id = 1，唯一键冲突，下一条数据的id = 1 + batchSize即200 = 201场景2：catch吃掉异常public void batchInsertCatchException(List<TestTableDO> DOList) {if (CollectionUtils.isEmpty(DOList)) {return;}Lists.partition(DOList, 200).forEach(partitionData -> {//这里try的颗粒更小，在每个批次try。如果在最外层try了，批次1失败了，全部数据都会失败。后续的批次也不是执行了try {this.testTableAutoGeneratorMapper.batchInsert(partitionData);} catch (Exception e) {if (e instanceof DuplicateKeyException) {log.warn("测试数据,data:[{}]", GsonUtil.toJson(partitionData), e);} else {log.error("测试数据异常,data:[{}]", GsonUtil.toJson(partitionData), e);}}});1、同一个批次，插入多条数据时，是原子性的，一条失败，这个批次200条都失败，不会写入表中  
2、不同批次之间，没有原子性，只有报错的批次本身全部200条数据插入失败，其前面、后面批次均会写成功场景3：不catch异常 + 事务
@Service
public class TestService {@Resourceprivate ReturnSkuRepository returnSkuRepository;public void test(){//taskCode是唯一键TestTableDO d1 = TestTableDO.builder().taskCode("w5").poiId(323L).build();TestTableDO d2 = TestTableDO.builder().taskCode("w6").poiId(323L).build();TestTableDO d3 = TestTableDO.builder().taskCode("m3").poiId(323L).build();List<TestTableDO> testTableDOS = Lists.newArrayList(d1, d2, d3);returnSkuRepository.batchInsert(testTableDOS);//调用包含事务的方法batchInsert，不能和test方法在同一个类中，且batchInsert方法，必须是类的public方法}}public class ReturnSkuRepositoryImpl implement ReturnSkuRepository{@Resourceprivate TestTableAutoGeneratorMapper testTableAutoGeneratorMapper;@Override@Transactional(rollbackFor = DuplicateKeyException.class)public void batchInsert(List<TestTableDO> DOList) {if (CollectionUtils.isEmpty(DOList)) {return;}Lists.partition(DOList, 2).forEach(partitionData -> {this.testTableAutoGeneratorMapper.batchInsert(partitionData);});}
}
只要有一条数据插入失败，所有批次的所有数据，全部插入失败。即要么全成功，要么全失败补充：现在id = 1，唯一键冲突，下一条数据的id = 1 + 三个批次的全部数据-1 = 1+600 - 1 = 600SkuDO 和 OrderDO一起 批量插入时
思路：如果想保持Sku 和 Order的 全部数据要么都成功，要么都失败。那就二者包在一个事务方法中，同时二者本身又在一个单独的事务方法中

每个数据的某个、某几个字段都是一样的（操作日期、这些sku都是用一个网店下的sku）

public interface SellOutWarnSkuMapper extends SellOutWarnSkuAutoGeneratorMapper {@Insert({"<script>","insert into sellout_warn_sku","(operation_day, sale_day, lot_code, warn_type, sku_id, net_poi_id, net_poi_name," +"      poi_id, sku_name, sku_supplier_id, sku_supplier_name, sku_price, sku_shelf_type, " +"      sku_temperature_level, sku_category_id, sku_all_category, base_predict_quantity, " +"      or_predict_quantity, sales_volume, sales_rate, max_sale_num," +"      operator, is_sellout, is_second_delivery, status, ctime, create_by)","values ","<foreach collection='list' item='item' separator=','>","(#{item.operationDay,jdbcType=VARCHAR}, #{item.saleDay,jdbcType=VARCHAR},#{item.lotCode,jdbcType=VARCHAR}, #{item.warnType,jdbcType=INTEGER}, #{item.skuId,jdbcType=BIGINT},#{item.netPoiId,jdbcType=BIGINT},#{item.netPoiName,jdbcType=VARCHAR},","#{item.poiId,jdbcType=BIGINT},#{item.skuName,jdbcType=VARCHAR}, #{item.skuSupplierId,jdbcType=BIGINT},#{item.skuSupplierName,jdbcType=VARCHAR}, #{item.skuPrice,jdbcType=DECIMAL}, #{item.skuShelfType,jdbcType=INTEGER},","#{item.skuTemperatureLevel,jdbcType=TINYINT},#{item.skuCategoryId,jdbcType=INTEGER}, #{item.skuAllCategory,jdbcType=VARCHAR},#{item.basePredictQuantity,jdbcType=DECIMAL},","#{item.orPredictQuantity,jdbcType=DECIMAL},#{item.salesVolume,jdbcType=DECIMAL}, #{item.salesRate,jdbcType=DECIMAL}, #{item.maxSaleNum,jdbcType=DECIMAL},","#{item.operator,jdbcType=VARCHAR},#{item.isSellout,jdbcType=INTEGER}, #{item.isSecondDelivery,jdbcType=INTEGER},#{item.status,jdbcType=INTEGER},#{item.ctime,jdbcType=TIMESTAMP},#{item.createBy,jdbcType=VARCHAR})","</foreach> ","on duplicate key update ","operation_day = values(operation_day),","sale_day = values(sale_day),","lot_code = values(lot_code),","net_poi_id = values(net_poi_id),","sku_id = values(sku_id)","</script>"})@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")int batchInsertReplace(@Param("list") List<SellOutWarnSkuDO> list);
}

1.2.2 要插入的数据 和 db中已经存在的数据进行求差集，不在db的数据才插入

 //01.根据db数据，过滤掉oih再次下发的相同sku数据Map<String, OriginReturnSkuDO> oihReturnSkuMap = new HashMap<>();Map<String, OriginReturnSkuDO> dbReturnSkuMap = new HashMap<>();batchInsertSkuDOS.forEach(originReturnSkuDO -> {String uniqueKey = originReturnSkuDO.getPackKey() + originReturnSkuDO.getSkuId() + originReturnSkuDO.getTaskCode();oihReturnSkuMap.put(uniqueKey, originReturnSkuDO);});dbReturnSkuDOList.forEach(originReturnSkuDO -> {String uniqueKey = originReturnSkuDO.getPackKey() + originReturnSkuDO.getSkuId() + originReturnSkuDO.getTaskCode();dbReturnSkuMap.put(uniqueKey, originReturnSkuDO);});// 02. 遍历oih获得的数据，如果数据在db中了则remove掉（这部分数据不能再插入了）Iterator<Map.Entry<String, OriginReturnSkuDO>> iterator = oihReturnSkuMap.entrySet().iterator();while (iterator.hasNext()) {Map.Entry<String, OriginReturnSkuDO> entry = iterator.next();String uniqueKey = entry.getKey();if (Objects.nonNull(dbReturnSkuMap.get(uniqueKey))) {//说明db中有这条数据了//过滤掉该条数据iterator.remove();log.warn("从oih获取到重复sku数据,uniqueKey:[{}]", uniqueKey);}}//03.存过滤后的sku至list，最终需要insert的数据List<OriginReturnSkuDO> finalInsertList = new ArrayList<>(oihReturnSkuMap.values());

1.2.3 Column ‘x’ cannot be null

1、背景：mysql中xxx字段not null default 0，在使用mybatis自动生成的insert语句时，未对xxx字段进行赋值，插入db时，报此错

2、原因：使用mybatis自动生成的insert，默认对所有字段进行插入。如果未对字段xxx赋值，相当于setXxx = null，即插入db时xxx字段为null，不满足not null所以报错

3、解决：

• 使用mybatis的insert，给setXxx = 0

• 自己写insert语句，sql语句中没有xxx字段，则在落表时，xxx会被默认值0

• 使用mybatis的insertSelective

4、潜在风险点：

新需求需要新增字段y，如果设置y为not null default 0，那么就要评估代码中是否有使用mybatis的insert此表的逻辑

有的话，则需要给DO中y字段设置setY = 0，否则原本insert会报错：Column ‘y’ cannot be null

参考：https://www.cnblogs.com/sfnz/p/15618329.html

1.2.4记录批量写【增、改】成功的int count值

public int func(List<MyDO> doList) {return doList.partition(myDO, 200).stream().map(dos -> myapper.batchInsert(dos)).reduce(Integer::sum).orElse(0);}

二、改

2.1 updateByExampleSelective：未制定的表的列，属性不会进行更改

        //确认那些数据要改（where条件）RefundOutboundBillPOExample updateExample = new RefundOutboundBillPOExample();RefundOutboundBillPOExample.Criteria updateCriteria = updateExample.createCriteria();updateCriteria.andPoiIdEqualTo(poiId);updateCriteria.andRefundBillNoEqualTo(refundBillNo.trim());updateCriteria.andStatusEqualTo(preStatus.byteValue());updateCriteria.andValidEqualTo(Boolean.TRUE);//要改的具体内容setRefundOutboundBillPO refundOutboundBillPO = new RefundOutboundBillPO();refundOutboundBillPO.setChangeBy(operator);refundOutboundBillPO.setStatus(status.byteValue());refundOutboundBillPO.setChangeAt(new Date());try {int res = refundOutboundBillPoMapper.updateByExampleSelective(refundOutboundBillPO, updateExample);}

2.2 ：updateByExample：未执行值的列，会被置为null

    int updateByExampleSelective(@Param("record") T record, @Param("example") E example);int updateByExample(@Param("record") T record, @Param("example") E example);

2.3 乐观锁

点击展开内容

场景1：

表中自带一些乐观锁性质的字段。比如状态status

A、B都能够看到，也都能够操作。就会出现并发问题。假如A先操作成功了update table set status= 23 where id = 1，将status状态改了。此时尽管B也进来操作了，update table set status= 23 where id = 1时，status状态已经改过了。所以update会失败。这种是乐观锁的效果

场景2:更新拣货规则, 通过乐观锁控制并发

• 表中加乐观锁的字段。 version bigint(10) NOT NULL DEFAULT ‘1’ COMMENT ‘并发版本号’,

• 类中：

  /*
  * 字段: ver_lock
  * 说明: 用于数据库乐观锁
  */
  *private Integer verLock;

  • update语句

    int affectLines = rulePOMapper.updateRule(rulePO);
    if (affectLines <= 0) {throw new BusinessException(Constants.SYSTEM_ERROR, "系统并发更新失败");
    }
    

        <update id="updateRule" parameterType="com.sankuai.grocerywms.logistics.sharedwos.dal.entity.RulePO">update ruleset rule_content = #{rule.ruleContent}, ver_lock=ver_lock+1<if test="rule.changeBy != null and rule.changeBy != ''">,change_by = #{rule.changeBy}</if>where poi_id = #{rule.poiId} and rule_no = #{rule.ruleNo} and ver_lock=#{rule.verLock} and valid=1</update>
    

    A、B同时进来，而且此时version=1。二人都有机会update

    此时A执行update： update table set k1=v1 ,k2=v2,version = version+1 where id = rule.id and version = #{rule.version}

    update table set k1 = v1,k2 = v2, version = version + 1 where id = 1 and version = 1
    

    A执行完sql语句后，id=1的数据，version=2了

    此时B执行sql语句，拿着version=1去执行

    update table set k1 = v1,k2 = v2, version = version + 1 where id = 1 and version = 1
    

2.4 普通修改

    @Update("<script> UPDATE sellout_warn_task SET execute_status = #{status},change_by =#{modifier} WHERE " +"operation_day =#{operationDay} AND sale_day=#{saleDay} AND lot_code=#{lotCode}</script>")int updateTaskExecuteStatus(@Param("saleDay") String saleDay, @Param("operationDay") String operationDay,@Param("lotCode") String lotCode, @Param("status") int status, @Param("modifier") String modifier);

2.5 批量修改

批量更新数据-多个字段都需要更新

    @Update("<script>" +"update sellout_warn_sku  " +"   <trim prefix='set' suffixOverrides=','> " +"       <trim prefix='status =case' suffix='end,'> " +"           <foreach collection='list' item='item' index='index'> " +"               <if test='item.status !=null'>      when id=#{item.id} then #{item.status} </if>   " +"           </foreach>    " +"       </trim>    " +"       <trim prefix='change_by =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.changeBy !=null'>      when id=#{item.id} then #{item.changeBy} </if> " +"          </foreach>    " +"       </trim>  " +"       <trim prefix='fail_reason =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.failReason !=null'>      when id=#{item.id} then #{item.failReason} </if> " +"          </foreach>    " +"       </trim>  " +"       <trim prefix='process_source =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.processSource !=null'>      when id=#{item.id} then #{item.processSource} </if> " +"          </foreach>    " +"       </trim>  " +"       <trim prefix='max_sale_num =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.maxSaleNum !=null'>      when id=#{item.id} then #{item.maxSaleNum} </if> " +"          </foreach>    " +"       </trim>  " +"   </trim>  " +"where id in  " +"   <foreach collection='list' index='key' item='item' separator=',' open='(' close=')'>    #{item.id}  " +"   </foreach>" +"</script>")int batchUpdateStatusAndReason(List<SellOutWarnSkuDO> sellOutWarnSkuDos);

批量更新数据，单个字段的更新

@Update("<script>" +"update sellout_warn_sku  " +"   <trim prefix='set' suffixOverrides=','> " +"       <trim prefix='base_predict_quantity =case' suffix='end,'> " +"           <foreach collection='list' item='item' index='index'> " +"               <if test='item.basePredictQuantity !=null'>      when id=#{item.id} then #{item.basePredictQuantity} </if>   " +"           </foreach>    " +"       </trim>    " +"       <trim prefix='or_predict_quantity =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.orPredictQuantity !=null'>      when id=#{item.id} then #{item.orPredictQuantity} </if> " +"          </foreach>    " +"       </trim>  " +"   </trim>  " +"where id in  " +"   <foreach collection='list' index='key' item='item' separator=',' open='(' close=')'>    #{item.id}  " +"   </foreach>" +"</script>")int batchUpdatePredicateValue(List<SellOutWarnSkuDO> sellOutWarnSkuDos);

@Update("<script>" +"update stc_block_sku  " +"   <trim prefix='set' suffixOverrides=','> " +"       <trim prefix='change_by =case' suffix='end,'> " +"           <foreach collection='list' item='item' index='index'> " +"               <if test='item.changeBy !=null'>      when id=#{item.id} then #{item.changeBy} </if>   " +"           </foreach>    " +"       </trim>    " +"       <trim prefix='utime =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.utime !=null'>      when id=#{item.id} then #{item.utime} </if> " +"          </foreach>    " +"       </trim>  " +"       <trim prefix='valid =case' suffix='end,'>    " +"           <foreach collection='list' item='item' index='index'>     " +"               <if test='item.valid !=null'>      when id=#{item.id} then #{item.valid} </if> " +"          </foreach>    " +"       </trim>  " +"   </trim>  " +"where id in  " +"   <foreach collection='list' index='key' item='item' separator=',' open='(' close=')'>    #{item.id}  " +"   </foreach>" +"</script>")int batchUpdateByPrimaryKeySelective(List<StcBlockSkuDO> batchDeleteBlockSkuDOList);

2.6 updateByPrimaryKeySelective

ReturnpalnTaskDO returnpalnTaskDO = new ReturnpalnTaskDO().setId(id).setStatus(8);  
repository.update(returnpalnTaskDO, ReturnpalnTaskDO.Column.status)public int update(ReturnpalnTaskDO returnpalnTaskDO, ReturnpalnTaskDO.Column... selective) {return mapper.updateByPrimaryKeySelective(qtyApplyDo, selective);}ModifyQtyBillDO modifyDO = new ModifyQtyBillDO().setId(billId).setStatus(APPROVE.code)                  .setModifyResultType(ModifyResultTypeEnum.MODIFY_BY_SUPPLIER_APPLY.code).setModifySuccessMaxSaleQty(model.getExpectMaxSaleQty()).setApproveSaleQty(lockQty);modifyQtyRepository.updateByModel(modifyDO, ModifyMaxSaleQtyApplyDO.Column.status,ModifyMaxSaleQtyApplyDO.Column.modifyResultType, ModifyMaxSaleQtyApplyDO.Column.modifySuccessMaxSaleQty,ModifyMaxSaleQtyApplyDO.Column.approveSaleQty);int updateByModel(ModifyQtyBillDO model, ModifyMaxSaleQtyApplyDO.Column... selective);public int updateByModel(ModifyQtyBillDO model, ModifyMaxSaleQtyApplyDO.Column... selective) {return mapper.updateByPrimaryKeySelective(model, selective);}

2.7 将ids们的某些字段值进行修改

    <update id="updateStatusById">update modify_max_sale_qty_applyset status = #{statusCode},modify_result_type =#{modifyResultTypeCode},approver =#{approver}where id in<foreach collection="ids" open="(" close=")" item="item" separator=",">#{item}</foreach></update>

三、查

前要：常见三种查询

• 普通Repository查询 List query(model)

可以将经常查询的几个参数，封装成model，不同类型的查询，都可以通过这个model去查。

public class StcBlackSkuModel {/*** 网店集合*/private List<Long> netPoiIds;/*** 当前用户输入的skuId集合*/private List<Long> skuIdList;private Integer offset;private Integer limit;/*** 业务类型: {@link BlackSkuBusinessTypeEnum}*/private Integer businessType;
}

然后再构造Example的时，每个属性都判空下，非空才set criterria值。这样一个Repository查询接口就非常通用

• 走master查询
• 通过greaterThanId查询

3.0 强制走索引

SELECT  * 
FROM XXX_log force index(ctime) 
WHERE `ctime` BETWEEN '2017-09-11 09:34:13'  AND '2017-10-11 09:34:13'
ORDER BY  id DESC 
LIMIT 0, 30

3.1 普通查询

分页 + in

    @Select("<script>" +"select " +"id,operation_day,sale_day,warn_type,sku_id,net_poi_id,sku_price,sku_category_id,or_predict_quantity,max_sale_num,status " +"from " +"sellout_warn_sku " +"where " +"operation_day in " +"<foreach collection='operateDates' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +"#{id}" +"</foreach>" +"and " +"sale_day=#{saleDate} " +"and " +"net_poi_id in " +"<foreach collection='netPoiIds' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +"#{id}" +"</foreach>" +"and " +"sku_category_id=#{categoryId} "+"and " +" valid = 1 " +"limit " +"#{offset},#{rows}"+"</script>")@Results({@Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),@Result(column = "operation_day", jdbcType = JdbcType.VARCHAR, property = "operationDay"),@Result(column = "sale_day", jdbcType = JdbcType.VARCHAR, property = "saleDay"),@Result(column = "warn_type", jdbcType = JdbcType.INTEGER, property = "warnType"),@Result(column = "sku_id", jdbcType = JdbcType.BIGINT, property = "skuId"),@Result(column = "net_poi_id", jdbcType = JdbcType.BIGINT, property = "netPoiId"),@Result(column = "sku_price", jdbcType = JdbcType.BIGINT, property = "orPredictQuantity"),@Result(column = "sku_category_id", jdbcType = JdbcType.INTEGER, property = "skuCategoryId"),@Result(column = "or_predict_quantity", jdbcType = JdbcType.BIGINT, property = "orPredictQuantity"),@Result(column = "max_sale_num", jdbcType = JdbcType.BIGINT, property = "maxSaleNum"),@Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER)})List<SellOutWarnSkuDO> sellOutWarnForProcurementWorkBench(@Param("operateDates") List<String> operateDates,@Param("saleDate") String saleDate,@Param("netPoiIds") List<Long> netPoiIds,@Param("categoryId") Long skuCategoryId,@Param("offset") Integer offset,@Param("rows") Integer rows);

也可以这样

两点

1、每个关键字或字段后面，空出来一格

2、需要@Result一一对应

@Result：https://blog.csdn.net/heartsdance/article/details/119734906

3、@Result中的jdbcType = JdbcType.INTEGER，可以省略. 并且表中字段和DO中字段相同的列，比如id、status、valid这种，可以不用@Result注解修饰

    @Results({@Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),@Result(column = "sale_day", jdbcType = JdbcType.VARCHAR, property = "saleDay"),@Result(column = "lot_code", jdbcType = JdbcType.VARCHAR, property = "lotCode"),@Result(column = "warn_type", jdbcType = JdbcType.INTEGER, property = "warnType"),@Result(column = "sku_id", jdbcType = JdbcType.BIGINT, property = "skuId"),@Result(column = "net_poi_id", jdbcType = JdbcType.BIGINT, property = "netPoiId"),@Result(column = "sku_supplier_id", jdbcType = JdbcType.BIGINT, property = "skuSupplierId"),@Result(column = "max_sale_num", jdbcType = JdbcType.BIGINT, property = "maxSaleNum"),@Result(column = "or_predict_quantity", jdbcType = JdbcType.BIGINT, property = "orPredictQuantity"),})

最普通

@Select("<script> SELECT DISTINCT sku_category FROM sellout_warn_category_count WHERE operation_day " +"=#{operationDay} and sale_day=#{saleDay} AND lot_code=#{lotCode} AND valid=1 </script>")List<Long> queryCategoriesBySaleDayAndOperationDayAndLotCode(@Param("saleDay") String saleDay,@Param("operationDay") String operationDay, @Param("lotCode") String lotCode);

子查询

 @Select({"<script>" +" select" +" a.pc_id as pc_id, a.material_sku_id as material_sku_id," +" a.material_rate as material_rate" +" from material_sku_loss a" +" where pc_id = #{pcId}" +" and material_sku_id in" +"<foreach collection='materialSkuIds' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +" #{id} " +"</foreach>" +" and process_date = (select max(process_date) from material_sku_loss where material_sku_id = a. material_sku_id)" +"</script>"})@Results({@Result(column = "pc_id", property = "pcId", jdbcType = JdbcType.BIGINT),@Result(column = "material_sku_id", property = "materialSkuId", jdbcType = JdbcType.BIGINT),@Result(column = "material_rate", property = "materialRate", jdbcType = JdbcType.DECIMAL)})List<MaterialSkuLossDO> getMaterialSkuLoss(Long pcId, List<Long> materialSkuIds);

in查询

 @Select("<script>" +"select sku_id from clear_inventory_recommend where poi_id=#{poiId} and version=#{version} and sku_id in " +"<foreach collection='skuIdList' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +"    #{id}\n" +"    </foreach>" +"</script>")List<Long> queryClearStockSkuIdList(Long poiId, List<Long> skuIdList, String version);@Select(("<script>" +"select sku_id from sku_operation_record where effective_date=#{effectiveDate} and status=#{status} and sku_id in " +"<foreach collection='skuIdList' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +"#{id}" +"</foreach>" +" and poi_id in " +"<foreach collection='poiIdList' index = 'index' item = 'id' open= '(' separator=', ' close=')'>\n" +"#{id}" +"</foreach>" +"</script>"))List<Long> querySkuIdList(String effectiveDate, List<Long> poiIdList, List<Long> skuIdList, Integer status);

！=

 @Select({"<script>","select distinct rdc_id from sku_stock_target_management","where schedule_date = #{scheduleDate} and  algo_status = #{algoSyncStatus} and sync_type != 3","</script>"})List<Long> querySyncRdcIdListByDate(Integer algoSyncStatus,String scheduleDate);

排序和limit

@Select("select result from job_run_result where job_name=#{jobName} and run_status=#{runStatus} order by id asc limit #{rows}")
List<String> getRecentlyResult(String jobName, Integer runStatus, Integer rows);或者
limit #{from},#{size}@Select({"<script>","select distinct rdc_id from sku_stock_target_management","where schedule_date between #{startDate} and #{endDate} and algo_status = #{algoSyncStatus} and sync_type != 3","</script>"})List<Long> querySyncRdcIdList(Integer algoSyncStatus,String startDate,String endDate);

left join

 @Select(value = "select r.id,r.name,r.description from user_role ur left join role r on ur.role_id=r.id and ur.user_id=#{userId}")List<Role> getRoleListByUserId(Integer uid);

3.2 查询的PO中字段含有text属性的数据时，选择selectByExampleWithBLOBs

List<RefundOutboundBillExtraFieldsPO> extraFieldsPOS = extraFieldsPOMapper.selectByExampleWithBLOBs(extraFieldsPOExample);

若检索大字段时，则需要使用selectByExampleWithBLOBs ，一般情况则使用selectByExample 即可。

注意：如果我们要在数据库中查询的结果集中，包含text类型的字段，一定要用selectByExampleWithBLOBs，否则会查不到对应字段的结果集。

3.3 深分页问题

推荐使用id，而非limit、offset

public List<SellOutWarnSkuDO> querySellOutWarnInfoIdGreaterThan(@NonNull String saleDate,@NonNull List<Long> netPoiIdList, @NonNull List<Long> secondCategoryIdList) {// 构建查询条件LocalDate operateLocalDateT1 = DateUtils.toLocalDate(saleDate, DateUtils.yyyyMMdd);String operateDateT0 = DateUtils.localDateToString(operateLocalDateT1.minusDays(1), DateTimeFormatter.ofPattern(DateUtils.yyyyMMdd));List<String> operateDates = Lists.newArrayList(operateDateT0, saleDate);boolean loop = true;long id = 0L;List<SellOutWarnSkuDO> resultDOList = Lists.newArrayList();try {do {SellOutWarnSkuDOExample example = new SellOutWarnSkuDOExample();example.limit(LionUtils.getBatchQuerySellOutWarnSize());example.setOrderByClause("id asc");SellOutWarnSkuDOExample.Criteria criteria = example.createCriteria();criteria.andOperationDayIn(operateDates);criteria.andSaleDayEqualTo(saleDate);criteria.andNetPoiIdIn(netPoiIdList);criteria.andSkuCategoryIdIn(secondCategoryIdList);criteria.andValidEqualTo(Boolean.TRUE);criteria.andIdGreaterThan(id);List<SellOutWarnSkuDO> selectByExample;selectByExample = sellOutWarnSkuUdfMapper.selectByExample(example);if (CollectionUtils.isNotEmpty(selectByExample)) {resultDOList.addAll(selectByExample);int size = selectByExample.size();if (size < LionUtils.getBatchQuerySellOutWarnSize()) {loop = false;} else {id = selectByExample.get(size - 1).getId();}} else {loop = false;}} while (loop);} catch (Exception e) {log.error("queryAllSellOutWarnSkuDOList Exception,saleDate:[{}],netPoiIdList:[{}], secondCategoryIdList:[{}] ",saleDate, GsonUtil.toJson(netPoiIdList), GsonUtil.toJson(secondCategoryIdList), e);}return resultDOList;}public List<OriginReturnSkuDO> getAllOriginReturnSkus(String packKey, String taskCode) {long id = 0L;List<OriginReturnSkuDO> result = Lists.newArrayList();List<OriginReturnSkuDO> selectByExample;boolean loop = true;taskCode = Objects.isNull(taskCode) ? StringUtils.EMPTY : taskCode;do {OriginReturnSkuDOExample example = new OriginReturnSkuDOExample();example.limit(BATCH_THRESHOLD);example.setOrderByClause("id asc");OriginReturnSkuDOExample.Criteria criteria = example.createCriteria();criteria.andPackKeyEqualTo(packKey);criteria.andValidEqualTo(Boolean.TRUE);criteria.andIdGreaterThan(id);criteria.andNeedPackEqualTo(Boolean.TRUE);criteria.andTaskCodeEqualTo(taskCode);selectByExample = originReturnSkuMapper.selectByExample(example);if (CollectionUtils.isNotEmpty(selectByExample)) {result.addAll(selectByExample);int size = selectByExample.size();if (size < BATCH_THRESHOLD) {loop = false;} else {id = selectByExample.get(size - 1).getId();}} else {loop = false;}} while (loop);log.info("getOriginReturnSkus,packKey:[{}],taskCode:[{}],result:[{}]", packKey, taskCode,GsonUtil.toJson(result));return result;}

底层执行的sql

selectid,sku_id,task_code
fromorigin_return_sku
WHERE(pack_key = 'a'and valid = trueand id > 0and need_pack = trueand task_code = 'b')
order byid asc
limit5;select * from sellout_warn_sku WHERE ( operation_day in ( '20220831' , '20220901' ) and sale_day = '20220901' and net_poi_id in (  ) and sku_category_id in ( ) and valid = true and id > 0 ) order by id asc limit 1000 ===============================================================================================selectid,sku_id,task_code
fromorigin_return_sku
WHERE(pack_key = 'a'and valid = trueand id > 13260这是根据id = selectByExample.get(size - 1).getId()计算出来的值and need_pack = trueand task_code = 'b')
order byid asc
limit5;

3.4 selectByExampleWithRowBounds：不是真正意义上的分页查询,底层并没有分野查询。会扫面全量数据

https://www.jianshu.com/p/f445e163b5ed

1、selectByExample

<if test="rows != null"><if test="offset != null">limit ${offset}, ${rows}</if><if test="offset == null">limit ${rows}</if>

方式0:

example.limit(offset, limit);

方式一：

前端传递offset和limit

首页0，50

下一页：50，50

这种形式，使用

example.setOffset(request.getPaging().getOffset());
example.setRows(request.getPaging().getLimit());

方式二：

        example.page()//第一页，page = 0public ReturnPlanOrderDOExample page(Integer page, Integer pageSize) {this.offset = page * pageSize;this.rows = pageSize;return this;}

3.5 走主库查

    private List<StcBlockSkuDO> queryStcBlockSkuByMaster(Long netPoiId, List<Long> skuIds) {//查询主库try {ZebraForceMasterHelper.forceMasterInLocalContext();return blockSkuRepository.queryBlockByMaster(netPoiId, skuIds);} catch (Exception e) {log.error("走主库查询黑名单sku,netPoiId:[{}], skuIds:[{}]", netPoiId, GsonUtils.toJsonString(skuIds), e);throw new BusinessException(RespCodeEnum.BIZ_ERROR.code, "走主库查询黑名单sku error");} finally {ZebraForceMasterHelper.clearLocalContext();}}

3.6 一个查询条件作为一个key形式去查询（防止过多的in条件，导致笛卡尔乘积），使用OR的形式

1、从MySQL 5.7.3开始，优化器能够将范围扫描访问方法应用于这种形式的查询：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));2、以前，要使用范围扫描，需要将查询写为：
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' );3、为了优化器使用范围扫描，查询必须满足以下条件：
只有IN谓词可以使用，NOT IN 不行。
IN谓词左边的行构造函数中可能只有列引用。
IN谓词的右边必须有多个行构造函数。

手写sql

 @Results(id="predictSalesByRegionDTO",value={@Result(column="region_id", jdbcType= JdbcType.BIGINT, property="regionId"),@Result(column="region_name", jdbcType= JdbcType.BIGINT, property="regionName"),@Result(column="planning_date" ,jdbcType=JdbcType.VARCHAR, property="planningDate" ),@Result(column="predict_base_date" ,jdbcType= JdbcType.VARCHAR, property="predictBaseDate" ),@Result(column="role_type" ,jdbcType=JdbcType.BIGINT, property="roleType" ),@Result(column="planning_value" ,jdbcType=JdbcType.BIGINT, property="planningValue" ),@Result(column="rdc_id", jdbcType=JdbcType.BIGINT ,property="rdcId" )})@Select({"<script>","select  region_id, region_name, planning_date, predict_base_date, role_type, planning_value, rdc_id ","from plan_collaboration_data ","where planning_date = #{planningDate}","<choose>","<when test='regions != null and regions.size() &gt; 0'>"," and region_id in","<foreach collection='regions' item='region' open= '(' separator=', ' close=')'>","#{region}","</foreach>","</when>","<otherwise>","and region_id is not null","</otherwise>","</choose>","<if test='conditions != null and conditions.size() &gt; 0'>"," and ","<foreach collection='conditions' item='condition' open= '(' separator='or' close=')'>","<trim prefix='(' suffix=')'>","predict_base_date = #{condition.predictBaseDate} and role_type = #{condition.roleType}","</trim>","</foreach>","</if>","<when test='rdcIds != null and rdcIds.size() &gt; 0'>"," and rdc_id in ","<foreach collection='rdcIds' item='rdcId' open= '(' separator=', ' close=')'>","#{rdcId}","</foreach>","</when>","</script>"})List<PredictSalesByRdcDTO> queryDataByRegionRoleAndDate(String planningDate, List<Long> regions, List<Long> rdcIds,List<PredictBaseAndRoleCondition> conditions);

或者mybatis

example.or(新的criteria)

参考：

 @Results({@Result(column = "id", property = "id", id = true),@Result(column = "sale_day", property = "saleDay"),@Result(column = "net_poi_id", property = "netPoiId"),@Result(column = "sku_id", property = "skuId"),@Result(column = "max_sale_num_after", property = "maxSaleNumAfter"),@Result(column = "ctime", property = "ctime")})@Select("<script>" +"select " +"id, sale_day, net_poi_id, sku_id, max_sale_num_after, ctime " +"from " +"sdc_trusteeship_sku_operate_log " +"where " +"<if test='conditions != null and conditions.size() &gt; 0'>" +"<foreach collection='conditions' item='condition' open= '(' separator='or' close=')'>" +"<trim prefix='(' suffix=')'>" +"sale_day = #{condition.saleDate} and net_poi_id = #{condition.netPoiId} and sku_id = #{condition.skuId}" +"</trim>" +"</foreach>" +"</if>" +"</script>")List<SdcTrusteeshipSkuOperateLogDO> queryOperateLogMaxSaleAfter(@Param("conditions") List<SkuScheduleKey> conditions);

a,b,c,d in(不同的值)

SELECTorigin_id,receive_id,execute_type,business_type,origin_cw_id
FROMreturn_plan_order
WHERE(origin_id,receive_id,execute_type,business_type,origin_cw_id) in (SELECTorigin_id,receive_id,execute_type,business_type,origin_cw_idFROMreturn_plan_orderWHEREreturn_day = '20220809' --   AND origin_id = 10000915GROUP BYorigin_id,receive_id,execute_type,business_type,origin_cw_idHAVINGcount(*) > 1)and return_day = '20220809'

四、其它

4.1 example中criteria对同一个字段赋值不会覆盖前一个值。

结果是：同一个字段会有两个值，即 a = 1 and a =2

数据库中没数据

select 
poi_id,refund_bill_no,source,status,supplier_id,complete_picking_time,create_at
from refund_outbound_bill 
where valid = 0 and valid = 1;