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:
JavaScript
x
<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:
JavaScript
<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.
Advertisement
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:
JavaScript
<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:
JavaScript
<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>