需求:查询学生对象,包含一对多对象属性三个(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
- 导入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>
- 在入口类配置
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);
}
}
- 写dao
public interface StudentDao extends Mapper<Student> {}
这就写完了 -_- WTF
省略若干类似dao
- 在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多表连接查询的方法,
但是这种方法效率实在低的可怜,这里只是提供一种本人的思路,实战中最好不要为了偷懒使用