java分页排序在SpringDataJPA和SpringFramework中不起作用

kkbh8khc  于 2021-06-26  发布在  Java
关注(0)|答案(2)|浏览(265)

我正在尝试为一个人实现跨多个属性的搜索功能。
这是模型。

@Entity
@NoArgsConstructor
@Getter
@Setter
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "USER_ID")
    private long id;

    private String firstName;

    private String surName;

    private int age;

    private Date DOB;

    private String description;

    private String highestEducationQualification;

    private String occupation;

    private String employer;

    private String college;

    private String school;

    private String eyecolor;

    private double weight;

    private double height;

    private String PPSnumber;

    private boolean driversLicence;

    private boolean provisionalLicence;

    private String bankIBAN;

    private long phoneNumber;

    private char gender;

    private String emailAddress;

    private String websiteAddress;

    private String homeAddress;

}

这是我的存储库。

@Repository
public interface PersonRepo extends JpaRepository<Person, Long>{

    List<Person> searchByFirstNameContainingAllIgnoreCase(String firstName, Pageable page);
    List<Person> searchBySurNameContainingAllIgnoreCase(String surName, Pageable page);
    List<Person> searchByAge(int age, Pageable page);
    List<Person> searchByDescriptionContainingAllIgnoreCase(String desc, Pageable page);
    List<Person> searchByHighestEducationQualificationContainingAllIgnoreCase(String edu, Pageable page);
    List<Person> searchByOccupationContainingAllIgnoreCase(String occ, Pageable page);
    List<Person> searchByEmployerContainingAllIgnoreCase(String emp, Pageable page);
    List<Person> searchByCollegeContainingAllIgnoreCase(String emp, Pageable page);
    List<Person> searchBySchoolContainingAllIgnoreCase(String emp, Pageable page);
    List<Person> searchByEyecolorContainingAllIgnoreCase(String eye, Pageable page);
    List<Person> searchByWeight(double weight, Pageable page);
    List<Person> searchByHeight(double height, Pageable page);
    List<Person> searchByPPSnumberIgnoreCase(String emp, Pageable page);
    List<Person> searchByDriversLicence(boolean emp, Pageable page);
    List<Person> searchByProvisionalLicence(boolean emp, Pageable page);
    List<Person> searchByBankIBANAllIgnoreCase(String emp, Pageable page);
    List<Person> searchByPhoneNumber(long phone, Pageable page);
    List<Person> searchByGender(char emp, Pageable page);
    List<Person> searchByEmailAddressIgnoreCase(String emp, Pageable page);
    List<Person> searchByWebsiteAddressContainingAllIgnoreCase(String emp, Pageable page);
    List<Person> searchByHomeAddressContainingAllIgnoreCase(String emp, Pageable page);
}

服务功能。

class PersonService {

    @Autowired
    private PersonRepo personRepo;

    @Override
    public List<Person> searchByAllAttributes(String toSearch, int page, int quan, String sortBy, boolean ascending) {
        int ageToSearch = 0;
        try {
            ageToSearch = Integer.parseInt(toSearch);
        } catch (Exception e) {

        }
        double toSearchDouble = 0;
        try {
            toSearchDouble = Double.parseDouble(toSearch);
        } catch (Exception e) {

        }
        long phoneToSearch = 0;
        try {
            phoneToSearch = Long.parseLong(toSearch);
        } catch (Exception e) {

        }

        System.out.println(toSearchDouble);

        List<Person> results;

        Pageable firstPageWithTwoElements = PageRequest.of(page, quan, Sort.by("firstName").descending());

        results = personRepo.searchByFirstNameContainingAllIgnoreCase(toSearch, firstPageWithTwoElements);
        results.addAll(personRepo.searchBySurNameContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByAge(ageToSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByDescriptionContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));

        results.addAll(personRepo.searchByCollegeContainingAllIgnoreCase(toSearch, firstPageWithTwoElements));
        results.addAll(personRepo.searchBySchoolContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByEmployerContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByOccupationContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByHighestEducationQualificationContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByEyecolorContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByWeight(toSearchDouble,firstPageWithTwoElements));
        results.addAll(personRepo.searchByHeight(toSearchDouble,firstPageWithTwoElements));
        results.addAll(personRepo.searchByPPSnumberIgnoreCase(toSearch,firstPageWithTwoElements));
        //drivers and provisional
        results.addAll(personRepo.searchByBankIBANAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByPhoneNumber(phoneToSearch,firstPageWithTwoElements));
        //gender
        results.addAll(personRepo.searchByEmailAddressIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByWebsiteAddressContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));
        results.addAll(personRepo.searchByHomeAddressContainingAllIgnoreCase(toSearch,firstPageWithTwoElements));

        results = removeDuplicatePersons(results);

        return results;
    }

    List<Person> removeDuplicatePersons(List<Person> toRemove){
        List<Person> result = toRemove;

         List<Person> listWithoutDuplicates = new ArrayList<>(
                  new HashSet<Person>(result));

        return listWithoutDuplicates;
    }

}

正如您将看到的,有一个硬编码的sort对象,它具有firstname和descending。每当我调用这个函数时,它都会返回一个随机排序的数据。这样不行。我努力将其硬编码以消除参数数据损坏的可能性,但即使硬编码也不起作用。 toSearch 是字符串搜索查询。 Page 以及 quan (数量)用于分页。分页工作,但排序不工作。任何帮助是感激的,如果你需要我解释更多的代码,添加一个评论。
也有一个控制器类,正如您可能想象的那样。我也可以添加代码,但它不会直接影响代码的逻辑。控制器调用服务函数并将其作为json返回给web应用程序。我在postman中通过请求调用服务函数的rest控制器函数对这两个函数进行了调试。它以json的形式将数据返回给postman,我在实现web应用程序中也做了同样的操作,但是数据没有被排序。
您将注意到person类模型上的4个注解。实体是持久性的。noargsconstructor和getter以及setter是lombok的一部分,lombok是一个允许您省略getter、setter和constructor的包,它们是在编译时添加的。

vi4fp9gy

vi4fp9gy1#

问题不在于排序。问题在于你执行搜索的方式。你调用18个不同的搜索并合并它们。每一个搜索都是排序的,但它不能保证 results 列表已排序。
例子:
第一次搜索返回: {"Betty", "Adam"} (按名字降序排列)
第二次搜索返回: {"Zack", "Fiona"} (按名字降序排列)
但结果是: {"Betty", "Adam", "Zack", "Fiona"} 看起来是随机排列的。
您总是可以在java端进行排序,但由于大列表的性能问题,不建议这样做。
要解决此问题,您需要使用一个查询进行搜索。您可以通过规范使用spring引导查询来实现这一点,更多信息可以在这里找到。

xt0899hw

xt0899hw2#

您可能正在寻找jpa标准api。您可以使用jpa规范api构建查询,与您所做的相比,jpa规范api有两个明显的优势:
可以在一个或多个查询中进行查询(性能更高)。
一旦构建,更改和调整就更简单了(如果您设计的类/模式很好的话)。
这里有一个简单的例子:

@Repository
public interface PersonRepo extends JpaRepository<Person,Long>, JpaSpecificationExecutor<Person> {}

这是我写的一个快速组件。您可以看到它在哪里使用jpa规范api创建sql,然后使用repo运行它。

@Component
public class PersonSearcher {

    @Autowired
    private PersonRepo personRepo;

    /*
        Would be better taking a "Form"/Object with your search criteria.
     */
    public void search(String name, Integer ageMin, Integer ageMax, Pageable pageable) {

        //Get "all"
        Specification<Person> personSpecification = Specification.not(null);

        //Create "Predicates" (like the where clauses).
        personSpecification = personSpecification.and(new MyPersonSpec("firstName", name, "like"));
        personSpecification = personSpecification.and(new MyPersonSpec("age", ageMin, "gt"));
        personSpecification = personSpecification.and(new MyPersonSpec("age", ageMax, "lt"));

        //Run query using Repo. Spring paging still works.
        final Page<Person> filterdPersons = personRepo.findAll(personSpecification, pageable);

    }

    private static class MyPersonSpec implements Specification<Person> {

        private final String field;
        private final Object value;
        private final String operation;

        private MyPersonSpec(String field, Object value, String operation) {
            this.field = field;
            this.value = value;
            this.operation = operation;
        }

        @Override
        public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            switch (operation) {
                case "like":
                    return criteriaBuilder.like(root.get(field), "%" + value.toString().toLowerCase() + "%");
                case "equal":
                    return criteriaBuilder.equal(root.get(field), value);
                case "gt":
                    return criteriaBuilder.greaterThan(root.get(field), (int) value);
                case "lt":
                    return criteriaBuilder.lessThan(root.get(field), (int) value);

                default:
                    throw new RuntimeException("Unexpected `op`.");
            }
        }
    }
}

这里有一个快速测试,以确保它编译…你会想做一些正确的Assert,也许@datajpa测试,而不是@springboottest。。。

@SpringBootTest
@ActiveProfiles("tc")
@Log4j2
class PersonSearcherTest {

    @Autowired
    private PersonSearcher personSearcher;

    @Test
    void search() {
        personSearcher.search("Bob",1, 20,
                PageRequest.of(0,5, Sort.by(Sort.Direction.DESC,"emailAddress"))
        );
    }
}

以及规范生成并在日志中运行的sql:

08 Jan 2021 23:24:19,840 [DEBUG] --- o.h.SQL                        : 
    select
        person0_.user_id as user_id1_18_,
        person0_.dob as dob2_18_,
        person0_.ppsnumber as ppsnumbe3_18_,
        person0_.age as age4_18_,
        person0_.bankiban as bankiban5_18_,
        person0_.college as college6_18_,
        person0_.description as descript7_18_,
        person0_.drivers_licence as drivers_8_18_,
        person0_.email_address as email_ad9_18_,
        person0_.employer as employe10_18_,
        person0_.eyecolor as eyecolo11_18_,
        person0_.first_name as first_n12_18_,
        person0_.gender as gender13_18_,
        person0_.height as height14_18_,
        person0_.highest_education_qualification as highest15_18_,
        person0_.home_address as home_ad16_18_,
        person0_.occupation as occupat17_18_,
        person0_.phone_number as phone_n18_18_,
        person0_.provisional_licence as provisi19_18_,
        person0_.school as school20_18_,
        person0_.sur_name as sur_nam21_18_,
        person0_.website_address as website22_18_,
        person0_.weight as weight23_18_ 
    from
        person person0_ 
    where
        person0_.age<20 
        and person0_.age>1 
        and (
            person0_.first_name like ?
        ) 
    order by
        person0_.email_address desc limit ?

相关问题