您好,登錄后才能下訂單哦!
今天小編給大家分享一下MybatisPlusException:Failed to process,Error SQL異常報錯如何解決的相關(guān)知識點,內(nèi)容詳細(xì),邏輯清晰,相信大部分人都還太了解這方面的知識,所以分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后有所收獲,下面我們一起來了解一下吧。
Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: SELECT
goods_spu.`id`,
goods_spu.`tenant_id`,
goods_spu.`shop_id`,
goods_spu.`spu_code`,
goods_spu.`name`,
goods_spu.`sell_point`,
null `description`,
goods_spu.`category_first`,
goods_spu.`category_second`,
goods_spu.`category_shop_first`,
goods_spu.`category_shop_second`,
goods_spu.`pic_urls`,
goods_spu.`shelf`,
goods_spu.`sort`,
goods_spu.`price_down`,
goods_spu.`price_up`,
goods_spu.`sale_num`,
goods_spu.`create_time`,
goods_spu.`update_time`,
goods_spu.`spec_type`,
goods_spu.`del_flag`,
goods_spu.`points_give_switch`,
goods_spu.`points_give_num`,
goods_spu.`points_deduct_switch`,
goods_spu.`points_deduct_scale`,
goods_spu.`points_deduct_amount`,
goods_spu.`freight_templat_id`,
goods_spu.`verify_status`,
goods_spu.`verify_detail`,
goods_spu.`channel`,
goods_spu.`self_pick_addr_id`
, sum(s.stock) as stock
FROM goods_spu as goods_spu
left join goods_sku as s on goods_spu.id=s.spu_id
group by goods_spu.id;
WHERE goods_spu.`name` LIKE CONCAT('%',?,'%') ### Cause:
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy180.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:121)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy195.selectPage1WithStock(Unknown Source)
at com.joolun.cloud.mall.admin.service.impl.GoodsSpuServiceImpl.page1WithStock(GoodsSpuServiceImpl.java:72)
at com.joolun.cloud.mall.admin.service.impl.GoodsSpuServiceImpl$$FastClassBySpringCGLIB$$cb4904f8.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.joolun.cloud.mall.admin.service.impl.GoodsSpuServiceImpl$$EnhancerBySpringCGLIB$$936a8c1a.page1WithStock(<generated>)
at com.joolun.cloud.mall.admin.controller.GoodsSpuController.getGoodsSpuPageWithStock(GoodsSpuController.java:82)
at com.joolun.cloud.mall.admin.controller.GoodsSpuController$$FastClassBySpringCGLIB$$4d6ff776.invoke(<generated>)
運營提出,對首頁進(jìn)行店鋪裝修時,需看到庫存字段,
這樣不會上到了0庫存的品,導(dǎo)致了用戶下單體驗的問題。
因此,在Mybatis Plus中新增了庫存字段以展示
因為原來這個接口是是做了一個基于Mybatis Plus的分頁,但是因為庫存這個字段,存在sku的表中
因此使用left join ,但是又因為SPU與SKU之間是一對多的關(guān)系,因此會有多個0庫存的情況,
這里采用了count進(jìn)行加和的方式,不得不說,設(shè)計的非常巧妙,因為加和起來庫存都為0的話,說明肯定是0庫存的呀。
因此有了以下代碼
<select id="selectPage1WithStock" resultMap="goodsSpuMap5"> SELECT <include refid="goodsSpuSql2"/>, sum(s.stock) as stock FROM goods_spu as goods_spu left join goods_sku as s on goods_spu.id=s.spu_id group by goods_spu.id; <where> <if test="query.shopId != null"> AND goods_spu.`shop_id` = #{query.shopId} </if> <if test="query.shelf != null"> AND goods_spu.`shelf` = #{query.shelf} </if> <if test="query.verifyStatus != null"> AND goods_spu.`verify_status` = #{query.verifyStatus} </if> <if test="query.categorySecond != null"> AND goods_spu.`category_second` = #{query.categorySecond} </if> <if test="query.name != null"> AND goods_spu.`name` LIKE CONCAT('%',#{query.name},'%') </if> <if test="query.spuCode != null"> AND goods_spu.`spu_code` = #{query.spuCode} </if> <if test="query.specType != null"> AND goods_spu.`spec_type` = #{query.specType} </if> <if test="query.pointsGiveSwitch != null"> AND goods_spu.`points_give_switch` = #{query.pointsGiveSwitch} </if> <if test="query.pointsDeductSwitch != null"> AND goods_spu.`points_deduct_switch` = #{query.pointsDeductSwitch} </if> </where> </select>
以下代碼看起來,沒有問題,但是確有一個問題,注意在,存在一個分號,
我們都知道,分號是一段SQL語句的結(jié)尾,所以,
以上代碼肯定是后面的語句肯定是執(zhí)行不到的
但是到底是group by 語句放在前面呢?還是where 語句在前,這個問題,可以直接在數(shù)據(jù)庫驗證下即可
寫法一:WHERE在前,GROUP BY在后
SELECT goods_spu.*, sum(s.stock) AS stock FROM goods_spu AS goods_spu LEFT JOIN goods_sku AS s ON goods_spu.id = s.spu_id WHERE goods_spu.`name` LIKE CONCAT('%', 'A', '%') GROUP BY goods_spu.id
發(fā)現(xiàn),數(shù)據(jù)能夠正常查詢
寫法二:GROUP BY在前,WHERE在后
會發(fā)現(xiàn)無法正常查詢出來結(jié)果
經(jīng)過以上的驗證,得到了最終修改之后的SQL語句如下
<select id="selectPage1WithStock" resultMap="goodsSpuMap5"> SELECT <include refid="goodsSpuSql2"/>, sum(s.stock) as stock FROM goods_spu as goods_spu left join goods_sku as s on goods_spu.id=s.spu_id <where> <if test="query.shopId != null"> AND goods_spu.`shop_id` = #{query.shopId} </if> <if test="query.shelf != null"> AND goods_spu.`shelf` = #{query.shelf} </if> <if test="query.verifyStatus != null"> AND goods_spu.`verify_status` = #{query.verifyStatus} </if> <if test="query.categorySecond != null"> AND goods_spu.`category_second` = #{query.categorySecond} </if> <if test="query.name != null"> AND goods_spu.`name` LIKE CONCAT('%',#{query.name},'%') </if> <if test="query.spuCode != null"> AND goods_spu.`spu_code` = #{query.spuCode} </if> <if test="query.specType != null"> AND goods_spu.`spec_type` = #{query.specType} </if> <if test="query.pointsGiveSwitch != null"> AND goods_spu.`points_give_switch` = #{query.pointsGiveSwitch} </if> <if test="query.pointsDeductSwitch != null"> AND goods_spu.`points_deduct_switch` = #{query.pointsDeductSwitch} </if> </where> group by goods_spu.id </select>
經(jīng)過測試驗證,分頁也能正常,而且模糊查詢也順利。
出現(xiàn)該問題的原因有兩個
(1)對Mybatis的 where標(biāo)簽 和 if 標(biāo)簽不熟悉
where標(biāo)簽的三個特性:
where標(biāo)簽可以被解析為where 關(guān)鍵字只有if標(biāo)簽有內(nèi)容的情況下才會插入where子句;第二,若子句的開通為 “AND” 或 “OR”,where標(biāo)簽會將它替換去除; (2)開發(fā)完成代碼后,應(yīng)該做回歸測試
回歸測試是指修改了舊代碼后,重新進(jìn)行測試以確認(rèn)修改沒有引入新的錯誤或?qū)е缕渌a產(chǎn)生錯誤。
以上就是“MybatisPlusException:Failed to process,Error SQL異常報錯如何解決”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家閱讀完這篇文章都有很大的收獲,小編每天都會為大家更新不同的知識,如果還想學(xué)習(xí)更多的知識,請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。