关于一个需要多个表连接查询的通用Mapper解决方法

需求：查询学生对象，包含一对多对象属性三个（Group，City，Clazz），多对多集合一个（labels）

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "cm_student")
public class Student {
    private String id;
    private String name;
    private Integer age;
    private String remark;
    private Group group;
    private City city;
    private Clazz clazz;
    private List<Label> labels;
}

dao接口方法：List queryAllStudent();
mapper文件

 <resultMap id="rm" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="remark" column="remark"/>
        
        <association property="city" javaType="City">
            <id property="id" column="cityid"/>
            <result property="name" column="cityname"/>
        </association>
        
        <association property="clazz" javaType="Clazz">
            <id property="id" column="cid"/>
            <result property="name" column="cname"/>
        </association>
        
        <association property="group" javaType="Group">
            <id property="id" column="gid"/>
            <result property="name" column="gname"/>
        </association>
        
        <collection property="labels" ofType="Label">
            <id property="id" column="lid"/>
            <result property="name" column="lname"/>
        </collection>
    </resultMap>

<sql id="s">
        SELECT c9.id,c9.name,c9.age,c9.remark,c9.cityid cityid,c9.cityname cityname,c9.lid lid,c9.lname lname,c9.cid cid,c9.cname cname,c9.gid,c10.name gname
 FROM(SELECT c7.id,gid,c7.name,c7.age,c7.remark,c7.cityid cityid,c7.cityname cityname,c7.lid lid,c7.lname lname,c8.id cid,c8.name cname FROM
(SELECT c5.id,c5.name,c5.age,c5.remark,c5.lid lid,c5.lname lname,c6.id cityid,c6.name cityname,c5.cid,gid FROM 
(SELECT c3.id,c3.name,c3.age,c3.remark,c3.cityid,c3.cid,c4.id lid,c4.name lname,gid from
(SELECT id,name,age,remark,cityid,cid,gid,lid from cm_student c1 left join cm_stu_label c2 on c1.id=c2.sid
)c3 LEFT JOIN cm_label c4 on c3.lid = c4.id WHERE c4.status = 0)c5 LEFT JOIN cm_city c6 ON c5.cityid = c6.id)c7
LEFT JOIN cm_class c8 on c7.cid =c8.id )c9 LEFT JOIN cm_group c10 on c9.gid = c10.id
    </sql>
<select id="queryAllStudent" resultMap="rm">
        <include refid="s"></include>
</select>

本人写这个SQL写的已经神经麻木，生活无法自理，最近刚自学通用mapper
想着–使用通用mapper在service 封装student对象

使用 通用Mapper

1. 导入jar包

通用mapper内置
1.maybatis与springboot集成jar包
2.mybatis的jar包
所以这两个包不需要导入pom，否则报错
<dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>2.1.1</version>
    </dependency>
<dependency>

2. 在入口类配置

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;  //tk开头
@SpringBootApplication
@MapperScan(basePackages = "com.dao")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

3. 写dao

public interface StudentDao extends Mapper<Student> {}

这就写完了 -_- WTF
省略若干类似dao

4. 在service封装

	@Autowired
    private StudentDao studentDao;
    @Autowired
    private Stu_labelDao stu_labelDao;
    @Autowired
    private CityDao cityDao;
    @Autowired
    private ClazzDao clazzDao;
    @Autowired
    private GroupDao groupDao;
    @Autowired
    private LabelDao labelDao;

    @Test
    public void testQuesyAllStudent(){
        List<Student> students = studentDao.selectAll();
        for (Student student:students){
        	
        	//通用Mapper的dao方法  select（Object o），根据对象非空属性做条件查询查询
            //city属性
            List<City> cities = cityDao.select(student.getCity());
            City city = cities.get(0);
            student.setCity(city);
            
            //clazz属性
            List<Clazz> clazzes = clazzDao.select(student.getClazz());
            Clazz clazz = clazzes.get(0);
            student.setClazz(clazz);
            
            //group属性
            List<Group> groups = groupDao.select(student.getGroup());
            Group group = groups.get(0);
            student.setGroup(group);
            
            //label集合
            Stu_label stu_label = new Stu_label();
            stu_label.setSid(student.getId());
            List<Stu_label> stu_labels = stu_labelDao.select(stu_label);
            List<Label> labels = new ArrayList<>();
            for (Stu_label s:stu_labels){
                Label label = labelDao.selectByPrimaryKey(s.getLid());//主键策略，根据主键查询
                labels.add(label);
            }
            student.setLabels(labels);
        }

    }

通用mapper提供哟中让我们可以在java代码里解决这种N多表连接查询的方法，
但是这种方法效率实在低的可怜，这里只是提供一种本人的思路，实战中最好不要为了偷懒使用