MyBatis总结（三）

动态SQL

1. if元素
   if元素是简单的条件判断，可用来实现简单的条件选择
   UserInfoMapper.xml映射文件

<select id="findUserInfoByUserNameWithIf" parameterType="UserInfo" resultType="UserInfo">
	select * from user_info ui
	<if test="userName!=null and userName!=''">
		where ui.userName like concat(concat('%'),#{userName}.'%')
	</if>
</select>

如果传入的查询条件成立，即userName非空，就会将where语句拼接的select语句中，反之忽略
UserInfoMapper接口

public interface UserInfoMapper{
   
	List<UserInfo> findUserInfoByUserNameWithIf(UserInfo ui);
}

测试类

@Test
public void testFindUserInfoByUserNameWithIf(){
   
	UserInfoMapper uim = sqlSession.getMapper(UserInfoMapper.class);
	//创建UserInfo对象，根据条件查询UserInfo对象
	UserInfo cond = new UserInfo();
	cond.setUserName("j");
	List<UserInfo> uis = uim.findUserInfoByUserNameWithIf(cond);
	for(UserInfo ui:uis){
   
		sout(ui.toString);
	}
}

2. where，if元素
   当if元素较多时，可能会拼装成where and或者where or之类的关键字多余的错误SQL语句，where元素会自动剔除where关键字后面多余的or或and
   UserInfoMapper.xml

<select id="findUserInfoByUserNameAndStatus" parameterType="UserInfo" resultType="UserInfo">
	select * from user_info ui
	<where>
		<if test="userName!=null and userName!=''">
			ui.userName like concat(concat('%',#{userName}),'%')
		</if>
		<if test="status>-1">
			and ui.status = #{status}
		</if>
	</where>
</select>

UserInfoMapper接口

List<UserInfo> findUserInfoByUserNameAndStatus