With MyBatis. How can I map two different records in one table, then construct a single query result when joining that table?

The definition of my query result entity has two fields, origin and destination, which are both Location type, and I’m trying to fetch the information in location table with JOINS.Here are the resultMap definition and SQL:

    <resultMap id="queryConditionMap" type="com.offersupport.model.OfferQueryCondition">
        <id column="query_id" property="queryId"/>
        <result column="departure_date" property="departureDate"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <association property="origin" column="origin_id" javaType="com.offersupport.model.MaerskLocation">
            <id column="location_id" property="locationId"/>
            <result column="city_rkst_code" property="cityRkstCode"/>
            <result column="unloc_code" property="unlocCode"/>
            <result column="city_name" property="cityName"/>
            <result column="country_name" property="countryName"/>
            <result column="region_name" property="regionName"/>
        </association>
        <association property="destination" column="destination_id"
                     javaType="com.offersupport.model.MaerskLocation">
            <id column="location_id" property="locationId"/>
            <result column="city_rkst_code" property="cityRkstCode"/>
            <result column="unloc_code" property="unlocCode"/>
            <result column="city_name" property="cityName"/>
            <result column="country_name" property="countryName"/>
            <result column="region_name" property="regionName"/>
        </association>
    </resultMap>

SQL:

    <select id="getOfferQueryConditionByModel" resultMap="queryConditionMap">
        SELECT
        qc.query_id,
        qc.departure_date,
        qc.create_time,
        l1.location_id,
        l1.city_rkst_code,
        l1.unloc_code,
        l1.city_name,
        l1.country_name,
        l1.region_name,
        l2.location_id,
        l2.city_rkst_code,
        l2.unloc_code,
        l2.city_name,
        l2.country_name,
        l2.region_name
        FROM query_condition mqc
        INNER JOIN location ml1 ON qc.origin_id = l1.location_id
        INNER JOIN location ml2 ON qc.destination_id = l2.location_id
        <where>
            <if test="condition.origin.locationId!=null">
                AND origin_id = #{condition.origin.locationId}
            </if>
            <if test="condition.destination.locationId!=null">
                AND destination_id = #{condition.destination.locationId}
            </if>
            <if test="condition.departureDate!=null">
                AND departure_date = #{condition.departureDate}
            </if>
        </where>
    </select>

It is supposed that the origin and destination are different records, however I found that origin and destination turned out to be same…

Can anybody please tell me how to fix it or where is the problem?

I’m using MyBatis 3.2.2 and MS SQL Server 2008 R2.

Answer

When the result list contains columns with the same name, you will need to provide an alias for at least one of the columns. For example:

<resultMap id="queryConditionMap" type="com.offersupport.model.OfferQueryCondition">
  <id column="query_id" property="queryId"/>
  <result column="departure_date" property="departureDate"/>
  <result column="create_time" property="createTime"/>
  <result column="update_time" property="updateTime"/>
  <association property="origin" column="origin_id" javaType="com.offersupport.model.MaerskLocation">
    <id column="l1_location_id" property="locationId"/>
    <result column="l1_city_rkst_code" property="cityRkstCode"/>
    <result column="l1_unloc_code" property="unlocCode"/>
    <result column="l1_city_name" property="cityName"/>
    <result column="l1_country_name" property="countryName"/>
    <result column="l1_region_name" property="regionName"/>
  </association>
  <association property="destination" column="destination_id" javaType="com.offersupport.model.MaerskLocation">
    <id column="location_id" property="locationId"/>
    <result column="city_rkst_code" property="cityRkstCode"/>
    <result column="unloc_code" property="unlocCode"/>
    <result column="city_name" property="cityName"/>
    <result column="country_name" property="countryName"/>
    <result column="region_name" property="regionName"/>
  </association>
</resultMap>

SQL:

<select id="getOfferQueryConditionByModel" resultMap="queryConditionMap">
  SELECT
    qc.query_id,
    qc.departure_date,
    qc.create_time,
    l1.location_id as l1_location_id,
    l1.city_rkst_code as l1_city_rkst_code,
    l1.unloc_code as l1_unloc_code,
    l1.city_name as l1_city_name,
    l1.country_name as l1_country_name,
    l1.region_name as l1_region_name,
    l2.location_id,
    l2.city_rkst_code,
    l2.unloc_code,
    l2.city_name,
    l2.country_name,
    l2.region_name
  FROM query_condition mqc
  INNER JOIN location ml1 ON qc.origin_id = l1.location_id
  INNER JOIN location ml2 ON qc.destination_id = l2.location_id
  <where>
    <if test="condition.origin.locationId!=null">
      AND origin_id = #{condition.origin.locationId}
    </if>
    <if test="condition.destination.locationId!=null">
      AND destination_id = #{condition.destination.locationId}
    </if>
    <if test="condition.departureDate!=null">
      AND departure_date = #{condition.departureDate}
    </if>
  </where>
</select>

Advertisement

Answer