java中的接口是类吗
333
2023-03-02
Mybatis应用mysql存储过程查询数据实例
Mybatis应用mysql存储过程查询数据实例
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 intelligenhttp://ceSort
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(http://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文件：
CALL searchAllList(#{tradingAreaId},#{categoryName},#{intelligenceSort},#{priceBegin},#{priceEnd},#{commodityName},#{flag});
其他和直接调用sql语句一样了
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
暂时没有评论，来抢沙发吧~