CRUD-SQL

tmax52HZ

已于 2024-04-07 22:12:12 修改

阅读量423

点赞数 1

文章标签： sql java 数据库 mybatis

于 2023-11-16 07:55:12 首次发布

本文链接：https://blog.csdn.net/tmax52HZ/article/details/134432958

版权

文章目录

前置
- - insertSelective和upsertSelective使用姿势
  - 手写sql，有两种方式
一、增
二、改
三、查
四、其它
- 4.1 example中criteria对同一个字段赋值不会覆盖前一个值。

前置

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, "已存在拣货任务与容器绑定");
        }
//没有再insert
        return 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 {

    @Resource
    private 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{

    @Resource
    private 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 = 600


SkuDO 和 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);

        //要改的具体内容set
        RefundOutboundBillPO 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 rule
        set 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_apply
        set status = #{statusCode},
        modify_result_type =#{modifyResultTypeCode},
        approver =#{approver}
        where id in
        <foreach collection="ids" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
    </update>

2.8 自定义批量修改

@Update("<script>" +
		" update table set valid = 1 " +
		" where appraisal_version = 2  and event_date = #{saleDate} "+
		" and step in " +
			"<foreach collection = 'stepList' item = 'item' separator=',' open'(' close = ')'>" +
		 		"#{item}" +
		 	"</foreach>" + 
		"</script>")
int update(Data saleDate, List<Integer> stepList)

三、查

前要：常见三种查询

普通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

select
  id,
  sku_id,
  task_code
from
  origin_return_sku
WHERE
  (
    pack_key = 'a'
    and valid = true
    and id > 0
    and need_pack = true
    and task_code = 'b'
  )
order by
  id asc
limit
  5;
  
   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 
   
===============================================================================================
 select
  id,
  sku_id,
  task_code
from
  origin_return_sku
WHERE
  (
    pack_key = 'a'
    and valid = true
    and id > 13260这是根据id = selectByExample.get(size - 1).getId()计算出来的值
    and need_pack = true
    and task_code = 'b'
  )
order by
  id asc
limit
  5;

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 = 0
  public 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(不同的值)

SELECT
  	origin_id,
    receive_id,
    execute_type,
    business_type,
    origin_cw_id
FROM
  return_plan_order
WHERE
  (
    origin_id,
    receive_id,
    execute_type,
    business_type,
    origin_cw_id
  ) in (
    SELECT
      origin_id,
      receive_id,
      execute_type,
      business_type,
      origin_cw_id
    FROM
      return_plan_order
    WHERE
      return_day = '20220809' --   AND origin_id = 10000915
    GROUP BY
      origin_id,
      receive_id,
      execute_type,
      business_type,
      origin_cw_id
    HAVING
      count(*) > 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;