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