Mybatis association与collection关联查询

Mybatis使用association与collection关联查询

• association:复杂的类型联合,一个复杂的关联,就是在查出结果后,根据情况将其映射到一个类中(其是一个对象),即resultMap中的一个property对应的是一个类
• collection:复杂的类型集合,查出结果后,根据情况将其映射到一个集合中,resultMap中的一个property的对象类型是一个集合对象(通常是List)

下面看我的mapper.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对应的interface-->
<mapper namespace="com.gm.dao.ext.OrderExtMapper" >
    <!--type对应的是该resultMap对应的实体类-->
    <resultMap id="BaseResultMap" type="com.gm.dao.entity.ext.OrderExtEntity" >
        <id column="order_id" property="orderId" jdbcType="VARCHAR" />
        <result column="customer_id" property="customerId" jdbcType="VARCHAR" />
        <result column="customer_name" property="customerName" jdbcType="VARCHAR"/>
        <result column="total_price" property="totalPrice" jdbcType="INTEGER" />
        <result column="order_desc" property="orderDesc" jdbcType="VARCHAR" />
        <result column="order_pay_amount" property="orderPayAmount" jdbcType="INTEGER" />
        <result column="order_pay_type" property="orderPayType" jdbcType="INTEGER" />
        <result column="order_change" property="orderChange" jdbcType="INTEGER" />
        <result column="order_status" property="orderStatus" jdbcType="VARCHAR" />
        <result column="created_person" property="createdPerson" jdbcType="VARCHAR" />
        <result column="created_timestamp" property="createdTimestamp" jdbcType="TIMESTAMP" />
        <result column="updated_person" property="updatedPerson" jdbcType="VARCHAR" />
        <result column="updated_timestamp" property="updatedTimestamp" jdbcType="TIMESTAMP" />
        <collection property="orderDetailList" ofType="com.gm.dao.entity.OrderDetail" column="order_id"
                    select="selectOrderDetails"/>
        <!--column属性代表传递给selectOrderRecords的参数-->
        <collection property="orderRecords" ofType="com.gm.dao.entity.OrderRecord" column="order_id"
                    select="selectOrderRecords"/>
    </resultMap>
    <resultMap id="OrderDetailMap" type="com.gm.dao.entity.ext.OrderDetailExtEntity">
        <id column="order_id" jdbcType="VARCHAR" property="orderId" />
        <id column="goods_id" jdbcType="VARCHAR" property="goodsId" />
        <result column="sales_count" jdbcType="INTEGER" property="salesCount" />
        <result column="sales_total_price" jdbcType="INTEGER" property="salesTotalPrice" />
        <result column="sales_desc" jdbcType="VARCHAR" property="salesDesc" />
        <result column="sales_goods_status" jdbcType="VARCHAR" property="salesGoodsStatus" />
        <!--引用其他文件的select(com.gm.dao.GoodsMapper.selectByPrimaryKey)-->
        <association property="goods" column="goods_id" javaType="com.gm.dao.entity.Goods"
                     select="com.gm.dao.GoodsMapper.selectByPrimaryKey"/>
    </resultMap>
    <sql id="Base_Column_List" >
        order_id, t.customer_id, total_price, order_desc, order_pay_amount, order_pay_type, order_change,
        order_status, t.created_person, t.created_timestamp, t.updated_person, t.updated_timestamp
    </sql>
    <!--该id,selectOrderDetails,与resultMap中的collection对应-->
    <select id="selectOrderDetails" resultMap="OrderDetailMap">
        SELECT
          order_id,
          goods_id,
          sales_count,
          sales_total_price,
          sales_desc,
          sales_goods_status
        FROM order_detail
        WHERE order_id = #{order_id}
    </select>
    <!--com.gm.dao.OrderRecordMapper.BaseResultMap,这种写法可以引用其他文件的BaseResultMap-->
    <select id="selectOrderRecords" resultType="com.gm.dao.entity.OrderRecord" resultMap="com.gm.dao.OrderRecordMapper.BaseResultMap">
        SELECT
          order_id,
          update_time,
          update_person,
          update_desc,
          order_origin_status,
          order_target_status
        FROM order_record
        WHERE order_id = #{order_id}
    </select>
    <select id="getOrderList" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT
          <include refid="Base_Column_List"></include>,customer_name
        FROM `order` AS t
        LEFT JOIN customer AS t2 ON t.customer_id = t2.customer_id
        <where>
            1 = 1
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                AND t.created_timestamp BETWEEN CONCAT('',#{startTime,jdbcType=VARCHAR},' 00:00:00')
                  AND CONCAT('',#{endTime,jdbcType=VARCHAR},' 23:59:59')
            </if>
            <if test="minPrice != 0 and maxPrice != 0">
                AND total_price BETWEEN #{minPrice,jdbcType=INTEGER} AND #{maxPrice,jdbcType=INTEGER}
            </if>
            <if test="customerId != null and customerId !=''">
                AND t.customer_id = #{customerId,jdbcType=VARCHAR}
            </if>
            <if test="orderStatus != null and orderStatus != ''">
                AND order_status = #{orderStatus,jdbcType=VARCHAR}
            </if>
        </where>
        ORDER BY t.updated_timestamp DESC
        LIMIT #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </select>
</mapper>

• BaseResultMap对应的实体类:collection代表一对多的关系(OrderExtEntity与OrderDetailExtEntity)
  
• OrderDetailMap对应的实体类:association代表一对一(goods与OrderExtEntity一对一)的关于
  

此外mybatis的mapper文件中还可以引用其他mapper文件中的resultMap与查询接口(association或者collection中的select)