1. 创建 mysql 存储过程 ， 这是个复杂查询加上了判断 比较复杂
Crear procedimiento SearchAllist (en Tradingareaid Varchar (50), en CategoryName Varchar (100), en IntelligencesSt Tinyint sin firmar, en PriceBegin Double, en Pricdeend Doble, en CommodityName Varchar (200), en Flag Tinyint Unsigned) Begnif = 0 Thenselectb.User_Business_IDId BusinessId, B.Shop_Name, B.Total_Score, B.Shop_Logo, B.Average_Consume, D.Category_Name, B.Shop_addressFromuser_Business_Commodity Aleft Unirse a User_Business B en B.User_Business_ID = A.User_Business_idleft se unen a User_Business_Category C en C.Buse B.user_business_idleft ome service_category d en d.category_id = c.category_one_idwhere1 = 1andif (categoryName no es nulland longitud (trim (categoryName))> 0, d.Category_Name = CategyName, 1 = 1) y PriceBeGin! = 0, b.average_consume> = PriceBegin, 1 = 1 = 1 = 1) y PriceBeGin! = 0, b.average_consume> = PriceBegin, 1 = 1 = 1) 0, B.average_consume <= Pricdeend, 1 = 1) y (CommodityName no es nulland longitud (trim (CommodityName))> 0, a. Name Like Concat ('%', CommodityName, '%'), 1 = 1) y b.is_delete = 0and B.Shop_Setup_state = 1and A.Is_Delete = 0and C.IS_DELET 0group bya.user_business_idorder bycase IntelligencesStWhen 1 entonces'b.total_order desc'when 2 entonces'b.total_score desc'when 3 entonces'b.create_time desc'else'b.create_time asc'end; elseseelectb.user_business_id BusinessId, B.Shop_Name, B.Total_Score, B.Shop_Logo, B.Average_Consume, D.Category_Name, B.Shop_addressFromuser_Business_Commodity Aleft Unirse a User_Business B en B.User_Business_ID = A.User_Business_idleft se unen a User_Business_Category C en C.Buse B.user_business_idleft Únete a Service_Category D en d.category_id = c.category_two_idwhere1 = 1Andif (categoryName no es nulland longitud (trim (categoryName))> 0, d.category_name = categoryName, 1 = 1) andif (PriceBeGin! = 0, b.average_consume> = PriceBeGin, 1 = 1) (PriceEnd! 0, B.average_consume <= Pricdeend, 1 = 1) y (CommodityName no es nulland longitud (trim (CommodityName))> 0, a. Name Like Concat ('%', CommodityName, '%'), 1 = 1) y b.is_delete = 0and B.Shop_Setup_state = 1and A.Is_Delete = 0and C.IS_DELET 0group bya.user_business_idorder bycase IntelligencesStWhen 1 entonces'b.total_order Desc'When 2 entonces'B.Total_Score Desc'When 3 entonces'B.Create_Time Desc'else'B.Create_Time ASC'END; end if; end;2. 查看存储过程是否创建成功.
Mostrar estado del procedimiento;
3.sqlMapper 文件 ：
<select id = "SearchAllIST1" Parametermap = "SearchAllistMap" DeclaryPe = "Callable" dentType = "com.dongjia168.platform.vo.erp.crm.businessshopesp"> llamar SearchAllist (#{TradingaraId},#{categoryName},#{IntelligencesIt},#{PriceBegin},#{Pricdeend},#{CommodityName},#{flag}); </select><parameterMap id="searchAllListMap" type="com.dongjia168.platform.vo.erp.crm.BusinessShopReq"> <parameter property="tradingAreaId" jdbcType="VARCHAR" mode="IN"/> <parameter property="categoryName" jdbcType="VARCHAR" mode="IN"/> <parameter propiedad = "inteligencia" jdbctype = "entero" mode = "en"/> <parámetro propiedad = "preciobegin" jdbctype = "double" mode = "en"/> <parameter Property = "Priceend" jdbctype = "double" mode = "en"/> <parameter Property = "CommodityName" jdbctypee = "var" mode "en"/"en"/<parameter Property = "CommodityName" JDBCTYPE = "VAR" VAR "MODE" en "/" En ". Propiedad = "Flag" JDBCType = "Integer" Mode = "en"/> </Parametermap>
其他和直接调用 SQL 语句一样了
以上这篇 mybatis 应用 mysql 存储过程查询数据实例就是小编分享给大家的全部内容了 ， 希望能给大家一个参考 也希望大家多多支持武林网。