转:https://blog.csdn.net/qq_33561055/article/details/78861131
平时在开发过程中dao、bean和XML文件都是自动生成的,很少写XML的配置关系,今天记录一下mybatis的关联查询中的多对一和一对多的情况。
- 首先是有两张表(学生表Student和老师Teacher表),为了更易懂,这里只设置了最简单的几个必要字段。表结构如下图
Student表:
Teacher表:
- 创建实体bean
Teacher.java:
- import java.util.List;
- /**
- * TODO
- * @version 创建时间:2017年12月21日 上午9:02:45
- */
- public class Teacher {
- private Integer id;
- private String name;
- private String className;
- private List<Student> students;
- public List<Student> getStudents() {
- return students;
- }
- public void setStudents(List<Student> students) {
- this.students = students;
- }
- public Integer getId() {
- return id;
- }
- public void setId(Integer id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getClassName() {
- return className;
- }
- public void setClassName(String className) {
- this.className = className;
- }
- }
Sfudent.java
- /**
- * TODO
- *
- * @author 作者 E-mail:2332999366@qq.com
- * @version 创建时间:2017年12月21日 上午9:01:17
- */
- public class Student {
- private Integer id;
- private String name;
- private Integer teacherId;
- private String className;
- private Teacher teacher;
- public Teacher getTeacher() {
- return teacher;
- }
- public void setTeacher(Teacher teacher) {
- this.teacher = teacher;
- }
- public Integer getId() {
- return id;
- }
- public void setId(Integer id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public Integer getTeacherId() {
- return teacherId;
- }
- public void setTeacherId(Integer teacherId) {
- this.teacherId = teacherId;
- }
- public String getClassName() {
- return className;
- }
- public void setClassName(String className) {
- this.className = className;
- }
- @Override
- public String toString() {
- return "{id:"+this.id+",name:"+this.name+",className:"+this.className+",teacherId:"+this.teacherId+"}";
- }
- }
- 下面重点来了:配置Mapper.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.tz.mybatis.dao.studentDao">
- <!-- /一对多的第一种写法,一般考虑到性能问题,不会这么实现 -->
- <resultMap type="Teacher" id="teacherMap">
- <id column="id" property="id"/>
- <result column="name" property="name"/>
- <collection property="students" ofType="Student" column="id">
- <id column="sid" property="id"/><!-- 这里的column对应的是下面查询的别名,而不是表字段名 -->
- <result column="sname" property="name"/><!-- property对应JavaBean中的属性名 -->
- <result column="className" property="className"/>
- </collection>
- </resultMap>
- <!-- 查询所有的老师级各自的所有学生 -->
- <select id="getTeachers" parameterType="Teacher" resultMap="teacherMap">
- SELECT
- t.id,
- t.NAME,
- t.class_Name,
- s.id AS sid,
- s. NAME AS sname,
- s.class_name as className
- FROM
- teacher t
- LEFT JOIN student s ON t.id = s.teacher_id
- </select>
- </mapper>
- 测试类:
- package com.tz.test;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.List;
- 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.Before;
- import org.junit.Test;
- import com.tz.mybatis.bean.Student;
- import com.tz.mybatis.bean.Teacher;
- public class TeacherTest {
- private SqlSessionFactory sqlSessionFactory;
- @Before
- public void init() throws IOException {
- String resource = "mybatis-config.xml";
- InputStream inputStream = Resources.getResourceAsStream(resource);
- sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
- }
- @Test
- public void getTeachers() {
- SqlSession session = sqlSessionFactory.openSession();
- List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getTeachers");
- System.out.println(list);
- }
- }
下面给出第二种写法:
- <!-- //一对多的第二种写法/ -->
- <resultMap type="Teacher" id="teacherMaps">
- <id column="id" property="id"/>
- <result column="name" property="name"/>
- <result column="class_name" property="className"/>
- <collection property="students" ofType="Student" select="getStudents" column="id">
- </collection>
- </resultMap>
- <!-- 查询所有的老师级各自的所有学生 -->
- <select id="getAllTeacher" parameterType="Teacher" resultMap="teacherMaps">
- SELECT
- t.id,
- t.NAME,
- t.class_name
- FROM
- teacher t
- </select>
- <select id="getStudents" parameterType="int" resultType="Student">
- select
- s.id,
- s. NAME,
- s.class_name as className
- from student s
- where teacher_id = #{id}
- </select>
测试类:
- @Test
- public void getTeachers2() {
- SqlSession session = sqlSessionFactory.openSession();
- List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getAllTeacher");
- System.out.println(list);
- }
查询学生信息(多对一):
首先还是配置文件:
- <resultMap type="Student" id="studentMap">
- <id column="id" property="id"/>
- <result column="name" property="name"/>
- <result column="class_name" property="className"/>
- <result column="teacher_id" property="teacherId"/>
- <association property="teacher" select="getTeacher" column="teacher_id" javaType="Teacher">
- <!-- 这里要注意的是column对应的是student中的外键,而且需是表字段名 -->
- </association>
- </resultMap>
- <select id="getStudent" resultMap="studentMap">
- SELECT
- s.id,
- s.name,
- s.class_name,
- s.teacher_id
- FROM
- student s
- </select>
- <select id="getTeacher" resultType="Teacher" parameterType="int">
- SELECT
- t.id,
- t.name,
- t.class_name as className
- FROM teacher t
- where id = #{teacher_id}
- </select>
测试类:
- @Test
- public void getStudents() {
- SqlSession session = sqlSessionFactory.openSession();
- List<Student> list = session.selectList("com.tz.mybatis.dao.studentDao.getStudent");
- System.out.println(list);
- }
最后:当然如果不想配置这么麻烦的信息,可以直接写一个关联查询的SQL语句,返回结果直接由Map接受即可。不过这样就不太符合面向对象的理念了。