前提介绍
在mybatis如何进行多对一、一对多(一对一)的多表查询呢?本章带你认识如何非常顺滑的解决!
基础使用篇
一对一
association
association通常用来映射一对一的关系,例如,有个类user,对应的实体类如下:
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Student {
private int id;
private String name;
/**
* 学生要关联一个老师
*/
private Teacher teacher;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Teacher {
private int id;
private String name;
}
Dao层进行Mapper查询操作
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") int id);
Teacher getTeacher2(@Param("tid") int id);
}
Dao层进行Mapper.xml文件
<resultMap id="StudentTeacher" type="com.sunreal.pojo.Student">
<result column="id" property="id"></result>
<result column="name" property="name"></result>
<association property="teacher" column="id" javaType="com.sunreal.pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="StudentTeacher">
select *
from student
</select>
<select id="getTeacher" resultType="com.sunreal.pojo.Teacher">
select *
from teacher
where id = #{id}
</select>
<resultMap id="StudentTeacher2" type="com.sunreal.pojo.Student">
<result column="sid" property="id"></result>
<result column="sname" property="name"></result>
<association property="teacher" javaType="com.sunreal.pojo.Teacher">
<result property="name" column="tname"></result>
</association>
</resultMap>
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid, s.name sname, t.name tname
from student s,
teacher t
where s.tid = t.id
</select>
- assocication:可以指定联合的JavaBean对象
- select:指定相关查询结果sqlid
- property="role“:指定哪个属性是联合的对象
- javaType:指定这个属性对象的类型
- column="{javabean熟悉=数据库字段,Javabean属性=数据库字段}"
<association property="role" javaType="com.queen.mybatis.bean.Role"> <id column="role_id" property="id"/> <result column="roleName" property="roleName"/> </association>
以上如果跨越命名空间的情况下:select:需要用namespace.selectId进行指定。
collection
@Alias("Student")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Student {
private int id;
private String name;
private int tid;
}
@Alias("Teacher")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Teacher {
private int id;
private String name;
/**
* 一个老师包含多个学生
*/
private List<Student> studentList;
}
Dao层进行Mapper查询操作
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") int id);
Teacher getTeacher2(@Param("tid") int id);
}
Dao层进行Mapper.xml文件
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
<collection property="studentList" ofType="Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<result property="tid" column="tid"></result>
</collection>
</resultMap>
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid, s.name sname, t.name name, t.id tid
from student s,
teacher t
where s.tid = t.id
and t.id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="studentList" javaType="ArrayList" ofType="Student"
select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getTeacher2" resultMap="TeacherStudent2">
select *
from teacher
where id = #{tid}
</select>
<select id="getStudentByTeacherId" resultType="Student">
select *
from student
where tid = #{tid}
</select>
注意:各个表之间尽量不要有重名字段,包括主键id,不然可能会造成数据混乱错误;
-
JavaType和ofType都是用来指定对象类型的
- property="指的是对象内部(List类型)的属性信息字段名称"
- JavaType是用来指定pojo中属性的类型
- ofType指定的是映射到list集合属性中pojo的类型。
- column="{javabean熟悉=数据库字段,Javabean属性=数据库字段}"
- select:指定相关查询结果sqlid
”特叔“使用篇
一对一映射
实体列 class Tb_blog/TbBlog
private long blogId;
private String blogTitle;
private String blogContent;
private Date createTime;
private String blogType;
private String sId;
private Tb_author author;
List<TbAuthor> tbAuthorList;
实体类 class TbAuthor
private long id;
private String username;
private String password;
private String email;
private String address;
private String phone;
private TbBlog tbBlog;
private List<TbBlog> tbBlogList;
resultMap标签配置
<resultMap id="blogMap" type="Tb_blog" >
<id column="blogId" property="blogId"/>
<result column="blogTitle" property="blogTitle"/>
<result column="blogContent" property="blogContent"/>
<result column="blogType" property="blogType"/>
<result column="createTime" property="createTime"/>
<result column="sId" property="sId"/>
<result column="id" property="author.id"/> <!-- 映射第二张表的实体类属性 -->
<result column="username" property="author.username"/>
<result column="password" property="author.password"/>
<result column="email" property="author.email"/>
</resultMap>
<select id="selectBlogAndAuthor" resultMap="blogMap">
select * from tb_blog g inner join tb_author r
on g.blogId = r.id
</select>
在sql加入别名alias与field属性字段一样,也可以自动注入进入。
association标签配置
<resultMap id="blogMap" type="Tb_blog" >
<id column="blogId" property="blogId"/>
<result column="blogTitle" property="blogTitle"/>
<result column="blogContent" property="blogContent"/>
<result column="blogType" property="blogType"/>
<result column="createTime" property="createTime"/>
<!-- 一对一高效率写法 association一对一关联 property属性为实体类中的第二张表的属性名 -->
<association property="tb_author" javaType="TbAuthor"><!--javaType属性为 返回的实体类对象 -->
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="selectBlogAndAuthor" resultMap="blogMap">
select * from tb_blog g inner join tb_author r on g.blogId = r.id
</select>
collection标签配置
mapper接口定义
AuthorMapper.interface
//!通过id 和映射文件中 association的column属性的值sId关联 来嵌套查询 嵌套查询的第二条sql语句都要写条件来关联第一张表
List<TbAuthor> selectAuthorandBlogAssociation(int id);
BlogMapper.interface
List<TbBlog> selectBlogAndAuthorAssociation();
AuthorMapper.xml
<select id="selectAuthorandBlogAssociation" resultType="com.xqh.pojo.TbAuthor">
select * from tb_author where id=#{id}
</select>
<resultMap id="mapCollection" type="TbAuthor">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<collection property="tbBlogList" column="id"
select="com.xqh.mapper.BlogMapper.selectBlogAndAuthor"
fetchType="lazy">
</collection>
</resultMap>
<select id="selectAuthor_BlogList" resultMap="mapCollection">
select * from tb_author
</select>
BlogMapper.xml
<select id="selectBlogAndAuthor" resultType="com.xqh.pojo.TbBlog">
select * from tb_blog where sId = #{id}
</select>
总结
多表查询一对一映射
association标签
不嵌套 property=当前实体类中的第二种表的属性名 javaType=返回的实体类
嵌套 多加两个属性 column=当前实体类 关联的 第二张表 的外键字段 select=“第二条查询语句” (必须给第二条sql语句写参数限制 不然会获得所有值)
多表查询一对多
collection标签
不嵌套 property=当前实体类中的第二种表的属性名 ofType=返回是实体类
property=当前实体类中的第二种表的属性名 javaType=返回的实体类
嵌套 多加两个属性 column=当前实体类 关联的 第二张表 的外键字段 select=“第二条查询语句” (必须给第二条sql语句写参数限制 不然会获得所有值)
2.多表查询一对多
collection标签
不嵌套 property=当前实体类中的第二种表的属性名 ofType=返回是实体类
嵌套 多加一个属性 column=当前实体类 关联的 第二张表 的外键字段 select=“第二条查询语句” (必须给第二条sql语句写参数限制 不然会获得所有值) [ofType = collection一对多嵌套查询 嵌套查询所有结果 不需写返回类型因为 select已经映射]