微客导航 » 文章资讯 » Mybatis应用mysql存储过程查询数据实例

Mybatis应用mysql存储过程查询数据实例

2023-09-18 12:58:05 317

1.创建mysql存储过程，这是个复杂查询加上了判断，比较复杂

CREATEPROCEDUREsearchAllList(
	INtradingAreaIdVARCHAR(50),
	INcategoryNameVARCHAR(100),
	INintelligenceSortTINYINTUNSIGNED,
	INpriceBeginDOUBLE,
	INpriceEndDOUBLE,
	INcommodityNameVARCHAR(200),
	INflagTINYINTUNSIGNED
)
BEGIN
IFflag=0THEN
	SELECT
		B.user_business_idbusinessId,
		B.shop_name,
		B.total_score,
		B.shop_logo,
		B.average_consume,
		D.category_name,
		B.shop_address
	FROM
		user_business_commodityA
	LEFTJOINuser_businessBONB.user_business_id=A.user_business_id
	LEFTJOINuser_business_categoryCONC.business_id=B.user_business_id
	LEFTJOINservice_categoryDOND.category_id=C.category_one_id
	WHERE
		1=1
	AND
	IF(
		categoryNameISNOTNULL
		ANDLENGTH(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(
		commodityNameISNOTNULL
		ANDLENGTH(TRIM(commodityName))>0,
		A.NAMELIKEconcat('%',commodityName,'%'),
		1=1
	)
	ANDB.is_delete=0
	ANDB.shop_setup_state=1
	ANDA.is_delete=0
	ANDC.is_delete=0
	ANDD.is_delete=0
	GROUPBY
		A.user_business_id
	ORDERBY
		CASEintelligenceSort
	WHEN1THEN
		'B.total_orderDESC'
	WHEN2THEN
		'B.total_scoreDESC'
	WHEN3THEN
		'B.create_timeDESC'
	ELSE
		'B.create_timeASC'
	END;

ELSE
	SELECT
		B.user_business_idbusinessId,
		B.shop_name,
		B.total_score,
		B.shop_logo,
		B.average_consume,
		D.category_name,
		B.shop_address
	FROM
		user_business_commodityA
	LEFTJOINuser_businessBONB.user_business_id=A.user_business_id
	LEFTJOINuser_business_categoryCONC.business_id=B.user_business_id
	LEFTJOINservice_categoryDOND.category_id=C.category_two_id
	WHERE
		1=1
	AND
	IF(
		categoryNameISNOTNULL
		ANDLENGTH(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(
		commodityNameISNOTNULL
		ANDLENGTH(TRIM(commodityName))>0,
		A.NAMELIKEconcat('%',commodityName,'%'),
		1=1
	)
	ANDB.is_delete=0
	ANDB.shop_setup_state=1
	ANDA.is_delete=0
	ANDC.is_delete=0
	ANDD.is_delete=0
	GROUPBY
		A.user_business_id
	ORDERBY
		CASEintelligenceSort
	WHEN1THEN
		'B.total_orderDESC'
	WHEN2THEN
		'B.total_scoreDESC'
	WHEN3THEN
		'B.create_timeDESC'
	ELSE
		'B.create_timeASC'
	END;

ENDIF;
END;

2.查看存储过程是否创建成功：

showprocedurestatus;

3.sqlMapper文件：


CALLsearchAllList(#{tradingAreaId},#{categoryName},#{intelligenceSort},#{priceBegin},#{priceEnd},#{commodityName},#{flag});

其他和直接调用sql语句一样了

以上这篇Mybatis应用mysql存储过程查询数据实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。

返回顶部
3162201930
czq8825@qq.com

Mybatis应用mysql存储过程查询数据实例

热门推荐

随机推荐