mybatis多表关联映射

数据准备：

在MySQL数据库中，创建一个名为mybatis的数据库，在此数据库中创建一个student表，course表以及sc表，同时预先插入几条数据。如图所示（下图只是表样例，具体数据记录自己编制）：

表1  student表

表2  course表

表3  sc表

一、创建从实体学生类到课程类的关联映射，实现对两表关联数据的查询。

创建MyBaitsDemo1项目，使用mapper.xml绑定接口，实现对student表，course表关联数据的查询操作。编写程序，完成如下操作：

（1）查询学号为201215121学生的信息及选课情况（课程号及课程名）；

（2）查询李姓学生的信息及选课情况（课程号，课程名，课程学分）；

（3）查询选修了2号课程的学生信息及课程名。

实体类

package pojo;

import java.util.List;

public class Student {
    private String sno;
    private String sname;
    private String ssex;
    private int sage;
    private String sdept;
    private List<Course> course;
    public Student(){}
    @Override
    public String toString() {
        return "Student{" +
                "sno='" + sno + '\'' +
                ", sname='" + sname + '\'' +
                ", ssex='" + ssex + '\'' +
                ", sage=" + sage +
                ", sdept='" + sdept + '\''+
                '}';
    }
    public List<Course> getCourse() {
        return course;
    }
    public void setCourse(List<Course> course) {
        this.course = course;
    }
    public String getSno() {
        return sno;
    }
    public void setSno(String sno) {
        this.sno = sno;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public String getSsex() {
        return ssex;
    }
    public void setSsex(String ssex) {
        this.ssex = ssex;
    }
    public int getSage() {
        return sage;
    }
    public void setSage(int sage) {
        this.sage = sage;
    }
    public String getSdept() {
        return sdept;
    }
    public void setSdept(String sdept) {
        this.sdept = sdept;
    }
}

package pojo;

public class Course {
    private Integer cno;
    private String cname;
    private Integer cpno;
    private Integer ccredit;
    public Course() {
    }
    public Course(Integer cno, String cname, Integer cpno, Integer ccredit) {
        this.cno = cno;
        this.cname = cname;
        this.cpno = cpno;
        this.ccredit = ccredit;
    }
    @Override
    public String toString() {
        return "Course{" +
                "cno=" + cno +
                ", cname='" + cname + '\'' +
                ", cpno=" + cpno +
                ", ccredit=" + ccredit +
                '}';
    }
    public Integer getCno() {
        return cno;
    }
    public void setCno(Integer cno) {
        this.cno = cno;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    public Integer getCpno() {
        return cpno;
    }
    public void setCpno(Integer cpno) {
        this.cpno = cpno;
    }
    public Integer getCcredit() {
        return ccredit;
    }
    public void setCcredit(Integer ccredit) {
        this.ccredit = ccredit;
    }
}

 1.创建映射文件

在Mapper下创建一个映射文件CourseMapper.xml，在已配置好的mybatis-config.xml文件中添加路径。

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org.//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="DAO.CourseMapper">
    <select id="selectId" resultMap="StuAndDept">
        select  * from student s,sc d,course c
        where s.sno=#{sno} and d.sno=#{sno} and d.cno=c.cno
    </select>
    <select id="selectName" resultMap="StuAndDept">
        select * from student s,sc d,course c
        where  s.sname like '${sname}%' and s.sno=d.sno and d.cno=c.cno
    </select>
    <select id="selectCno" resultMap="StuAndDept">
        select * from student s,sc d,course c
        where d.cno=#{cno} and d.cno=c.cno and d.sno=s.sno
    </select>
    <resultMap id="StuAndDept" type="pojo.Student">
        <id property="sno" column="sno"/>
        <result property="sname" column="sname"/>
        <result property="ssex" column="ssex"/>
        <result property="sage" column="sage"/>
        <result property="sdept" column="sdept"/>
        <collection property="course" ofType="pojo.Course">
            <id property="cno" column="cno"/>
            <result property="cname" column="cname"/>
            <result property="cpno" column="cpno"/>
            <result property="ccredit" column="ccredit"/>
        </collection>
    </resultMap>
</mapper>

2.创建接口实现动态代理

1.在包DAO创建一个CourseMapper.java的接口文件，创建以下方法

public interface CourseMapper {
    Student selectId(@Param("sno")String sno);
    Student selectName(@Param("sname")String sname);
    List<Student> selectCno(@Param("cno")Integer cno);
}

 3.测试

package Test;

import DAO.CourseMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Course;
import pojo.Student;
import utils.MybatisUtil;

import java.util.List;

public class SourceTest {
    @Test
    public void selectId(){
        SqlSession session = MybatisUtil.openSession();
        CourseMapper mapper = session.getMapper(CourseMapper.class);
        Student student = mapper.selectId("201215121");
        Print(student);
        session.close();
    }
    @Test
    public void selectName(){
        SqlSession session = MybatisUtil.openSession();
        CourseMapper mapper = session.getMapper(CourseMapper.class);
        Student student = mapper.selectName("李");
        Print(student);
        session.close();
    }
    void Print(Student student){
        System.out.println(student);
        for (Course course : student.getCourse()) {
            System.out.println(course.toString());
        }
    }
    @Test
    public void selectCno() {
        SqlSession session = MybatisUtil.openSession();
        CourseMapper mapper = session.getMapper(CourseMapper.class);
        List<Student> students = mapper.selectCno(2);
        for (Student student : students) {
            Print(student);
            System.out.println("---------------------");
        }
        session.close();
    }
}

4.结果展示

问题

 当遇到这种问题的时候建议大家检查一下使用到的实体类，是否有空参的构造器，mybatis自动装配创建类是使用空参构造器的，

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error building SqlSession.
### The error may exist in mapper/CourseMapper.xml
### The error occurred while processing mapper_resultMap[StuAndDept]
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'mapper/CourseMapper.xml'. Cause: org.apache.ibatis.builder.BuilderException: Ambiguous collection type for property 'course'. You must specify 'javaType' or 'resultMap'.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:80)
	at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:64)
	at Test.main(Test.java:19)

大家遇到这种问题的时候，要确保映射文件中的property值和实体类中的值是否一致

 以上是我朋友做的时候遇到的问题，仅供大家参考。