06 - mybatis - 多表级联查询

一对一查询

继承方式

==注意：如果两个类中存在相同的列名，那么无法进行赋值，必须设置不哦那个的

public class User extends Account implements Serializable {

 private Integer id;
 private String username;
 private String gender;
	。。。
}
public class Account implements Serializable {

 public Integer id;
 private Integer UID;
 public String money;
 ....
}

<select id="selectUserAndAccount" resultType="user">
 select * from user u inner join account a on u.id = a.uid
</select>

public List<User> selectUserAndAccount();

属性注入

public class Account implements Serializable {

 private Integer id;
 private Integer UID;
 private String money;

 private User user;
 ....
}
public class User extends Account implements Serializable {

 private Integer id;
 private String username;
 private String gender;
 ...
}

<resultMap id="accountUser" type="account">
 <id column="id" property="id"></id>
 <result column="uid" property="UID"></result>
 <result property="money" column="money"></result>
 <association property="user" javaType="user">
     <id column="id" property="id"></id>
     <result column="user_name" property="username"></result>
     <result column="gender" property="gender"></result>
 </association>
</resultMap>

<select id="selectUserAndAccount2" resultMap="accountUser">
 select * from user u inner join account a on u.id = a.uid
</select>

public List<Account> selectUserAndAccount2();

7 一对多查询

方式一,通过sql连接查询

数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-thp4UlWb-1645683704845)(1 基础.assets/image-20201209212623450.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hBxjVwrg-1645683704847)(1 基础.assets/image-20201209212632279.png)]

一个学生对应多个老师

mapper

public interface StudentMapper {
   public List<Student> findAllOneToMany();
}

mapper.xml

<resultMap id="studentManyMap" type="student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="address" column="address"></result>
<collection property="teachers" ofType="teacher">
   <id column="tid" property="tid"></id>
   <result property="tname" column="tname"></result>
</collection>
</resultMap>

<select id="findAllOneToMany" resultMap="studentManyMap">
select * from student s left join teacher t on s.id=t.sid
</select>

测试

@Test
public void findOneToMany() throws IOException {
InputStream stream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(stream);
SqlSession session = sessionFactory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);

List<Student> studentList = mapper.findAllOneToMany();

System.out.println(studentList);
}

方式二：通过mapper接口的配置单表sql循环查询

mapper接口

--studentMapper
public List<Student> findSelectMany();

--teacherMapper
public List<Teacher> teachers(Integer id);    

mapper.xml

--studentMapper.xml
<resultMap id="selectManyManp" type="student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="address" column="address"></result>
<collection property="teachers" ofType="teacher"  column="id" select="cn.laixueit.mapper.TeacherMapper.teachers">
</collection>
</resultMap>
## 注意：column:对应的当前类的的主键，是传递过去的参
<select id="findSelectMany" resultMap="selectManyManp">
select * from student
</select>

--teacherMapper.xml
<select id="teachers" parameterType="int" resultType="teacher">
select * from teacher where sid = #{id}
</select>

测试

@Test
public void findOneToMany2() throws IOException {
InputStream stream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(stream);
SqlSession session = sessionFactory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
TeacherMapper mapper2 = session.getMapper(TeacherMapper.class);

List<Student> studentList = mapper.findSelectMany();
//        System.out.println(mapper2.teachers(1));
System.out.println(studentList);
//        for (Student student : studentList) {
//            System.out.println(student);
//            System.out.println("\t"+student.getTeachers());
//        }
}

一个老师对应多个学生

查询一个老师，老师返回map，在map中通过result进行查询即可

<resultMap id="findmay" type="cn.laixueit.pojo.Teacher">
       <id property="id" column="tid"></id>
       <result property="tname" column="tname"></result>
       <collection property="students" ofType="cn.laixueit.pojo.Student" column="tid" select="cn.laixueit.mapper.StudentMapper.list">
       </collection>
   </resultMap>
   <select id="findt" resultMap="findmay" parameterType="int">
       select  t.id tid,t.name tname from teacher t where id = #{id}
   </select>