Mybatis接口映射,嵌套查询

接口映射:
就在java的包中新建一个接口(inteface),然后映射一个已有的mapper.xml(在上一篇文章中说到了)
在mapper的命名空间(namespace)配置接口的全限定名

然后在接口中配置:
mapper文件中:

<?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">
<!-- 
    方法二:采用映射接口方式间接操作时,
    namespace:命名空间值:与映射接口的全限定名一致
-->
<mapper namespace="com.yyt.mapper.CourseMapper01">
    <!-- 
        insert :表示插入映射 
        id:表示插入映射的名字 
        parameterType:要插入的数据类型
    -->
    <insert id="inserCourse" parameterType="Course">
        <!-- 插入操作,#{id}相当于getId() -->
        insert into course values(#{id},#{courseCode},#{courseName})
    </insert>
    <!-- 查询 
         使用parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型
         resultType="Course"就表示将查询结果封装成一个User类的对象返回
     -->
    <select id="selectCourse" parameterType="int" resultType="Course" >
        select * from course where id=#{id}
    </select>
    <!-- 删除 
        按照id删除  参数就是int
    -->
    <delete id="deleteCourse" parameterType="int" >
        delete from course where id=#{id}
    </delete>
    <!-- 更新 
        parameterType:要更新的数据类型
    -->
    <update id="updateCourse" parameterType="Course">
        update course set courseCode=#{courseCode},courseName=#{courseName} where id=#{id}
    </update>
    <!-- 查询所有的 -->
    <select id="findAllCourse" resultType="Course" >
        select * from course
    </select>
</mapper>

public interface CourseMapper01 {
    /**
     * 方法名与映射文件中的id一致
     * 参数的个数只能有一个,多个值使用对象或集合来操作
     * 参数类型与映射文件中的id值一致,parameterType的值一致
     * 当操作是insert,update,delete时,返回诶性可能为void或int
     * 添加
     * @param course
     * @return
     */
    int inserCourse(Course course);
    /**
     * 当操作是查询操作的时候,返回类型是与resultType的类型是一致的
     * 查询
     * @param id
     * @return
     */
    Course selectCourse(int id);
    /**
     * 删除
     * @param id
     * @return
     */
    int deleteCourse(int id);
    /**
     * 修改
     * @param course
     * @return
     */
    int updateCourse(Course course);
    /**
     * 当操作是查询操作的时候,返回类型是与resultType的类型是一致的
     * 如果只是对个类型就是这个类型的list类型
     * 查询集合
     * @return
     */
    List<Course> findAllCourse();
}

测试类:

public class ConnTest03 {
    /**
     * 数据库操作: 增(插入): 删(删除): 改(修改): 查(查询): 读取数据:
     */
    // 增
    @Test
    public void testInsert() {
        SqlSession session = SqlSessionUtils.getSqlSession(true);
        Course course = new Course(4, "yc0025", "java4培训");
        //通过制定映射接口的类对象给mybatis框架,mybatis框架自动生成一个映射接口的实现类对象
        CourseMapper01 mapper =session.getMapper(CourseMapper01.class);
        int result=mapper.inserCourse(course);
        SqlSessionUtils.colse(session);
        assertEquals("插入失败", 1, result);
    }

    // 查询
    @Test
    public void testSelect() {
        SqlSession session = SqlSessionUtils.getSqlSession(true);
        CourseMapper01 mapper =session.getMapper(CourseMapper01.class);
        Course course=mapper.selectCourse(4);
        System.out.println(course);
        SqlSessionUtils.colse(session);
        assertNotNull("对象为空", course);
    }

    // 更新
    @Test
    public void testUpdate() {
        SqlSession session = SqlSessionUtils.getSqlSession(true);
        CourseMapper01 mapper =session.getMapper(CourseMapper01.class);
        Course course = new Course(4, "yc0024", "java5培训");
        int result=mapper.updateCourse(course);
        SqlSessionUtils.colse(session);
        assertEquals("更新失败", 1, result);
    }

    // 删除
    @Test
    public void testDelete() {
        SqlSession session = SqlSessionUtils.getSqlSession(true);
        CourseMapper01 mapper =session.getMapper(CourseMapper01.class);
        int id=4;
        int result=mapper.deleteCourse(id);
        SqlSessionUtils.colse(session);
        assertEquals("更新失败", 1, result);
    }
    @Test
    public void testFindAll(){
        SqlSession session=SqlSessionUtils.getSqlSession();
        CourseMapper01 mapper =session.getMapper(CourseMapper01.class);
        List<Course> list=mapper.findAllCourse();
        SqlSessionUtils.colse(session);
        System.out.println(list);
        assertNotNull("查询为空", list);
    }
}

嵌套查询:在数据库表中有很多主外键,在对数据库表中字段进行实体类封装的时候我们将外键可以封装成引用这个数据库表的实体对象,如果这样的话,我们对一个表做我们前面的查询的时候,外键这个字段的数据将查询不出来,但是使用嵌套查询的话,我们可以将这样实体类中的实体类的数据也查询出来
数据库脚本:

CREATE TABLE student (
 id number(11) primary key,
 name varchar2(10) NOT NULL,
 gender char(2) NOT NULL,
 major varchar2(20) NOT NULL,
 grade char(4) NOT NULL,
 supervisorId number(11) DEFAULT NULL
     constraint FK_tea_stu_sid references teacher (id )
);
 CREATE TABLE teacher (
 id number(11) primary key,
 name varchar2(10) NOT NULL,
 gender char(2) NOT NULL,
 researchArea varchar2(20) NOT NULL,
 title varchar2(6) NOT NULL
);

实体类对象:

//学生
public class Student {
    private int id;
    private String name;
    private String gender;
    private String major;
    private String grade;
    private Teacher teacher;//将这里的外键替换成了老师对象

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getGrade() {
        return grade;
    }

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

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", major=" + major + ", grade=" + grade + ", teacher=" + teacher + "]";
    }

}
//老师
public class Teacher {
    private int id;
    private String name;
    private String gender;
    private String researchArea;
    private String title;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getResearchArea() {
        return researchArea;
    }

    public void setResearchArea(String researchArea) {
        this.researchArea = researchArea;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + ", gender=" + gender + ", researchArea=" + researchArea + ", title=" + title + "]";
    }

}

利用我前面说的接口映射的,我们首先配置mpper.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.yyt.mapper.StudentMapper">
    <!-- 自定义resultMap  
        type:对象类型 
        id:名字
     嵌套结果-->
    <resultMap type="Student" id="StudentMap">
        <id property="id" column="id"/><!-- 主键 -->
        <result property="name" column="name"/><!-- 普通结果 -->
        <result property="gender" column="gender"/>
        <result property="major" column="major"/>
        <result property="grade" column="grade"/>
        <!-- 一个对象的映射  
            property:对应实体类的属性名
            column:对应的数据库的字段名
            javaType:java中的对象
         -->
        <association property="teacher" column="supervisorId" javaType="Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
            <result property="gender" column="tgender"/>
            <result property="researchArea" column="researchArea"/>
            <result property="title" column="title"/>
        </association>
    </resultMap>
    <select id="findStudentById" parameterType="int" resultMap="StudentMap" >
        select s.*,t.*,t.id tid,t.name tname,t.gender tgender from student s join  teacher t on t.id=s.supervisorid and s.id=#{id}
    </select>
</mapper>

第二种:

<?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.yyt.mapper.StudentMapper2">
    <!-- 自定义resultMap   嵌套结果-->
    <resultMap type="Student" id="StudentMap">
        <id property="id" column="id"/><!-- 主键 -->
        <result property="name" column="name"/><!-- 普通结果 -->
        <result property="gender" column="gender"/>
        <result property="major" column="major"/>
        <result property="grade" column="grade"/>
        <!-- 一个对象的映射  
            property:对应实体类的属性名
            column:对应的数据库的字段名
            javaType:java中的对象
         -->
        <association property="teacher" column="supervisorId" select="selectTeacherById"/>
    </resultMap>
    <!-- 内部查询  不用在映射的接口中申明 -->
    <select id="selectTeacherById" resultType="Teacher" parameterType="int">
        select * from teacher where id=#{supervisorId}
    </select>
    <select id="findStudentById" parameterType="int" resultMap="StudentMap">
        select * from student where id=#{id}
    </select>
</mapper>

映射接口:

//方法一
public interface StudentMapper {
    Student findStudentById(int id);
}
//方法二
public interface StudentMapper2 {
    Student findStudentById(int id);
}

测试类:

public class SelectAllTest {
    @Test
    public void testSelect(){
        SqlSession session=SqlSessionUtils.getSqlSession();
        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        Student student=studentMapper.findStudentById(6);
        System.out.println(student);
        assertNotNull("对象为空",student);
    }
    @Test
    public void testSelects(){
        SqlSession session=SqlSessionUtils.getSqlSession();
        StudentMapper2 studentMapper=session.getMapper(StudentMapper2.class);
        Student student=studentMapper.findStudentById(6);
        System.out.println(student);
        assertNotNull("对象为空",student);
    }
}

利用两种方法就可以查询到数据了
结果:
方法一的:

方法二: