动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
if
//根据用户名或pwd查询用户 List<UserInfo> queryUserInfoByUsernameOrPwd(UserInfo userInfo);
<select id="queryUserInfoByUsernameOrPwd" resultType="UserInfo">
select * from user_info where username=#{userName}
<if test="userPassword!=null and userPassword!=''">
and user_password=#{userPassword}
</if>
</select>
支持无userPassword值:
public static void main( String[] args ) throws Exception{
SqlSession sqlSession = getSqlSession();
UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo=new UserInfo();
userInfo.setUserName("zhangsan");
List<UserInfo> userInfos=mapper.queryUserInfoByUsernameOrPwd(userInfo);
for (UserInfo _userInfo : userInfos) {
System.out.println(_userInfo);
}
sqlSession.close();
}
支持有userPassword值
public static void main( String[] args ) throws Exception{
SqlSession sqlSession = getSqlSession();
UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo=new UserInfo();
userInfo.setUserName("zhangsan");
userInfo.setUserPassword("123");
List<UserInfo> userInfos=mapper.queryUserInfoByUsernameOrPwd(userInfo);
for (UserInfo _userInfo : userInfos) {
System.out.println(_userInfo);
}
sqlSession.close();
}
单纯使用if无法两者有1则返回结果
where
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<select id="queryUserInfoByUsernameOrPwd" resultType="UserInfo">
select * from user_info
<where>
<if test="userName!=null and userName!=''">
username=#{userName}
</if>
<if test="userPassword!=null and userPassword!=''">
and user_password=#{userPassword}
</if>
</where>
</select>
实现两者有一返回结果
set
//动态更新 Integer updateUserInfoByParmas(UserInfo userInfo);
<update id="updateUserInfoByParmas">
update user_info
<set>
<if test="userName!=null and userName!=''">username=#{userName},</if>
<if test="userPassword!=null and userPassword!=''">userPassword=#{userPassword},</if>
update_time=now()
</set>
where user_id=#{userId}
</update>
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。
public static void main( String[] args ) throws Exception{
SqlSession sqlSession = getSqlSession();
UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo=new UserInfo();
userInfo.setUserId(4L);
userInfo.setUserName("wangwu");
mapper.updateUserInfoByParmas(userInfo);
sqlSession.commit();
sqlSession.close();
}
foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历
//根据ids查询用户(in)
List<UserInfo> queryUserInfoByIds(@Param("ids") List<Long> idList);
<select id="queryUserInfoByIds" resultType="UserInfo">
SELECT *
FROM user_info
WHERE user_id in
<!--需使用@Parma 与collection字段对应-->
<foreach item="id" index="index" collection="ids"
open="(" separator="," close=")">
#{id} <!--此处字段与item对应-->
</foreach>
</select>
public static void main( String[] args ) throws Exception{
SqlSession sqlSession = getSqlSession();
UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
List<UserInfo> userInfos=mapper.queryUserInfoByIds(Arrays.asList(1L,4L));
for (UserInfo userInfo : userInfos) {
System.out.println(userInfo);
}
sqlSession.close();
}