两表联查（一对多）

1、创建学生（student）表和班级（grade）表

CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(30),
    cid INT
);

INSERT INTO student VALUES (1,"张三",2);
INSERT INTO student VALUES (2,"李四",3);
INSERT INTO student VALUES (3,"王五",1);
INSERT INTO student VALUES (4,"李二麻子",4);
INSERT INTO student VALUES (5,"鸭蛋",1);
INSERT INTO student VALUES (6,"狗蛋",1);
INSERT INTO student VALUES (7,"王刚",4);
INSERT INTO student VALUES (8,"詹丹",4);
INSERT INTO student VALUES (9,"赵蛋",3);
INSERT INTO student VALUES (10,"咸鸭蛋",2);
INSERT INTO student VALUES (11,"鹌鹑蛋",1);
INSERT INTO student VALUES (12,"鸡蛋",1);

CREATE TABLE grade(
    cid INT PRIMARY KEY,
    cname VARCHAR(30)
);

INSERT INTO grade VALUES (1,"初一一班");
INSERT INTO grade VALUES(2,"初一二班");
INSERT INTO grade VALUES (3,"初一三班");
INSERT INTO grade VALUES(4,"初一四班");

2、创建Student和Grade实体类

package com.fan.entity;

public class Student {
    private Integer sid;
    private String sname;
    private Integer cid;

    private Grade grade;

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

   public Grade getGrade() {
        return grade;
    }

    public void setGrade(Grade grade) {
        this.grade = grade;
    }
}

package com.fan.entity;

import java.util.List;

public class Grade {
    private Integer cid;
    private String cname;

    private List<Student> studentList;

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

3、创建StudentAndGradeDao接口

package com.fan.dao;

import com.fan.entity.Grade;


public interface StudentAndGradeDao {
    //根据班级id查找班级信息以及对应的学生信息
    public Grade findByGradeId(int cid);
}

4、配置StudentAndGradeDaoMapper.xml映射文件并将其配置到mybatis-config.xml

<mapper namespace="com.fan.dao.StudentAndGradeDao">
    <resultMap id="a1" type="Grade">
        <id property="cid" column="cid"></id>
        <result property="cname" column="cname"></result>
        <collection property="studentList" ofType="Student">
            <id property="sid" column="sid"></id>
            <result property="sname" column="sname"></result>
            <result property="cid" column="cid"></result>
        </collection>
    </resultMap>

    <select id="findByGradeId" resultMap="a1">
        select * from grade,student where grade.cid=student.cid and grade.cid=#{cid}
    </select>
</mapper>

mybatis-config.xml获取对应的映射文件

<mapper resource="Mapper/StudentAndGradeDaoMapper.xml"></mapper>

5、添加测试类

import com.fan.dao.StudentAndGradeDao;
import com.fan.entity.Grade;
import com.fan.entity.Student;
import com.fan.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;


public class Test2 {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentAndGradeDao studentAndGradeDao = sqlSession.getMapper(StudentAndGradeDao.class);
        Grade byGradeId = studentAndGradeDao.findByGradeId(2);
        System.out.println(byGradeId.getCid()+"----"+byGradeId.getCname());

        System.out.println(byGradeId.getCname()+"学生名单如下");
        List<Student> studentList = byGradeId.getStudentList();
        for (Student student:studentList) {
            System.out.println(student.getSname());
        }


    }
}

测试结果如下：

测试结果