终南山--SpringBoot系列之Spring Data Jpa连表查询和分页

本章小宋带同学们了解一下JPA的连表查询和分页操作

JPA连表查询和分页

这里我们继续讲JPA如何实现连表和分页，因为JPA可以在repository层自定义sql所以也不难。

1.实体类

创建三个实体类。

@Entity
@Data
@NoArgsConstructor
publicclass Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String companyName;
    private String description;

    public Company(String name, String description) {
        this.companyName = name;
        this.description = description;
    }
}

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
publicclass School {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String name;
    private String description;
}

@Entity
@Data
@NoArgsConstructor
publicclass Person {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String name;
    private Integer age;
    private Long schoolId;
    private Long companyId;

    public Person(String name, Integer age) {
        this.name = name;
        this.age = age;
    }

}

2. 自定义Sql实现连表查询

假如我们当前要通过 person 表的 id 来查询 Person 的话，我们知道 Person 的信息一共分布在Company、School、Person这三张表中，所以，我们如果要把 Person 的信息都查询出来的话是需要进行连表查询的。

首先我们需要创建一个包含我们需要的 Person 信息的 DTO 对象,我们简单第将其命名为 UserDTO，用于保存和传输我们想要的信息。

@Data
@NoArgsConstructor
@Builder(toBuilder = true)
@AllArgsConstructor
publicclass UserDTO {
    private String name;
    privateint age;
    private String companyName;
    private String schoolName;
}

写一个方法查询Person的基本信息

	/**
     * 连表查询
     */
    @Query(value = "select new com.saijia.modules.live.entity.UserDTO(p.name,p.age,c.companyName,s.name) " +
            "from Person p left join Company c on  p.companyId=c.id " +
            "left join School s on p.schoolId=s.id " +
            "where p.id=:personId")
    Optional<UserDTO> getUserInformation(@Param("personId") Long personId);

2. 自定义 SQL 语句连表查询并实现分页操作

查询当前所有的人员信息并实现分页，可以按照下面这种方式.为了实现分页，我们在@Query注解中还添加了 countQuery 属性

@Query(value = "select new com.saijia.modules.live.entity.UserDTO(p.name,p.age,c.companyName,s.name) " +
        "from Person p left join Company c on  p.companyId=c.id " +
        "left join School s on p.schoolId=s.id ",
        countQuery = "select count(p.id) " +
                "from Person p left join Company c on  p.companyId=c.id " +
                "left join School s on p.schoolId=s.id ")
Page<UserDTO> getUserInformationList(Pageable pageable);

使用方法

//分页选项
PageRequest pageRequest = PageRequest.of(0, 3, Sort.Direction.DESC, "age");
Page<UserDTO> userInformationList = personRepository.getUserInformationList(pageRequest);
//查询结果总数
System.out.println(userInformationList.getTotalElements());// 6
//按照当前分页大小，总页数
System.out.println(userInformationList.getTotalPages());// 2
System.out.println(userInformationList.getContent());

3. IN的操作查询

在 sql 语句中加入我们需要筛选出符合几个条件中的一个的情况下，可以使用 IN 查询，对应到 JPA 中也非常简单。比如下面的方法就实现了，根据名字过滤需要的人员信息。

@Query(value = "select new com.saijia.modules.live.entity.UserDTO(p.name,p.age,c.companyName,s.name) " +
        "from Person p left join Company c on  p.companyId=c.id " +
        "left join School s on p.schoolId=s.id " +
        "where p.name IN :peopleList")
List<UserDTO> filterUserInfo(List peopleList);

实际使用:

List<String> personList=new ArrayList<>(Arrays.asList("person1","person2"));
List<UserDTO> userDTOS = personRepository.filterUserInfo(personList);

4. BETWEEN操作查询

查询满足某个范围的值。比如下面的方法就实现查询满足某个年龄范围的人员的信息。

@Query(value = "select new com.saijia.modules.live.entity.UserDTO(p.name,p.age,c.companyName,s.name) " +
            "from Person p left join Company c on  p.companyId=c.id " +
            "left join School s on p.schoolId=s.id " +
            "where p.age between :small and :big")
    List<UserDTO> filterUserInfoByAge(int small,int big);

实际使用：

List<UserDTO> userDTOS = personRepository.filterUserInfoByAge(19,20);

5. 测试

@SpringBootTest
@RunWith(SpringRunner.class)
publicclass PersonRepositoryTest2 {
    @Autowired
    private PersonRepository personRepository;

    @Sql(scripts = {"classpath:/init.sql"})
    @Test
    public void find_person_age_older_than_18() {
        List<Person> personList = personRepository.findByAgeGreaterThan(18);
        assertEquals(1, personList.size());
    }

    @Sql(scripts = {"classpath:/init.sql"})
    @Test
    public void should_get_user_info() {
        Optional<UserDTO> userInformation = personRepository.getUserInformation(1L);
        System.out.println(userInformation.get().toString());
    }

    @Sql(scripts = {"classpath:/init.sql"})
    @Test
    public void should_get_user_info_list() {
        PageRequest pageRequest = PageRequest.of(0, 3, Sort.Direction.DESC, "age");
        Page<UserDTO> userInformationList = personRepository.getUserInformationList(pageRequest);
        //查询结果总数
        System.out.println(userInformationList.getTotalElements());// 6
        //按照当前分页大小，总页数
        System.out.println(userInformationList.getTotalPages());// 2
        System.out.println(userInformationList.getContent());
    }

    @Sql(scripts = {"classpath:/init.sql"})
    @Test
    public void should_filter_user_info() {
        List<String> personList=new ArrayList<>(Arrays.asList("person1","person2"));
        List<UserDTO> userDTOS = personRepository.filterUserInfo(personList);
        System.out.println(userDTOS);
    }

    @Sql(scripts = {"classpath:/init.sql"})
    @Test
    public void should_filter_user_info_by_age() {
        List<UserDTO> userDTOS = personRepository.filterUserInfoByAge(19,20);
        System.out.println(userDTOS);
    }
}

讲到这里本章对Spring Data Jpa连表查询和分页知识讲解也就结束了，如果想了解更多知识可以在对应的专栏中看系列文章，谢谢大家的观看，希望能给各位同学带来帮助。如果觉得博主写的还可以的，可以点赞收藏。 😉

posted @ 2020-12-09 18:29 奋斗的小宋阅读(454) 评论(0) 收藏举报

刷新页面返回顶部

奋斗的小宋