在实际项目中，多对多关系也是非常常见的关系，比如，一个购物系统中，一个用户可以有多个订单，这时一对多的关系；一个订单中可购买多种商品，一种商品也可以属于多个不同的订单，订单和商品之间就是多对多的关系。对于数据库中多对多的关系建议使用一个中间表来维护关系，中间表中的订单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()));
}
一对多：
多对多：
