业务Bean SchoolBO.java
@Data
public class SchoolBO {
private Long id;
private String name;
private LocalDate buildDate;
private SchoolLicenseBO sl;
private List<StudentBO> studentList;
private List<TeacherBO> teacherList;
/**
* 1 <-> 1
*/
@Data
public static class SchoolLicenseBO {
private Long id;
private String number;
private String remark;
}
/**
* 1 <-> N
*/
@Data
public static class StudentBO {
private Long id;
private String name;
private Integer grade;
private String sex;
private Long schoolId;
}
/**
* N <-> N
*/
@Data
public static class TeacherBO {
private Long id;
private String name;
private Integer age;
private Long schoolId;
}
}
查询测试:
@Test
public void testQueryUtils() {
String parentSql = "select s.id,\n" +
" s.name,\n" +
" s.build_date as \"buildDate\",\n" +
" sl.id as \"sl.id\",\n" +
" sl.number as \"sl.number\",\n" +
" sl.remark as \"sl.remark\"\n" +
"from t_school s\n" +
" LEFT JOIN t_school_license sl on sl.id = s.school_license_id\n" +
"where s.id IN (:id)";
List<SchoolBO> schoolBOList = sharpService.query(parentSql, Params.builder(1).pv("id", "552173736070144000, 552181593272410112").build(), SchoolBO.class);
if (CollectionUtils.isEmpty(schoolBOList)) {
return;
}
Collection<?> refValues = schoolBOList.stream().map(SchoolBO::getId).collect(Collectors.toSet());
// 1 <-> N
// select id, name, grade, sex from t_school_student where school_id IN (552173736070144000, 552181593272410112);
Map<?, List<SchoolBO.StudentBO>> schoolStudentMap = QueryUtils.subTableValueMap("id, name, grade, sex, school_id", "t_school_student", "school_id", refValues, SchoolBO.StudentBO.class);
// N <-> N
// select t.id, t.name, t.age, school_id from t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id where school_id IN (552173736070144000, 552181593272410112)
Map<?, List<SchoolBO.TeacherBO>> schoolTeacherMap = QueryUtils.subTableValueMap("t.id, t.name, t.age, school_id", "t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id", "school_id", refValues, SchoolBO.TeacherBO.class);
schoolBOList.forEach(t -> {
t.setStudentList(ObjectUtils.defaultIfNull(schoolStudentMap.get(t.getId()), Collections.emptyList()));
t.setTeacherList(ObjectUtils.defaultIfNull(schoolTeacherMap.get(t.getId()), Collections.emptyList()));
});
System.out.println(schoolBOList);
}