SQL: DECLARE @DocId INT=0, @prodcuts NVARCHAR(MAX) =#{products}; IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL DROP TABLE #TempProduct; SELECT * INTO #TempProduct FROM OPENJSON(@prodcuts) WITH(goodID INT,price DECIMAL(16,2),buyNum INT,attribute VARCHAR(200) ) SELECT TP.*, P.IsUsePoints AS isUsePoints, P.IsPointProduct AS isPointProduct, G.Source AS [source], G.MouldNo AS mouldNo, P.ProductGroupID AS productGroupId FROM #TempProduct TP INNER JOIN dbo.cGoods G WITH(NOLOCK)ON G.ID=TP.goodID INNER JOIN dbo.cProducts P WITH(NOLOCK)ON P.MouldNo=G.MouldNo WHERE G.Enabled=1 AND P.Enabled=1;入参:为下面结构的JSON字符串格式
[
{
"goodID": 1785711286,
"buyNum": 1,
"price": 0.1,
"stockQtyStore": 0,
"stockQtyWeb": 11,
"attribute": "",
"storeShopID": 0,
"isKplus": 0,
"isPointDeductCoupon": 0
},
{
"goodID": 3134609,
"buyNum": 2,
"price": 620,
"stockQtyStore": 0,
"stockQtyWeb": 20,
"attribute": "",
"storeShopID": 0,
"isKplus": 0,
"isPointDeductCoupon": 0
}
]
实体:
public class ProductParam implements Serializable { private static final long serialVersionUID = 3469804689628919429L; /** * 商品ID */ @JsonProperty("goodID") public Integer goodId; /** * 商品数量 */ public Integer buyNum; /** * 单价 */ public BigDecimal price; /** * 附加属性 */ public String attribute; /** * 活动ID */ public Integer marketingId; /** * 活动类型 1=满克减 2=分段克减 */ public Integer type; /** * 优惠金额 */ public BigDecimal reduceAmount; /** * 线下礼券ID */ public String offlineActivityId; public BigDecimal getTotalPrice(){ return price.multiply(new BigDecimal(Integer.toString(this.buyNum))); } }
问题:goodId用了驼峰格式后面的d是小写,但是实体中用
@JsonProperty("goodID")指定了大小写格式,导致转换json后的数据goodID读取不到,要与注解@JsonProperty指定的一致,或者没指定的时候默认是按照驼峰格式转换,实在不确定可以先把转换后的入参json字符串打印出来,在sql的入参对应指定转换的字段名保持一致即可正常转换