Mybatis的三种映射关系以及联表查询

目录

一、概念

二、一对一

1、配置generatorConfig.xml

2、Vo包的编写

3、xml的sql编写

4、编写对应接口及实现类

5、测试

三、一对多

1、Vo包类的编写

2、xml的sql编写

3、编写对应接口及实现类

4、测试

四、多对多

1、Vo类

2、xml的sql配置

3、接口及接口实现类

4、测试

---

一、概念

1、MyBatis中表之间的关系是如何映射的处理的？
resultType：使用多表查询我们经常会resultType="java.utils.Map" ,我们不推荐这样写，但是这样写对自己比较有利。

好处：resultType 是直接将查询结果映射到 Java 对象，可以使用简单的类型（如 int、String）或复杂的自定义类型。它的好处是简单直观，易于使用。
弊端：对于复杂的关系映射，resultType 可能会变得冗长，并且无法处理一对多或多对多的关系映射。
resultMap：resultMap 允许我们定义复杂的映射规则，将结果集中的多个字段映射到一个对象中。

好处：可以处理复杂的关系映射，支持一对多或多对多的关系映射。我们可以在 resultMap 中定义映射规则，指定字段与属性间的映射关系，并通过嵌套 resultMap 处理表之间的关系。
弊端：相对于 resultType，resultMap 的配置较为繁琐。

二、一对一

1、配置generatorConfig.xml

在我们的配置文件里面配置我们需要的几个表，自动生成所需文件

        <table schema="" tableName="t_hibernate_book" domainObjectName="HBook"enableCountByExample="false" enableDeleteByExample="false"enableSelectByExample="false" enableUpdateByExample="false"></table><table schema="" tableName="t_hibernate_book_category" domainObjectName="HBookCategory"enableCountByExample="false" enableDeleteByExample="false"enableSelectByExample="false" enableUpdateByExample="false"></table><table schema="" tableName="t_hibernate_category" domainObjectName="HCategory"enableCountByExample="false" enableDeleteByExample="false"enableSelectByExample="false" enableUpdateByExample="false"></table><table schema="" tableName="t_hibernate_order" domainObjectName="HOrder"enableCountByExample="false" enableDeleteByExample="false"enableSelectByExample="false" enableUpdateByExample="false"></table><table schema="" tableName="t_hibernate_order_item" domainObjectName="HOrderItem"enableCountByExample="false" enableDeleteByExample="false"enableSelectByExample="false" enableUpdateByExample="false"></table>

然后生成我们想要的model和xml映射文件

2、Vo包的编写

当然我们要先建立这个包里面的类才能更好的下一步。

我们现在示例的是一对一的，所以根据前面以此类推我们建立一个HOrderItemVo类

package com.liwen.vo;import com.liwen.model.HOrder;
import com.liwen.model.HOrderItem;/*** @软件包名 com.liwen.vo* @用户 liwen* @create 2023-08-26 下午4:37* @注释说明：*/
public class HOrderItemVo extends HOrderItem {private HOrder hOrder;public HOrder gethOrder() {return hOrder;}public void sethOrder(HOrder hOrder) {this.hOrder = hOrder;}
}

3、xml的sql编写

在我们的里面添加一个sql的方法编写

    <resultMap id="HOrderItemVoMap" type="com.liwen.vo.HOrderItemVo"><result column="order_itemId" property="orderItemId"/><result column="product_id" property="productId"/><result column="quantity" property="quantity"/><result column="oid" property="oid"/><!--association是一对一的关系--><association property="hOrder" javaType="com.liwen.model.HOrder"><result column="order_id" property="orderId"/><result column="order_no" property="orderNo"/></association></resultMap><select id="selectByHOrderId" resultMap="HOrderItemVoMap" parameterType="java.lang.Integer">select *from t_hibernate_order o,t_hibernate_order_item oiwhere o.order_id = oi.oidand oi.order_item_id = #{oiid}</select>

4、编写对应接口及实现类

在上面我们已经写好了sql，我们生成对应的接口及接口实现方法。

在我们生成的HOrderItemMapper 接口里面编写

package com.liwen.mapper;import com.liwen.model.HOrderItem;
import com.liwen.vo.HOrderItemVo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;@Repository
public interface HOrderItemMapper {int deleteByPrimaryKey(Integer orderItemId);int insert(HOrderItem record);int insertSelective(HOrderItem record);HOrderItem selectByPrimaryKey(Integer orderItemId);int updateByPrimaryKeySelective(HOrderItem record);int updateByPrimaryKey(HOrderItem record);HOrderItemVo selectByHOrderId(@Param("oiid") Integer oiid);
}

创建一个biz的包，里面编写一个HOrderItemBiz接口类并且编写接口方法

package com.liwen.biz;import com.liwen.vo.HOrderItemVo;/*** @软件包名 com.liwen.biz* @用户 liwen* @create 2023-08-26 下午4:48* @注释说明：*/
public interface HOrderItemBiz {HOrderItemVo selectByHOrderId(Integer oiid);
}

在这个biz里面新建一个impl包，里面创建一个HOrderItemBizImpl 接口实现类，继承HOrderItemBiz

package com.liwen.biz.impl;import com.liwen.biz.HOrderItemBiz;
import com.liwen.mapper.HOrderItemMapper;
import com.liwen.vo.HOrderItemVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-26 下午4:48* @注释说明：*/
@Service
public class HOrderItemBizImpl implements HOrderItemBiz {@Autowiredprivate HOrderItemMapper hOrderItemMapper;@Overridepublic HOrderItemVo selectByHOrderId(Integer oiid) {return hOrderItemMapper.selectByHOrderId(oiid);}
}

5、测试

package com.liwen.biz.impl;import com.liwen.biz.HOrderItemBiz;
import com.liwen.vo.HOrderItemVo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-26 下午4:58* @注释说明：*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-context.xml"})
public class HOrderItemBizImplTest {@Autowiredprivate HOrderItemBiz hOrderItemBiz;@Testpublic void selectByHOrderId() {HOrderItemVo hOrderItemVo = hOrderItemBiz.selectByHOrderId(27);System.out.println(hOrderItemVo);}
}

三、一对多

1、Vo包类的编写

因为我们是一对多的所以我们再编写vo类的时候，里面是使用list集合

package com.liwen.vo;import com.liwen.model.HOrder;
import com.liwen.model.HOrderItem;import java.util.ArrayList;
import java.util.List;/*** @软件包名 com.liwen.vo* @用户 liwen* @create 2023-08-26 下午3:55* @注释说明：*/
public class HOrderVo extends HOrder {//    一个订单存在多个订单项private List<HOrderItem> hOrderItems = new ArrayList<>();public List<HOrderItem> gethOrderItems() {return hOrderItems;}public void sethOrderItems(List<HOrderItem> hOrderItems) {this.hOrderItems = hOrderItems;}
}

2、xml的sql编写

在原本的基础的sql上我们增加一个一对多的sql

 <!--  resultType="com.liwen.vo.HOrderVo" 在多表的字段是无法使用的--><!--  我们要写一个resultMap映射--><resultMap id="HOrderVoMap" type="com.liwen.vo.HOrderVo"><!--   每个订单对应的属性，column：数据库属性名；property：实体类属性名     --><result column="order_id" property="orderId"/><result column="order_no" property="orderNo"/><!--   我们设置hOrderItems数组里面的属性     --><!--   collection是一对多的关系     --><collection property="hOrderItems" ofType="com.liwen.model.HOrderItem"><result column="order_itemId" property="orderItemId"/><result column="product_id" property="productId"/><result column="quantity" property="quantity"/><result column="oid" property="oid"/></collection></resultMap><select id="byOid" resultMap="HOrderVoMap" parameterType="java.lang.Integer">select *from t_hibernate_order o,t_hibernate_order_item oiwhere o.order_id = oi.oidand o.order_id = #{oid}</select>

3、编写对应接口及实现类

根据sql生成的对应的HOrderMapper 类里面生成已经编写好的sql方法

package com.liwen.mapper;import com.liwen.model.HOrder;
import com.liwen.vo.HOrderVo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;@Repository
public interface HOrderMapper {int deleteByPrimaryKey(Integer orderId);int insert(HOrder record);int insertSelective(HOrder record);HOrder selectByPrimaryKey(Integer orderId);int updateByPrimaryKeySelective(HOrder record);int updateByPrimaryKey(HOrder record);//============================================HOrderVo byOid(@Param("oid") Integer oid);
}

在biz包里面新建一个接口HOrderBiz 

package com.liwen.biz;import com.liwen.vo.HOrderVo;/*** @软件包名 com.liwen.biz* @用户 liwen* @create 2023-08-26 下午4:15* @注释说明：*/
public interface HOrderBiz {HOrderVo byOid(Integer oid);
}

在biz包里面的impl里面新建一个Java类实现HOrderBiz 接口

package com.liwen.biz.impl;import com.liwen.biz.HOrderBiz;
import com.liwen.mapper.HOrderMapper;
import com.liwen.vo.HOrderVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-26 下午4:16* @注释说明：*/
@Service
public class HOrderBizImpl implements HOrderBiz {@Autowiredprivate HOrderMapper hOrderMapper;@Overridepublic HOrderVo byOid(Integer oid) {return hOrderMapper.byOid(oid);}
}

4、测试

package com.liwen.biz.impl;import com.liwen.biz.HOrderBiz;
import com.liwen.vo.HOrderVo;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import static org.junit.Assert.*;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-26 下午4:22* @注释说明：*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-context.xml"})
public class HOrderBizImplTest {@Autowiredprivate HOrderBiz hOrderBiz;@Testpublic void byOid() {HOrderVo hOrderVo = hOrderBiz.byOid(7);System.out.println(hOrderVo);}}

四、多对多

1、Vo类

package com.liwen.vo;import com.liwen.model.HBook;
import com.liwen.model.HCategory;import java.util.List;/*** @软件包名 com.liwen.vo* @用户 liwen* @create 2023-08-27 下午10:29* @注释说明：*/
public class HBookVo extends HBook {private List<HCategory> hCategoryList;public List<HCategory> gethCategoryList() {return hCategoryList;}public void sethCategoryList(List<HCategory> hCategoryList) {this.hCategoryList = hCategoryList;}
}

package com.liwen.vo;import com.liwen.model.HBook;
import com.liwen.model.HCategory;import java.util.ArrayList;
import java.util.List;/*** @软件包名 com.liwen.vo* @用户 liwen* @create 2023-08-27 下午11:03* @注释说明：*/
public class HCategoryVo extends HCategory {private List<HBook> hBooks = new ArrayList<>();public List<HBook> gethBooks() {return hBooks;}public void sethBooks(List<HBook> hBooks) {this.hBooks = hBooks;}
}

2、xml的sql配置

分别在不同的xml配置文件里面配置

 <resultMap id="HBookVo" type="com.liwen.vo.HBookVo"><result column="book_id" property="bookId"/><result column="book_name" property="bookName"/><result column="price" property="price"/><collection property="hCategoryList" ofType="com.liwen.model.HCategory"><result column="category_id" property="categoryId"/><result column="category_name" property="categoryName"/></collection></resultMap><!--    根据书籍id查询出书籍信息及所属类别--><select id="selectByBId" resultMap="HBookVo" parameterType="java.lang.Integer">select *from t_hibernate_book b,t_hibernate_book_category bc,t_hibernate_category cwhere b.book_id = bc.bidand bc.cid = c.category_idand b.book_id = #{bid}</select>

<resultMap id="HCategoryVo" type="com.liwen.vo.HCategoryVo"><result column="category_id" property="categoryId"/><result column="category_name" property="categoryName"/><collection property="hBooks" ofType="com.liwen.model.HBook"><result column="book_id" property="bookId"/><result column="book_name" property="bookName"/><result column="price" property="price"/></collection></resultMap><select id="selectByCId" resultMap="HCategoryVo" parameterType="java.lang.Integer">select *from t_hibernate_book b,t_hibernate_book_category bc,t_hibernate_category cwhere b.book_id = bc.bidand bc.cid = c.category_idand c.category_id = #{cid}</select>

3、接口及接口实现类

在生成的接口类里面编写对应的接口方法

package com.liwen.mapper;import com.liwen.model.HBook;
import com.liwen.vo.HBookVo;
import org.apache.ibatis.annotations.Param;public interface HBookMapper {int deleteByPrimaryKey(Integer bookId);int insert(HBook record);int insertSelective(HBook record);HBook selectByPrimaryKey(Integer bookId);int updateByPrimaryKeySelective(HBook record);int updateByPrimaryKey(HBook record);HBookVo selectByBId(@Param("bid") Integer bid);
}

package com.liwen.mapper;import com.liwen.model.HCategory;
import com.liwen.vo.HCategoryVo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;@Repository
public interface HCategoryMapper {int deleteByPrimaryKey(Integer categoryId);int insert(HCategory record);int insertSelective(HCategory record);HCategory selectByPrimaryKey(Integer categoryId);int updateByPrimaryKeySelective(HCategory record);int updateByPrimaryKey(HCategory record);HCategoryVo selectByCId(@Param("cid") Integer cid);
}

在biz包里面新建一个HBookBiz接口类

package com.liwen.biz;import com.liwen.vo.HBookVo;/*** @软件包名 com.liwen.biz* @用户 liwen* @create 2023-08-27 下午10:50* @注释说明：*/
public interface HBookBiz {HBookVo selectByBId(Integer bid);
}

package com.liwen.biz;import com.liwen.vo.HCategoryVo;public interface HCategoryBiz {HCategoryVo selectByCId(Integer cid);
}

在Biz里面的impl包里面新

package com.liwen.biz.impl;import com.liwen.biz.HBookBiz;
import com.liwen.mapper.HBookMapper;
import com.liwen.vo.HBookVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-27 下午10:53* @注释说明：*/
@Service
public class HBookBizImpl implements HBookBiz {@Autowiredprivate HBookMapper hBookMapper;@Overridepublic HBookVo selectByBId(Integer bid) {return hBookMapper.selectByBId(bid);}
}

建HBookBizImpl 接口实现HBookBiz接口类

package com.liwen.biz.impl;import com.liwen.biz.HBookBiz;
import com.liwen.mapper.HBookMapper;
import com.liwen.vo.HBookVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-27 下午10:53* @注释说明：*/
@Service
public class HBookBizImpl implements HBookBiz {@Autowiredprivate HBookMapper hBookMapper;@Overridepublic HBookVo selectByBId(Integer bid) {return hBookMapper.selectByBId(bid);}
}

package com.liwen.biz.impl;import com.liwen.biz.HCategoryBiz;
import com.liwen.mapper.HCategoryMapper;
import com.liwen.vo.HCategoryVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-27 下午11:12* @注释说明：*/
@Service
public class HCategoryBizImpl implements HCategoryBiz {@Autowiredprivate HCategoryMapper hCategoryMapper;@Overridepublic HCategoryVo selectByCId(Integer cid) {return hCategoryMapper.selectByCId(cid);}
}

4、测试

package com.liwen.biz.impl;import com.liwen.biz.HBookBiz;
import com.liwen.vo.HBookVo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import static org.junit.Assert.*;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-27 下午10:59* @注释说明：*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-context.xml"})
public class HBookBizImplTest {@Autowiredprivate HBookBiz hBookBiz;@Testpublic void selectByBId() {HBookVo hBookVo = this.hBookBiz.selectByBId(8);System.out.println(hBookVo);}
}

package com.liwen.biz.impl;import com.liwen.biz.HCategoryBiz;
import com.liwen.vo.HCategoryVo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import static org.junit.Assert.*;/*** @软件包名 com.liwen.biz.impl* @用户 liwen* @create 2023-08-27 下午11:14* @注释说明：*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-context.xml"})
public class HCategoryBizImplTest {@Autowiredprivate HCategoryBiz hCategoryBiz;@Testpublic void selectByCId(){HCategoryVo hCategoryVo = hCategoryBiz.selectByCId(8);System.out.println(hCategoryVo);hCategoryVo.gethBooks().forEach(System.out::println);}}