在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>