动态sql
where
<select id="queryUserByName" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
where 1=1
<if test="userSex != null">
and user_age = #{userSex}
</if>
<if test="userMinAge != null">
and user_age >= #{userMinAge}
</if>
<if test="userMaxAge != null">
and user_age <= #{userMaxAge}
</if>
order by user_age
</select>
<select id="queryUserByName" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
<where>
<if test="userSex != null">
and user_age = #{userSex}
</if>
<if test="userMinAge != null">
and user_age >= #{userMinAge}
</if>
<if test="userMaxAge != null">
and user_age <= #{userMaxAge}
</if>
</where>
order by user_age
</select>
trim
<select id="queryUserByName" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
<trim prefix="where" prefixOverrides="and | or" suffix="order by user_age">
<if test="userSex != null">
and user_age = #{userSex}
</if>
<if test="userMinAge != null">
and user_age >= #{userMinAge}
</if>
<if test="userMaxAge != null">
and user_age <= #{userMaxAge}
</if>
</trim>
</select>
foreach
<select id="queryUserByName" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
where user_name in
<foreach collection="list" item="userName" separator="," open="(" close=")">
#{userName}
</foreach>
</select>
模糊查询
${key}
表示获取参数。先获取参数的值拼接到SQL语句中,再编译执行SQL语句#{key}
表示获取参数。先完成SQL编译(预编译),预编译之后再将获取的参数设置到SQL中,可以避免SQL注入
dao
// 模糊查询需要使用${}取值。与sql进行拼接
// 在使用${}时,即使只有一个参数也需要使用@Param注解声明参数的key (非String的对象参数可以不用声明)
// 使用HashMap
List<User> queryUserNameByKeyWord(HashMap params);
// 使用String
List<User> queryUserNameByKeyWord(@Param("kw") String keyWord);
mapper
<select id="queryUserNameByKeyWord" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
where user_name like '%${keyWord}%'
</select>
<!-- 如果参数是String类型,需要指定parameterType -->
<select id="queryUserNameByKeyWord" parameterType="java.lang.String" resultMap="userMap">
select user_id, user_name, user_pwd, user_realname, user_sex, user_age
from users
where user_name like '%${kw}%'
</select>
test
@Test
public void testQueryUserNameByKeyWord() {
UserDAO userDAO = MyBatisUtil.getMapper(UserDAO.class);
HashMap<String, Object> params = new HashMap<>();
params.put("keyWord", "s");
List<User> users = userDAO.queryUserNameByKeyWord(params);
List<User> users = userDAO.queryUserNameByKeyWord("s");
for (User user : users) {
System.out.println(user);
}
}