Mybatis入门五：探索Mapper Java接口与Map参数的灵活结合

1.mapper.xml 配置

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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.code.mapper.StudentMapper">

    <!-- 开启二级缓存 -->
    <cache size="1024" flushInterval="6000" readOnly="true" eviction="LRU"></cache>
    <!-- 引用另一个映射文件中配置的二级缓存 -->
    <!-- <cache-ref namespace="com.code.mapper.UserMapper"/> -->

    <resultMap type="StudentBean" id="studentMap">
        <id property="id" column="student_id" javaType="int"/>
        <result property="name" column="f_name" javaType="java.lang.String"/>
        <result property="age" column="f_age" javaType="int"/>
        <result property="className" column="f_classes" javaType="java.lang.String"/>
        <result property="java" column="f_java" javaType="int"/>
        <result property="db" column="f_db" javaType="int"/>
        <result property="web" column="f_web" javaType="int"/>
        <result property="html" column="f_html" javaType="int"/>
    </resultMap>

    <!--
        if条件:
        在条件个数不确定的时候使用
        比如根据各科分数进行查询(用户可选择查询哪几个以及分数线大于多少)
        传递过来的参数是一个Map,在Map中元素是 <科目,分数>
     -->
    <select id="findStudentByCondition" parameterType="java.util.Map" resultMap="studentMap">
        select * from t_student where 1=1
        <if test="java != null">
            and f_java &gt;= #{java}
        </if>
        <if test="web != null">
            and f_web &gt;= #{web}
        </if>
        <if test="db != null">
            and f_db &gt;= #{db}
        </if>
        <if test="html != null">
            and f_html &gt;= #{html}
        </if>
    </select>

    <!--
        where条件:专门用于查询,可以嵌套if条件使用
        1. 自动加上where语句以and/or开头,自动删除第一个and或or
        如果
     -->
    <select id="findStudentByCondition2" parameterType="java.util.Map" resultMap="studentMap">
        select * from t_student
        <where>
            <if test="java != null">
                and f_java &gt;= #{java}
            </if>
            <if test="web != null">
                and f_web &gt;= #{web}
            </if>
            <if test="db != null">
                and f_db &gt;= #{db}
            </if>
            <if test="html != null">
                and f_html &gt;= #{html}
            </if>
        </where>
    </select>

    <!--
        与where功能类似,提供了前缀和后缀的功能
     -->
    <select id="findStudentByCondition3" parameterType="java.util.Map" resultMap="studentMap">
        select * from t_student
        <trim prefix="where" prefixOverrides="and">
            <if test="java != null">
                and f_java &gt;= #{java}
            </if>
            <if test="web != null">
                and f_web &gt;= #{web}
            </if>
            <if test="db != null">
                and f_db &gt;= #{db}
            </if>
            <if test="html != null">
                and f_html &gt;= #{html}
            </if>
        </trim>
    </select>

    <!-- 根据Map集合中已有的key来更新数据表,不需要更新全部的数据表 -->
    <update id="updateStudentByCondition" parameterType="java.util.Map">
        update t_student
        <trim prefix="set" suffixOverrides=",">
            <if test="java != null">
                f_java = #{java},
            </if>
            <if test="web != null">
                f_web = #{web},
            </if>
            <if test="db != null">
                f_db = #{db},
            </if>
            <if test="html != null">
                f_html = #{html},
            </if>
        </trim>
        <if test="id != null">
            where student_id = #{id}
        </if>
    </update>

    <!--
        set条件:专门用于update语句
        当为修改的字段数目不确定时使用
        自动加上set,自动去掉最后一个逗号
     -->
    <update id="updateStudentByCondition2" parameterType="java.util.Map">
        update t_student
        <set>
            <if test="java != null">
                f_java = #{java},
            </if>
            <if test="web != null">
                f_web = #{web},
            </if>
            <if test="db != null">
                f_db = #{db},
            </if>
            <if test="html != null">
                f_html = #{html},
            </if>
        </set>
        <if test="id != null">
            where student_id = #{id}
        </if>
    </update>

    <!--
        主要用于查询in的情况
     -->
    <select id="findStudentInClass"  resultMap="studentMap">
        select * from t_student
            <if test="classes != null">
                <where>
                    f_classes in
                    <foreach item="className" collection="classes"
                        open="(" separator="," close=")">
                        #{className}
                    </foreach>
                </where>
            </if>

    </select>

    <!--
        choose:适用于条件个数不变,但是条件项改变的查询
     -->
    <select id="findStudentByOneCondition" resultMap="studentMap">
        select * from t_student
        <choose>
            <when test="condition == 'name'">
                where f_name like #{value}
            </when>
            <when test="condition == 'age'">
                where f_age = #{value}
            </when>
            <when test="condition == 'className'">
                where f_classes like '%${value}%'
            </when>
            <otherwise>
                where student_id > 0
            </otherwise>
        </choose>
    </select>

    <select id="findStudentByOneCondition2" parameterType="java.util.Map" resultMap="studentMap">
        select * from t_student
        <choose>
            <when test="condition == 'name'">
                where f_name like #{value}
            </when>
            <when test="condition == 'age'">
                where f_age = #{value}
            </when>
            <when test="condition == 'className'">
                where f_classes like '%${value}%'
            </when>
            <otherwise>
                where student_id > 0
            </otherwise>
        </choose>
    </select>

</mapper>

2.编写 mapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
package com.code.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.code.bean.UserBean;
public interface UserMapper {
	public int addUser(@Param("user")UserBean user);
	public int deleteUserByID(@Param("userID")int userID);
	public int updateUserNameByID(@Param("name")String name, @Param("id")int id);
	public int updateUserByID(@Param("user")UserBean user,@Param("id")int id);
	public UserBean getUserByID(@Param("id")int id);
	public List<UserBean> getAllUsers(@Param("colName")String colName);
	public List<UserBean> getAllUsersLikeName(@Param("name")String name);
}

当使用 Map 做为参数时，可以用 _parameter.containsKey(变量名)来判 断 map 中是否包含有些变量：

1
2
3
4
5
6
7
8
9
10
11
<select id="selectRule" parameterType="Map" resultType="com.ourangel.weixin.domain.Rule">
    SELECT ruleId,msgType,event,respId,reqValue,firstRespId,createDate,yn
    FROM oal_tb_rule
    WHERE yn = 1
    <if test="_parameter.containsKey('msgType')">
        AND msgType = #{msgType,jdbcType=VARCHAR})
    </if>
    <if test="_parameter.containsKey('event')">
        AND event = #{event,jdbcType=VARCHAR})
    </if>
</select>