MyBatis中的一对多和对多一，多对多

主要就是resultMap中
association（关联） – 一个复杂的类型关联;许多结果将包成这种类型（多对一）

嵌套结果映射 – 关联本身可以是一个 resultMap 元素，或者从别处引用一个
collection（集合） – 复杂类型的集合（一对多）

嵌套结果映射 – 集合本身可以是一个 resultMap 元素，或者从别处引用一个
更多详细信息，在mybatis官方文档就能找到，建议大家可以直接看官方文档
mybatis官方文档
什么是关联：

在mybatis中是用来处理"has a"关系，比如一个员工有一个部门，即一个员工关联一个部门，所以association能用来处理我们数据中所谓的一对一，多对一关系（一个部门有多个员工，但是对于员工来说，一个员工只能关联一个部门）。
什么是集合：

将嵌套映射的结果集合到一个list中，比如一个部门有多个员工，即1个部门对应多个员工，对于部门来说，一个部门有多个员工。

可以看到，在处理我们数据关系的时候，就是对这2个属性的使用，而一对多和多对一都是相互的，只是各自站的角度不同。
实例：

这个实例只说这2个属性的使用方法，具体的配置和运行结果在SSM环境搭建文章中写出。

1.首先是数据库，沿用之前员工管理系统的数据库设计点击这里，上面是之前写过的一篇数据库设计，包含sql语句。
2.实体类：

我这里只列要实现映射功能用到的类user，role，department。department和user，是1对多关系，1个部门拥有多个员工。

role和user是多对多关系，1个角色可能有多个员工，而1个员工也可能有多个角色。而要实现多对多，在程序中则是拆分成2个一对多，详情见下面的实体类注释。
1）User.java
public class User {
	private int user_id;
	private String user_name;
	private String user_gender;
	private String user_email;
	private String user_phone;
	private String user_address;
	private Date user_birthday;
	private int department_id;

	//一对多，1个用户可能有多个角色。
	private List<Role> roles;
	
	//一对一，1个用户属于一个部门
	private Department department;

	public User() {
		super();
	}

	public User(String user_name, String user_gender,
			String user_email, String user_phone, String user_address,
			Date user_birthday, int department_id) {
		super();
		this.user_name = user_name;
		this.user_gender = user_gender;
		this.user_email = user_email;
		this.user_phone = user_phone;
		this.user_address = user_address;
		this.user_birthday = user_birthday;
		this.department_id = department_id;
	}

	public int getUser_id() {
		return user_id;
	}

	public void setUser_id(int user_id) {
		this.user_id = user_id;
	}

	public String getUser_name() {
		return user_name;
	}

	public void setUser_name(String user_name) {
		this.user_name = user_name;
	}

	public String getUser_gender() {
		return user_gender;
	}

	public void setUser_gender(String user_gender) {
		this.user_gender = user_gender;
	}

	public String getUser_email() {
		return user_email;
	}

	public void setUser_email(String user_email) {
		this.user_email = user_email;
	}

	public String getUser_phone() {
		return user_phone;
	}

	public void setUser_phone(String user_phone) {
		this.user_phone = user_phone;
	}

	public String getUser_address() {
		return user_address;
	}

	public void setUser_address(String user_address) {
		this.user_address = user_address;
	}

	public Date getUser_birthday() {
		return user_birthday;
	}

	public void setUser_birthday(Date user_birthday) {
		this.user_birthday = user_birthday;
	}

	public int getDepartment_id() {
		return department_id;
	}

	public void setDepartment_id(int department_id) {
		this.department_id = department_id;
	}

	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	public List<Role> getRoles() {
		return roles;
	}

	public void setRoles(List<Role> roles) {
		this.roles = roles;
	}

	@Override
	public String toString() {
		return "User [user_id=" + user_id + ", user_name=" + user_name
				+ ", user_gender=" + user_gender + ", user_email=" + user_email
				+ ", user_phone=" + user_phone + ", user_address="
				+ user_address + ", user_birthday=" + user_birthday
				+ ", department_id=" + department_id + ", roles=" + roles
				+ ", department=" + department + "]\n";
	}
}

2）Role.java
public class Role {
	private int role_id;
	private String role_name;
	
	//一对多，1个角色也可能被多个用户拥有,所以是list
	private List<User> users;
	
	public Role() {
		
	}
	
	public Role(int role_id, String role_name) {
		super();
		this.role_id = role_id;
		this.role_name = role_name;
	}

	public int getRole_id() {
		return role_id;
	}

	public void setRole_id(int role_id) {
		this.role_id = role_id;
	}

	public String getRole_name() {
		return role_name;
	}

	public void setRole_name(String role_name) {
		this.role_name = role_name;
	}

	public List<User> getUsers() {
		return users;
	}

	public void setUsers(List<User> users) {
		this.users = users;
	}

	@Override
	public String toString() {
		return "Role [role_id=" + role_id + ", role_name=" + role_name
				+ ", users=" + users + "]";
	}

}


3）Department.java
public class Department {
	private int department_id;
	private String department_name;
	
	//1对多，1个部门多个用户
	private List<User> users;
	
	public Department() {
		super();
	}

	public Department(int department_id, String department_name,
			List<User> users) {
		super();
		this.department_id = department_id;
		this.department_name = department_name;
		this.users = users;
	}

	public int getDepartment_id() {
		return department_id;
	}

	public void setDepartment_id(int department_id) {
		this.department_id = department_id;
	}

	public String getDepartment_name() {
		return department_name;
	}

	public void setDepartment_name(String department_name) {
		this.department_name = department_name;
	}

	public List<User> getUsers() {
		return users;
	}

	public void setUsers(List<User> users) {
		this.users = users;
	}

	@Override
	public String toString() {
		return "Department [department_id=" + department_id
				+ ", department_name=" + department_name + ", users=" + users
				+ "]"+"\n";
	}
}

3.实体类的mapper文件

1）UserDepartmentMapper.xml，多对一，多个user对应1个department，对于每个user来说只关心自己的那个department，即每个user关联自己的department，使用association
<?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="employee_management.mapper.UserDepartmentMapper">
	<resultMap type="employee_management.entity.User"
		id="userDepartmentList">
		<id property="user_id" column="user_id" javaType="java.lang.Integer" />
		<result property="user_name" column="user_name" javaType="java.lang.String" />
		<result property="user_gender" column="user_gender" javaType="java.lang.String" />
		<result property="user_email" column="user_email" javaType="java.lang.String" />
		<result property="user_phone" column="user_phone" javaType="java.lang.String" />
		<result property="user_address" column="user_address" javaType="java.lang.String" />
		<result property="user_birthday" column="user_birthday"
			javaType="java.util.Date" />
		<result property="department_id" column="department_id"
			javaType="java.lang.Integer" />
			<!-- property是user实体类中department类属性的名字
			1个user有1个department-->
		<association property="department"
			javaType="employee_management.entity.Department">
			<id property="department_id" column="department_id" javaType="java.lang.Integer" />
			<result property="department_name" column="department_name"
				javaType="java.lang.String" />
		</association>
	</resultMap>
	
	<select id="getAll" resultMap="userDepartmentList">
		select u.*,d.department_name from user u left join department d on u.department_id=d.department_id;
	</select>
</mapper>

2）UserMapper.xml，一对多，使用collection，1个user对应多个role，同时也是多对多中user这一方的一对多。如果要实现多对多，还需要完成1个role对应多个user,即完成2个1对多，写法同这里，就不在赘述。
<?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="employee_management.mapper.UserMapper">

	<!-- get user list only -->
	<resultMap type="employee_management.entity.User" id="userList">
		<id property="user_id" column="user_id" javaType="java.lang.Integer" />
		<result property="user_name" column="user_name" javaType="java.lang.String" />
		<result property="user_gender" column="user_gender" javaType="java.lang.String" />
		<result property="user_email" column="user_email" javaType="java.lang.String" />
		<result property="user_phone" column="user_phone" javaType="java.lang.String" />
		<result property="user_address" column="user_address" javaType="java.lang.String" />
		<result property="user_birthday" column="user_birthday"
			javaType="java.util.Date" />
		<result property="department_id" column="department_id"
			javaType="java.lang.Integer" />
	</resultMap>

	<!-- get user list with role -->
	<resultMap type="employee_management.entity.User" id="userRoleList"
		extends="userList">
		<!-- collection中不是javaType而是ofType,通过property也知道是list，即多个。
		extends属性，即在父resultMap的基础上再加上子resultMap的结果，在这里即再加上部门的信息。 -->
		<collection property="roles" ofType="employee_management.entity.Role">
			<id property="role_id" column="role_id" javaType="java.lang.Integer" />
			<result property="role_name" column="role_name" javaType="java.lang.String" />
		</collection>
	</resultMap>

	</resultMap>
	
<select id="getAll" resultMap="userList">
		select * from user
	</select>
<select id="getAllUserAndRole" resultMap="userRoleList">
		select u.*,r.* from
		user u left join user_role ur on u.user_id=ur.user_id
		left join role r
		on r.role_id=ur.role_id;
	</select>

结论：不管是一对多还是多对一，还是多对多，只需要知道数据是需要一对一关联还是多个结果映射到1个list，就可以很好的在实体的pojo,mapper文件中写出来。

在学习MyBatisPlus 时，简单的查询非常简单，只需继承了相关类，就能够进行增删改。但是在实际运用时，对象之间的关系非常复杂，一对多、多对一、多对多。网上查询了大量i资料都无法解决此问题。

难道要把所有的用Mybatis的重写一次？

重写一次Plus的方法还能不能用？

实在没办只能查看官网https://mp.baomidou.com/guide/在注解处找到了可能的解决方案



@TableName注解可以设置对应的resultMap 看到这里我想是不是，在Mapper中设置好resultMap，map中使用association、或者collection就能解决了。结果是可以的，上图：

实体类



mapper



rest效果



完美解决！！！

sharding 传送门.

设计模式传送门.

Mybatis04_多对多查询
customer表结构：



customer表内容：



goods表结构：



goods表内容：



中间表customer_goods表结构：



中间表customer_goods表内容：



Customer实体：
package com.blu.entity;

import java.util.List;
import lombok.Data;

@Data
public class Customer {
	private long id;
	private String name;
	private List<Goods> goods;
}

Goods实体：
package com.blu.entity;

import java.util.List;
import lombok.Data;

@Data
public class Goods {
	private long id;
	private String name;
	private List<Customer> customers;
}

查询Customer带Goods
CustomerRepository接口：
package com.blu.repository;

import com.blu.entity.Customer;

public interface CustomerRepository {
	public Customer findById(long id);
}

CustomerRepository.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.blu.repository.CustomerRepository">
	<resultMap id="customerMap" type="com.blu.entity.Customer">
		<id column="cid" property="id"></id>
		<result column="cname" property="name"></result>
		<collection property="goods" ofType="com.blu.entity.Goods">
			<id column="gid" property="id" />
			<result column="gname" property="name" />
		</collection>
	</resultMap>
	<select id="findById" parameterType="long" resultMap="customerMap">
		select c.id cid,c.name cname,g.id gid,g.name gname
		from customer c,goods g,customer_goods cg 
		where c.id = #{id} and cg.cid = c.id and cg.gid = g.id
	</select>
</mapper>

注册mapper
<mapper resource="com/blu/mapper/CustomerRepository.xml"></mapper>

测试方法：
public class Test {
	public static void main(String[] args) {
		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		CustomerRepository customerRepository = sqlSession.getMapper(CustomerRepository.class);
		Customer customer = customerRepository.findById(1L);
		System.out.println(customer);
		sqlSession.close();	
	}
}

结果：
Customer(id=1, name=张三, goods=[Goods(id=1, name=电视, customers=null), Goods(id=3, name=洗衣机, customers=null)])



查询Goods带Customer


GoodsRepository 接口：
package com.blu.repository;

import com.blu.entity.Goods;

public interface GoodsRepository {
	public Goods findById(long id);
}

GoodsRepository.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.blu.repository.GoodsRepository">
	<resultMap id="goodsMap" type="com.blu.entity.Goods">
		<id column="gid" property="id"></id>
		<result column="gname" property="name"></result>
		<collection property="customers" ofType="com.blu.entity.Customer">
			<id column="cid" property="id" />
			<result column="cname" property="name" />
		</collection>
	</resultMap>
	
	<select id="findById" parameterType="long" resultMap="goodsMap">
		select c.id cid,c.name cname,g.id gid,g.name gname
		from customer c,goods g,customer_goods cg
		where g.id = #{id} and cg.cid = c.id and cg.gid = g.id
	</select>
</mapper>

注册mapper
<mapper resource="com/blu/mapper/GoodsRepository.xml"></mapper>

测试方法：
public class Test {
	public static void main(String[] args) {
		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		GoodsRepository goodsRepository = sqlSession.getMapper(GoodsRepository.class);
		Goods goods = goodsRepository.findById(3L);
		System.out.println(goods);
		sqlSession.close();
	}
}

结果：
Goods(id=3, name=洗衣机, customers=[Customer(id=1, name=张三, goods=null), Customer(id=3, name=李华, goods=null)])

场景：使用三张数据表：student学生表、teacher教师表、position职位表

一个学生可以有多为老师、一位老师可以有多个学生、但是一个老师只能有一个职位：教授、副教授、讲师；但是一个职位可以有多个老师：例如教授可以多人

这里则产生了：

一对一关系，从老师角度：老师对职位一对一

一对多关系，从职位角度：职位对老师一对多

多对多关系：查找被教授教导的所有学生（首先职位对老师一对多，老师再对学生再对多、这里便有了一对多对多）

数据表：

老师表

CREATE TABLE `tb_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_no` varchar(20) DEFAULT NULL,
  `t_name` varchar(20) DEFAULT NULL,
  `position_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `tb_teacher` */

insert  into `tb_teacher`(`id`,`t_no`,`t_name`,`position_id`) values
(1,'163314001','张文远',1),
(2,'163314002','赵传智',1),
(3,'163314003','风清扬',2),
(4,'163314004','王汇智',2),
(5,'163314005','汪思远',3);

学生表

CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_stu_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `tb_student` */

insert  into `tb_student`(`id`,`t_stu_name`) values 

(1,'赵依'),

(2,'钱迩'),

(3,'张山'),

(4,'李石'),

(5,'王武'),

(6,'马柳');

职位表

CREATE TABLE `tb_position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_pos_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `tb_position` */

insert  into `tb_position`(`id`,`t_pos_name`) values 

(1,'教授'),

(2,'副教授'),

(3,'讲师');

最后是教师学生关系表

CREATE TABLE `tb_stu_teach` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_stu_id` int(11) DEFAULT NULL,
  `t_teach_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

/*Data for the table `tb_stu_teach` */

insert  into `tb_stu_teach`(`id`,`t_stu_id`,`t_teach_id`) values 

(1,1,1),

(2,1,2),

(3,1,3),

(4,2,2),

(5,2,3),

(6,2,4),

(7,3,3),

(8,3,4),

(9,3,5),

(10,4,4),

(11,4,5),

(12,4,1);


最后在eclipse中的目录结构如下：



希望您明白sqlMapConfig该如何配置，以及jdbc.properties和log4j的作用。

贴出POJO中的三个实体(注意：以下的POJO都用了lombok来快速生成setter和getter等，lomok具体使用，请见此文)：

Position.java

package com.pojo;

import java.io.Serializable;

import lombok.Data;

@Data
public class Position implements Serializable {
	private int id;
	private String name;
	
	private Teacher teacher;

}


Student.java

package com.pojo;

import java.io.Serializable;
import java.util.List;

import lombok.Data;

@Data
public class Student implements Serializable {
	private String id;
	private String name;
	
	private List<Teacher> list;
}

Teacher.java

package com.pojo;

import java.io.Serializable;
import java.util.List;

import lombok.Data;

@Data
public class Teacher implements Serializable {
	private int id;
	
	private String no;
	private String name;
	private List<Student> studentList;
	
	private Position pos;
}


注意：关系表不用以实体表示出来，表示外键关系的ID也不用写在实体中(一般我们也不使用外键)

 

再贴另一个MybatisUtil.java工具类

package com.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtil {
	private static SqlSessionFactory sqlSessionFactory = null;
	
	static {
		String resource = "sqlMapConfig.xml";
		// 首先要加载核心配置文件：从classpath下开始找。
		InputStream in;
		try {
			in = Resources.getResourceAsStream(resource);
			
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		} catch (IOException e) {
			throw new RuntimeException(e.getMessage());
		}	
	}
	
	public static SqlSession getSqlSession() {
		
		return sqlSessionFactory.openSession();
	}
	
	public static SqlSessionFactory getSqlSessionFactory() {
		return sqlSessionFactory;
	}
}


一对一：老师对职位

TeacherMapper.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.mapper.TeacherMapper">
	<resultMap type="Teacher" id="teacherPositionResultMap">
		<id property="id" column="id"/>
		<result property="no" column="t_no"/>
		<result property="name" column="t_name"/>
		<!-- association：配置的一对一属性 -->
		<!-- property：名字
			 javaType：类型
		 -->
		<association property="pos" javaType="Position">
			<id property="id" column="id"/>
			<result property="name" column="t_pos_name"/>
		</association>
	</resultMap>
	
	<!-- 一对一关联查询，查询老师及其对应的职位 -->
	<!-- 注意：id不能相同，当多个值传入，比如包装类的时候，我们才能够用SQL片段的形式来做if判断，单个值是不行的 -->
	<select id="queryTeacherPositionResultMapById" resultMap="teacherPositionResultMap" parameterType="Integer">
		SELECT *
		FROM tb_teacher t
		LEFT JOIN tb_position p
		ON t.position_id = p.id
		where t.id = #{id}
	</select>

	<select id="queryTeacherPositionResultMap" resultMap="teacherPositionResultMap">
		SELECT *
		FROM tb_teacher t
		LEFT JOIN tb_position p
		ON t.`position_id` = p.id
	</select> 
</mapper>

TeacherMapper.java接口

package com.mapper;

import java.util.List;

import com.pojo.Teacher;

public interface TeacherMapper {
	public List<Teacher> queryTeacherPositionResultMap();
	
	public Teacher queryTeacherPositionResultMapById(Integer id);
}


测试一对一：

package com.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.TeacherMapper;
import com.pojo.Teacher;
import com.util.MyBatisUtil;

public class TestOneToOne {
	@Test
	public void testOneToOne() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		System.err.println(sqlSession);
		
		TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
		
		List<Teacher> list = teacherMapper.queryTeacherPositionResultMap();
		
		System.out.println(list);
		
		Teacher teacher = teacherMapper.queryTeacherPositionResultMapById(1);
		System.out.println(teacher);
	}
}


 

一对多：职位对老师

PositionMapper.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.mapper.PositionMapper"> 
	<resultMap type="Position" id="positionTeacherResultMap">
		<id property="id" column="id"/>
		<result property="name" column="t_pos_name"/> <!-- t_name -->
		<!-- 
		property同association中的一样是属性名称(javaBean中的)；
		javaType也同association中的是类型，
		最后多了一个OfType，因为一对多，不像一对一是单个的！我们这里是List集合，list和List都可以。
		一对多其中是放的一个集合所以这个是集合的泛型的类型，这里我们的list中放的是Teacher：
		所以这里是Teacher。
		 -->
		<collection property="teacherList" javaType="list" ofType="Teacher" >
			<!-- 
				一对多出现的问题：
					当数据库表中，主表的主键id和明细表的 ...
					当表中的字段名相同时怎么办？多表联查？
					
					注意：Mybatis中做多表联查的时候，不管是
					一对一、一对多、一对多对多：多对多：
					都不能有字段重名的情况：不管是主键还是普通字段。
					一旦字段重名的话，就会造成数据少自动赋值，或者覆盖，甚至重复赋值！
					规避和解决此类问题的方法：
						1.尽量不要表间重名，mybatis里处理起来很麻烦！id和普通字段都是。
						但是在表多的时候，很难不会出现字段重名的情况。主键id最容易重名！
						那么就要用以下的办法了！
						
						2.在mybatis中写原生SQL进行查询的时候，查的字段尽可能的少，这
						也影响速率，强烈禁止使用*，用多少查多少！这样也能及时发现字段重
						名的情况！
						
						3.最后如果真的需要查出重名的字段，并且修改数据库字段名造成的更改
						过大，这里推荐的方式是给字段取别名，在写resultMap映射的时候，其
						中的column属性就填写SQL语句中查出字段取的别名，这样就能解决重复
						问题了！
			 -->
			<id property="id" column="t_id"/>
			<result property="no" column="t_no"/>
			<result property="name" column="t_name"/>	
		</collection>
	</resultMap>
	
	<select id="queryPositionTeacherResultMapById" resultMap="positionTeacherResultMap" 
		parameterType="Integer">
		<!-- 
		SELECT *
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		WHERE p.id = #{id}
		-->
		
		SELECT 
		p.*, 
		t.id t_id,
		t.t_name,
		t.t_no
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		WHERE p.id = #{id}
	</select>
	
	<select id="queryPositionTeacherResultMap" resultMap="positionTeacherResultMap" >
		<!-- 
		SELECT *
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		-->
		
		SELECT 
		p.*, 
		t.id t_id,
		t.t_name,
		t.t_no
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		
	</select>
</mapper>



TeacherMapper.java接口

package com.mapper;

import java.util.List;

import com.pojo.Position;

public interface PositionMapper {
	public Position queryPositionTeacherResultMapById(Integer id);
	
	public List<Position> queryPositionTeacherResultMap();
}


测试一对多：

package com.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.PositionMapper;
import com.pojo.Position;
import com.util.MyBatisUtil;

public class TestOneToMany {
	
	@Test
	public void testOneToMany() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		PositionMapper positionMapper = sqlSession.getMapper(PositionMapper.class);
		List<Position> list = positionMapper.queryPositionTeacherResultMap();
		
		System.out.println(list);
		
		Position pos = positionMapper.queryPositionTeacherResultMapById(1);
		
		System.out.println(pos);
	}
}


多对多：职位是教授的老师教授的所有学生(一对多对多：只要你愿意可以一直对多下去...)

PositionMapper.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.mapper.PositionMapper"> 
	<resultMap type="Position" id="positionStudentResultMap">
		<!-- <id property="id" column="id"/> -->
		<result property="name" column="t_pos_name"/>
		<collection property="teacherList" javaType="list" ofType="Teacher" >
			<result property="name" column="t_name"/>	
			<collection property="studentList" javaType="list" ofType="Student">
				<result property="name" column="t_stu_name"/>
			</collection>
		</collection>
	</resultMap>
	
	<select id="selectPositionStudentByPosId" resultMap="positionStudentResultMap" parameterType="Integer">
		SELECT p.t_pos_name, t.t_name, s.t_stu_name
		FROM tb_position p
		INNER JOIN tb_teacher t ON p.id = t.position_id
		LEFT JOIN tb_stu_teach st ON st.t_teach_id = t.id
		LEFT JOIN tb_student s ON s.id = st.t_stu_id
		WHERE p.id = #{id}
	</select>
</mapper>



PositionMapper.java接口

package com.mapper;

import com.pojo.Position;

public interface PositionMapper {
	public Position selectPositionStudentByPosId(Integer id);
	
}


测试：

package com.test;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.PositionMapper;
import com.pojo.Position;
import com.util.MyBatisUtil;

public class TestManyToMany {
	
	@Test
	public void testManyToMany() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		PositionMapper positionMapper = sqlSession.getMapper(PositionMapper.class);
		Position pos = positionMapper.selectPositionStudentByPosId(1);
		
		System.out.println(pos);
	}
}