Mybatis坑之collection单属性list

最新推荐文章于 2024-08-26 14:31:55 发布

一个有梦想的混子

最新推荐文章于 2024-08-26 14:31:55 发布

阅读量2.6k

点赞数 2

分类专栏： java开发文章标签： mybatis

本文链接：https://blog.csdn.net/qq_33454884/article/details/105855598

版权

java开发专栏收录该内容

32 篇文章 0 订阅

订阅专栏

今天写了一个一对多的查询用到了collection，首先是这样的

  <resultMap id="userParamMap" type="com.veoer.mind.entity.UserParam">
    <id column="u_id" jdbcType="VARCHAR" property="id" />
    <result column="company_id" jdbcType="VARCHAR" property="companyId" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="sex" jdbcType="INTEGER" property="sex" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="id_card" jdbcType="VARCHAR" property="idCard" />
    <result column="work_code" jdbcType="VARCHAR" property="workCode" />
    <result column="position" jdbcType="VARCHAR" property="position" />
    <result column="if_delete" jdbcType="INTEGER" property="ifDelete" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="supplier_user_id" jdbcType="VARCHAR" property="supplierUserId" />
    <result column="supplier_id" jdbcType="VARCHAR" property="supplierId" />
    <collection property="supplierOrganizationIdList" column="orgrm_" javaType="java.util.ArrayList" ofType="java.lang.String"></collection>
  </resultMap>

这样的

 <select id="findByPageForSupplier" resultMap="userParamMap">
    select
    u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
    ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id,orgr.supplier_organization_id as orgrm_supplier_organization_id
    from user u
    left join user_relation ur on u.id = ur.user_id
    left join user_organization_relation uor on u.id = uor.user_id
    left join organization_relation orgr on uor.organization_id = orgr.organization_id
      where  orgr.supplier_id = #{supplierId}
    and u.company_id = #{companyId} and orgr.company_id=#{companyId}  and uor.company_id = #{companyId}
      <if test="name != '' and name != null">
        and u.name like '%#{name}%'
      </if>
      <if test="time != null">
        and u.update_time > #{time}
      </if>
    </select>

查询结果是这样的

仔细看会发现，我的id和supplierOrganzationIdList里面的值一样，WTFK，我各种修改我的collection，各种查资料，感觉我的collection写的没毛病啊，但是结果始终是这样，最后机缘巧合将我的sql改了一下，把我的list查询条件调到了第一位，也就是这样

    <select id="findByPageForSupplier" resultMap="userParamMap">
    select
    orgr.supplier_organization_id as orgrm_supplier_organization_id,
    u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
    ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id
    from user u
    left join user_relation ur on u.id = ur.user_id
    left join user_organization_relation uor on u.id = uor.user_id
    left join organization_relation orgr on uor.organization_id = orgr.organization_id
      where  orgr.supplier_id = #{supplierId}
    and u.company_id = #{companyId} and orgr.company_id=#{companyId}  and uor.company_id = #{companyId}
      <if test="name != '' and name != null">
        and u.name like '%#{name}%'
      </if>
      <if test="time != null">
        and u.update_time > #{time}
      </if>
    </select>

惊喜的发现查询的结果对了，坑爹啊！