关于java:网友都说MyBatis多表查询太难了小白就这我都学会了

255次阅读

共计 5911 个字符，预计需要花费 15 分钟才能阅读完成。

该文章是介绍《MyBatis 实现多表查问》，依据我以前的学习笔记以及工作中长时间积攒所整顿的一份笔记，纯干货，心愿对大家有帮忙。

MyBatis 实现多表查问

一、多对一查问

数据库的筹备

创立两张表，一张老师表，一张学生表

将老师主键 id 关联学生外键 tid

创立 sql 的语句

create table teacher(
   id int  primary key,
    teacher_name varchar(30) not null
)

insert into teacher(id,teacher_name) values (1,'毛老师')

create table student(
  id int  primary key,
    student_name varchar(30) not null,
    tid int default null
)

// 建设主外键关联
alter table student add constraint teacher_student_id foreign key (tid) references teacher(id)

insert into student values (1,'小明',1)
insert into student values (2,'小毛',1)
insert into student values (3,'小红',1)
insert into student values (4,'大黄',1)
insert into student values (5,'超儿',1)

我的项目构造

应用 Lombok 插件，创立实体类。（进步整洁度，次要想 toulan）

@Data
public class Student {
    private int id;
    private String name;
    // 学生须要关联一个老师
    private Teacher teacher;
}

@Data
public class Teacher {
    private int id;
    private String name;
}

1、嵌套查询处理

1. 编写接口

public interface StudentMapper {
    // 查问所有学生的信息以及对应老师的信息
    public List<Student> getStudent();}
  1. 编写 StudentMapper.xml 的查问语句（重点）
<mapper namespace="dao.StudentMapper">
<!--    思路：1. 查问所有学生的信息
        依据查问进去的学生 tid，寻找对应的老师

-->
    <select id="getStudent" resultMap="StudentTeacher">
    select * from student
    </select>
    <resultMap id="StudentTeacher" type="pojo.Student">
<!--        简单的属性须要独自解决 是对象就应用 association，是汇合就应用 collection-->
<!--    select 子查问    -->
        <result property="name" column="student_name"/>
        <association property="teacher" column="tid" javaType="pojo.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="pojo.Teacher">
        select * from teacher where id=#{id}
    </select>

3. 测试类

 @Test
    public void getStudent(){SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {System.out.println(student);
        }
        sqlSession.close();}

测试后果

2、联结查询处理

1. 编写接口

// 依照后果嵌套查问
public List<Student> getStudent2();
  1. 编写 StudentMapper.xml 的查问语句（重点）
<!--    依照后果嵌套解决 -->
    <select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.student_name sname,t.teacher_name tname
    from student s,teacher t
    where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="pojo.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="pojo.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

3. 编写测试类

 @Test
    public void getStudent(){SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {System.out.println(student);
        }
        sqlSession.close();}

测试后果

二、一对多查问

更改实体类

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}
@Data
public class Teacher {
    private int id;
    private String name;
    // 一个老师领有多个学生
    private List<Student> students;
}

1、嵌套查询处理

1. 编写接口

Teacher getTeacher2(@Param("tid") int id);

因为字段不统一，要做映射

2. 次要 TeacherMapper.xml 的查问语句（重点）

<select id="getTeacher2" resultMap="TeacherStudent2">
            select * from teacher where id=#{tid}
    </select>
    <resultMap id="TeacherStudent2" type="pojo.Teacher">
        <result property="name" column="teacher_name"/>
        <collection property="students" javaType="ArrayList" ofType="pojo.Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="pojo.Student">
        select * from student where tid=#{tid}
    </select>

3. 测试类

@Test
    public void getTeacher(){SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();}

测试后果：

Teacher(id=0, name= 毛老师, students=[Student(id=1, name=null, tid=1), Student(id=2, name=null, tid=1), Student(id=3, name=null, tid=1), Student(id=4, name=null, tid=1), Student(id=5, name=null, tid=1)])

2、联结查询处理

1. 编写接口

// 获取指定老师下的所有学生及老师的信息
    Teacher getTeacher(@Param("tid") int id);

因为字段不统一，要做映射

2. 次要 TeacherMapper.xml 的查问语句（重点）

<!--    按后果嵌套查问 -->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.student_name sname,t.teacher_name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="pojo.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--        简单的属性须要独自解决 是对象就应用 association，是汇合就应用 collection
        javaType="" 指定的属性类型
        汇合中的泛型信息，应用 ofType 获取 -->
        <collection property="students" ofType="pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

3. 测试类

 @Test
    public void getTeacher(){SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();}

测试后果：

Teacher(id=1, name= 毛老师, students=[Student(id=1, name= 小明, tid=1), Student(id=2, name= 小毛, tid=1), Student(id=3, name= 小红, tid=1), Student(id=4, name= 大黄, tid=1), Student(id=5, name= 超儿, tid=1)])

总结：

本章就应用了简略的两张表联结查问，介绍简略的应用，更简单的多表联结次要在编写 sql 的时候难度大点，或者是嵌套查问要更谨严点官网文档也给了具体的非常复杂的多表查问如下：mybatis，这么简单的看的我头疼

<!-- 非常复杂的语句 -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>

在咱们编写的时候留神点：
不要遗记注册 Mapper.xml
在初学的时候尽量不要给实体类取别名，为了不要混同，加深了解
实体类字段要和数据库字段统一，如果不统一，那就要用 result 标签做映射
简单的属性须要独自解决，是对象就应用 association，是汇合就应用 collection 来映射
javaType=”” 指定的属性类型
汇合中的泛型信息，应用 ofType 获取
​ 多留神简单属性的嵌套应用
JavaType & ofType
JavaType 用来指定实体类中属性的类型
ofType 用来指定映射到 List 或者汇合中的实体类 pojo 类型，泛型中的束缚类型

  • 以上就是《MyBatis 实现多表查问》的分享
  • 也欢送大家交换探讨，该文章若有不正确的中央，心愿大家多多包涵。
  • 你们的反对就是我最大的能源，如果对大家有帮忙给个赞哦~~~

正文完
java
发表至： java
2021-05-06
 0
关于java:JAVA定时任务-JDK-Timer
关于java:SpringCloud-应用在-Kubernetes-上的最佳实践-线上发布可灰度
关于java:Tomcat性能调优
关于java:Java多线程梳理之一多线程开发入门