1.<if>标签
<select id="selectById" resultType="com.chen.pojo.User">
//2=2 进行等值判断
select * from emp where 2=2
<if test="minSal!=null">
and salary>=#{minSal}
</if>
<if test="maxSal!=null">
//这里<=会识别成标签开头的< 换成<![CDATA[<=]]>
and salary <![CDATA[<=]]>#{maxSal}
</if>
</select>
private static SqlSessionFactory ssf=null;
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
ssf=new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void selectById(){
Map map = new HashMap<>();
//这里的key值为mapper当中的#{}
map.put("minSal",3000);
map.put("maxSal",6000);
//自动提交
SqlSession ss = ssf.openSession(true);
List<User> user = ss.selectList("UserMapper.selectById", map);
for(User u:user){
System.out.println(u);
}
}
2.<set>标签
<update id="updateById">
update emp
<set>
//进行不为空判断 防止数据丢失 通过参数传递进行修改
<if test="name!=null">
name=#{name},
</if>
<if test="job!=null">
job=#{job},
</if>
<if test="salary!=null">
salary=#{salary}
</if>
</set>
where id=#{id}
</update>
@Test
public void updateById(){
Map map=new HashMap();
map.put("salary",8000);
map.put("id",6);
SqlSession ss = ssf.openSession(true);
int rows = ss.update("UserMapper.updateById", map);
System.out.println(rows);
}
3.<foreach>标签
xxx:为数组/集合时 用array/list
多个参数传递 用Map集合中的key值
<foreach collection="xxx" open="通过什么开头" close="以什么结束" separator="以什么分割" item="item">
#{item} 这里为item中的值
</foreach>
3.1批量删除
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="array" open="(" close=")" separator="," item="item">
#{item}
</foreach>
</delete>
@Test
public void deleteByIds(){
Integer [] arr={6,7,8};
SqlSession ss = ssf.openSession(true);
int rows = ss.delete("UserMapper.deleteByIds", arr);
System.out.println(rows);
}
3.2批量查询
<select id="selectByIds" resultType="com.chen.pojo.User">
select * from emp where id in
<foreach collection="array" open="(" close=")" separator="," item="item">
#{item}
</foreach>
</select>
@Test
public void selectByIds(){
Integer [] arr={13,14};
SqlSession ss = ssf.openSession(true);
List<User> list = ss.selectList("UserMapper.selectByIds", arr);
for(User u:list){
System.out.println(u);
}
}
3.3批量修改
<update id="updateByIds">
update emp set salary=salary+#{sal} where id in
<foreach collection="ids" open="(" close=")" separator="," item="item">
#{item}
</foreach>
</update>
@Test
public void updateByIds(){
Integer [] arr={13,14};
Integer sal=600;
Map map=new HashMap();
//connection 中 放的为map中的key值 这里为ids
map.put("ids",arr);
map.put("sal",sal);
SqlSession ss = ssf.openSession(true);
int rows = ss.update("UserMapper.updateByIds", map);
System.out.println(rows);
}