Определение моего объекта результата запроса имеет два поля, origin
и destination
, оба типа Location
, и я пытаюсь получить информацию в таблице location
с помощью JOINS
. Вот определение resultMap
и 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>
Предполагается, что origin
и destination
разные записи, однако я обнаружил, что origin
и destination
оказались одинаковыми...
Кто-нибудь может сказать мне, как это исправить или где проблема?
Я использую MyBatis 3.2.2 и MS SQL Server 2008 R2.
Если список результатов содержит столбцы с одинаковыми именами, вам потребуется указать псевдоним хотя бы для одного из столбцов. Например:
<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>