Mybatis 常用标签

where标签：筛选条件存在，则添加where关键字和筛选条件，并且去掉第一个条件中的and关键字或者or关键字。反之，不会添加where关键字及任何条件。

if标签：判断条件是否成立，成立则执行if标签中内容。反之，不做任何操作。test属性则是判断的条件内容。

使用where标签和if标签实现动态查询

UserMapper.xml

<mapper namespace="com.company.mapper.UserMapper">
    <select id="queryUserByWhere" resultMap="userResultMap" parameterType="com.company.pojo.User">
        select * from userinfo
        <where>
            <if test="name!='' and name!=null">
                and UserName like #{name}
            </if>
            <if test="sex!='' and sex!=null">
                and UserSex=#{sex}
            </if>
            <if test="age!='' and age!=null">
                and UserAge=#{age}
            </if>
            <if test="address!='' and address!=null">
                and UserAddress=#{address}
            </if>
        </where>
    </select>
    <resultMap id="userResultMap" type="com.company.pojo.User">
        <id column="UserId" property="id"></id>
        <result column="UserName" property="name"></result>
        <result column="UserSex" property="sex"></result>
        <result column="UserAge" property="age"></result>
        <result column="UserAddress" property="address"></result>
    </resultMap>
</mapper>

测试

public class MybatisWhereTest {
    private SqlSession sqlSession;
    @Before
    public void setSession() throws Exception{
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        sqlSession= sqlSessionFactory.openSession();
    }
    @Test
    public void test(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user=new User();
        user.setName("二%");
        user.setAge(27);
        System.out.println(user);
        List<User> userList = userMapper.queryUserByWhere(user);
        userList.forEach(System.out::println);
        sqlSession.close();
    }

}

sql标签：用于提取公共的sql片段。当使用时，直接引用id值就可以了。

include标签：用于引用sql标签中的内容。

<select id="queryUserByWhere" resultMap="userResultMap" parameterType="com.company.pojo.User">
        select <include refid="com.company.mapper.UserMapper.commonColumn"/> from userinfo
</select>
<sql id="commonColumn">
     UserId,UserName,UserAge
</sql>

foreach标签：遍历集合，拼接sql语句

1）遍历list集合

    <select id="queryUserByids" resultMap="userResultMap" parameterType="list">
        select * from userinfo where userId in
        <!--
             foreach 遍历集合，拼接sql语句
             collection属性：遍历的集合
             open属性：遍历集合之前，要拼接的字符
             close属性：遍历集合之后，要拼接的字符
             separator属性：遍历的集合元素之间的分隔符
             item属性：遍历集合出来的数据
        -->
        <foreach collection="list" open="(" close=")" separator="," item="id">
            ${id}
        </foreach>
    </select>

测试

    @Test
    public void queryUserByids() {
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(3);
        list.add(7);
        list.add(8);
        list.add(10);
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryUserByids(list);
        userList.forEach(System.out::println);
        sqlSession.close();
    }

2）遍历数组

    <select id="queryUserByArray" resultMap="userResultMap" parameterType="int[]">
        select * from userinfo where userid in
        <foreach collection="array" open="(" separator="," close=")" item="id">
            #{id}
        </foreach>
    </select>

测试

    @Test
    public void queryUserbyArray(){
        int[] ids={1,3,7,9,10};
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryUserByArray(ids);
        userList.forEach(System.out::println);
        sqlSession.close();
    }

 使用<foreach>标签需要注意的，当传入的list或者数组为空时，则出现错误。

<if test="list != null and list.size>0">
    do something
</if>
<if test="list==null or list.size==0">
    and 1=0
</if>

双重<if>标签进行判断，这样既不会出现错误，也不会出现逻辑错误。当list或数组为null或空时，不会查询出数据。当list或数组有值时，则根据list或数组中的值进行查询。

    <select id="queryUserByIds" resultType="user" parameterType="list">
        select * from users
        <where>
            <if test="list!=null and list.size()>0">
                <foreach collection="list" item="id" open="and id in (" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <if test="list==null||list.size()==0">
               and 1=0
            </if>
        </where>
    </select>