Java中mybaties中双表连接_mybatis实现多表一对一，一对多，多对多关联查询

原文：https://blog.csdn.net/m0_37787069/article/details/79247321

1、一对一

关键字：association

作用：针对pojo对象属性的映射

property：pojo的属性名

javaType：pojo类名

(1) 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集

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;

(2) 嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型

column="引入执行另外定制sql方法的参数值(外键)"

select="执行定制sql方法名"

PersonMapper2.xml

select="com.gec.mapper.CardMapper.queryCardById">

select * fromtbl_person;

CardMapper.xml

select c.id as card_id,c.code fromtbl_card cwhere c.id=#{id};

SELECT c.id AS card_id, c.code, p.*FROM tbl_card c,tbl_person p WHERE c.id=p.card_id;

2 、一对多

mybatis如何实现一对多的实现？(学生与班级)

(1) 嵌套结果:

ClazzMapper.xml

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;

StudentMapper.xml

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;

(2) 嵌套查询：

ClazzMapper.xml

select="com.gec.mapper.StudentMapper.queryStudentByClazzId"

>

select * fromtbl_clazz;

StudentMapper.xml

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 * from tbl_student where clazz_id=#{id};

3、多对多

商品表、订单表之间就是以多对多关联

商品与订单的关系表

描述多对多的数据表实现

(1)商品pojo:

Article.java

public classArticle implements Serializable {privateInteger articleId;privateString name;privateDouble price;privateString remark;private Listorders;

省略setter/gettera方法

}

(2)商品表映射：

ArticleMapper.xml

select="com.gec.mapper.OrderMapper.findOrderByArticleId"

>

select * from tb_article whereidin (select article_id from tb_item where order_id=#{id})

select * from tb_article where id=#{id}

(3)订单pojo：

Order.java

public classOrder {privateInteger orderid;privateString code;privateDouble total;private Listarticles;

省略setter/getter方法

}

(4)订单表映射：

OrderMapper.xml

select="com.gec.mapper.ArticleMapper.findArtcleByOrderId">

select * from tb_order whereidin (select order_id from tb_item where article_id=#{id})