**
多对一
环境搭建
表的字段数据
Student与Teacher的属性
//Student的属性
private int id;
private String name;
private Teacher teacher;
//Teacher的属性
private int id;
private String name;
问题:需要查询所有的Student对象,但是其中包含了Teacher这个属性,是复杂查询
解决方案1
按照查询嵌套处理
<!--结果集映射-->
<resultMap id="StudentMap" type="Student">
<!--column是数据库中的字段 property是实体类的属性 相同的可省略
复杂的属性需要单独处理 对象:association 集合:collection 这个很重要-->
<!--<result column="id" property="id"/>-->
<!--<result column="name" property="name"/>-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="StudentMap">
select * from student
</select>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{id}
</select>
**
解决方案2
**
按照结果嵌套处理
<select id="getStudent2" resultMap="StudentMap2">
select s.id sid,s.name sname,t.name tname,t.id ttid
from student s,teacher t
where s.tid=t.id;
</select>
<resultMap id="StudentMap2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="ttid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
**
一对多
**
环境搭建
pojo*
//学生
@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> studentList;
}
dao
//获取指定老师下的所有学生和老师信息
Teacher getTeacher(@Param("tid") int id);
待解决的问题
复杂查询:teacher有一个属性为student列表
方案1:按结果嵌套查询(理解较为简单)
resources
<!--案结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherMap">
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=#{tid} ;
</select>
<resultMap id="TeacherMap" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--复杂的属性,我们需要单独处理 对象:association 集合:collection
javaType=""指定属性的类型
对于集合中的泛型类型,我们使用ofType获取-->
<collection property="studentList" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
test
@Test
public void getTeacherTest(){
//第一步:获取sqlsession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//方式一:getMapper
TeacherMapper userMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher1 = userMapper.getTeacher(1);
System.out.println(teacher1);
//关闭sqlSession
sqlSession.close();
}
结果
**
方案2
**
按照查询嵌套查询
<!--第二种:-->
<select id="getTeacher2" resultMap="TeacherMap2">
select * from mybatis_test1.teacher where id=#{tid};
</select>
<resultMap id="TeacherMap2" type="Teacher">
<result property="id" column="id"/>
<!--相同的可省略,但是上述的id在下面用到了,需要写-->
<collection property="studentList" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from mybatis_test1.student where tid=#{id};
</select>
**
总结:
**
按结果嵌套查询:便于理解,sql繁琐
按照查询嵌套查询 :sql简单,特别对于数据表字段多的很实用