一、本课目标
- 掌握使用if+where完成多条件查询
- 掌握使用if+trim完成多条件查询
二、动态SQL
- 基于OGNL表达式
- 使用动态SQL完成多条件查询等逻辑实现
- 用于实现动态SQL的元素主要有：
if、trim、where、set、choose（when、otherwise）、foreach
三、if
需求说明：改造查询用户信息列表的演示示例，增加查询条件
- 用户角色（根据角色id查询）
- 用户名称（模糊查询）
3.1以前的写法
配置文件：
<resultMap type="user" id="userList">
<result property="userRoleName" column="roleName"/>
</resultMap>
<!-- 查询用户列表 -->
<select id="getUserList" resultMap="userList">
select
u.*, r.roleName
from
smbms_user u, smbms_role r
where
userName like CONCAT('%',#{userName},'%')
and
userRole=#{userRole}
and
u.userRole=r.id
</select>
接口代码：
public List<User> getUserList(
@Param("userName")String userName,
@Param("userRole")Integer roleId
);
测试代码：
@Test
public void testGetUserList() {
List<User> userList = null;
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
// 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
// userList = sqlSession.selectList("mmp.UserMapper.getUserList");
userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", 3);
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:userList) {
logger.debug("testGetUserList userCode:" + user.getUserCode()
+ "and userName" + user.getUserName() );
}
}
运行结果：
image.png
现在的问题是：当传入用户角色参数为空的时候，查询结果为空吗？
测试代码这样写：
@Test
public void testGetUserList() {
List<User> userList = null;
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
// 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
// userList = sqlSession.selectList("mmp.UserMapper.getUserList");
userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", null);
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:userList) {
logger.debug("testGetUserList userCode:" + user.getUserCode()
+ "and userName" + user.getUserName() );
}
}
运行结果：
image.png
这是因为执行的sql语句是这个样子：
image.png
这个时候我是拿不到任何数据的。但是这个时候我往往需要的是把这个空的条件去掉之后的其他掉件下的数据。
比如我之前写的排片页面，用ajax去获取厅号、日期，只有在日期发生改变的时候我才会调用ajax去获取数据。这样做的问题是：1、在厅号发生改变的时候无法查询，2、厅号没填的情况下查询不到数据。如果进行优化的话，则需要获取这两个框的数据进行非空判断，然后分别调用不同的sql语句，挺麻烦的还是。我想要的结果是我直接把页面上这两个框的数据拿到发送给后台，就只有一条sql语句，在sql中判断如果是空的话则这一个where条件就不执行。
3.2动态sql写法
xml文件：
<resultMap type="user" id="userList">
<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
public void testGetUserList() {
List<User> userList = null;
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
// 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
// userList = sqlSession.selectList("mmp.UserMapper.getUserList");
userList = sqlSession.getMapper(UserMapper.class).getUserList("", null);
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:userList) {
logger.debug("testGetUserList userCode:" + user.getUserCode()
+ "and userName" + user.getUserName() );
}
}
运行结果：
image.png
可以看到，sql语句变化了。
四、 where
需求同上。
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>
测试代码：
@Test
public void testGetUserList() {
List<User> userList = null;
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
// 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
// userList = sqlSession.selectList("mmp.UserMapper.getUserList");
userList = sqlSession.getMapper(UserMapper.class).getUserList("", 3);
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:userList) {
logger.debug("testGetUserList userCode:" + user.getUserCode()
+ "and userName" + user.getUserName() );
}
}
运行结果：
image.png
运行报了sql异常，原因是当第一个条件为空的时候，去拼接sql语句，会多出来一个and。而if那个例子里面没有出现这个问题的原因是在where后面有一个固定的条件，后续条件直接拼接就行了。
解决办法：
修改配置文件如下：
<!-- 查询用户列表 -->
<select id="getUserList" resultType="user">
select
u.*
from
smbms_user u
<where>
<if test="userName != null and userName != ''">
userName like CONCAT('%',#{userName},'%')
</if>
<if test="userRole != null">
userRole=#{userRole}
</if>
</where>
</select>
测试结果正常。
分析：where标签会自动识别标签内是否有返回值，如果有的话，则会自动插入where，如果没有的话，则会自动剔除。插入了之后，后面的语句以and或者or开头，它也会进行一个自动剔除，所以说非常灵活。
where主要是简化了sql语句中where的条件判断，可以智能处理and和or
where主要是可以去除多余的and和or，而不能自行添加and和or
五、trim
image.png
trim元素会自动识别在他这个标签内是否含有返回值，如果有返回值，他就会在自己所包含的内容前面加上一些前缀，也可以在后面加上一些后缀，也可以把包含的首部的一些内容覆盖掉，或者是尾部的一些内容进行覆盖。与这四个操作对应的属性就是图中的四个属性。
在上面的基础上修改xml文件：
<!-- 查询用户列表 -->
<select id="getUserList" resultType="user">
select
u.*
from
smbms_user u
<trim prefix="where" prefixOverrides="and/or">
<if test="userName != null and userName != ''">
userName like CONCAT('%',#{userName},'%')
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</trim>
</select>
测试代码：
@Test
public void testGetUserList() {
List<User> userList = null;
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtil.createSqlSession();
// 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
// userList = sqlSession.selectList("mmp.UserMapper.getUserList");
userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", 3);
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:userList) {
logger.debug("testGetUserList userCode:" + user.getUserCode()
+ "and userName" + user.getUserName() );
}
}
运行结果：
image.png
注：这个地方视频讲的也不是太清楚，可以自己深入摸索一下。
trim跟where一样，也只是只能剔除，不能自动添加and和or。所以在使用这两者的时候，在if的条件判断里面，尽量把and加上，这样就不会出错了。
六、小结
image.png
