MyBatis-多表查询（一对一）-组团学

1、多表查询（一对一）

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

学生表（students）：

班级表（classes）：

1.2、需求分析（一对一）

查询所有学生信息，关联查询下班级信息。

注意：

因为一个学生信息只能在一个班级里，所以从查询学生信息出发关联查询班级信息为一对一查询。

如果从班级信息出发查询班级下的学生信息则为一对多查询，因为一个班级可以有多个学生。

方式一SQL 如下：

select s.*,c.cname from students s,classes c where s.cid=c.cid;

方式二SQL如下：

select s.sid,s.sname,s.ssex,s.sage,c.* from students s,classes c where s.cid=c.cid

2、案例实现-方式一

2.1、编写实体类文件

Students.java

package com.tyschool.mb005.javabean;

import java.io.Serializable;

public class Students implements Serializable {
    private int sid;
    private String sname;
    private String ssex;
    private int sage;

    public int getSid() {
        return sid;
    }

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

    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;
    }

    @Override
    public String toString() {
        return "Students{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", ssex='" + ssex + '\'' +
                ", sage=" + sage +
                '}';
    }
}

Classes.java

import java.io.Serializable;

public class Classes implements Serializable {
    private int cid;
    private String cname;

    public int getCid() {
        return cid;
    }

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

    public String getCname() {
        return cname;
    }

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

    @Override
    public String toString() {
        return "Classes{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                '}';
    }
}

2.2、定义返回值接收类文件

StudentsClasses.java

import java.io.Serializable;

public class StudentsClasses extends Students implements Serializable {
    private String cname;

    public String getCname() {
        return cname;
    }

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

    @Override
    public String toString() {
        return "StudentsClasses{" +
                "cname='" + cname + '\'' +
                '}';
    }
}

2.3、创建IStudentsDao.java文件

import com.tyschool.mb005.javabean.StudentsClasses;

import java.util.List;

public interface IStudentsDao {
    List<StudentsClasses> findAll();
}

2.4、创建IStudentsDao.xml文件

<?xml version="1.0" encoding="UTF-8"?>
        <!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.IStudentsDao">
<select id="findAll" resultType="com.tyschool.mb005.javabean.StudentsClasses">
        select s.*,c.cname from students s,classes c where s.cid=c.cid
    </select>
</mapper>

注意：

写好的IStudentsDao.xml文件要在SqlMapConfig.xml中加入映射

<mappers>
        <mapper resource="com/tyschool/mb005/user/dao/IStudentsDao.xml"></mapper>
</mappers>

2.5、创建测试类MbStudentsTest.java文件

package com.tyschool.mb005.test;

import com.tyschool.mb005.javabean.StudentsClasses;
import com.tyschool.mb005.students.dao.IStudentsDao;
import com.tyschool.mb005.user.dao.IUserDao;
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 MbStudentsTest {
    private InputStream in;
    private SqlSession session;
    private IStudentsDao studentsDao;
    @Test
    public void findAll(){
        List<StudentsClasses> list=studentsDao.findAll();
        for(StudentsClasses sc :list){
            System.out.println(sc);
        }
    }
    @Before
    public void init()throws IOException {
        in= Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory factory=builder.build(in);
        session=factory.openSession();
        studentsDao=session.getMapper(IStudentsDao.class);
    }
    @After
    public void destroy() throws IOException {
        session.commit();;
        session.close();
        in.close();
    }
}

3、案例实现-方式二

使用resultMap，定义专门的resultMap用于映射一对一查询结果。
通过面向对象的关系可以得知，我们可以在Students类中加入一个Classes类的对象来代表这个学生是哪个班级的。

3.1、修改Students.java文件

加入Classes对象

private Classes classes;

public Classes getClasses() {
        return classes;
}

public void setClasses(Classes classes) {
        this.classes = classes;
}

3.2、修改IStudentsDao.java文件

import com.tyschool.mb005.javabean.Students;
import com.tyschool.mb005.javabean.StudentsClasses;

import java.util.List;

public interface IStudentsDao {
    List<StudentsClasses> findAll();
    List<Students> findAll1();
}

3.3、修改IStudentsDao.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!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.IStudentsDao">
    <resultMap id="studentsMap" type="com.tyschool.mb005.javabean.Students">
        <id column="sid" property="sid"></id>
        <result column="sname" property="sname"></result>
        <result column="ssex" property="ssex"></result>
        <result column="sage" property="sage"></result>
        <association property="classes" javaType="com.tyschool.mb005.javabean.Classes">
            <id column="cid" property="cid"></id>
            <result column="cname" property="cname"></result>
        </association>
    </resultMap>
    <select id="findAll" resultType="com.tyschool.mb005.javabean.StudentsClasses">
        select s.*,c.cname from students s,classes c where s.cid=c.cid
    </select>
    <select id="findAll1" resultMap="studentsMap">
        select s.sid,s.sname,s.ssex,s.sage,c.* from students s,classes c where s.cid=c.cid
    </select>
</mapper>