兩表聯(lián)查（一對(duì)多）

1槽驶、創(chuàng)建學(xué)生（student）表和班級(jí)（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、創(chuàng)建Student和Grade實(shí)體類

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、創(chuàng)建StudentAndGradeDao接口

package com.fan.dao;

import com.fan.entity.Grade;


public interface StudentAndGradeDao {
    //根據(jù)班級(jí)id查找班級(jí)信息以及對(duì)應(yīng)的學(xué)生信息
    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獲取對(duì)應(yīng)的映射文件

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

5婿斥、添加測(cè)試類

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()+"學(xué)生名單如下");
        List<Student> studentList = byGradeId.getStudentList();
        for (Student student:studentList) {
            System.out.println(student.getSname());
        }


    }
}

測(cè)試結(jié)果如下：

測(cè)試結(jié)果