Mybatis应用mysql存储过程查询数据实例
作者:Pionner17 时间:2024-01-16 09:05:52
1.创建mysql存储过程,这是个复杂查询加上了判断,比较复杂
CREATE PROCEDURE searchAllList (
IN tradingAreaId VARCHAR (50),
IN categoryName VARCHAR (100),
IN intelligenceSort TINYINT UNSIGNED,
IN priceBegin DOUBLE,
IN priceEnd DOUBLE,
IN commodityName VARCHAR (200),
IN flag TINYINT UNSIGNED
)
BEGIN
IF flag = 0 THEN
SELECT
B.user_business_id businessId,
B.shop_name,
B.total_score,
B.shop_logo,
B.average_consume,
D.category_name,
B.shop_address
FROM
user_business_commodity A
LEFT JOIN user_business B ON B.user_business_id = A.user_business_id
LEFT JOIN user_business_category C ON C.business_id = B.user_business_id
LEFT JOIN service_category D ON D.category_id = C.category_one_id
WHERE
1 = 1
AND
IF (
categoryName IS NOT NULL
AND LENGTH(TRIM(categoryName)) > 0,
D.category_name = categoryName,
1 = 1
)
AND
IF (
priceBegin != 0,
B.average_consume >= priceBegin,
1 = 1
)
AND
IF (
priceEnd != 0,
B.average_consume <= priceEnd,
1 = 1
)
AND
IF (
commodityName IS NOT NULL
AND LENGTH(TRIM(commodityName)) > 0,
A. NAME LIKE concat('%', commodityName, '%'),
1 = 1
)
AND B.is_delete = 0
AND B.shop_setup_state = 1
AND A.is_delete = 0
AND C.is_delete = 0
AND D.is_delete = 0
GROUP BY
A.user_business_id
ORDER BY
CASE intelligenceSort
WHEN 1 THEN
'B.total_order DESC'
WHEN 2 THEN
'B.total_score DESC'
WHEN 3 THEN
'B.create_time DESC'
ELSE
'B.create_time ASC'
END;
ELSE
SELECT
B.user_business_id businessId,
B.shop_name,
B.total_score,
B.shop_logo,
B.average_consume,
D.category_name,
B.shop_address
FROM
user_business_commodity A
LEFT JOIN user_business B ON B.user_business_id = A.user_business_id
LEFT JOIN user_business_category C ON C.business_id = B.user_business_id
LEFT JOIN service_category D ON D.category_id = C.category_two_id
WHERE
1 = 1
AND
IF (
categoryName IS NOT NULL
AND LENGTH(TRIM(categoryName)) > 0,
D.category_name = categoryName,
1 = 1
)
AND
IF (
priceBegin != 0,
B.average_consume >= priceBegin,
1 = 1
)
AND
IF (
priceEnd != 0,
B.average_consume <= priceEnd,
1 = 1
)
AND
IF (
commodityName IS NOT NULL
AND LENGTH(TRIM(commodityName)) > 0,
A. NAME LIKE concat('%', commodityName, '%'),
1 = 1
)
AND B.is_delete = 0
AND B.shop_setup_state = 1
AND A.is_delete = 0
AND C.is_delete = 0
AND D.is_delete = 0
GROUP BY
A.user_business_id
ORDER BY
CASE intelligenceSort
WHEN 1 THEN
'B.total_order DESC'
WHEN 2 THEN
'B.total_score DESC'
WHEN 3 THEN
'B.create_time DESC'
ELSE
'B.create_time ASC'
END;
END IF;
END;
2.查看存储过程是否创建成功:
show procedure status;
3.sqlMapper文件:
<select id="searchAllList1" parameterMap="searchAllListMap" statementType="CALLABLE" resultType="com.dongjia168.platform.vo.erp.crm.BusinessShopResp">
CALL searchAllList(#{tradingAreaId},#{categoryName},#{intelligenceSort},#{priceBegin},#{priceEnd},#{commodityName},#{flag});
</select>
<parameterMap id="searchAllListMap" type="com.dongjia168.platform.vo.erp.crm.BusinessShopReq">
<parameter property="tradingAreaId" jdbcType="VARCHAR" mode="IN"/>
<parameter property="categoryName" jdbcType="VARCHAR" mode="IN"/>
<parameter property="intelligenceSort" jdbcType="INTEGER" mode="IN"/>
<parameter property="priceBegin" jdbcType="DOUBLE" mode="IN"/>
<parameter property="priceEnd" jdbcType="DOUBLE" mode="IN"/>
<parameter property="commodityName" jdbcType="VARCHAR" mode="IN"/>
<parameter property="flag" jdbcType="INTEGER" mode="IN"/>
</parameterMap>
其他和直接调用sql语句一样了
来源:http://blog.csdn.net/mengyinjun217/article/details/78933548
标签:Mybatis,mysql,存储,查询,数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python 计算两个列表的相关系数的实现
2021-10-07 10:43:57
![](https://img.aspxhome.com/file/2023/9/94159_0s.png)
Python对象与引用的介绍
2023-04-30 12:51:59
![](https://img.aspxhome.com/file/2023/9/102849_0s.png)
如何用Python提取10000份log中的产品信息
2023-06-30 06:23:21
![](https://img.aspxhome.com/file/2023/2/118632_0s.png)
Python合并多个Excel数据的方法
2022-11-02 06:31:33
![](https://img.aspxhome.com/file/2023/0/81820_0s.png)
HTML5 移动页面自适应手机屏幕宽度详解
2022-08-14 23:14:43
Python中那些 Pythonic的写法详解
2023-09-14 20:01:59
Python读写压缩文件的方法
2023-06-02 19:37:53
初学者快看,Python下划线的五个作用介绍
2023-03-03 18:23:40
![](https://img.aspxhome.com/file/2023/7/68297_0s.jpg)
谈谈为什么你的 JavaScript 代码如此冗长
2024-04-17 09:50:07
我所钟爱的HTML5资源
2010-07-23 09:25:00
![](https://img.aspxhome.com/file/UploadPic/20107/23/01-73s.jpg)
Python使用新浪微博API发送微博的例子
2023-12-22 08:43:45
![](https://img.aspxhome.com/file/2023/1/81471_0s.png)
深入了解Django中间件及其方法
2022-04-22 09:19:10
![](https://img.aspxhome.com/file/2023/0/70690_0s.jpg)
浅谈python为什么不需要三目运算符和switch
2022-04-02 16:22:39
网页优化之加速图片显示(CSS Sprite)
2007-09-29 21:39:00
![](https://img.aspxhome.com/file/UploadPic/20079/29/200792921437566s.gif)
JavaScript多种页面刷新方法小结
2024-04-22 12:53:09
解决layer弹出层msg的文字不显示的问题
2024-04-23 09:07:51
![](https://img.aspxhome.com/file/2023/3/135933_0s.jpg)
详解如何在vue项目中使用layui框架及采坑
2024-04-09 10:58:35
解决pytorch 的state_dict()拷贝问题
2022-10-05 22:03:57
tensorflow2.0教程之Keras快速入门
2022-05-11 22:34:17
python tkinter模块的简单使用
2021-11-20 16:52:33
![](https://img.aspxhome.com/file/2023/5/75325_0s.png)