动态SQL查询
1、if标签
将上一篇getUserList中传入实体类
public List<User> getUserList(User user);
改为传参:
public List<User> getUserList(@Param("userName")String userName,
@Param("userRole")Integer roleId);
当输入参数为null时无法查询出想要的结果,因此加入if判断非空来拼接:
UserMapper.xml语句
<!-- 当数据库中的字段信息与对象的属性不一致时需要通过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 userRole=#{userRole}
</if>
<if test="userName != null and userName !=''">
and userName like CONCAT('%',#{userName},'%')
</if>
</select>
Test测试类(roleId传入值为空)及结果:
@Test
public void testGetUserList() {
List<User> userList=null;
SqlSession sqlSession = null;
/*
* User user = new User(); user.setUserName("赵"); user.setUserRole(2);
*/
String userName = "孙";
Integer roleId = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
//创建sqlSession
//调用mapper文件来对数据进行操作,必须将mapper文件引入到mybatis-config.xml文件中
//userList=sqlSession.selectList("cn.smbms.dao.user.UserMapper.getUserList");
userList=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for(User _user:userList) {
logger.debug("testGetUserList---->"+_user.getUserCode()+" and username:"+_user.getUserName()+" and userRoleName:");
}
}
2、where标签:
将上UserMapper.xml文件改为
<select id="getUserList" resultType="user">
select u.* from smbms_user u where
<if test = "userName != null and userName != ''">
and userName like CONCAT('%',#{userName},'%')
</if>
<if test = "userRole != null">
and userRole = #{userRole}
</if>
</select>
当输入userName为空时,语句拼接后变为
因此需要引入where标签动态:
<select id="getUserList" resultType="user">
select u.* from smbms_user u
<where>
<if test = "userName != null and userName != ''">
and userName like CONCAT('%',#{userName},'%')
</if>
<if test = "userRole != null">
and userRole = #{userRole}
</if>
</where>
</select>
where标签会自动剔除and和or语句,运行结果如下:
3、trim标签:
trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides;正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where元素的功能。
用trim代替where
<select id="getUserList" resultType="user">
select u.* from smbms_user u
<trim prefix = "where" prefixOverrides = "and | or">
<if test = "userName != null and userName != ''">
and userName like CONCAT('%',#{userName},'%')
</if>
<if test = "userRole != null">
and userRole = #{userRole}
</if>
</trim>
</select>
结果与之前一致