MyBatis多对多映射配置示例

1.订单（Order）和商品（Aticle）是多对多关系，即一个订单可以包括多个商品，一个商品也可以属于多个订单；用户（User）与订单（Order）是一对多的关系，即一个订单只能属于一个用户。以下代码演示了在持久化映射文件中对查询这类关系的sql语句配置。

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">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.OrderMapper">

	<resultMap type="org.fkit.domain.Order" id="orderResultMap">
		<id property="id" column="oid"/>
	  	<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="articles" javaType="ArrayList"
	  column="oid" ofType="org.fkit.domain.Article"
	  select="org.fkit.mapper.ArticleMapper.selectArticleByOrderId"
	  fetchType="lazy">
	  	<id property="id" column="id"/>
	  	<result property="name" column="name"/>
	  	<result property="price" column="price"/>
	  	<result property="remark" column="remark"/>
	  </collection>
	</resultMap>
	
	<!-- 注意，如果查询出来的列同名，例如tb_user表的id和tb_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>

AticleMapper.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">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.ArticleMapper">
  
  <select id="selectArticleByOrderId" parameterType="int" 
  resultType="org.fkit.domain.Article">
  	SELECT * FROM tb_article WHERE id IN ( 
		SELECT article_id FROM tb_item WHERE order_id = #{id} 
	) 
  </select>
  

</mapper>

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">
<!-- namespace指用户自定义的命名空间。 -->
<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>