Mybatis进行多表查询

àì夳堔傛蜴生んèń 2023-10-01 17:37 10阅读 0赞

## 多对一 ##

> 多个学生对应一个老师

### 数据库字段 ###

**student**

> id
> 
> name
> 
> tid

**teacher**

> id
> 
> name

### 实体类 ###

#### Student ####

@Data
    public class Student{
        
        private int id;
        private String name;
        private Teacher teacher
    }

#### Teacher ####

@Data
    public class Teacher{
        
        private id;
        private String name;
    }

### 按照查询嵌套处理 ###

<select id="getStudent" resultMap ="StudentTeacher">
    	select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
    	<result property="id" column="id"></result>
        <result property="name" column="name"></result>
        
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>
    
    
    <select id="getTeacher" resultType="Teacher">
    	select * from teacher where id=#{id}
    </select>

### 按照结果嵌套处理 ###

<select id="getStudent2" resultMap="StudentTeacher2">
    	select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
    	<result property="id" column="sid"></result>
        
        <association property="teacher" javaType="Teacher">
        	<result property="name" column="tname"></result>
        </association>
    </resultMap>

## 一对多 ##

> 一个老师对应多个学生

### 实体类 ###

#### Student ####

@Data
    public class Student{
        
        private int id;
        private String name;
        private int tid;
    }

#### Teacher ####

@Data
    public class Teacher{
        
        private id;
        private String name;
        private List<Student> students;
    }

### 按照查询嵌套处理 ###

<select id="getTeacher2" resultMap ="StudentTeacher2">
    	select * from teacher where id = #{tid}
    </select>
    <resultMap id="StudentTeacher2" type="Teacher">
        
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherID"></collection>
    </resultMap>
    
    
    <select id="getStudentByTeacherID" resultType="Student">
    	select * from student where tid=#{tid}
    </select>

### 按照结果嵌套处理 ###

<select id="getStudent" resultMap="StudentTeacher">
    	select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
    	<result property="id" column="tid"></result>
        <result property="name" column="tname"></result>
        
        <collection property="students" ofType="Student">
            <result property="id" column="sid"></result>
        	<result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>

## 小结 ##

1.  关联-association 【多对一】
2.  集合-collection 【一对多】
3.  javaType和ofType
    
    1.  javaType用来指定实体类中的属性的类型
    2.  ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型