java - Specification.where().and() 在规范弹簧的查询中表现异常?
问题描述
我正在尝试在 Spring Boot 中按规范查询中的多个属性进行搜索。当我输入一个单词进行搜索时,它可以工作,但是当我输入两个单词时,它没有提供正确的输出。
这是模型。
@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 boolean driversLicence;
private boolean provisionalLicence;
private long phoneNumber;
private char gender;
private String emailAddress;
private String websiteAddress;
private String homeAddress;
private String image;
}
这是回购。
@Repository
public interface PersonRepo extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {
List<Person> searchByFirstNameContainingOrSurNameContainingAllIgnoreCase(String firstName, String surName);
Person findByFirstName(String firstName);
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> searchByDriversLicence(boolean emp, Pageable page);
List<Person> searchByProvisionalLicence(boolean 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);
}
这里是服务。
public List<Person> searchBySpecAll(String search, String page, String num, String sortBy, String ascending){
ArrayList<PersonSpecification> personSpecs = new ArrayList<PersonSpecification>();
ArrayList<String> searchWords = new ArrayList<String>();
for(String s : search.split(" ")) {
searchWords.add(s);
}
int numWords = searchWords.size();
System.out.println("num words");
for(String s : searchWords) {
System.out.println(s);
int age = 0;
try {
age = Integer.parseInt(s);
} catch (Exception e) {
}
double d = 0;
try {
d = Double.parseDouble(s);
} catch (Exception e) {
}
char gender = 0;
try {
gender = search.charAt(0);
} catch (Exception e) {
}
PersonSpecification spec = new PersonSpecification(new SearchCriteria("firstName", ":", s));
PersonSpecification spec2 = new PersonSpecification(new SearchCriteria("surName", ":", s));
PersonSpecification spec3 = new PersonSpecification(new SearchCriteria("age", ":", age));
PersonSpecification spec4 = new PersonSpecification(new SearchCriteria("description", ":", s));
PersonSpecification spec5 = new PersonSpecification(new SearchCriteria("highestEducationQualification", "=", s));
PersonSpecification spec6 = new PersonSpecification(new SearchCriteria("occupation", ":", s));
PersonSpecification spec7 = new PersonSpecification(new SearchCriteria("employer", ":", s));
PersonSpecification spec8 = new PersonSpecification(new SearchCriteria("college", ":", s));
PersonSpecification spec9 = new PersonSpecification(new SearchCriteria("school", ":", s));
PersonSpecification spec10 = new PersonSpecification(new SearchCriteria("eyecolor", "=", s));
PersonSpecification spec11 = new PersonSpecification(new SearchCriteria("weight", "=", d));
PersonSpecification spec12 = new PersonSpecification(new SearchCriteria("height", "=", d));
PersonSpecification spec17 = new PersonSpecification(new SearchCriteria("phoneNumber", "=", d));
//PersonSpecification spec18 = new PersonSpecification(new SearchCriteria("gender", "=", gender));
PersonSpecification spec19 = new PersonSpecification(new SearchCriteria("emailAddress", "=", s));
PersonSpecification spec20 = new PersonSpecification(new SearchCriteria("websiteAddress", ":", s));
PersonSpecification spec21 = new PersonSpecification(new SearchCriteria("homeAddress", ":", s));;
personSpecs.add(spec);
personSpecs.add(spec2);
personSpecs.add(spec3);
//personSpecs.add(spec4);
//personSpecs.add(spec5);
personSpecs.add(spec6);
personSpecs.add(spec7);
personSpecs.add(spec8);
personSpecs.add(spec9);
personSpecs.add(spec10);
personSpecs.add(spec11);
personSpecs.add(spec12);
personSpecs.add(spec17);
//personSpecs.add(spec18);
personSpecs.add(spec19);
personSpecs.add(spec20);
personSpecs.add(spec21);
}
int pageNum = 0;
try {
pageNum = Integer.parseInt(page);
} catch (Exception e) {
}
int numResults = 0;
try {
numResults = Integer.parseInt(num);
} catch (Exception e) {
}
boolean asc = true;
if(ascending.equals("false"))
asc = false;
Pageable firstPageWithFourElements = PageRequest.of(pageNum, numResults, Sort.by(Sort.Direction.DESC, sortBy));;
if(asc) {
firstPageWithFourElements = PageRequest.of(pageNum, numResults, Sort.by(Sort.Direction.ASC, sortBy));
}
ArrayList<Specification<Person>> specs = new ArrayList<Specification<Person>>();
Specification<Person> specTotal = null;
int numSpecs = 14;
int iters = personSpecs.size() / numSpecs;
System.out.println(personSpecs.size());
System.out.println("Iterations of personSpecs: " + iters);
for(int i = 0; i < iters; i++) {
specs.add(Specification.where(personSpecs.get(i)).or(personSpecs.get(i + 1)).or(personSpecs.get(i + 2)).or(personSpecs.get(i + 3)).or(personSpecs.get(i + 4)).or(personSpecs.get(i + 5)).or(personSpecs.get(i + 6)).or(personSpecs.get(i + 7)).or(personSpecs.get(i + 8)).or(personSpecs.get(i + 9)).or(personSpecs.get(i + 10)).or(personSpecs.get(i + 11)).or(personSpecs.get(i + 12)).or(personSpecs.get(i + 13)));
}
System.out.println("Number of specifications to append to total: " + specs.size());
int totalToAppend = 0;
totalToAppend = specs.size();
System.out.print(totalToAppend);
switch(totalToAppend) {
case 1: {
specTotal = specs.get(0);
System.out.println(1 + " case");
break;
}
case 2: {
specTotal = Specification.where(specs.get(0).and(specs.get(1)));
break;
}
case 3: {
specTotal = Specification.where(specs.get(0).and(specs.get(1)).and(specs.get(2)));
break;
}
case 4: {
specTotal = Specification.where(specs.get(0).and(specs.get(1)).and(specs.get(2)).and(specs.get(3)));
break;
}
}
//specTotal = Specification.where(spec).or(spec2).or(spec3).or(spec6).or(spec7).or(spec8).or(spec9).or(spec11).or(spec12).or(spec19).or(spec21).or(spec20).or(spec4).or(spec5).or(spec10).or(spec17);
Page<Person> results = personRepo.findAll(specTotal, firstPageWithFourElements);
List<Person> res = results.getContent();
return res;
}
您会注意到,该功能通过在必要时将查询拆分为单词来工作。迭代每个单词,为每个单词创建一个规范数组列表。然后它Specification.where()
通过访问先前在 arraylist 中创建的规范创建了两个,并将它们组合在一个开关中,具体取决于创建 total 的单词数Specification.where()
。具体取决于输入的字数。还支持分页、排序和排序方向。当我输入一个单词时,它工作正常。但是当我输入两个单词时,它会给我不正确的输出。它显示另一个姓氏包含输入的搜索查询词的人。
例如
当我输入“亚当”作为搜索查询时,它会显示我的个人数据。但是当我输入“Adam Howard”时,它会显示另一个名为 Edi Adamiec 的人。我想输出亚当霍华德的数据。我试过在“case 2 switch”中使用 .or()`` 而不是,但这不是我想要的功能。Edi Adamiec 这个名字来自数据库中的虚拟数据,我使用了一个虚拟数据生成器。如果需要,我可以提供数据库数据,请告诉我。请,感谢您的帮助。
这是生成的 SQL。
Hibernate: select person0_.user_id as user_id1_0_, person0_.age as age2_0_, person0_.college as college3_0_, person0_.description as descript4_0_, person0_.dob as dob5_0_, person0_.drivers_licence as drivers_6_0_, person0_.email_address as email_ad7_0_, person0_.employer as employer8_0_, person0_.eyecolor as eyecolor9_0_, person0_.first_name as first_n10_0_, person0_.gender as gender11_0_, person0_.height as height12_0_, person0_.highest_education_qualification as highest13_0_, person0_.home_address as home_ad14_0_, person0_.image as image15_0_, person0_.occupation as occupat16_0_, person0_.phone_number as phone_n17_0_, person0_.provisional_licence as provisi18_0_, person0_.school as school19_0_, person0_.sur_name as sur_nam20_0_, person0_.website_address as website21_0_, person0_.weight as weight22_0_ from person person0_ where (person0_.first_name like ? or person0_.home_address like ? or person0_.website_address like ? or person0_.email_address=? or person0_.phone_number=0 or person0_.height=0.0 or person0_.weight=0.0 or person0_.eyecolor=? or person0_.school like ? or person0_.college like ? or person0_.employer like ? or person0_.occupation like ? or person0_.age=0 or person0_.sur_name like ?) and (person0_.home_address like ? or person0_.website_address like ? or person0_.email_address=? or person0_.phone_number=0 or person0_.height=0.0 or person0_.weight=0.0 or person0_.eyecolor=? or person0_.school like ? or person0_.college like ? or person0_.employer like ? or person0_.occupation like ? or person0_.age=0 or person0_.sur_name like ? or person0_.first_name like ?) order by person0_.first_name asc limit ?
我实际上想搜索所有属性,其中一些属性是一个单词,到文本段落。我希望将搜索查询分解为单词,以便它可以按名字和姓氏进行搜索。
解决方案
我认为问题在于您添加了对其他人来说person0_.weight=0.0
是正确的判断。尝试删除这些。
推荐阅读
- pharo - Pharo ZnSecureServer - SSL 异常:接受失败 [代码:-5]
- javascript - 我想将值(php数组)移动到js数组
- php - Wordpress wp_query:按 meta_query 键排序,然后按日期排序(固定/粘性)
- javascript - 复杂的 BackgroundImage 样式在反应中不起作用,但在 css/javascript 中起作用
- javascript - 如何从两个具有键的对象中获取新的 JavaScript 对象,并在键匹配时取平均值
- sql - 选择不同的 customer_id
- java - Spring Batch - 管理器中的远程分区 - 工作环境 - CSV 文件
- python - 如何在 Python 和 Qt Quick QML 应用程序中实现简化的双向数据绑定
- php - 如何在laravel中检测JFIF图像类型
- html - 如何通过单击按钮创建动态折叠?