动态SQL
-
基于OGNL表达式
-
完成多条件查询等逻辑实现
-
用于实现动态SQL的元素主要有
-
if
-
trim
-
where
-
set
-
choose(when、otherwise)
-
foreach
-
if
- 改造查询用户信息列表的演示示例,增加查询条件 用户角色(根据角色id查询) 用户名称(模糊查询)
<!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
<resultMap type="User" id="userList">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="phone" column="phone"/>
<result property="birthday" column="birthday"/>
<result property="gender" column="gender"/>
<result property="userRole" column="userRole"/>
<result property="userRoleName" column="roleName"/>
</resultMap>
<!-- 查询用户列表 -->
<select id="getUserList" resultMap="userList">
select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id
<if test="userRole != null">
and u.userRole = #{userRole}
</if>
<if test="userName != null and userName != ''">
and u.userName like CONCAT ('%',#{userName},'%')
</if>
</select>
where
-
简化SQL语句中where条件判断
-
智能处理and和or
<select id="getUserList" resultType="User">
select * from smbms_user
<where>
<if test="userName != null and userName != ''">
and userName like CONCAT ('%',#{userName},'%')
</if>
<if test="userRole != null">
and userRole = #