Mybatis联合查询

一对一联合查询

查询student表和pwd表的一对一

1、写resultMap

<resultMap id="OneToOneResultMap" type="Student">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="salary" jdbcType="INTEGER" property="salary" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <association property="pwd" javaType="Md5pwd">
      <result column="m_id" javaType="Integer" jdbcType="INTEGER" property="id"></result>
      <result column="m_pwd" jdbcType="VARCHAR" javaType="String" property="pwd"></result>
    </association>
  </resultMap>

2、写select

<select id="oneToOneFindStudentById" resultMap="OneToOneResultMap">
    select
    s.id, s.password, s.age, s.salary, s.address, s.name,
    m.id as m_id, m.pwd as m_pwd
    from students s left join md5pwd m on m.id=s.id
    where s.id=#{id}
  </select>

3、调用即可

一对多联合查询

1、写resultMap

<resultMap id="OneToMulResultMap" type="Student">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="salary" jdbcType="INTEGER" property="salary" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <collection property="pwd" ofType="Md5pwd">
      <id property="id" column="p_id"></id>
      <result column="pwd" property="pwd"></result>
    </collection>
  </resultMap>

2、写select

<select id="oneToOneFindStudentById" resultMap="OneToMulResultMap">
    select
        s.id, s.password, s.age, s.salary, s.address, s.name
    from students s left join md5pwd m on m.id=s.id
    where s.id=#{id}
  </select>

3、调用即可

 

多对多联合查询

1、写resultMap

<resultMap id="MulToMulResultMap" type="Student">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="salary" jdbcType="INTEGER" property="salary" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <collection property="roles" ofType="Role">
      <result column="r_id" property="id"></result>
      <result column="r_role" property="role"></result>
    </collection>
  </resultMap>

2、写select

  <select id="mulToMulFindStudent" resultMap="MulToMulResultMap">
    select s.id, s.password, s.age, s.salary, s.address, s.name,
    r.id as r_id, r.role as r_role
    from
    students as s left join s2r on s.id=s2r.sid left join role as r on s2r.rid=r.id;
  </select>

3、调用即可