mybatis关联集合List&分布查询传递多列值

更多精彩文章欢迎关注公众号“Java之康庄大道”

场景：查询部门的同时，要求查询此部门下的所有用户。

部门(Department)

    private Integer id;
    private String departmentName;
    private List<Blogger> bloggers;//关联List集合。部门下所有bloggers

用户（Blogger）

    private Integer id;
    private String username;
    private String password;
    private String profile;
    private String nickname;
    private String sign;
    private String imagename;
    private Department dep;

方法一：结果集下collection关联

接口DepartMapper.java

package com.yunqing.mybatis.dao;

import com.yunqing.mybatis.bean.Department;

public interface DepartmentMapper {

    Department getDepByIdStep2(Integer id);

    Department getDepAndBloggers(Integer id);

    Department getDepStep(Integer id);
}

DepartmentMapper.xml

<resultMap id="map" type="com.yunqing.mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="department" property="departmentName"/>
        <collection property="bloggers" ofType="com.yunqing.mybatis.bean.Blogger">
            <id column="bid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </collection>
    </resultMap>
    <select id="getDepAndBloggers" resultMap="map">
        SELECT d.id did,d.department,b.id bid,b.username,b.`password` FROM t_dep d LEFT JOIN t_blogger b ON d.id=b.depId WHERE d.id=#{id}
    </select>

测试类

public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "conf/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory;
    }
@Test
    public void getDepAndBlogger() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession();
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        Department depAndBloggers = mapper.getDepAndBloggers(1);
        System.out.println(depAndBloggers);
        System.out.println(depAndBloggers.getBloggers());
    }

方法二：分布查询

BloggerMapper.java

//根据部门id查询此部门下的人

package com.yunqing.mybatis.dao;

import com.yunqing.mybatis.bean.Blogger;
import com.yunqing.mybatis.bean.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface BloggerMapper {
    @Select("select * from t_blogger")
    List<Blogger> getAllBlogger();

    @MapKey("username")
    Map<String,Blogger> getAllBloggerReturnMap();

    Blogger getBloggerAndDepById(Integer id);

    Blogger getBloggerAndDepByIdAss(Integer id);

    Blogger getBloggerByIdStep1(Integer id);

    List<Blogger> getBloggersByDepId(Integer depId);

}

BloggerMapper.xml

<resultMap id="mapp" type="com.yunqing.mybatis.bean.Blogger">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
    </resultMap>
    <select id="getBloggersByDepId" resultMap="mapp">
        SELECT id,username,password FROM t_blogger WHERE depId=#{depId}
    </select>

再根据部门id查询部门

DepartmentMapper.java

package com.yunqing.mybatis.dao;

import com.yunqing.mybatis.bean.Department;

public interface DepartmentMapper {

    Department getDepByIdStep2(Integer id);

    Department getDepAndBloggers(Integer id);

    Department getDepStep(Integer id);
}

DepartmentMapper.xml

<resultMap id="maop" type="com.yunqing.mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="department" property="departmentName"/>
        <collection property="bloggers" select="com.yunqing.mybatis.dao.BloggerMapper.getBloggersByDepId"
                    column="did">

        </collection>
    </resultMap>
    <select id="getDepStep" resultMap="maop">
        SELECT id did,department FROM t_dep WHERE id=#{id}
    </select>

测试类

@Test
    public void getDepStep() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession();
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        Department depStep = mapper.getDepStep(1);
        System.out.println(depStep);
        System.out.println(depStep.getBloggers());

    }

结果打印：

 分布查询时如果需要传递多列值，可以

<resultMap id="maop" type="com.yunqing.mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="department" property="departmentName"/>
        <collection property="bloggers" select="com.yunqing.mybatis.dao.BloggerMapper.getBloggersByDepId"
                    column="{depId=did}" fetchType="eager">

        </collection>
    </resultMap>
    <select id="getDepStep" resultMap="maop">
        SELECT id did,department FROM t_dep WHERE id=#{id}
    </select>

传递多列值，column="{column1=key1,column2=key2}"

cloumn1的来源是，select的里的方法的传递的参数。

List<Blogger> getBloggersByDepId(Integer depId);

虽然在设置中已经开启了延迟加载，但是在此处的fetchType也可以控制是否延迟加载，lazy延迟加载，eager立即加载。