java - 使用 MyBatis。如何在一个表中映射两条不同的记录,然后在加入该表时构造一个查询结果?
问题描述
我的查询结果实体的定义有两个字段origin
和destination
,它们都是Location
类型,我正在尝试使用.Here获取定义和 SQLlocation
表中的信息:JOINS
resultMap
<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>
推荐阅读
- firebase - Firestore 不断写文件
- matlab - 如何从 3D 数组的选定列构造矩阵?
- android - ViewModel 在同一个 Activity 中跨 Fragment 的作用域
- python - 来自 Automate the Boring Stuff 的示例程序未按描述工作
- c++ - 如何用字符串管道?
- javascript - Webview Javascript 在 Android 7.0 及更高版本中不起作用
- javascript - 如何将选定的值呈现到多下拉框中
- r - 变量在模型中没有级别的错误
- spring-boot - 有没有办法在 searchTerm(Apache Camel 邮件组件)中添加 Startswith
- python - 如何连接父类的 __str__() 函数中的文本?