一·复杂查询环境搭建
在数据库库中建学生表和老师表,建造一个老师有多个学生的一对多关系:
drop table if exists teacher;
create table teacher(
id int primary key ,
name varchar(20)
)engine = innodb charset = utf8;
drop table if exists student;
create table student(
id int primary key ,
name varchar(20),
tid int,
foreign key (tid) references teacher(id)
)engine = innodb charset = utf8;
insert into teacher(id, name) values (1,'何老师');
insert into student values (1,'张三',1),(2,'李四',1),(3,'王五',1),(4,'赵六',1);
二·多对一
构建实体类pojo学生和老师,这里直接使用lombok偷懒。
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private Teacher teacher;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}
编写TeacherMapper和StudentMapper接口,并在核心配置文件中注册Mapper。
编写Mapper.xml:
这里通过学生查老师,在StudentMapper.xml中编写:
<!-- 查询嵌套-->
<select id="getStudent" resultMap="studentMap">
select * from mybatis.student
</select>
<resultMap id="studentMap" type="student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<association property="teacher" column="tid" select="getTeacher"></association>
<!-- 用当前列的tid去调用getTeacher的select返回给teacher-->
</resultMap>
<select id="getTeacher" resultType="teacher">
select * from mybatis.teacher where id=#{id}
</select>
以上为查询嵌套,将学生的tid再通过另外一个查询getTeacher查询到老师,并映射到teacher属性中。
通过column里的参数传递给嵌套查询去查询。用assocation处理,注意要表明javaType.
下面结果嵌套:
采用连接查询,要映射所有字段,包括id和name
<!-- 结果嵌套-->
<select id="getStudent2" resultMap="studentMap2">
select s.*,t.name tname from mybatis.student s,mybatis.teacher t
</select>
<resultMap id="studentMap2" type="student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<association property="teacher" javaType="teacher">
<!-- 对于对象嵌套进去,将column分别映射成里面的属性-->
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
</association>
</resultMap>
遇到对象就嵌套进去,将column分别映射成里面的属性。建议采用结果嵌套,简单明了。
三,一对多
修改实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
改为一个老师拥有一个学生集合。
编写TeacherMapper.xml
结果嵌套:
一般结果嵌套会使用连接查询,连表的字段有相同的,所以都要映射,查询语句会查出来多个结果,除集合之外的字段相同。
其余的合并到集合中!!!
注意表明ofType为集合里的泛型。
<!-- 根据结果嵌套-->
<select id="getTeacher" resultMap="teacherMap">
select t.id,t.name,s.id sid,s.name sname,s.tid stid
from mybatis.teacher t,mybatis.student s
where t.id=s.tid and tid=#{id}
</select>
<resultMap id="teacherMap" type="teacher">
<!-- 即使数据库字段和对象属性名称相同但也要显示映射,查询语句会查出来多个结果,除集合之外的字段相同
其余的合并到集合中!!!必须都要映射!!!
-->
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<collection property="students" ofType="student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<result property="tid" column="stid"></result>
</collection>
</resultMap>
查询嵌套:
先查询老师,再根据老师的id去调用另一个查询去查询学生,再返回给集合,不需要标注ofType
!-- 根据查询嵌套-->
<select id="getTeacher2" resultMap="teacherMap2">
select * from mybatis.teacher where id=#{id}
</select>
<resultMap id="teacherMap2" type="teacher">
<result property="id" column="id"></result>
<collection property="students" column="id" select="getStudentById"></collection>
</resultMap>
<select id="getStudentById" resultType="student">
select * from mybatis.student where tid=#{id}
</select>
由于结果嵌套采用连接查询所有字段都要映射,当字段较少时使用前者,字段较多时建议使用后者!