mybatis-复杂查询(一对多,多对一)

一、多对一(例如多个学生对应一个老师)

1、学生实体

复制代码
package com.me.domain;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}
复制代码

2、老师实体

1
2
3
4
5
6
7
8
9
package com.me.domain;
 
import lombok.Data;
 
@Data
public class Teacher {
    private int id;
    private String name;
}

方法一:嵌套查询(按查询嵌套处理)

1、 StudentMapper-getStudentList:

复制代码

<select id="getStudentList" resultMap="studentTea">
  select * from student
</select>


<resultMap id="studentTea" type="com.me.domain.Student">
  <result property="id" column="id"/>
  <result property="name" column="name"/>
  <association property="teacher" column="tid" javaType="com.me.domain.Teacher" select="getTeacher"/>

</resultMap>

<select id="getTeacher" resultType="com.me.domain.Teacher">
  select * from teacher where id = #{tid}
</select>

复制代码

2、测试

复制代码

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

复制代码

3、结果

 

 方法二、联合查询(按结果嵌套处理)

1、 StudentMapper-getStudentList2:

复制代码

<select id="getStudentList2" resultMap="studentTea2">
  select s.id sid ,s.name sname ,t.id tid ,t.name tname
  from student s,teacher t
  where s.tid = t.id
</select>
<resultMap id="studentTea2" type="com.me.domain.Student">
  <result property="id" column="sid"/>
  <result property="name" column="sname"/>
  <association property="teacher" javaType="com.me.domain.Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
  </association>
</resultMap>

复制代码

2、测试

复制代码

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

复制代码

3、结果

 

 二、一对多(一个老师有多个学生)

1、学生实体

复制代码

package com.me.domain;

import lombok.Data;

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

复制代码

2、老师实体

复制代码

package com.me.domain;

import lombok.Data;

import java.util.List;

@Data
public class Teacher2 {
  private int id;
  private String name;
  private List<Student2> students;
}

复制代码

方法一:嵌套查询(按查询嵌套处理)

1、 TeacherMapper-getTeacher2:

复制代码

<select id="getTeacher2" resultMap="teacherStu2">
  select * from teacher where id = #{id}
</select>
<resultMap id="teacherStu2" type="com.me.domain.Teacher2" >
  <result property="id" column="id"/>
  <result property="name" column="name"/>
  <collection property="students" javaType="ArrayList" ofType="com.me.domain.Student2" select="getStudentByTid" column="id"/>
</resultMap>


<select id="getStudentByTid" resultType="com.me.domain.Student2">
  select * from student where tid = #{id}
</select>

复制代码

2、测试

复制代码

@org.junit.Test
public void getTeacher2(){
  SqlSession sqlSession = MyBatisUtils.getSqlSession();
  TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
  Teacher2 teacher = mapper.getTeacher2(1);
  System.out.println(teacher.toString());
  sqlSession.close();
}

复制代码

3、结果

 

  方法二、联合查询(按结果嵌套处理)

1、 TeacherMapper-getTeacher:

复制代码

<select id="getTeacher" resultMap="teacherStu">
  select s.id sid ,s.name sname ,t.id tid ,t.name tname
  from student s, teacher t
  where s.tid = t.id and t.id =#{id}
</select>
<resultMap id="teacherStu" type="com.me.domain.Teacher2">
  <result property="id" column="tid"/>
  <result property="name" column="tname"/>
  <collection property="students" ofType="com.me.domain.Student2">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="tid"/>
  </collection>
</resultMap>

复制代码

2、测试

复制代码

@org.junit.Test
public void getTeacher(){
  SqlSession sqlSession = MyBatisUtils.getSqlSession();
  TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
  Teacher2 teacher = mapper.getTeacher(1);
  System.out.println(teacher.toString());
  sqlSession.close();
}

复制代码

3、结果

 三、小结

1、集合--collection【一对多】

2、关联--association【多对一】

3、javaType:用来指定实体类中属性的类型

4、ofType:用来指定映射到List或集合中的实体类型,泛型中的约束类型。

5、嵌套查询可读性强,容易理解,但是联合查询书写简便一点。

posted @   冯虚御风,不知所止  阅读(102)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗?
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句:使用策略模式优化代码结构
· 字符编码:从基础到乱码解决
· 提示词工程——AI应用必不可少的技术
点击右上角即可分享
微信分享提示