Mybatis應用mysql存儲過程查詢數據實例

1. mysql存儲過程，這是個複雜查詢加上了判斷，比較複雜

創建程序搜索列表（在TradingAreaid varchar（50）中，在類別名稱varchar（100）中，在impellenceSort tinyint insint insign insign insperebin，PriceBegin in PriceEnd double，in PriceEnd double，in CommocitityName varchar（200），flag tinyint insint inflag tinyint insign inflag tinyint inst in flag tinyint insign in nimint nosigned）beginif flag = 0 businessId,B.shop_name,B.total_score,B.shop_logo,B.average_consume,D.category_name,B.shop_addressFROMuser_business_commodity ALEFT JOIN user_business B ON B.user_business_id = A.user_business_idLEFT JOIN user_business_category C ON C.business_id = b.user_business_idleft加入servication_category d on d.category_id = c.category_one_idwhere1 = 1andif（categoryName不是nulland length（trim（categoryName））> 0，d.category_name = category_name = categoryname = categoryname，category name，1 = 1）andif（價格begin！= 0，b.average_clave_clavery_converice_clavery_converice_clavery_clavery！ 0,B.average_consume <= priceEnd,1 = 1)ANDIF (commodityName IS NOT NULLAND LENGTH(TRIM(commodityName)) > 0,A. NAME LIKE concat('%', commodityName, '%'),1 = 1)AND B.is_delete = 0AND B.shop_setup_state = 1AND A.is_delete = 0AND C.is_delete = 0AND D.is_delete = 0group bya.user_business_idorder bycase IntelliCencesErt當1 then'b.total_order desc'when 2 then'b.total_score desc'when 3 then'b.create_time desc'else'else'else'b.create'b.create_create_time ass asc'end businessId,B.shop_name,B.total_score,B.shop_logo,B.average_consume,D.category_name,B.shop_addressFROMuser_business_commodity ALEFT JOIN user_business B ON B.user_business_id = A.user_business_idLEFT JOIN user_business_category C ON C.business_id = b.user_business_idleft加入servision_category d on d.Category_id = c.category_two_idwerh1 = 1andif（categoryName不是nulland length（trim（categoryName））> 0，d.category_name = category_name = categoryname = categoryName，1 = 1 = 1 = 1 = 1 = 1 = 0，price.auspry（b.a = b.a = price）= price（b.a = price！ 0,B.average_consume <= priceEnd,1 = 1)ANDIF (commodityName IS NOT NULLAND LENGTH(TRIM(commodityName)) > 0,A. NAME LIKE concat('%', commodityName, '%'),1 = 1)AND B.is_delete = 0AND B.shop_setup_state = 1AND A.is_delete = 0AND C.is_delete = 0AND D.is_delete = 0group bya.user_business_idorder bycase IntellionencesTort當1 then'b.total_order desc's then'b.total_score desc's desc'when 3 then'b.create_time desc'else_else'else'b.create_create_create_time asc asc'end;

2.查看存儲過程是否創建成功：：

顯示過程狀態；

3.SQLMAPPER文件：：

 <select ID =“ searchalllist1” parametermap =“ searchalllistmap” statementType =“ callable” resultype =“ com.dongjia168.platform.vo.erp.crm.crm.crm.crm.businessshopresp”> call searchAlllist（＃{tradingAreaId}，＃{categoryName}，＃{imic ensicesort}，＃{PriceBegin}，＃{PriceEnd}，＃{cossodityName}，＃{flag}）; </select>

 <parametermap id =“ searchalllistmap” type =“ com.dongjia168.platform.vo.erp.cr.crm.businessshopreq”> <參數屬性=“ tradingAreaID” jdbctype =“ integer” mode =“ in”/> <參數屬性=“ PriceBegin” jdbctype =“ double”模式=“ in”/> <parameter property =“ PriceEnd” JDBCTYEND“ JDBCTYPE”模式=“ in”/> </parametermap>

SQL語句一樣了

mybatis應用mysql存儲過程查詢數據實例就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持武林網。 ，也希望大家多多支持武林網。