1.创建mysql存储过程、这是个复杂查询加上了判断、比较复杂
手順SearchAllList(Tradingareaid varchar(50)、カテゴリネームvarchar(100)、Intelligencesort tinyint unsigned、in prishingname varchar(200)、flag tinyint unsigned)beginif flag = 0thenselectb.user_byness_id_id BusinessId、B.Shop_Name、B.Total_Score、B.Shop_Logo、B.Average_Consume、D.Category_Name、B.Shop_AddressFromUser_Business_ComdityAleft User_Business B on B.User_Business_id = A.User_Busines B.USER_BUSINESS_IDLEFT JOIN SERVICE_CATEGORY D ON D.CATEGORY_ID = C.CATELY_ONE_ID = 1ANDIF(categoryNameはnulland(trim(categoryname))> 0、d.category_name = categoryName、1 = 1)andif(pricebegin!= 0、b。 0、b.average_consume <= priceend、1 = 1)andif(commoditynameはnulland length(trim(commodityname))> 0、a。concat( '%'、commodityname、 '%')、1 = 1)、b.is_delete = 0and b.shop_setup_state = 0and a.is_delete = 0and 0and 0Group bya.user_business_idordord bycase intelligencesortorthwhen 1 then'b.total_order desc'when 2 then'b.total_score desc BusinessId、B.Shop_Name、B.Total_Score、B.Shop_Logo、B.Average_Consume、D.Category_Name、B.Shop_AddressFromUser_Business_ComdityAleft User_Business B on B.User_Business_id = A.User_Busines b.user_business_idleft join service_category d on d.category_id = c.category_two_id = 1andif(categorynameはnulland length(trim(categoryname))> 0、d.category_name = categoryname、1 = 1)andif(pricebegin!= 0、b。 0、b.average_consume <= priceend、1 = 1)andif(commoditynameはnulland length(trim(commodityname))> 0、a。concat( '%'、commodityname、 '%')、1 = 1)、b.is_delete = 0and b.shop_setup_state = 0and a.is_delete = 0and 0and 0Group bya.user_business_idordord bycase intelligencesortorthen 1 then'b.total_order desc'when 2 then'b.total_score desc
2.查看存储过程是否创建成功:
手順のステータスを表示します。
3.SQLMAPPER文件:
<select id = "searchalllist1" parametermap = "searchallListmap" statementType = "callable" resultType = "com.dongjia168.platform.vo.erp.crm.businessshopresp"> call SearchAlllist(#{tradingareaid}、#{categoryname}、#{intelligencesort}、#{pricebegin}、#{priceend}、#{commodityName}、#{flag}); </select><parametermap id = "SearchAllListmap" Type = "com.dongjia168.platform.vo.cr.crm.businessshopreq"> <parameter Property = "tradingareaid" jdbctype = "varchar" mode = "in"/>> <パラメータープロパティ= "jdbctype =" varchar "/> <parmetrプロパティ= "Intelligencesort" jdbctype = "integer" mode = "in"/> <parameter Property = "pricebegin" jdbctype = "double" mode = "in"/> <parameterプロパティ= "プライスエンド" jdbctype = "double" mode = "in"/>> <parproperty = "modityname" in "in" in "in" in ""/> "in" in "/>" bamter jdbctype = "integer" mode = "in"/> </parametermap>
sql语句一样了
mybatis