mybatis实现多表一对一，一对多，多对多关联查询

1、一对一
关键字：association
作用：针对pojo对象属性的映射
      property：pojo的属性名
      javaType：pojo类名
(1) 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
<resultMap type="com.gec.domain.Person" id="basePersonResultMap"> <id
column="id" property="personId"/> <result column="name" property="name"/>
<result column="sex" property="sex"/> <result column="age" property="age"/>
<association property="card" javaType="com.gec.domain.Card"> <!-- 1、构造器注入
<constructor> <idArg column="id" javaType="int"/> <arg column="code"
javaType="string"/> </constructor> --> <!-- 2、setter注入 --> <result column="id"
property="cardId"/> <result column="code" property="code"/> </association>
</resultMap> <select id="queryUserList" resultMap="basePersonResultMap"> select
p.id as personId,p.name,p.sex,p.age,c.* from tbl_person p,tbl_card c where
p.card_id=c.id; </select>(2) 嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
    column="引入执行另外定制sql方法的参数值（外键）"
    select="执行定制sql方法名"
PersonMapper2.xml
<resultMap type="com.gec.domain.Person" id="basePersonResultMap"> <id
column="id" property="personId"/> <result column="name" property="name"/>
<result column="sex" property="sex"/> <result column="age" property="age"/>
<association property="card" javaType="com.gec.domain.Card" column="card_id"
select="com.gec.mapper.CardMapper.queryCardById"> </association> </resultMap>
<select id="queryUserList" resultMap="basePersonResultMap"> select * from
tbl_person; </select>CardMapper.xml
<resultMap type="com.gec.domain.Card" id="baseCardResultMap"> <id
column="card_id" property="cardId"/> <result column="code" property="code"/>
</resultMap> <select id="queryCardById" resultMap="baseCardResultMap"> select
c.id as card_id,c.code from tbl_card c where c.id=#{id}; </select> <resultMap
type="com.gec.domain.Card" id="queryCardResultMap" extends="baseCardResultMap">
<association property="person" javaType="com.gec.domain.Person"> <id
column="card_id" property="personId"/> <result column="name" property="name"/>
<result column="sex" property="sex"/> <result column="age" property="age"/>
</association> </resultMap> <select id="queryCardList"
resultMap="queryCardResultMap"> SELECT c.id AS card_id, c.code, p.* FROM
tbl_card c,tbl_person p WHERE c.id=p.card_id; </select>2 、一对多
mybatis如何实现一对多的实现？(学生与班级)
(1) 嵌套结果:
ClazzMapper.xml
<resultMap type="com.gec.domain.Clazz" id="baseClazzResultMap"> <id
column="id" property="clazzId"/> <result column="clazz_name"
property="clazzName"/> <result column="code" property="code"/> </resultMap>
<resultMap type="com.gec.domain.Clazz" id="queryClazzList2ResultMap"
extends="baseClazzResultMap"> <collection property="studentList"
javaType="ArrayList" ofType="com.gec.domain.Student"> <id column="stu_id"
property="studentId"/> <result column="name" property="name"/> <result
column="sex" property="sex"/> <result column="age" property="age"/>
</collection> </resultMap> <select id="queryClazzList2"
resultMap="queryClazzList2ResultMap"> SELECT c.*, s.id AS
stu_id,s.name,s.sex,s.age FROM tbl_clazz c LEFT JOIN tbl_student s ON
c.id=s.clazz_id; </select>StudentMapper.xml
<resultMap type="com.gec.domain.Student" id="baseStudentResultMap"> <id
column="id" property="studentId"/> <result column="name" property="name"/>
<result column="sex" property="sex"/> <result column="age" property="age"/>
</resultMap> <resultMap type="com.gec.domain.Student"
id="queryStudentListResultMap" extends="baseStudentResultMap"> <association
property="clazz" javaType="com.gec.domain.Clazz"> <id column="cls_id"
property="clazzId"/> <result column="clazz_name" property="clazzName"/> <result
column="code" property="code"/> </association> </resultMap> <select
id="queryStudentList" resultMap="queryStudentListResultMap"> SELECT s.*,c.id AS
cls_id,c.clazz_name,c.code FROM tbl_student s,tbl_clazz c WHERE
s.clazz_id=c.id; </select>(2) 嵌套查询：
ClazzMapper.xml
<resultMap type="com.gec.domain.Clazz" id="baseClazzResultMap"> <id
column="id" property="clazzId"/> <result column="clazz_name"
property="clazzName"/> <result column="code" property="code"/> </resultMap>
<resultMap type="com.gec.domain.Clazz" id="queryClazzListResultMap"
extends="baseClazzResultMap"> <collection property="studentList"
javaType="ArrayList" column="id" ofType="com.gec.domain.Student"
select="com.gec.mapper.StudentMapper.queryStudentByClazzId" > </collection>
</resultMap> <select id="queryClazzList" resultMap="queryClazzListResultMap">
select * from tbl_clazz; </select>StudentMapper.xml
<resultMap type="com.gec.domain.Student" id="baseStudentResultMap"> <id
column="id" property="studentId"/> <result column="name" property="name"/>
<result column="sex" property="sex"/> <result column="age" property="age"/>
</resultMap> <resultMap type="com.gec.domain.Student"
id="queryStudentListResultMap" extends="baseStudentResultMap"> <association
property="clazz" javaType="com.gec.domain.Clazz"> <id column="cls_id"
property="clazzId"/> <result column="clazz_name" property="clazzName"/> <result
column="code" property="code"/> </association> </resultMap> <select
id="queryStudentList" resultMap="queryStudentListResultMap"> SELECT s.*,c.id AS
cls_id,c.clazz_name,c.code FROM tbl_student s,tbl_clazz c WHERE
s.clazz_id=c.id; </select> <!-- 根据班级的id，获取学生列表 --> <select
id="queryStudentByClazzId" resultMap="baseStudentResultMap"> select * from
tbl_student where clazz_id=#{id}; </select>3、多对多
商品表、订单表之间就是以多对多关联
商品与订单的关系表
描述多对多的数据表实现
(1)商品pojo:
Article.java
public class Article implements Serializable { private Integer articleId;
private String name; private Double price; private String remark; private
List<Order> orders; 省略setter/gettera方法 }(2)商品表映射：
ArticleMapper.xml<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" > <mapper
namespace="com.gec.mapper.ArticleMapper"> <resultMap type="article"
id="baseArticleResultMap"> <id column="id" property="articleId"/> <result
column="NAME" property="name"/> <result column="price" property="price"/>
<result column="remark" property="remark"/> </resultMap> <resultMap
type="article" id="findArtcleByIdResultMap" extends="baseArticleResultMap">
<collection property="orders" javaType="ArrayList"
ofType="com.gec.domain.Article" column="id"
select="com.gec.mapper.OrderMapper.findOrderByArticleId" > </collection>
</resultMap> <!-- 根据订单id查询商品 --> <select id="findArtcleByOrderId"
resultMap="baseArticleResultMap"> select * from tb_article where id in (select
article_id from tb_item where order_id=#{id}) </select> <select
id="findArtcleById" resultMap="findArtcleByIdResultMap"> select * from
tb_article where id=#{id} </select> </mapper>(3)订单pojo：
Order.java
public class Order { private Integer orderid; private String code; private
Double total; private List<Article> articles; 省略setter/getter方法 }(4)订单表映射：
OrderMapper.xml<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" > <mapper
namespace="com.gec.mapper.OrderMapper"> <resultMap type="order"
id="baseOrderResultMap"> <id column="orderId" property="orderid"/> <result
column="CODE" property="code"/> <result column="total" property="total"/>
</resultMap> <resultMap type="order" id="queryOrderByUserIdRsultMap"
extends="baseOrderResultMap"> <collection property="articles"
javaType="ArrayList" ofType="article" column="orderId"
select="com.gec.mapper.ArticleMapper.findArtcleByOrderId"> </collection>
</resultMap> <!-- 根据商品id查询订单 --> <select id="findOrderByArticleId"
resultMap="baseOrderResultMap"> select * from tb_order where id in (select
order_id from tb_item where article_id=#{id}) </select> </mapper>

• « 上一篇：程序猿悲喜录：魔都，想说爱你不容易
• » 下一篇：高德的Vue地图插件