if标签

        我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。

mapper接口

public interface UserDao {//复杂条件查询public List<User> findByUser(User user);
}

mapper映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.by.dao.UserDao"><select id="findByUser" resultType="user">select * from user where 1=1<if test="username!=null and username != ''">and username=#{username}</if><if test="birthday!=null">and birthday=#{birthday}</if><if test="sex!=null and sex != ''">and sex=#{sex}</if><if test="address!=null and address != ''">and address=#{address}</if></select>
</mapper>

测试

@Testpublic void testFindAll(){UserDao userDao = sqlSession.getMapper(UserDao.class);User user = new User();user.setSex("男");user.setAddress("香港");List<User> userList = userDao.findByUser(user);for(User u : userList){System.out.println(u);}}

where标签

        为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。

mapper映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.by.dao.UserDao"><select id="findByUser" resultType="User">select * from user where and username=#{username}<!--where标签将if标签代码块包起来,并去掉开头 “AND” 或 “OR”--><where><if test="username!=null and username != ''">and username=#{username}</if><if test="birthday!=null">and birthday=#{birthday}</if><if test="sex!=null and sex != ''">and sex=#{sex}</if><if test="address!=null and address != ''">and address=#{address}</if></where></select>
</mapper>

set标签

        set标签用于动态包含需要更新的列，并会删掉额外的逗号

mapper

public void updateByUser(User user);

mapper映射文件 

<update id="updateByUser" parameterType="user">update user<set><if test="username!=null and username != '' ">username=#{username},</if><if test="birthday!=null">birthday=#{birthday},</if><if test="sex!=null and sex != '' ">sex=#{sex},</if><if test="address!=null and address != '' ">address=#{address},</if></set>where id=#{id}</update>

测试

@Testpublic void testUpdateByUser(){UserDao userDao = sqlSession.getMapper(UserDao.class);User user = new User();user.setId(50);user.setBirthday(new Date());user.setAddress("加拿大");userDao.updateByUser(user);}

trim标签

        trim标签可以代替where标签、set标签

mapper

    //修改public void updateByUser2(User user);

mapper映射文件 

<update id="updateByUser2" parameterType="User">update user<!-- 增加SET前缀，忽略，后缀 --><trim prefix="SET" suffixOverrides=","><if test="birthday!=null">birthday=#{birthday},</if><if test="sex!=null and username != '' ">sex=#{sex},</if><if test="address!=null and username != '' ">address=#{address},</if></trim>where id=#{id}</update>

foreach标签

        foreach标签的常见使用场景是集合进行遍历  

mapper

    //批量删除public void deleteUserByIds(@Param("ids") List<Integer> ids);//批量添加public void insertUsers(@Param("userList") List<User> userList);

mapper映射文件 

<delete id="deleteUserByIds" parameterType="list">delete from user where id in<!--collection：取值list、array、map、@Param("keyName")、对象的属性名item：循环取出的具体对象open：起始符separator：分隔符close：结束符--><foreach collection="ids" item="id" open="(" close=")" separator=",">#{id}</foreach></delete><insert id="insertUsers" parameterType="list">INSERT INTO user(username,password,birthday,sex,address)VALUES<foreach collection ="userList" item="user" separator =",">(#{user.username},#{user.password},#{user.birthday},#{user.sex},#{user.address})</foreach></insert>

测试

 @Testpublic void testDeleteUserByIds(){UserDao userDao = sqlSession.getMapper(UserDao.class);List<Integer> ids = new ArrayList();ids.add(50);ids.add(64);ids.add(67);userDao.deleteUserByIds(ids);}@Testpublic void testInsertUsers(){UserDao userDao = sqlSession.getMapper(UserDao.class);long start = System.currentTimeMillis();List<User> userList = new ArrayList<>();for(int i = 0 ;i < 10000; i++) {User user = new User();user.setUsername("刘德华");user.setPassword("111");user.setBirthday(new Date());user.setSex("男");user.setAddress("香港");//userDao.insertUser(user);userList.add(user);}userDao.insertUsers(userList);long end = System.currentTimeMillis();System.out.println("一万条数据总耗时：" + (end-start) + "ms" );sqlSession.commit();}

sql标签

        sql元素标签用来定义可重复使用的SQL代码片段，使用时只需要用include元素标签引用即可

mapper

   //复杂条件查询public List<User> findByUser3(User user);

mapper映射文件 

 <!-- 定义SQL片段 --><sql id="query_user_where"><if test="username!=null and username != ''">and username=#{username}</if><if test="birthday!=null">and birthday=#{birthday}</if><if test="sex!=null and sex != ''">and sex=#{sex}</if><if test="address!=null and address != ''">and address=#{address}</if></sql><select id="findByUser3" resultType="User">select * from user<where><include refid="query_user_where"></include></where></select>

测试

 @Testpublic void testFindAll3(){UserDao userDao = sqlSession.getMapper(UserDao.class);User user = new User();user.setAddress("香港");user.setUsername("刘德华");List<User> userList = userDao.findByUser3(user);for(User u : userList){System.out.println(u);}}