mybatis查询表数据（传参、传对象、传集合对象等等）

一、根据用户id和角色的enabled状态获取用户的角色（传参形式）

1. 接口如下：

/*
    * 根据用户id和角色的enabled状态获取用户的角色
    * @param userId
    * @param enabled
    * */
    List<SysRole> selectRolesByUserIdAndEnabled
            (@Param("userId")Long userId, @Param("enabled") Integer enabled);

2. xml中：

<select id="selectRolesByUserIdAndEnabled"
            resultType="com.example.simple.model.SysRole">
        SELECT r.id,
                r.role_name,
                r.enabled,
                r.create_by,
                r.create_time
                FROM mybatis.sys_user u
                inner join mybatis.sys_user_role ur on u.id = ur.user_id
                INNER JOIN mybatis.sys_role r on ur.role_id = r.id
                where u.id = #{userId}  and r.enabled = #{enabled}

    </select>

注：inner join：左右关联，左右两表均有数据时才能取对应的所有数据

3. 测试代码如下：

@Test
    public void testselectRolesByUserIdAndEnabled(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysRole> userList = userMapper.selectRolesByUserIdAndEnabled(1L,1);
            Assert.assertNotNull(userList);
            Assert.assertTrue(userList.size()>0);
        }finally {
            sqlSession.close();
        }

    }

二、根据用户id和enabled状态获取用户角色 （传对象 ）

1. 接口如下：

 /*
    * 根据用户id和enabled状态获取用户角色 （传对象 ）
    * */
    List<SysRole> selectRolesByUserAndRole
            (@Param("user")SysUser user,@Param("role")SysRole role);

2. xml如下：

<select id="selectRolesByUserAndRole"
            resultType="com.example.simple.model.SysRole">
        SELECT r.id,
                r.role_name,
                r.enabled,
                r.create_by,
                r.create_time
                FROM mybatis.sys_user u
                inner join mybatis.sys_user_role ur on u.id = ur.user_id
                INNER JOIN mybatis.sys_role r on ur.role_id = r.id
                where u.id = #{user.id}  and r.enabled = #{role.enabled}

    </select>

3。 测试函数如下：

 @Test
    public void testselectRolesByUserAndRole(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            sysUser.setId(1L);
            SysRole sysRole = new SysRole();
            sysRole.setEnabled(1);
            List<SysRole> userList = userMapper.selectRolesByUserAndRole(sysUser,sysRole);
            Assert.assertNotNull(userList);
            Assert.assertTrue(userList.size()>0);
        }finally {
            sqlSession.close();
        }

    }

三、根据邮箱地址和用户名字来查询user表（if标签传参）

1. 接口中：

/*
    * 根据邮箱地址和用户名字来查询user表
    * */
    List<SysUser> selectByUser(SysUser sysUser);

2. xml中：

<select id="selectByUser" resultType="com.example.simple.model.SysUser">
        select id,
            user_name userName,
            user_password userPassword,
            user_email userEmail,
            user_info userInfo ,
            head_img headImg,
            create_time createTime
        from sys_user
        <where>
            <if test="userName != null and  userName !=''">
                and user_name like concat ('%',#{userName},'%')
            </if>
            <if test="userEmail != null and userEmail !=''">
                and user_email = #{userEmail}
            </if>
        </where>


    </select>

3. 测试函数如下：

@Test
    public void testSelectByUser(){
        SqlSession sqlSession = getSqlSession();
        try {

            //只查询用户名时
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser query = new SysUser();
            query.setUserName("ad");
            List<SysUser> sysUserList = userMapper.selectByUser(query);
            Assert.assertTrue(sysUserList.size()>0);

            //只查询用户邮箱时
            query = new SysUser();
            query.setUserEmail("test@mybatis.com");
            sysUserList = userMapper.selectByUser(query);
            Assert.assertTrue(sysUserList.size()>0);

            //用户邮箱和用户名都查询
            query = new SysUser();
            query.setUserName("admin");
            query.setUserEmail("test@mybatis.com");
            sysUserList = userMapper.selectByUser(query);
            Assert.assertTrue(sysUserList.size()== 0);

        }finally {
            sqlSession.close();
        }

    }

四、根据用户id集合查询（传list集合）

1. 接口中：

/*
    * 根据用户id集合查询
    * */
    List<SysUser> selectByIdList(List<Long> idList);

2. xml中：

<select id="selectByIdList" resultType="com.example.simple.model.SysUser">
        select id,
            user_name userName,
            user_password userPassword,
            user_email userEmail,
            user_info userInfo ,
            head_img headImg,
            create_time createTime
        from sys_user
        where id in
        <foreach collection="list" open="(" close=")" separator=","
            item="id" index="i">
            #{id}
        </foreach>

3. 测试函数如下：

@Test
    public void testSelectByidList(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<Long> idList = new ArrayList<Long>();
            idList.add(1L);
            idList.add(1001L);
            //业务逻辑中必须校验 idList.size() > 0
            List<SysUser> sysUserList = userMapper.selectByIdList(idList);
            Assert.assertTrue(sysUserList.size()>0);
        }finally {
            sqlSession.close();
        }

    }

注：collection：值为要选代循环的属性名。

　　item：值为从法代对象中取出的每一个值。

　　index：索引值