• 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>

    • 1
    • 2
    • 3
    • 4
    • 5

    一、增

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

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    幂等键

    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
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    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的 全部数据要么都成功，要么都失败。那就二者包在一个事务方法中，同时二者本身又在一个单独的事务方法中

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80

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

    public interface SellOutWarnSkuMapper extends SellOutWarnSkuAutoGeneratorMapper {

    @Insert({
            ""
    })
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int batchInsertReplace(@Param("list") List<SellOutWarnSkuDO> list);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    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
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    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);
    }

    • 1
    • 2
    • 3
    • 4
    • 5

    二、改

    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);
        }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

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

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

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

    • 1
    • 2
    • 3

    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, "系统并发更新失败");
}

        • 1
        • 2
        • 3
        • 4
            <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>

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8

        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

        • 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

        • 1

    2.4 普通修改

        @Update("")
    int updateTaskExecuteStatus(@Param("saleDay") String saleDay, @Param("operationDay") String operationDay,
            @Param("lotCode") String lotCode, @Param("status") int status, @Param("modifier") String modifier);

    • 1
    • 2
    • 3
    • 4

    2.5 批量修改

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

        @Update("")
    int batchUpdateStatusAndReason(List<SellOutWarnSkuDO> sellOutWarnSkuDos);
    

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

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

    @Update("")
    int batchUpdatePredicateValue(List<SellOutWarnSkuDO> sellOutWarnSkuDos);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    @Update("")
    int batchUpdateByPrimaryKeySelective(List<StcBlockSkuDO> batchDeleteBlockSkuDOList);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    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);
    }


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    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>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    三、查

    前要：常见三种查询

    • 普通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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    然后再构造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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    3.1 普通查询

    分页 + in

        @Select(""
    )
    @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
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    也可以这样

    两点

    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"),
    })

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    最普通

    @Select("")
    List<Long> queryCategoriesBySaleDayAndOperationDayAndLotCode(@Param("saleDay") String saleDay,
            @Param("operationDay") String operationDay, @Param("lotCode") String lotCode);

    • 1
    • 2
    • 3
    • 4

    子查询

     @Select({""
    })
    @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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    in查询

     @Select("")
    List<Long> queryClearStockSkuIdList(Long poiId, List<Long> skuIdList, String version);

    @Select((""
    ))
    List<Long> querySkuIdList(String effectiveDate, List<Long> poiIdList, List<Long> skuIdList, Integer status);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    ！=

     @Select({
        ""
    })
    List<Long> querySyncRdcIdListByDate(Integer algoSyncStatus,String scheduleDate);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    排序和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({
            ""
    })
    List<Long> querySyncRdcIdList(Integer algoSyncStatus,String startDate,String endDate);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

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

    • 1
    • 2

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

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

    • 1

    若检索大字段时，则需要使用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;
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81

    底层执行的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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    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>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    方式0:

    example.limit(offset, limit);

    • 1

    方式一：

    前端传递offset和limit

    首页0，50

    下一页：50，50

    这种形式，使用

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

    • 1
    • 2

    方式二：

            example.page()

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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();
        }

    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    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谓词的右边必须有多个行构造函数。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    手写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({
            ""
    })
    List<PredictSalesByRdcDTO> queryDataByRegionRoleAndDate(String planningDate, List<Long> regions, List<Long> rdcIds,
                                                            List<PredictBaseAndRoleCondition> conditions);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    或者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(""
    )
    List<SdcTrusteeshipSkuOperateLogDO> queryOperateLogMaxSaleAfter(@Param("conditions") List<SkuScheduleKey> conditions);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    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'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    四、其它

    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;

    • 1
    • 2
    • 3
    • 4
  • 相关阅读:
    基于Springboot实现毕业生信息招聘平台管理系统演示【项目源码+论文说明】分享
    【PyTorch】深度学习实践之线性模型Linear Model
    CodeChecker的安装及基本使用
    使用nrm 方式 管理npm 仓库
    API网关是如何提升API接口安全管控能力的
    无需复杂步骤，Win11用户轻松开启旧版文件资源管理器！
    力扣--动态规划1027.最长等差数列
    找链表的交点
    java计算机毕业设计河南口腔医疗机构线上服务系统源码+mysql数据库+系统+lw文档+部署
    Spring Boot整合Mybatis
  • 原文地址：https://blog.csdn.net/tmax52HZ/article/details/134432958