mybatis关联查询（同名字段问题）

在mybatis中用了pagehelper的时候如果遇到多表查询，尤其是关联查询（一对一，一对多）采用，

结果集（直接用标签（此种sql体现关联的只能在collection中用标签，不能像关联条件压入式可以用查询，也可用标签） ）

+sql（select o.*,r.*  opm_User o left join opm_user_role ur on o.id = ur.userid）语句体现关联，很容易碰到，要查的关联实体

有相同的列名，会造成（1，一对多多方只会出现一个（主键名相同时））2同时采用了pagehelper时，会出现文明确定义列的问题（ tmp_page.*这里

相当于有同的列），此时可以用别名处理

 

select *

  from (select tmp_page.*, rownum row_id

          from (select o.*,

                       r.id as r_id,

                       r.name as  aa,

                       r.organid as ff,

                       r.valid as fdf,

                       r.memo as f,

                       r.level1 as d,

                       r.subsystem as g

                  from opm_User o left join opm_user_role ur on o.id = ur.userid left join opm_role r on ur.roleid = r.id

                 where 1 = 1 and o.organId = 1

                 order by o.code) tmp_page

         where rownum <= 20)

 where row_id > 0

 

 例如：

 <resultMap id="BaseResultMapVo" type="com.esteel.system.beanVo.OpmUserVo" >

    <!--

      WARNING - @mbggenerated

    -->

    <id column="ID" property="id" jdbcType="VARCHAR" />

    <result column="CODE" property="code" jdbcType="VARCHAR" />

    <result column="PASSWORD" property="password" jdbcType="VARCHAR" />

    <result column="NAME" property="name" jdbcType="VARCHAR" />

    <result column="TELEPHONE" property="telephone" jdbcType="VARCHAR" />

    <result column="EMAIL" property="email" jdbcType="VARCHAR" />

    <result column="ORGANID" property="organid" jdbcType="VARCHAR" />

    <result column="VALID" property="valid" jdbcType="VARCHAR" />

    <result column="MEMO" property="memo" jdbcType="VARCHAR" />

    <result column="LEVEL1" property="level1" jdbcType="DECIMAL" />

    <result column="LAST_LOGIN_DATE" property="lastLoginDate" jdbcType="VARCHAR" />

    <result column="LAST_LOGIN_TIME" property="lastLoginTime" jdbcType="TIMESTAMP" />

    <result column="ONLINE_MARK" property="onlineMark" jdbcType="VARCHAR" />

    <result column="ONLINE_IP" property="onlineIp" jdbcType="VARCHAR" />

    <result column="SESSIONID" property="sessionid" jdbcType="VARCHAR" />

    <collection property="opmRole" ofType="com.esteel.system.bean.OpmRole" >

    <id column="r_id" property="id" jdbcType="VARCHAR" />

    <result column="r_name" property="name" jdbcType="VARCHAR" />

    <result column="r_organid" property="organid" jdbcType="VARCHAR" />

    <result column="r_valid" property="valid" jdbcType="VARCHAR" />

    <result column="r_memo" property="memo" jdbcType="VARCHAR" />

    <result column="r_level1" property="level1" jdbcType="DECIMAL" />

    <result column="r_subsystem" property="subsystem" jdbcType="VARCHAR" />

    </collection>

  </resultMap>

  

  <sql id="getRole">

    r.id as r_id,r.name as r_name,r.organid as r_organid,r.valid as r_valid,r.memo as r_memo,r.level1 as r_level1,r.subsystem as r_subsystem

  </sql>

  

  <select id="getUserByMarkId" parameterType="map" resultMap="BaseResultMapVo">

 select o.*, <include refid="getRole"></include>

       from opm_User o left join opm_user_role ur on o.id = ur.userid left join opm_role r on ur.roleid = r.id

      where 1 = 1

 <if test="organid!=null and organid!=''"> 

      and o.organId=#{organid} 

 </if>

 <if test="valid!=null and valid!=''"> 

       and o.valid=#{valid} 

 </if>

 <if test="level1!=null and level1!=''"> 

      and o.LEVEL1=#{level1}

 </if>

  order by o.code

  </select>

 

  =============这是原本数据库列

   <resultMap id="BaseResultMap" type="com.esteel.system.bean.OpmUser" >

    <!--

      WARNING - @mbggenerated

    -->

    <id column="ID" property="id" jdbcType="VARCHAR" />

    <result column="CODE" property="code" jdbcType="VARCHAR" />

    <result column="PASSWORD" property="password" jdbcType="VARCHAR" />

    <result column="NAME" property="name" jdbcType="VARCHAR" />

    <result column="TELEPHONE" property="telephone" jdbcType="VARCHAR" />

    <result column="EMAIL" property="email" jdbcType="VARCHAR" />

    <result column="ORGANID" property="organid" jdbcType="VARCHAR" />

    <result column="VALID" property="valid" jdbcType="VARCHAR" />

    <result column="MEMO" property="memo" jdbcType="VARCHAR" />

    <result column="LEVEL1" property="level1" jdbcType="DECIMAL" />

    <result column="LAST_LOGIN_DATE" property="lastLoginDate" jdbcType="VARCHAR" />

    <result column="LAST_LOGIN_TIME" property="lastLoginTime" jdbcType="TIMESTAMP" />

    <result column="ONLINE_MARK" property="onlineMark" jdbcType="VARCHAR" />

    <result column="ONLINE_IP" property="onlineIp" jdbcType="VARCHAR" />

    <result column="SESSIONID" property="sessionid" jdbcType="VARCHAR" />

  </resultMap>