[Solved] Mybatis multi-table query error: Column ‘id’ in field list is ambiguous

Mybatis error example:


<resultMap id="JoinResultMap" type="com.WorkDto">
	<id column="id" jdbcType="BIGINT" property="id"/>
	<result column="work_city_code" jdbcType="VARCHAR" property="workCityCode"/>

	
	<collection property="guardInfos" ofType="com.GuardInfo">
		<id column="id"  jdbcType="BIGINT" property="id"  />
		<result column="work_id" jdbcType="BIGINT" property="workId" />
		<result column="guarder_code" jdbcType="VARCHAR" property="guarderCode" />
	</collection>
</resultMap>


<select id="selectById" parameterType="java.lang.Long" resultMap="JoinResultMap">
	select t1.id, work_city_code, 
	t2.id , t2.work_id, t2.guarder_code 
	from tt_work t1
	left join tt_work_info t2 
	on t1.id=t2.work_id
	where id = #{id,jdbcType=BIGINT}
</select>

The above will report an error: Column ‘id’ in field list is ambiguous

wrong reason:

When Mybatis multi-table query, multiple tables have fields with the same name, such as id, the names are repeated, and the corresponding table name is not specified.
There are two places to pay attention to:
(1) Change the column of Mybatis in one of the repeated fields to another name.
(2) Field plus the corresponding table name.

amend as below:

The following will
(1) modify the column corresponding to one of the ids to other unique names guard_info_id
(2) add the table name t1.id to the id in the query results and query conditions

<resultMap id="JoinResultMap" type="com.WorkDto">
	<id column="id" jdbcType="BIGINT" property="id"/>
	<result column="work_city_code" jdbcType="VARCHAR" property="workCityCode"/>

	<collection property="guardInfos" ofType="com.GuardInfo">
		<id column="guarder_info_id"  jdbcType="BIGINT" property="id"  />
		<result column="work_id" jdbcType="BIGINT" property="workId" />
		<result column="guarder_code" jdbcType="VARCHAR" property="guarderCode" />
	</collection>
</resultMap>


<select id="selectById" parameterType="java.lang.Long" resultMap="JoinResultMap">
	select t1.id,  work_city_code, 
	t2.id as guarder_info_id, t2.work_id, t2.guarder_code 
	from tt_work t1
	left join tt_work_info t2 
	on t1.id=t2.work_id
	where t1.id = #{id,jdbcType=BIGINT}
</select>

Similar Posts:

Leave a Reply Cancel reply