MyBatis之高级关联和集合映射（二、嵌套查询和嵌套结果小案例）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table student
(
    stuId number(4) primary key,
    stuName varchar2(20) not null,
    stuSex varchar2(4),
    stuBirthday date,
    classId number(4)
);

create table classes
(
    clsId number(4) primary key,
    clsName varchar2(20) not null,
    teacherId number(4)
);

create table teacher
(
    teacherId number(4) primary key,
    teacherName varchar2(20) not null,
    workYear number(2),
    professional varchar2(20)
);

1
2
3
4
5
<mappers>
 <mapper resource="com/yc/mybitis/entity/ClassesMapper.xml"/>
 <mapper resource="com/yc/mybitis/entity/StudentMapper.xml"/>
 <mapper resource="com/yc/mybitis/entity/TeacherMapper.xml"/>
 </mappers> 

1
2
3
4
5
6
7
8
9
10
<!-- 定义类型的别名 -->
    <typeAliases>
        <!--  只能为一种类型指定别名 -->
        <!--<typeAlias type="com.yc.mybitis.entity.User" alias="User"/>-->

        <!-- 给指定包的所有类定义别名， 别名就是与不带包的类名相同 -->
        <package name="com/yc/mybitis/entity"/>

    </typeAliases>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
public class MybatisUtil {
    private static Logger log=LoggerFactory.getLogger(MybatisUtil.class);
    private static SqlSessionFactory factory;
    static{
        try {
            log.debug("加载mybatis.xml的配置文件");
            InputStream in =Resources.getResourceAsStream("mybatis.xml");
            log.debug("加载mybatis.xml的配置文件成功");


            log.debug("通过配置文件的数据构建sql session工厂");
            factory=new SqlSessionFactoryBuilder().build(in);
            log.debug("通过配置文件的数据构建sql session工厂 【成功】"  );

            log.debug("生产sqlsession 工厂对象");
        } catch (IOException e) {
            e.printStackTrace();
            log.debug("加载mybatis.xml的配置文件失败",e);
        }

    }

    public static SqlSession getSession(){
        //原来这么些
        //InputStream in=MybatisUtil.class.getClassLoader().getResourceAsStream("mybatis.xml");

        //mybatis这么写
        SqlSession session=null;
        session=factory.openSession();
        log.debug("生产sqlsession 工厂对象 成功");
        return session;
    }

    /**
     * 
     * @param isAutoCommit :true: 自动提交事务， false 手动事务
     * @return
     */
    public static SqlSession getSession(boolean isAutoCommit){
        SqlSession session=null;
        session=factory.openSession( isAutoCommit );
        log.debug("生产sqlsession 工厂对象 成功");
        return session;
    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<mapper namespace="com.yc.mybatis.mapper.ClassesMapper"> 

    <!-- 第一种 嵌套查询  记录多条，所以不能使用  association   必须选择 collection：表示多个结果-->
    <resultMap type="Classes" id="ClassesMap">
        <id column="clsId" property="clsId"/><!-- 指定取出字段值会取出多次，不指定就只会取出一次 -->
        <collection property="stus" column="clsId" select="getStudents"></collection>
    </resultMap>

    <select id="getClassesById" parameterType="int" resultMap="ClassesMap">
        select * from classes where clsId=#{clsId} 
    </select>

    <select id="getStudents" resultType="Student">
        select * from student where classId=#{clsId} 
    </select>
    </mapper>

1
2
3
4
public interface ClassesMapper {

    public Classes getClassesById(int clsid);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class ClassesMapperTest {
    private ClassesMapper classesMapper;

    @Before
    public void setUp() throws Exception {
        classesMapper=MybatisUtil.getSession().getMapper(ClassesMapper.class);
    }

    @After
    public void tearDown() throws Exception {
        classesMapper=null;
    }

    @Test
    public void testGetClassesById() {
        Classes c=classesMapper.getClassesById(3001);
        System.out.println(c);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<mapper namespace="com.yc.mybatis.mapper.ClassesMapper02">

    <!--
        第二种 嵌套结果 记录多条，所以不能使用 association 必须选择 collection：表示多个结果 
        select * from student s join classes c on c.clsId=3001 and s.classId=c.clsId
    -->
    <resultMap type="Classes" id="ClassesMap03">
        <id column="clsId" property="clsId" /><!--指定取出字段值会取出多次，不指定就只会取出一次-->
        <result column="clsName" property="clsName" />

        <collection property="stus" ofType="Student" resultMap="StudentMap">
            <!--
                ofType:指定 list集合中的数据类型
            -->

        </collection>
    </resultMap>

    <resultMap type="Student" id="StudentMap">
        <id column="stuId" property="stuId" />
        <result column="stuName" property="stuName" />
        <result column="stuSex" property="stuSex" />
        <result column="classId" property="classId" />
        <!--<result column="stuBirthday" property="stuBirthday"/>-->
    </resultMap>


    <select id="getClassesById" parameterType="int" resultMap="ClassesMap03">
        select * from student s join classes c on c.clsId=#{clsId} and
        s.classId=c.clsId 
    </select>


</mapper>

1
2
3
4
5
6
7
8
<mappers>

 <!--<mapper resource="com/yc/mybitis/entity/ClassesMapper.xml"/>-->

 <mapper resource="com/yc/mybitis/entity/ClassesMapper02.xml"/>
<mapper resource="com/yc/mybitis/entity/StudentMapper.xml"/>
 <mapper resource="com/yc/mybitis/entity/TeacherMapper.xml"/>
 </mappers>