MyBatis-多表查询（一对多）

1、多表查询（一对多）

1.1、使用多表查询前的准备

准备好班级表和学生表

1.2、需求分析

查询所有班级下面的学生信息

班级信息和他的学生信息为一对多关系，并且在查询班级的信息过程中查询出学生信息。我们想到了左外连接查询比较合适。

SQL如下：

select c.cname,s.* from classes c left join students s on c.cid=s.cid order by c.cid

2、案例实现

2.1、新建StudentNew.java文件

新的students表的javabean

2.2、修改Classes.java文件

加入一个List对象存储StudentsNew数据

private StudentsNew students;
public StudentsNew getStudents() {
        return students;
}

public void setStudents(StudentsNew students) {
        this.students = students;
}

2.2、创建IClassesDao.java文件

import com.tyschool.mb005.javabean.Classes;

import java.util.List;

public interface IClassesDao {
    List<Classes> findAll();
}

2.3、创建IClassesDao.xml文件

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tyschool.mb005.students.dao.IClassesDao">
    <resultMap id="classesMap" type="com.tyschool.mb005.javabean.Classes">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"></result>
        <collection property="students" ofType="com.tyschool.mb005.javabean.StudentsNew">
            <id column="s_id" property="sid"></id>
            <result column="sname" property="sname"></result>
            <result column="ssex" property="ssex"></result>
            <result column="sage" property="sage"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="classesMap">
      select c.cname,s.sid as s_id,s.sname,s.ssex,s.sage from classes c left join students s  on c.cid=s.cid order by s.cid
    </select>
</mapper>

注：

collection标签是用于建立一对多中集合属性的对应关系

ofType属性用于指定集合元素的数据类型

property属性关联查询的结果集存储在哪个属性上

2.4、编写测试类MbClassesTest.java文件

import com.tyschool.mb005.javabean.Classes;
import com.tyschool.mb005.students.dao.IClassesDao;
import com.tyschool.mb005.students.dao.IStudentsDao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MbClassesTest {
    private InputStream in;
    private SqlSession session;
    private IClassesDao classesDao;
    @Test
    public void findAll(){
        List<Classes> list=classesDao.findAll();
        for(Classes c:list){
            System.out.println(c+":"+c.getStudents());
        }
    }
    @Before
    public void init()throws IOException {
        in= Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory factory=builder.build(in);
        session=factory.openSession();
        classesDao=session.getMapper(IClassesDao.class);
    }
    @After
    public void destroy() throws IOException {
        session.commit();;
        session.close();
        in.close();
    }
}