背景在我们系统中承接多种行业多种商家的多个业务条线不同场景的的不同业务诉求在实现上将个性和通用字段隔离通用字段是所有条线通用逻辑所共用的标准字段对于个别条线的个性化诉求则通过个性化扩展字段来实现。通用字段作用于通用逻辑所有条线走到相应功能时会对通用字段读写。而对于个性扩展字段而言只有用到个性化功能时才会对个性化扩展字段进行赋值。在数据库持久化存储上用不到该个性化扩展字段时该字段无需存储不占用额外存储空间。在表结构上使用JSON类型字段进行存储。在日常扩展字段的开发过程中不可避免的用到JSON函数进行字段处理。常用JSON函数一览NameDescription-Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().-Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).JSON_APPEND()Append data to JSON documentJSON_ARRAY()Create JSON arrayJSON_ARRAY_APPEND()Append data to JSON documentJSON_ARRAY_INSERT()Insert into JSON arrayJSON_CONTAINS()Whether JSON document contains specific object at pathJSON_CONTAINS_PATH()Whether JSON document contains any data at pathJSON_DEPTH()Maximum depth of JSON documentJSON_EXTRACT()Return data from JSON documentJSON_INSERT()Insert data into JSON documentJSON_KEYS()Array of keys from JSON documentJSON_LENGTH()Number of elements in JSON documentJSON_MERGE()Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()JSON_MERGE_PATCH()Merge JSON documents, replacing values of duplicate keysJSON_MERGE_PRESERVE()Merge JSON documents, preserving duplicate keysJSON_OBJECT()Create JSON objectJSON_PRETTY()Print a JSON document in human-readable formatJSON_QUOTE()Quote JSON documentJSON_REMOVE()Remove data from JSON documentJSON_REPLACE()Replace values in JSON documentJSON_SEARCH()Path to value within JSON documentJSON_SET()Insert data into JSON documentJSON_STORAGE_SIZE()Space used for storage of binary representation of a JSON documentJSON_TYPE()Type of JSON valueJSON_UNQUOTE()Unquote JSON valueJSON_VALID()Whether JSON value is validJSON字段实现项目场景诉求典型案例分析JSON字段对我们系统来说并不陌生有不少场景使用下面有一些典型的使用场景案例先放出来作为JSON字段项目使用背景铺垫一下。扩展字段的存储和查询动态扩展查询案例在二手库或逆向条件有比较多的扩展属性其他条线缺没有这些属性值这些属性以JSON扩展的方式存储。下面是一个JSON扩展字段的JSON数据示例:{ ppCode: PPDA4302865239B10F, zoneNo: STAGE-OUTBOUND, zoneName: 出库暂存区, zoneType: t, extendMap: { cid1: 13765, cid2: 14192, cid3: 14533, deptId: 1, jdFlag: 1, symbol: 300, brandId: 52368, cbjPrice: 2034, salesPin: xumingchen, commonType: 0, wareSource: 100, orderSource: it100, supplierCode: lgde, outTransferId: 1284009718, inboundSourceType: 100, purchaseChannelId: 1001 }, storeCode: , sourceModule: wms-pick, isolationZone: false, stageZoneType: OUTBOUND }这些扩展字段支持作为查询条件过滤库存用于出库建单。在服务层面为了支持动态增减的属性条件条件字段定义为Map类型与 JSON 字段中的 extendMap 所对应。list查询条件的特殊预处理SQL层面的动态查询效果元素包含条件使用案例设备组条件批属性条件序列号条件数值管理深度剖析案例案例背景在现有的场景中目前仅华冠场景支持重量库存其他条线暂无库存管理。在本案例中重量库存通过个性化扩展字段实现JSON类型。JSON类型的字段名是 extend_content其中重量字段 stockWeight 的 JSON path是 $.stockInfo.stockWeight 。stockInfo 的内容示例如下{ businessNo: OT2008735812539129856, stockWeight: 630, businessType: WMS_PICK, businessTypeName: 出库拣货 }增减重量库存单行更新通过CASE WHEN THEN 方式批量更新JSON字段中的重量库存剥茧第一层在调试中发现JSON字段如果想要通过 json_set 赋值必须依赖于该字段不能为null如果json_set 该JSON字段值为null则更新不上不会产生SQL语法错误。对于存量的历史null值字段统计并做一次初始化。初始化历史数据统计JSON字段extend_content值为null 的数据将 extend_content 为 null 的进行初始化统计JSON字段extend_content值不为null但其中的stockInfo为null的数据将 extend_content.stockInfo 为 null 的数据进行初始化对存量的历史数据初始化完毕考虑到某些场景可能还会持续产生 JSON 字段 null值情况考虑在代码中进行兼容如果想在 JSON 字段中进行 json_setSQL进行检查并自动进行前置初始化。以上SQL通过JSON_OBJECT函数将null值的JSON字段初始化为一个空JSON对象肉眼看上去是 {}。剥茧第二层除了上面历史null值的JSON字段外还发现一个更为复杂的场景JSON字段更新前不为null但是本次赋值更新操作却导致JSON字段整体变成了null匪夷所思单行更新方式因为是一次是更新一行没有问题。批量更新如果所有的明细stockWeight字段都有值更新也没问题。如果一次批量更新的明细中有的stockWeight字段有值有的没有值则更新会有问题不会出现SQL语法错误但是整个extend_content JSON字段会被错误地更新为null。在测试环境中调试的一个入参如下{ requestHeader: { sourceModule: inventory, requestIp: 11.50.45.137, warehouseNo: 6_6_618, businessType: INV-CHANGE-PROFIT-LOSS, businessTypeName: 盘盈亏, businessNo: CP2009231067168407553, uuid: wms.inv.change.profit.6_6_618.CP2009231067168407553.2009231067554283520.12, operator: guozhongqiang5 }, increaseStockDetailList: [ { detailBusinessNo: DPPT20092299369586442242, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, stockSkuIndicator: { sku: EMG172002001, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, increaseOperateType: normalIncrease, stockQty: 1.0000, recommendLocationNo: , externalNo: CP2009231067168407553, uniqueStockList: [], reason: {} }, { detailBusinessNo: DPPT2009229936958644224, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, stockSkuIndicator: { sku: EMG172002003, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, increaseOperateType: normalIncrease, stockQty: 0.0000, stockWeight: 3000, recommendLocationNo: , externalNo: CP2009231067168407553, uniqueStockList: [], reason: {} } ] }我们可以看到上面这个入参有两个明细对应的SKU分别是EMG172002001 和 EMG172002003。第一个明细只有stockQty字段无stockWeight字段第二个同时有stockQty字段和stockWeight字段stockWeight值为 3000。此时批量更新形成的SQL如下UPDATE st_stock set stock_qty case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872347100020736 AND status 0 THEN stock_qty 1.0000 WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN stock_qty 0.0000 end, extend_content case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN JSON_SET(COALESCE(extend_content, {}), $.stockInfo, COALESCE(JSON_EXTRACT(extend_content, $.stockInfo), JSON_OBJECT())) ELSE extend_content end, extend_content case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN json_set(extend_content, $.stockInfo.stockWeight, IFNULL(extend_content - $.stockInfo.stockWeight, 0) IFNULL(3000.0, 0)) end, update_time now(), version version 1, update_user guozhongqiang5, extend_content json_set(extend_content, $.stockInfo.businessType, INV-CHANGE-PROFIT-LOSS, $.stockInfo.businessTypeName, 盘盈亏, $.stockInfo.businessNo, CP2009231067168407553333) WHERE ( deleted 0 AND warehouse_no 6_6_618 AND id 2008872347100020736 AND status 0 ) or ( deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 )问题出现下图圈红的部分在上面的场景中一次批量更新有两个明细其中一个因为stockWeight为null不会被动态拼接到SQL中这个无对应的 WHEN THEN 则 extend_content 被置为null另一个明细因为有 WHEN THEN 则赋值正常。此时直观的想法是既然因为stockWeight为null的明细走不上 WHEN THEN 何不通过 ELSE 赋值为extend_content 本身来解决呢。按此思路进行代码调整如下调试明细stockWeigh不为空验证通过。剥茧第三层但是当所有明细stockWeight为null的时候因为 ELSE extend_content 在 if条件满足的时候才会动态拼接当此时 WHEN THEN 和 ELSE都不会拼接进去语法不通过。那么把 ELSE extend_content 从 if 条件判断拿出来放在 end的前面总可以了吧。这里借助 foreach 的 close 来拼接 ELSE extend_content 语句。在测试环境验证时当更新入参至少一个明细中的 stockWeight 不为null时确实没问题。当所有明细的stockWeight都为null时新的问题来了报错信息及分析过程如下### The error occurred while setting parameters ### SQL: UPDATE st_stock set stock_qtycase WHEN deleted 0 AND warehouse_no ? AND id ? and stock_qty ? * -1 THEN stock_qty ? WHEN deleted 0 AND warehouse_no ? AND id ? and stock_qty ? * -1 THEN stock_qty ? end, diff_qtycase WHEN deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 THEN diff_qty ? WHEN deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 THEN diff_qty ? end, extend_contentcase ELSE extend_content end, extend_contentcase ELSE extend_content end, update_time now() , version version 1 , update_user ? , extend_content json_set(extend_content, ?, ?, ?, ?, ?, ?) WHERE ( deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 AND status 0 ) or ( deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 AND status 0 ) /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.main.dao.StockOperationDao.simpleDecreaseDiffQty */ ### Cause: com.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near ELSE extend_content end, extend_contentcase ELSE extend_content at line 36 ; bad SQL grammar []; nested exception is com.sql.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near ELSE extend_content end,入参{ requestHeader: { sourceModule: inventory, requestIp: 11.158.12.73, warehouseNo: 6_6_618, businessType: INV-PROFIT-LOSS-TRANSFER-STOCK-MATCH, businessTypeName: 盘盈、盘亏单预占转移, businessNo: CL2009892868717576192, uuid: wms.inv.change.lock.transfer.loss.6_6_618.2009892868977623040.0, operator: guozhongqiang5 }, stockLockTransformType: difference2Change, stockLockTransformDetailList: [ { srcBusinessNo: DPPT2009892261415911424, destBusinessNo: CL2009892868717576192, stockSkuIndicator: { sku: EMG172002002, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, qty: 2 }, { srcBusinessNo: DPPT2009892261415911424, destBusinessNo: CL2009892868717576192, stockSkuIndicator: { sku: EMG172002004, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, qty: 4 } ], serialLockTransformDetailList: [ ] }入参本身没什么问题是符合要求的入参从本次需求的特征上来看只是没有重量字段重量字段非必填也不是必须的在很多场景下也不涉及重量库存的变化。数据数据库中的待更新数据本身也没有问题。到这里已经确认入参是正常场景待更新数据也没问题是SQL本身存在问题。trim prefixextend_contentcase suffixend, foreach collectionlist itemitem indexindex closeELSE extend_content if testitem.stockWeight ! null and item.stockWeight ! 0 WHEN include refidsimpleCommonIncreaseClauseOfItem/ THEN json_set(extend_content, #{item.stockWeightJsonPath, jdbcTypeVARCHAR}, IFNULL(extend_content - #{item.stockWeightJsonPath, jdbcTypeVARCHAR}, 0) IFNULL(#{item.stockWeight, jdbcTypeDECIMAL}, 0)) /if /foreach /trim结合代码来看入参stockWeight为null则 if 条件里的 WHEN THEN 拼不进去但 close 部分的 ELSE extend_content 可以拼入结合前面prefix的 extend_contentcase 和 suffix 的 end, 连在一起就是:extend_contentcase ELSE extend_content end,显然这个语法不通过的。解决方法也比较简单就是把 ELSE 语句改成 WHEN THEN 语句具体如下这样即使if语句不拼接进去整体拼出来的语句如下也是符合预期的extend_contentcase WHEN 11 THEN extend_content end,至此批量更新所有明细全有stockWeight全无stockWeight部分有部分无stockWeight的场景均已支持完毕。再来一层回过头看还有另外一个思路处理更为简单不用 CASE WHEN THEN 方式新写一个批量更新方法这种批量更新方式依赖于开启 allowMultiQueries 不禁感慨条条大路通罗马并非所有的路都一帆风顺但我们终究抵达终点。
抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践
背景在我们系统中承接多种行业多种商家的多个业务条线不同场景的的不同业务诉求在实现上将个性和通用字段隔离通用字段是所有条线通用逻辑所共用的标准字段对于个别条线的个性化诉求则通过个性化扩展字段来实现。通用字段作用于通用逻辑所有条线走到相应功能时会对通用字段读写。而对于个性扩展字段而言只有用到个性化功能时才会对个性化扩展字段进行赋值。在数据库持久化存储上用不到该个性化扩展字段时该字段无需存储不占用额外存储空间。在表结构上使用JSON类型字段进行存储。在日常扩展字段的开发过程中不可避免的用到JSON函数进行字段处理。常用JSON函数一览NameDescription-Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().-Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).JSON_APPEND()Append data to JSON documentJSON_ARRAY()Create JSON arrayJSON_ARRAY_APPEND()Append data to JSON documentJSON_ARRAY_INSERT()Insert into JSON arrayJSON_CONTAINS()Whether JSON document contains specific object at pathJSON_CONTAINS_PATH()Whether JSON document contains any data at pathJSON_DEPTH()Maximum depth of JSON documentJSON_EXTRACT()Return data from JSON documentJSON_INSERT()Insert data into JSON documentJSON_KEYS()Array of keys from JSON documentJSON_LENGTH()Number of elements in JSON documentJSON_MERGE()Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()JSON_MERGE_PATCH()Merge JSON documents, replacing values of duplicate keysJSON_MERGE_PRESERVE()Merge JSON documents, preserving duplicate keysJSON_OBJECT()Create JSON objectJSON_PRETTY()Print a JSON document in human-readable formatJSON_QUOTE()Quote JSON documentJSON_REMOVE()Remove data from JSON documentJSON_REPLACE()Replace values in JSON documentJSON_SEARCH()Path to value within JSON documentJSON_SET()Insert data into JSON documentJSON_STORAGE_SIZE()Space used for storage of binary representation of a JSON documentJSON_TYPE()Type of JSON valueJSON_UNQUOTE()Unquote JSON valueJSON_VALID()Whether JSON value is validJSON字段实现项目场景诉求典型案例分析JSON字段对我们系统来说并不陌生有不少场景使用下面有一些典型的使用场景案例先放出来作为JSON字段项目使用背景铺垫一下。扩展字段的存储和查询动态扩展查询案例在二手库或逆向条件有比较多的扩展属性其他条线缺没有这些属性值这些属性以JSON扩展的方式存储。下面是一个JSON扩展字段的JSON数据示例:{ ppCode: PPDA4302865239B10F, zoneNo: STAGE-OUTBOUND, zoneName: 出库暂存区, zoneType: t, extendMap: { cid1: 13765, cid2: 14192, cid3: 14533, deptId: 1, jdFlag: 1, symbol: 300, brandId: 52368, cbjPrice: 2034, salesPin: xumingchen, commonType: 0, wareSource: 100, orderSource: it100, supplierCode: lgde, outTransferId: 1284009718, inboundSourceType: 100, purchaseChannelId: 1001 }, storeCode: , sourceModule: wms-pick, isolationZone: false, stageZoneType: OUTBOUND }这些扩展字段支持作为查询条件过滤库存用于出库建单。在服务层面为了支持动态增减的属性条件条件字段定义为Map类型与 JSON 字段中的 extendMap 所对应。list查询条件的特殊预处理SQL层面的动态查询效果元素包含条件使用案例设备组条件批属性条件序列号条件数值管理深度剖析案例案例背景在现有的场景中目前仅华冠场景支持重量库存其他条线暂无库存管理。在本案例中重量库存通过个性化扩展字段实现JSON类型。JSON类型的字段名是 extend_content其中重量字段 stockWeight 的 JSON path是 $.stockInfo.stockWeight 。stockInfo 的内容示例如下{ businessNo: OT2008735812539129856, stockWeight: 630, businessType: WMS_PICK, businessTypeName: 出库拣货 }增减重量库存单行更新通过CASE WHEN THEN 方式批量更新JSON字段中的重量库存剥茧第一层在调试中发现JSON字段如果想要通过 json_set 赋值必须依赖于该字段不能为null如果json_set 该JSON字段值为null则更新不上不会产生SQL语法错误。对于存量的历史null值字段统计并做一次初始化。初始化历史数据统计JSON字段extend_content值为null 的数据将 extend_content 为 null 的进行初始化统计JSON字段extend_content值不为null但其中的stockInfo为null的数据将 extend_content.stockInfo 为 null 的数据进行初始化对存量的历史数据初始化完毕考虑到某些场景可能还会持续产生 JSON 字段 null值情况考虑在代码中进行兼容如果想在 JSON 字段中进行 json_setSQL进行检查并自动进行前置初始化。以上SQL通过JSON_OBJECT函数将null值的JSON字段初始化为一个空JSON对象肉眼看上去是 {}。剥茧第二层除了上面历史null值的JSON字段外还发现一个更为复杂的场景JSON字段更新前不为null但是本次赋值更新操作却导致JSON字段整体变成了null匪夷所思单行更新方式因为是一次是更新一行没有问题。批量更新如果所有的明细stockWeight字段都有值更新也没问题。如果一次批量更新的明细中有的stockWeight字段有值有的没有值则更新会有问题不会出现SQL语法错误但是整个extend_content JSON字段会被错误地更新为null。在测试环境中调试的一个入参如下{ requestHeader: { sourceModule: inventory, requestIp: 11.50.45.137, warehouseNo: 6_6_618, businessType: INV-CHANGE-PROFIT-LOSS, businessTypeName: 盘盈亏, businessNo: CP2009231067168407553, uuid: wms.inv.change.profit.6_6_618.CP2009231067168407553.2009231067554283520.12, operator: guozhongqiang5 }, increaseStockDetailList: [ { detailBusinessNo: DPPT20092299369586442242, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, stockSkuIndicator: { sku: EMG172002001, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, increaseOperateType: normalIncrease, stockQty: 1.0000, recommendLocationNo: , externalNo: CP2009231067168407553, uniqueStockList: [], reason: {} }, { detailBusinessNo: DPPT2009229936958644224, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, stockSkuIndicator: { sku: EMG172002003, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, increaseOperateType: normalIncrease, stockQty: 0.0000, stockWeight: 3000, recommendLocationNo: , externalNo: CP2009231067168407553, uniqueStockList: [], reason: {} } ] }我们可以看到上面这个入参有两个明细对应的SKU分别是EMG172002001 和 EMG172002003。第一个明细只有stockQty字段无stockWeight字段第二个同时有stockQty字段和stockWeight字段stockWeight值为 3000。此时批量更新形成的SQL如下UPDATE st_stock set stock_qty case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872347100020736 AND status 0 THEN stock_qty 1.0000 WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN stock_qty 0.0000 end, extend_content case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN JSON_SET(COALESCE(extend_content, {}), $.stockInfo, COALESCE(JSON_EXTRACT(extend_content, $.stockInfo), JSON_OBJECT())) ELSE extend_content end, extend_content case WHEN deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 THEN json_set(extend_content, $.stockInfo.stockWeight, IFNULL(extend_content - $.stockInfo.stockWeight, 0) IFNULL(3000.0, 0)) end, update_time now(), version version 1, update_user guozhongqiang5, extend_content json_set(extend_content, $.stockInfo.businessType, INV-CHANGE-PROFIT-LOSS, $.stockInfo.businessTypeName, 盘盈亏, $.stockInfo.businessNo, CP2009231067168407553333) WHERE ( deleted 0 AND warehouse_no 6_6_618 AND id 2008872347100020736 AND status 0 ) or ( deleted 0 AND warehouse_no 6_6_618 AND id 2008872388812374016 AND status 0 )问题出现下图圈红的部分在上面的场景中一次批量更新有两个明细其中一个因为stockWeight为null不会被动态拼接到SQL中这个无对应的 WHEN THEN 则 extend_content 被置为null另一个明细因为有 WHEN THEN 则赋值正常。此时直观的想法是既然因为stockWeight为null的明细走不上 WHEN THEN 何不通过 ELSE 赋值为extend_content 本身来解决呢。按此思路进行代码调整如下调试明细stockWeigh不为空验证通过。剥茧第三层但是当所有明细stockWeight为null的时候因为 ELSE extend_content 在 if条件满足的时候才会动态拼接当此时 WHEN THEN 和 ELSE都不会拼接进去语法不通过。那么把 ELSE extend_content 从 if 条件判断拿出来放在 end的前面总可以了吧。这里借助 foreach 的 close 来拼接 ELSE extend_content 语句。在测试环境验证时当更新入参至少一个明细中的 stockWeight 不为null时确实没问题。当所有明细的stockWeight都为null时新的问题来了报错信息及分析过程如下### The error occurred while setting parameters ### SQL: UPDATE st_stock set stock_qtycase WHEN deleted 0 AND warehouse_no ? AND id ? and stock_qty ? * -1 THEN stock_qty ? WHEN deleted 0 AND warehouse_no ? AND id ? and stock_qty ? * -1 THEN stock_qty ? end, diff_qtycase WHEN deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 THEN diff_qty ? WHEN deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 THEN diff_qty ? end, extend_contentcase ELSE extend_content end, extend_contentcase ELSE extend_content end, update_time now() , version version 1 , update_user ? , extend_content json_set(extend_content, ?, ?, ?, ?, ?, ?) WHERE ( deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 AND status 0 ) or ( deleted 0 AND warehouse_no ? AND id ? and diff_qty ? * -1 AND status 0 ) /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.main.dao.StockOperationDao.simpleDecreaseDiffQty */ ### Cause: com.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near ELSE extend_content end, extend_contentcase ELSE extend_content at line 36 ; bad SQL grammar []; nested exception is com.sql.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near ELSE extend_content end,入参{ requestHeader: { sourceModule: inventory, requestIp: 11.158.12.73, warehouseNo: 6_6_618, businessType: INV-PROFIT-LOSS-TRANSFER-STOCK-MATCH, businessTypeName: 盘盈、盘亏单预占转移, businessNo: CL2009892868717576192, uuid: wms.inv.change.lock.transfer.loss.6_6_618.2009892868977623040.0, operator: guozhongqiang5 }, stockLockTransformType: difference2Change, stockLockTransformDetailList: [ { srcBusinessNo: DPPT2009892261415911424, destBusinessNo: CL2009892868717576192, stockSkuIndicator: { sku: EMG172002002, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, qty: 2 }, { srcBusinessNo: DPPT2009892261415911424, destBusinessNo: CL2009892868717576192, stockSkuIndicator: { sku: EMG172002004, lotNo: -1, skuLevel: 100, packCode: 8c59689e8972a14e4883b0ea755b3702, ownerNo: EBU4398046536982 }, stockLocationIndicator: { locationNo: 01, containerLevel1: , containerLevel2: }, qty: 4 } ], serialLockTransformDetailList: [ ] }入参本身没什么问题是符合要求的入参从本次需求的特征上来看只是没有重量字段重量字段非必填也不是必须的在很多场景下也不涉及重量库存的变化。数据数据库中的待更新数据本身也没有问题。到这里已经确认入参是正常场景待更新数据也没问题是SQL本身存在问题。trim prefixextend_contentcase suffixend, foreach collectionlist itemitem indexindex closeELSE extend_content if testitem.stockWeight ! null and item.stockWeight ! 0 WHEN include refidsimpleCommonIncreaseClauseOfItem/ THEN json_set(extend_content, #{item.stockWeightJsonPath, jdbcTypeVARCHAR}, IFNULL(extend_content - #{item.stockWeightJsonPath, jdbcTypeVARCHAR}, 0) IFNULL(#{item.stockWeight, jdbcTypeDECIMAL}, 0)) /if /foreach /trim结合代码来看入参stockWeight为null则 if 条件里的 WHEN THEN 拼不进去但 close 部分的 ELSE extend_content 可以拼入结合前面prefix的 extend_contentcase 和 suffix 的 end, 连在一起就是:extend_contentcase ELSE extend_content end,显然这个语法不通过的。解决方法也比较简单就是把 ELSE 语句改成 WHEN THEN 语句具体如下这样即使if语句不拼接进去整体拼出来的语句如下也是符合预期的extend_contentcase WHEN 11 THEN extend_content end,至此批量更新所有明细全有stockWeight全无stockWeight部分有部分无stockWeight的场景均已支持完毕。再来一层回过头看还有另外一个思路处理更为简单不用 CASE WHEN THEN 方式新写一个批量更新方法这种批量更新方式依赖于开启 allowMultiQueries 不禁感慨条条大路通罗马并非所有的路都一帆风顺但我们终究抵达终点。
