1、条件查询(含有多个参数):
在xml文件中的<mapper>中,sql语句为:
<!-- 条件查询-->
<select id="selectByCondition" resultType="user">
select * from user
where uname like #{uname}
and password like #{password}
and gender like #{gender}
</select>
(1) 散装参数:如果方法中有多个参数,需要使用@Param("SQL参数占位符名称")
List<User> selectByCondition(@Param("uname") String uname,@Param("password") String password,@Param("gender") String gender);
测试案例:
List<User> users=userMapper.selectByCondition("%先生%","%2%","%女%");
(2)对象参数:对象的属性名称要和参数占位符名称一致
List<User> selectByCondition(User user);
测试案例:
User user=new User();
user.setUname("%先生%");
user.setPassword("%2%");
user.setGender("%女%");
List<User> users=userMapper.selectByCondition(user);
(3)以Map集合作为参数
List<User> selectByCondition(Map map);
测试案例:
Map map=new HashMap<>();
map.put("uname","%先%");
map.put("password","%2%");
map.put("gender","%女%");
List<User> users=userMapper.selectByCondition(map);
因为上述查询为模糊查询,虽然在每个参数的前后都加上特殊符号,如‘%’,‘_’等。
2、动态条件查询:
(1)if标签:
<select id="selectByCondition" resultType="user">
select *
from user
where
<if test="uname != null">
uname like #{uname}
</if>
<if test="password != null and password !=''">
and password like #{password}
</if>
<if test="gender != null and gender !='' ">
and gender like #{gender}
</if>
</select>
这样子可以动态条件的查询,但是这个查询有一定的缺点和问题:当第一个条件uname为空不满足时,或者三个条件都不满足时,会出现多余的and 或者where导致sql语句报错。
解决方案一:
用 恒等式 解决
<select id="selectByCondition" resultType="user">
select * from user
where 1=1
<if test="uname != null">
and uname like #{uname}
</if>
<if test="password != null and password !=''">
and password like #{password}
</if>
<if test="gender != null and gender !='' ">
and gender like #{gender}
</if>
</select>
解决方案二:
用<where>标签 替换where关键字,自动删除“无效”的and或者where
<select id="selectByCondition" resultType="user">
select *
from user
<where>
<if test="uname != null">
and uname like #{uname}
</if>
<if test="password != null and password !=''">
and password like #{password}
</if>
<if test="gender != null and gender !='' ">
and gender like #{gender}
</if>
</where>
</select>
(2)choose标签:
方式一:简单明了,相当于c语言的Switch,类似。
<select id="selectByConditionSingle" resultType="com.lingnan.pojo.User">
select *
from user
where
<choose><!--相当于switch-->
<when test="uname != null and uname !='' "><!--相当于case-->
uname like #{uname}
</when>
<when test="password != null and password !='' "><!--相当于case-->
password like #{password}
</when>
<when test="gender != null and gender !='' "><!--相当于case-->
gender like #{gender}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</select>
方式二:用where标签避免出现传参全为空导致sql语句出错问题
<select id="selectByConditionSingle" resultType="com.lingnan.pojo.User">
select *
from user
<where>
<choose><!--相当于switch-->
<when test="uname != null and uname !='' "><!--相当于case-->
uname like #{uname}
</when>
<when test="password != null and password !='' "><!--相当于case-->
password like #{password}
</when>
<when test="gender != null and gender !='' "><!--相当于case-->
gender like #{gender}
</when>
</choose>
</where>
</select>
3、增加:
接口方法:
/*
* 添加
* */
void add(User user);
xml文件的sql语句:
<insert id="add">
insert into user (uid, uname, password, gender, Flag)
values (#{uid}, #{uname}, #{password}, #{gender}, #{Flag})
</insert>
4、修改(动态):
以实体类为对象,进行修改,可根据所给的条件进行单个或多个修改。
接口方法:
/*
*修改
* */
int update(User user);
xml文件的sql语句:
包含<set>标签,可解决出现全空或者最后一个为空导致出现‘,’或者sql语句报错的问题。
<update id="update">
update user
<set>
<if test="uname != null and uname !=''">
uname = #{uname},
</if>
<if test="password != null and password !=''">
password = #{password},
</if>
<if test="gender != null and gender !=''">
gender = #{gender},
</if>
<if test="Flag != null and Flag !=''">
Flag = #{Flag}
</if>
</set>
where uid = #{uid};
</update>
5、删除:
(1)通过id单个删除:
接口方法:
/*
* 删除
* */
void deleteById(String uid);
xml文件的sql语句:
<delete id="deleteById">
delete from user where uid = #{uid};
</delete>
(2)通过所提供的参数(id数组)批量删除:
接口方法:
/*
* 批量删除
* */
(1)
void deleteByIds(String[] uids);
(2)
void deleteByIds(@Param("ids") String[] uids);
xml文件的sql语句:
mybatis会将数组参数,封装为一个Map集合。
(1)默认:array=数组
(2)使用注解@Param注解改变map集合的默认key的名称
(1)
<delete id="deleteByIds">
delete from user where uid
in
<foreach collection="array" item="uid" separator="," open="(" close=")">
#{uid}
</foreach>
;
</delete>
(2)
<delete id="deleteByIds">
delete from user where uid
in
<foreach collection="ids" item="uid" separator="," open="(" close=")">
#{uid}
</foreach>
;
</delete>