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>