Spring 数据 JPA：按连接表中的属性排序后的重复记录

Question

我在我的项目中遇到了一个麻烦，因此我将提供一个使用演示项目的例子。想象有两个实体:老师和学生。

@Entity
@Table(name = "STUDENTS")
public class Student {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private String surname;

    @ManyToOne
    private Teacher teacher;

    // getters and setters
}

@Entity
@Table(name = "TEACHERS")
public class Teacher  {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private String surname;

    @OneToMany(mappedBy = "teacher")
    List<Student> students = new ArrayList<>();

    // getters and setters

}

有一个存储库和一个服务类:

public interface TeacherRepository extends PagingAndSortingRepository<Teacher, Long>, JpaSpecificationExecutor<Teacher> {
}

@Service
public class TeacherService {

    @Autowired
    private TeacherRepository teacherRepository;

    @Autowired
    private TeacherToDTOMapper teacherToDTOMapper;

    public Page<TeacherDTO> findAll(Pageable pageable) {
        Specification<Teacher> specification = (root, criteriaQuery, criteriaBuilder) -> {

            return criteriaBuilder.lessThanOrEqualTo(root.get("id"), 2);
        };
        return teacherRepository.findAll(specification, pageable).map(teacherToDTOMapper::mapEntityToDTO);
    }
}

最后，一个控制器类：

@RestController
public class TeacherController {

    @Autowired
    private TeacherService teacherService;

    @GetMapping("/teachers")
    @ResponseStatus(HttpStatus.OK)
    public PagedModel<EntityModel<TeacherDTO>> findAll(PagedResourcesAssembler<TeacherDTO> resourcesAssembler, Pageable pageable) {
        Page<TeacherDTO> page = teacherService.findAll(pageable);
        return resourcesAssembler.toModel(page);
    }
}

在UI上有一个教师表。

用户筛选出具有 ID 的教师

然而，实际结果是：

{
  "_embedded": {
    "teacherDTOList": [
      {
        "id": 1,
        "name": "Aliyah",
        "surname": "Wiggins",
        "students": [
          {
            "id": 1,
            "name": "Amina",
            "surname": "Parks"
          },
          {
            "id": 2,
            "name": "Caleb",
            "surname": "Baker"
          },
          {
            "id": 3,
            "name": "Valentino",
            "surname": "Kent"
          },
          {
            "id": 4,
            "name": "Jaron",
            "surname": "Luna"
          },
          {
            "id": 5,
            "name": "Cayden",
            "surname": "Hoover"
          }
        ]
      },
      {
        "id": 1,
        "name": "Aliyah",
        "surname": "Wiggins",
        "students": [
          {
            "id": 1,
            "name": "Amina",
            "surname": "Parks"
          },
          {
            "id": 2,
            "name": "Caleb",
            "surname": "Baker"
          },
          {
            "id": 3,
            "name": "Valentino",
            "surname": "Kent"
          },
          {
            "id": 4,
            "name": "Jaron",
            "surname": "Luna"
          },
          {
            "id": 5,
            "name": "Cayden",
            "surname": "Hoover"
          }
        ]
      },
      {
        "id": 2,
        "name": "Macy",
        "surname": "Reyes",
        "students": [
          {
            "id": 6,
            "name": "Jasmin",
            "surname": "Friedman"
          },
          {
            "id": 7,
            "name": "Randall",
            "surname": "Archer"
          },
          {
            "id": 8,
            "name": "Caroline",
            "surname": "Davila"
          },
          {
            "id": 9,
            "name": "Sarah",
            "surname": "Pace"
          }
        ]
      }
    ]
  },
  "_links": {
    "self": {
      "href": "http://localhost:8080/teachers?page=0&size=3&sort=students.name,asc"
    }
  },
  "page": {
    "size": 3,
    "totalElements": 3,
    "totalPages": 1,
    "number": 0
  }
}

Aliyah Wiggins有重复的记录。我还没有找到适当的方法来获得预期的结果，所以现在我认为搜索与一个实体相关的对象并按另一个实体中的属性排序是不对的。演示项目可在此处获得。

Answer 1

您可以将查询设置为返回规范中的不同值：

criteriaQuery.distinct(true);

这通常由Spring Data在findAll方法上设置为true，但在您使用自己的规范时并未暗示。您现在可以看到数据库连接查询的结果（教师将为每个学生重复一次）。

我建议向模型添加一个标志属性，而不是按 id 跳过。引入隐藏/删除/私有之类的内容将简化代码并使其更具可读性。然后，您可以使用一个空的存储库接口方法：findByActiveTrue（）;

Answer 2

我认为以学生（即多人）的名字来排序教师（即一对多关系中的“一个”）是错误的。在尝试按学生的名字排序后，我们会得到以下结果：

ROW    TEACHER_NAME     TEACHER_SURNAME     STUDENT_FULL_NAME
1      Evan             Fuentes             Allyson Noble
2      Aliyah           Wiggins             Amina Parks
3      Evan             Fuentes             Ashlyn Berg
4      Aliyah           Wiggins             Caleb Baker
...

老师可以同时是第一个、第三个和最后一个。因此，这种排序方式毫无意义。此外，多对多在示例中更合适，但仍然没有改变事实。

Answer 3

您可以使用命名实体图来避免重复记录。

@NamedEntityGraph(name = "Teacher.students" ,attributeNodes = @NamedAttributeNode("students"))
@Entity
@Table(name = "TEACHERS")
public class Teacher  {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private String surname;

    @OneToMany(mappedBy = "teacher")
    List<Student> students = new ArrayList<>();

    // getters and setters

}

并将findAll方法重写为

@EntityGraph(value = "Teacher.students" , type = EntityGraphType.LOAD)
Page<Teacher> findAll(Specification<Teacher> spec, Pageable pageable);