Mybatis应用mysql存储过程查询数据实例
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存储过程查询数据实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。