服务器之家：专注于服务器技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|JAVA教程|ASP教程|编程技术|正则表达式|C/C++|IOS|C#|Swift|Android|JavaScript|易语言|

服务器之家 - 编程语言 - JAVA教程 - Mybatis应用mysql存储过程查询数据实例

Mybatis应用mysql存储过程查询数据实例

2021-03-13 14:20Pionner17 JAVA教程

下面小编就为大家分享一篇Mybatis应用mysql存储过程查询数据实例，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

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

  • mysql
  • 数据
  • 存储过程
  • 查询
  • mybatis

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    501 　 406 　 2000 　 1433 　 415 　 401 　 空心菱形 　 选择排序法 　 随机抽取 　 内存卡 　 下载器 　 十六进制字符 　 响应 　 静态工厂 　 构造函数 　 swt 　 Overview 　 对象池模式 　 子线程 　 短信猫 　 htmlparser 　 支票 　 金额大写转换 　 界面开发 　 AspectJ 　 面向切面编程 　 方法名 　 网址信息 　 动态代理模式 　 线程安全 　
    © 2019-2021 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图