- <if>
- <choose>
- <where>
- <set>
- <foreach>
- <bind>
例子1:<if>、<where>、<sql>、<include>
- 有一个用户表t_user和角色表t_role,通过传入keywords和userListRoleId来查询所有的用户表,当然可以添加keywords和userListRoleId的额外条件。
- 比如:我有以下的数据库:
t_user:
t_role:
sql语句为:
SELECT u.*,r.roleName FROM t_user AS u,t_role AS r WHERE u.roleId=r.roleId AND u.userId = 1
结果为:
mybatis的mapper文件中可以这样写:
<!--查询所有用户集合where语句 -->
<sql id="selectUserListWhere">
<where>
u.roleId=r.roleId
<if test="keywords!=null and keywords!=''" >
and (u.username like CONCAT('%',#{keywords},'%') or u.loginName like CONCAT('%',#{keywords},'%'))
</if>
<if test="userListRoleId!=null and userListRoleId!=''" >
and (u.roleId=#{userListRoleId})
</if>
</where>
</sql>
<!--查询所有用户集合列表 -->
<select id="selectUserList" parameterType="String" resultType="User">
select u.*,r.roleName from t_user as u,t_role as r
<include refid="selectUserListWhere" />
order by registerTime desc
</select>