1.創建mysql存儲過程，這是個復雜查詢加上了判斷，比較復雜
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
CREATE PROCEDURE searchAllList ( IN tradingAreaId VARCHAR (50), IN categoryName VARCHAR (100), IN intelligenceSort TINYINT UNSIGNED, IN priceBegin DOUBLE, IN priceEnd DOUBLE, IN commodityName VARCHAR (200), IN flag TINYINT UNSIGNED)BEGINIF flag = 0 THEN SELECT B.user_business_id businessId, B.shop_name, B.total_score, B.shop_logo, B.average_consume, D.category_name, B.shop_address FROM user_business_commodity A LEFT JOIN user_business B ON B.user_business_id = A.user_business_id LEFT JOIN user_business_category C ON C.business_id = B.user_business_id LEFT JOIN service_category D ON D.category_id = C.category_one_id WHERE 1 = 1 AND IF ( categoryName IS NOT NULL AND LENGTH(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 ( commodityName IS NOT NULL AND LENGTH(TRIM(commodityName)) > 0, A. NAME LIKE concat('%', commodityName, '%'), 1 = 1 ) AND B.is_delete = 0 AND B.shop_setup_state = 1 AND A.is_delete = 0 AND C.is_delete = 0 AND D.is_delete = 0 GROUP BY A.user_business_id ORDER BY CASE intelligenceSort WHEN 1 THEN 'B.total_order DESC' WHEN 2 THEN 'B.total_score DESC' WHEN 3 THEN 'B.create_time DESC' ELSE 'B.create_time ASC' END;ELSE SELECT B.user_business_id businessId, B.shop_name, B.total_score, B.shop_logo, B.average_consume, D.category_name, B.shop_address FROM user_business_commodity A LEFT JOIN user_business B ON B.user_business_id = A.user_business_id LEFT JOIN user_business_category C ON C.business_id = B.user_business_id LEFT JOIN service_category D ON D.category_id = C.category_two_id WHERE 1 = 1 AND IF ( categoryName IS NOT NULL AND LENGTH(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 ( commodityName IS NOT NULL AND LENGTH(TRIM(commodityName)) > 0, A. NAME LIKE concat('%', commodityName, '%'), 1 = 1 ) AND B.is_delete = 0 AND B.shop_setup_state = 1 AND A.is_delete = 0 AND C.is_delete = 0 AND D.is_delete = 0 GROUP BY A.user_business_id ORDER BY CASE intelligenceSort WHEN 1 THEN 'B.total_order DESC' WHEN 2 THEN 'B.total_score DESC' WHEN 3 THEN 'B.create_time DESC' ELSE 'B.create_time ASC' END;END IF;END; |
2.查看存儲過程是否創建成功：
|
1
|
show procedure status; |
3.sqlMapper文件：
|
1
2
3
|
<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> |
|
1
2
3
4
5
6
7
8
9
|
<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 property="intelligenceSort" jdbcType="INTEGER" mode="IN"/> <parameter property="priceBegin" jdbcType="DOUBLE" mode="IN"/> <parameter property="priceEnd" jdbcType="DOUBLE" mode="IN"/> <parameter property="commodityName" jdbcType="VARCHAR" mode="IN"/> <parameter property="flag" jdbcType="INTEGER" mode="IN"/> </parameterMap> |
其他和直接調用sql語句一樣了
以上這篇Mybatis應用mysql存儲過程查詢數據實例就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持服務器之家。
原文鏈接：http://blog.csdn.net/mengyinjun217/article/details/78933548
