MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。
我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
mapper接口
public interface UserDao {
//复杂条件查询
public List<User> findByUser(User user);
}
mapper映射文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//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>
测试
@Test
public 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 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。
若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。
mapper映射文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//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标签将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标签用于动态包含需要更新的列，并会删掉额外的逗号
mapper
public void updateByUser(User user);
<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>
测试
@Test
public 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标签可以代替where标签、set标签
mapper
//修改
public void updateByUser2(User user);
<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标签的常见使用场景是集合进行遍历
mapper
//批量删除
public void deleteUserByIds(@Param("ids") List<Integer> ids);
//批量添加
public void insertUsers(@Param("userList") List<User> userList);
<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>
测试
@Test
public 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);
}
@Test
public 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代码片段，使用时只需要用include元素标签引用即可
mapper
//复杂条件查询
public List<User> findByUser3(User user);
<!-- 定义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>
测试
@Test
public 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);
}
}