数据库外连接查询相同字段名，查询结果错误

最新推荐文章于 2023-02-17 11:00:42 发布

渐失ing

最新推荐文章于 2023-02-17 11:00:42 发布

阅读量364

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/qq_49819155/article/details/120715030

版权

SpringBoot MyBatis 外连接查询字段冲突数据库规范

关键词由CSDN通过智能技术生成

Java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库外连接查询相同字段名，查询结果错误

今天弄一个springboot集成mybatis的小项目时发现mapper.xml中的sql外连接查询
总出现查询结果部分错误，经过排查终于解决

表一

表二

根据表一的 dept_id 查询表二的 departmentName

resultMap:

<resultMap type="Employee" id="getAllEmployeeResultMap">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="email" column="email" />
        <result property="gender" column="gender" />
        <result property="createTime" column="create_time" />

        <association property="department" javaType="department">
            <id property="id" column="id" />
            <result property="departmentName" column="departmentName" />
        </association>
    </resultMap>

sql :

<select id="getAllEmployee" resultMap="getAllEmployeeResultMap">
        SELECT
	    e.id,
	    e.name,
	    e.email,
	    e.gender,
	    e.create_time,
	    d.id,
	    d.departmentName
	FROM
	`employee` e
	LEFT JOIN `department` d ON e.dept_id = d.id
    </select>

查询结果：

Employee{id=101, name='雄安', email='326@qq.com', gender=1, department=Department{id=101, departmentName='部门三'}, createTime=Tue Oct 12 00:15:40 CST 2021}
Employee{id=102, name='张飞', email='131313@qq.com', gender=0, department=Department{id=102, departmentName='部门一'}, createTime=Tue Oct 12 00:15:42 CST 2021}
Employee{id=103, name='网我', email='32121@qq.com', gender=1, department=Department{id=103, departmentName='部门五'}, createTime=Tue Oct 12 00:15:51 CST 2021}

发现问题：部门 id 总是等于员工id
问题所在：部门表和员工表的id字段都叫id，造成冲突
解决办法：将数据库重新规范的修改，并将对应的mapper.xml对应修改即可解决。

Employee{id=102, name='张飞', email='131313@qq.com', gender=0, department=Department{id=1001, departmentName='部门一'}, createTime=Tue Oct 12 00:15:42 CST 2021}
Employee{id=101, name='雄安', email='326@qq.com', gender=1, department=Department{id=1003, departmentName='部门三'}, createTime=Tue Oct 12 00:15:40 CST 2021}
Employee{id=103, name='网我', email='32121@qq.com', gender=1, department=Department{id=1005, departmentName='部门五'}, createTime=Tue Oct 12 00:15:51 CST 2021}