springboot mybatis 多表操作

---

mybatis 多表操作

 

 

*******************************

示例

 

school：id、name

student：id、name、age、school_id

 

********************

pojo 层

 

StudentInfo：查询学生所在学校名称

@Data
public class StudentInfo {

    private Integer id;
    private String name;
    private Integer age;
    private String schoolName;

}

 

StudentCount：统计学校学生数量

public class SchoolCount {

    private String schoolName;
    private Integer count;
}

 

********************

dao 层

 

StudentMapper

public interface StudentMapper extends Mapper<Student> {

    List<StudentInfo> getAllStudentInfo();
}

 

SchoolMapper

public interface SchoolMapper extends Mapper<School> {

    List<SchoolCount> getSchoolCount();
}

 

 

********************

mapper.xml 文件

 

StudentMapper.xml

 <select id="getAllStudentInfo" resultType="studentInfo">
    select a.id,a.name,a.age,b.name as schoolName from student a,school b where a.school_id=b.id;
  </select>

 

SchoolMapper.xml

  <select id="getSchoolCount" resultType="schoolCount">
    select b.name as schoolName,count(*) as count from student a,school b where a.school_id=b.id group by b.name;
  </select>

 

********************

controller 层

 

SchoolController

@RestController
public class SchoolController {

    @Autowired
    private SchoolMapper schoolMapper;

    @RequestMapping("/get")
    public List<SchoolCount> get(){
        return schoolMapper.getSchoolCount();
    }
}

 

StudentController

@RestController
public class StudentController {

    @Autowired
    private StudentMapper studentMapper;

    @RequestMapping("/get2")
    public List<StudentInfo> get2(){
        return studentMapper.getAllStudentInfo();
    }
}

 

 

*******************************

使用测试

 

localhost:8080/get

                        

 

localhost:8080/get2