MyBatis关联映射:多对多

在实际项目中,多对多关系也是非常常见的关系,比如,一个购物系统中,一个用户可以有多个订单,这时一对多的关系;一个订单中可购买多种商品,一种商品也可以属于多个不同的订单,订单和商品之间就是多对多的关系。对于数据库中多对多的关系建议使用一个中间表来维护关系,中间表中的订单id作为外键参照订单表的id,商品id作为外键参照商品表id,下面我们就用一个示例来看看MyBatis怎么处理多对多关系。
示例:ManyToManyTest
1.建表
user
goods
order
item
2.mapper
goodsMapperr.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.fkit.mapper.GoodsMapper">
<select id="selectGoodsByOrderId" parameterType="int" resultType="org.fkit.domain.Goods">
SELECT * FROM tb_goods WHERE ID IN(
SELECT GOODS_ID FROM tb_item WHERE ORDER_ID=#{id}
)
</select>
</mapper>
goodsmapper.java
public interface GoodsMapper
{
Goods selectGoodsByOrderId(int id);
}
orderMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.fkit.mapper.OrderMapper">
    <resultMap type="org.fkit.domain.Order" id="orderResultMap">
    <id property="id" column="ID" />
    <result property="code" column="CODE" />
    <result property="total" column="TOTAL" />
    <!-- 多对一关联映射:association -->
    <association property="user" javaType="org.fkit.domain.User">
    <id property="id" column="ID" />
    <result property="username" column="USERNAME" />
    <result property="loginname" column="LOGINNAME" />
    <result property="password" column="PASSWORD" />
    <result property="phone" column="PHONE" />
    <result property="address" column="ADDRESS" />
    </association>
    <!-- 多对多关系映射:collection -->
    <collection property="goods" javaType="ArrayList" column="oid" ofType="org.fkit.domain.Goods"
    select="org.fkit.mapper.GoodsMapper.selectGoodsByOrderId" fetchType="lazy">
    <id property="id" column="ID" />
    <result property="name" column="NAME" />
    <result property="price" column="PRICE" />
    <result property="remark" column="REMARK" />
    </collection>
    </resultMap>
    <!-- 注意 如果查询出来的列同名,例如user表的id和order表的id都是id 则需要使用别名区分 -->
    <select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
    SELECT u.*,o.ID AS oid,CODE,TOTAL,USER_ID
    FROM tb_user u,tb_order o
    WHERE u.ID=o.USER_ID
    AND o.ID=#{id}
    </select>
    <!-- 根据userId查询订单 -->
    <select id="selectOrderByUserId" parameterType="int" resultType="org.fkit.domain.Order">
    SELECT * FROM tb_order WHERE USER_ID=#{id}
    </select>
</mapper>
orderMapper.java
public interface OrderMapper
{
Order selectOrderById(int id);
}
userMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.fkit.mapper.UserMapper">
    <resultMap type="org.fkit.domain.User" id="userResultMap">
    <id property="id" column="ID" />
    <result property="username" column="USERNAME" />
    <result property="loginname" column="LOGINNAME" />
    <result property="password" column="PASSWORD" />
    <result property="phone" column="PHONE" />
    <result property="address" column="ADDRESS" />
    <!-- 一对多关联映射:collection -->
    <collection property="orders" javaType="ArrayList" column="id"
    ofType="org.fkit.domain.User" select="org.fkit.mapper.OrderMapper.selectOrderByUserId"
    fetchType="lazy">
    <id property="id" column="ID" />
    <result property="code" column="CODE" />
    <result property="total" column="TOTAL" />
    </collection>
    </resultMap>
    <select id="selectUserById" parameterType="int" resultMap="userResultMap">
    SELECT * FROM tb_user WHERE id=#{id}
    </select>
</mapper>
userMapper.java
public interface UserMapper
{
User selectUserById(int id);
}
3.控制器
//测试一对多关系
@GetMapping("/testSelectUserById")
public void testSelectUserById()
{
SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
//获得UserMapper接口代理对象
UserMapper um = sqlSession.getMapper(UserMapper.class);
//调用selectByUserId方法
User user = um.selectUserById(5);
//查看查询到的user对象信息
System.out.println(user.getUsername());
//查看user对象关联的订单信息
List<Order> orders = user.getOrders();
orders.forEach(order -> System.out.println(order));
}

 

//测试多对多关系
@GetMapping("/testSeletOrderById")
public void testSelectOrderById()
{
SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
//获得OrderMapper接口的代理对象
OrderMapper om = sqlSession.getMapper(OrderMapper.class);
//调用selectOrderById
Order order = om.selectOrderById(2);
//查看查询到的order信息
System.out.println(order.getId());
//查看order关联的用户信息
User user = order.getUser();
System.out.println(user.getUsername());
//查看order关联的商品信息
List<Goods> goods = order.getGoods();
goods.forEach(good -> System.out.println(good.getName()));
}
一对多:
多对多: