mybatis-高级结果映射

前言

mybatis对于简单的表可以通过resultType直接返回结果，如果是复杂的多张表查询结果就需要用到resultMap了。
记住 resultType 和 resultMap 之间只能同时使用一个。
官方介绍结果映射

高级结果映射举例

1. 创建三张有关联的表

这三张表分别是：
student(id, name, age) id为主键
course(id, name) id为主键
sc(student_id, course_id, grade) (student_id, course_id)为主键
其中sc表中的student_id、course_id是分别来自student和course的外键

create table student
(
    id   int         not null
        primary key,
    name varchar(20) null,
    age  int         null,
    constraint student_id_uindex
        unique (id)
);

create table course
(
    id   int         not null
        primary key,
    name varchar(20) null,
    constraint course_id_uindex
        unique (id)
);

create table sc
(
    student_id int not null,
    course_id  int not null,
    grade      int null,
    primary key (student_id, course_id),
    constraint sc_course_id_fk
        foreign key (course_id) references course (id),
    constraint sc_student_id_fk
        foreign key (student_id) references student (id)
);

自己适当插入一些数据

2. 创建对应的实体类

在包 com.sjj.domain 下

Student

package com.sjj.domain;

public class Student {
    private int id;
    private String name;
    private int age;

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

Course

package com.sjj.domain;

public class Course {
    private int id;
    private String name;

    public Course(int id, String name) {
        this.id = id;
        this.name = name;
    }

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

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

SC

这里没有写构造方法，如果写了返回的类型就固定了。要么写的很全，要么不写，否则会报返回类型不匹配的问题

package com.sjj.domain;

public class SC {
    // 关联学生表和课程表
    private Student student;
    private Course course;
    private int grade;

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }

    public Course getCourse() {
        return course;
    }

    public void setCourse(Course course) {
        this.course = course;
    }

    public int getGrade() {
        return grade;
    }

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

    @Override
    public String toString() {
        return "SC{" +
                "student=" + student +
                ", course=" + course +
                ", grade=" + grade +
                '}';
    }
}

3. 编写对应的dao类

在包 com.sjj.dao 下

StudentDao

package com.sjj.dao;

public interface StudentDao {

}

CourseDao

package com.sjj.dao;

public interface CourseDao {

}

SCDao

package com.sjj.dao;

public interface SCDao {

}

4. 在resources对应路径下创建XXXdao.xml

在包 com.sjj.dao 下

studentDao.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.sjj.dao.StudentDao">

</mapper>

courseDao.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.sjj.dao.CourseDao">

</mapper>

scDao.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.sjj.dao.SCDao">

</mapper>

5. 数据库配置文件中映射XXXdao.xml

在标签 configuration 中

    <mappers>
        <mapper resource="com/sjj/dao/studentDao.xml"/>
        <mapper resource="com/sjj/dao/courseDao.xml"/>
        <mapper resource="com/sjj/dao/scDao.xml"/>
    </mappers>

6. 编写方法

这里写一个查询学生详细选课信息的方法

SCDao

    // 查询学生选课详情信息
    List<SC> getSCDetail();

scDao.xml

<mapper namespace="com.sjj.dao.SCDao">

    <resultMap id="detailSC" type="com.sjj.domain.SC">
        <result property="grade" column="grade" />
        <association property="student" javaType="com.sjj.domain.Student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="age" column="age" />
        </association>
        <association property="course" javaType="com.sjj.domain.Course">
            <result property="id" column="cid" />
            <result property="name" column="cname" />
        </association>
    </resultMap>

    <select id="getSCDetail" resultMap="detailSC">
        select
            student.id sid,
            student.name sname,
            student.age,
            course.id cid,
            course.name cname,
            sc.grade
        from
             student,course,sc
        where
              student.id = sc.student_id AND course.id = sc.course_id;
    </select>
</mapper>

association：一个复杂类型的关联；许多结果将包装成这种类型，嵌套结果映射关联可以是 resultMap 元素，或是对其它结果映射的引用
property：映射到列结果的字段或属性。
column：数据库中的列名，或者是列的别名。
javaType：一个 Java 类的全限定名，或一个类型别名。
更详细的你可以点击前言中的官方介绍链接！！！

7. 编写测试类

mybatis工具类

package com.sjj.utils;

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static InputStream in;
    private static SqlSessionFactory factory;
    static {
        try {
            // 1,读取配置文件
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            // 2,创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            factory = builder.build(in);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 3,获取SqlSession对象
    public static SqlSession getSqlSession(){
        return factory.openSession();
    }

}

scDao测试类

package com.sjj.dao;

import com.sjj.domain.SC;
import com.sjj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class SCDaoTest {

    @Test
    public void testGetDetailMsg(){
        SqlSession session = MybatisUtils.getSqlSession();
        SCDao scdao = session.getMapper(SCDao.class);
        List<SC> scs = scdao.getSCDetail();
        for(SC sc : scs){
            System.out.println(sc);
        }
        // 释放资源
        session.close();
    }
}

运行测试

SC{student=Student{id=1001, name='小A', age=19}, course=Course{id=1, name='关系型数据库'}, grade=90}
SC{student=Student{id=1001, name='小A', age=19}, course=Course{id=2, name='操作系统'}, grade=88}
SC{student=Student{id=1003, name='小C', age=20}, course=Course{id=2, name='操作系统'}, grade=79}
SC{student=Student{id=1004, name='小D', age=17}, course=Course{id=1, name='关系型数据库'}, grade=85}

posted on 2022-03-21 09:08 S++ 阅读(89) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mybatis-结果映射

· mybatis-配置解析

· MyBatis

· 川职院-课堂笔记（2）5-27日起

· Mybatis

S++

导航

统计

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜