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
)
BEGIN
IF 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