mybatis使用association多表联查只返回一条结果

大致描述
同样一条SQL语句在navicat中查询的结果数量和mybatis查询得到的数量不同。

详细情况
SQL语句

SELECT pd.patrol_id, pd.work_id, u_info.user_name, u_info.user_tel, u_info.user_email, 
  pd.start_node_id AS start_id, start_.node_code AS start_code, start_.latitude AS start_latitude, start_.longitude AS start_longitude, 
  pd.end_node_id AS end_id, end_.node_code AS end_code, end_.latitude AS end_latitude, end_.longitude AS end_longitude 
FROM patrol_division AS pd 
  LEFT JOIN pipeline_node AS start_ ON pd.start_node_id = start_.node_id 
  LEFT JOIN pipeline_node AS end_ ON pd.end_node_id = end_.node_id 
  LEFT JOIN user_info AS u_info ON pd.work_id = u_info.user_id 
WHERE pd.patrol_id = 2;

navicat查询的结果

img

mybatis查询的结果

img

mybatis配置文件

    ="DivisionResultMap" type="PatrolDivision">
        ="patrol_id" jdbcType="INTEGER" property="patrolId" />
        ="workUser" javaType="User" resultMap="WorkResultMap"/>
        ="startNode" javaType="PipelineNode" resultMap="StartNodeResultMap"/>
        ="endNode" javaType="PipelineNode" resultMap="EndNodeResultMap"/>
    

    ="WorkResultMap" type="User">
        ="work_id" jdbcType="INTEGER" property="userId" />
        ="user_name" jdbcType="VARCHAR" property="userName" />
        ="user_email" jdbcType="VARCHAR" property="userEmail" />
        ="user_tel" jdbcType="VARCHAR" property="userTel" />
    

    ="StartNodeResultMap" type="PipelineNode">
        ="start_id" jdbcType="INTEGER" property="nodeId" />
        ="start_code" jdbcType="VARCHAR" property="nodeCode" />
        ="start_longitude" jdbcType="DECIMAL" property="longitude" />
        ="start_latitude" jdbcType="DECIMAL" property="latitude" />
    

    ="EndNodeResultMap" type="PipelineNode">
        ="end_id" jdbcType="INTEGER" property="nodeId" />
        ="end_code" jdbcType="VARCHAR" property="nodeCode" />
        ="end_longitude" jdbcType="DECIMAL" property="longitude" />
        ="end_latitude" jdbcType="DECIMAL" property="latitude" />

collection用这个

  • 这篇博客: Navicat中直接写sql能查出数据，但使用mybatis查不出来(最全解决方案)中的 4、解决办法： 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:

  • 1、检查是否出现我以上犯的错误，去掉多余的双引号
    2、数据库url配置：?useUnicode=true&characterEncoding=utf8

    下面附上，完整的mysql url后缀（解决时区问题）：

    ？useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=UTC