mybatis中的动态sql语句

1、if标签

熟悉的sql拼接：

    <select id="findUserByCondition" parameterType="User" resultType="User">
        select * from user where 1 = 1
        <if test="username != null and username != ''">
            and username like #{username}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
    </select>

test代码：

    @Test
    public void testFindByCondition(){
        User user = new User();
        user.setUsername("%王%");
        user.setSex("男");
        List<User> users = dao.findUserByCondition(user);
        for(User u : users)
            System.out.println(u);
    }

2、where标签

代替where 1 = 1的作用

    <select id="findUserByCondition" parameterType="User" resultType="User">
        select * from user
        <where>
        <if test="username != null and username != ''">
            and username like #{username}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        </where>
    </select>

3、foreach和sql标签

foreach标签
对着容器进行枚举操作

package com.czy.domain;

import java.util.List;

public class QueryVo {
    private User user;

    private List<Integer> ids;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        user.setUsername("%"+user.getUsername()+"%");
        this.user = user;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

  <select id="findUserInIds" parameterType="QueryVo" resultType="User">
        select * from user
        <where>
            <if test="ids != null and ids.size > 0">
                <foreach collection="ids" open="and id in (" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

sql标签
用来映射复用sql语句

<sql id="defaultUser">
        select * from user
    </sql>

    <select id="findUserInIds" parameterType="QueryVo" resultType="User">
        <include refid="defaultUser"></include>
        <where>
            <if test="ids != null and ids.size > 0">
                <foreach collection="ids" open="and id in (" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>