mybatis进阶：一对一查询

一对一查询

一对一查询。自己的主键对应别的表外键。连表查询

1、联合查询

<!--一对一 联合查询-->
  <select id="getIntegralInfoById" resultMap="ClassesResultMap">
    select ua.id uid,ua.*,pi.* from pms_integration pi,ums_admin ua
    where pi.member_id = ua.id
    and pi.id = #{id}
  </select>

  <resultMap type="com.ljs.integralmall.model.PmsIntegration" id="ClassesResultMap">
    <!-- 实体类的字段名和数据表的字段名映射 -->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="member_id" jdbcType="BIGINT" property="memberId" />
    <result column="integration" jdbcType="BIGINT" property="integration" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="review_status" jdbcType="VARCHAR" property="reviewStatus" />
    <association property="umsAdmin"
                 javaType="com.ljs.integralmall.model.UmsAdmin">
      <result column="uid" jdbcType="BIGINT" property="id"/>
      <result column="username" jdbcType="VARCHAR" property="username" />
      <result column="password" jdbcType="VARCHAR" property="password" />
      <result column="icon" jdbcType="VARCHAR" property="icon" />
      <result column="email" jdbcType="VARCHAR" property="email" />
      <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
      <result column="note" jdbcType="VARCHAR" property="note" />
      <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
      <result column="login_time" jdbcType="TIMESTAMP" property="loginTime" />
      <result column="status" jdbcType="INTEGER" property="status" />
    </association>
  </resultMap>

####因为两表的id数据库字段一致。不好区分。则这里umsAdmin表id指定为uid
查询sql语句则以别名方式查询即可

2、嵌套查询

将别的表数据查询出来。放回现表进行连表查询

<!--一对一 嵌套查询-->
  <select id="getIntegralInfoById2" resultMap="getIntegrationMap">
    select * from pms_integration pi
    left join ums_admin ua on pi.member_id = ua.id
    where pi.id = #{id}
  </select>

  <resultMap id="getIntegrationMap" type="com.ljs.integralmall.model.PmsIntegration">
    <association property="umsAdmin" javaType="com.ljs.integralmall.model.UmsAdmin"
    select="com.ljs.integralmall.mapper.UmsAdminMapper.selectByPrimaryKey"
                 column="{id=member_id}"/>
  </resultMap>

UserMapper.xml

  <select id="selectByPrimaryKey"  resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from ums_admin
    where id = #{id,jdbcType=BIGINT}
  </select>