springboot整合JPA 多表关联 ：一对多 多对多

补充一下自定义SQL

?这是连表查询，可以任意查出字符，用Map接收

  @Test
    void test3() {
        JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(em);
        QStudent student = QStudent.student;
        QMessage message = QMessage.message;
        //constructor(StuMesDto.class, student.id, student.name, student.grade, message.id, message.info)
        List<Map<Expression<?>, ?>> fetch = jpaQueryFactory.select(Projections.map(student.id, student.name, student.grade, message.id, message.info))
                .from(student)
                .innerJoin(message)
                .on(student.id.eq(message.stu_id))
                .orderBy(message.id.desc())
                .limit(1)
                .offset((1 - 1) * 1)
                .fetch();
        Object o = fetch.get(0).get(message.info);
        System.out.println(o);
    }

?这是连表查询，可以任意查出字符，用自定义对象接收

 @Test
    void test3() {
        JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(em);
        QStudent student = QStudent.student;
        QMessage message = QMessage.message;
        //
        List<StuMesDto> fetch = jpaQueryFactory.select(Projections.constructor(StuMesDto.class, student.id, student.name, student.grade, message.id, message.info))
                .from(student)
                .innerJoin(message)
                .on(student.id.eq(message.stu_id))
                .orderBy(message.id.desc())
                .limit(1)
                .offset((1 - 1) * 1)
                .fetch();
        StuMesDto o = fetch.get(0);
        System.out.println(o);
    }

package com.jmj.springDataApp.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StuMesDto {

    private Long sid;


    private String name;


    private Integer grade;

    private Long id;

    private String info;
}

QTeacher teacher = QTeacher.teacher;
QStudent student = QStudent.student;

List<TeacherWithStudentsDto> result = jpaQueryFactory
        .select(Projections.constructor(
                TeacherWithStudentsDto.class,
                teacher.id,
                teacher.name,
                Projections.list(student)))
        .from(teacher)
        .leftJoin(student).on(teacher.id.eq(student.teacher.id))
        .groupBy(teacher.id)
        .orderBy(teacher.id.asc())
        .limit(100) // 限制查询结果为前100个老师
        .fetch()
        .stream()
        .peek(dto -> dto.setStudents(dto.getStudents().subList(0, Math.min(dto.getStudents().size(), 10))))
        .collect(Collectors.toList());