多对一关联关系在现在现实生活中的例子很多很多,比如一个学校可以包含很多个学生,一个学生只属于一个学校,那么学生和学校就是多对一的关系。
下面用例子演示如何进行多对一的处理
首先是有两个表,分别是
学生表,有主键id, 姓名,tid(外键,对应哪个老师)
教师表,有主键id, 姓名
上面两个表的关系是多对一的关系
现在我们要从上面的表中:查询每个学生信息以及每个学生对应的老师信息
表对应实体类
package com.ys.pojo;
import lombok.Data;
import org.apache.ibatis.type.Alias;
@Data
@Alias("Student")
public class Student {
private int ID;
private String name;
// 学生需要关联一个老师
private Teacher teacher;
}
package com.ys.pojo;
import lombok.Data;
import org.apache.ibatis.type.Alias;
@Data
@Alias("Teacher")
public class Teacher {
private int id;
private String name;
}
方式一:联表查询,即按结果嵌套处理
1、接口
public interface StudentMapper {
List<Student> selectStudnets2();
}
2.mapper.xml (复杂的属性要单独处理,对象用association,如果是集合,用collection)
<select id="selectStudnets2" resultMap="studentteacher">
select stu.ID sid,stu.name sname,tea.name tname
from stu,tea
where stu.tid=tea.id;
</select>
<resultMap id="studentteacher" type="Student">
<result property="ID" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
3.测试
@Test
public void selectStudnets() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectStudnets2();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
4.结果
方式二:子查询,即按查询嵌套处理
1.接口
public interface StudentMapper {
List<Student> selectStudnets3();
}
2.mapper.xml
<select id="selectStudnets3" resultMap="StudentTeacher">
select * from stu
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="ID" column="ID"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select *
from tea
where id=#{id}
</select>
3.测试
@Test
public void selectStudnets() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectStudnets3();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
4.结果同上