Java >> Java tutorial >  >> Tag >> mybatis

Med MyBatis. Hvordan kan jeg kortlægge to forskellige poster i én tabel og derefter konstruere et enkelt forespørgselsresultat, når jeg forbinder den tabel?

Definitionen af ​​min forespørgselsresultatentitet har to felter, origin og destination , som begge er Location type, og jeg forsøger at hente oplysningerne i location tabel med JOINS .Her er resultMap definition og 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>

Det antages, at origin og destination er forskellige poster, men jeg fandt ud af, at origin og destination viste sig at være det samme...

Kan nogen venligst fortælle mig, hvordan jeg løser det, eller hvor er problemet?

Jeg bruger MyBatis 3.2.2 og MS SQL Server 2008 R2.

Svar

Når resultatlisten indeholder kolonner med samme navn, skal du angive et alias for mindst én af kolonnerne. For eksempel:

<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>

Java tag