四、动态sql语句

public class User implements Serializable {

    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
    //getter and setter
    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userAddress='" + userAddress + '\'' +
                ", userSex='" + userSex + '\'' +
                ", userBirthday=" + userBirthday +
                '}';
    }
}

public class QueryVo {

    private User user;

    private List<Integer> ids;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

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

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

public interface IUserDao {
    /**
     * 根据传入参数条件
     *      查询的条件：有可能有用户名，有可能有性别，也有可能有地址，还有可能是都有
     */
    List<User> findUserByCondition(User user);

    /**
     * 根据queryVo中提供的id集合，查询用户信息
     */
    List<User> findUserInIds(QueryVo queryVo);
}

映射配置文件

<?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.liaoxiang.dao.IUserDao">

    <resultMap id="userMap" type="uSeR">
        <id property="userId" column="id"></id>
        <result property="userName" column="username"></result>
        <result property="userAddress" column="address"></result>
        <result property="userSex" column="sex"></result>
        <result property="userBirthday" column="birthday"></result>
    </resultMap>

    <!-- 抽取重复的sql语句-->
    <sql id="defaultUser">
        select * from user
    </sql>

    <!-- 根据条件查询
    <select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select * from user where 1=1
        <if test="userName != null">
          and username = #{userName}
        </if>
        <if test="userSex != null">
            and sex = #{userSex}
        </if>
    </select>-->

    <select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select * from user
        <where>
            <if test="userName != null">
                and username = #{userName}
            </if>
            <if test="userSex != null">
                and sex = #{userSex}
            </if>
        </where>
    </select>

    <!-- 根据queryVo中的Id集合实现查询用户列表 -->
    <select id="findUserInIds" resultMap="userMap" parameterType="queryVo">
        <include refid="defaultUser"></include>
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
                	<!--select * from user WHERE id in ( ? , ? , ? , ? ) -->
                    <!-- 大括号里面写什么由item 后面的名称决定 -->
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

测试方法：

/**
 * 条件查询
 */
@Test
public void testFindByCondition(){
    User u = new User();
    u.setUserName("老王");
    u.setUserSex("女");
    List<User> users = userDao.findUserByCondition(u);
    for(User user : users){
        System.out.println(user);
    }
}
User{userId=50, userName='老王', userAddress='上海', userSex='女', userBirthday=Mon Jun 03 19:41:05 CST 2019}

/**
 * 条件查询
 */
@Test
public void testFindByCondition(){
    User u = new User();
    u.setUserName("老王");
    List<User> users = userDao.findUserByCondition(u);
    for(User user : users){
        System.out.println(user);
    }
}
User{userId=46, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{userId=50, userName='老王', userAddress='上海', userSex='女', userBirthday=Mon Jun 03 19:41:05 CST 2019}

/**
 * 根据id集合查询用户信息
 * 测试foreach标签的使用
 */
@Test
public void testFindInIds(){
    QueryVo vo = new QueryVo();
    List<Integer> list = new ArrayList<Integer>();
    list.add(41);
    list.add(45);
    list.add(46);
    list.add(49);
    vo.setIds(list);
    //5.执行查询所有方法
    List<User> users = userDao.findUserInIds(vo);
    for(User user : users){
        System.out.println(user);
    }
}
User{userId=41, userName='张三', userAddress='绵阳', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
User{userId=45, userName='李四', userAddress='广州', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
User{userId=46, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{userId=49, userName='王五', userAddress='西藏', userSex='女', userBirthday=Mon Jun 03 16:40:00 CST 2019}