在关联实体中使用规范 Spring Data Jpa 进行多列搜索? [英] Multi column search using Specifications Spring Data Jpa within associated entity?
问题描述
我正在回答这个问题 对单表的日期、整数和字符串数据类型字段执行多列搜索? 和 此方法必须返回 Specification
I am taking this question Perform multi column search on Date, Integer and String Data type fields of Single Table? and This method must return a result of type Specification<Employee> in Java 8 further ahead.
实际上我想在关联实体内搜索以及全局搜索的一部分.使用 JPA 2 Specifications API 可以吗?
Actually I wanted to search within association entity as well as a part of global search. Will that be possible using JPA 2 Specifications API ?
我有 Employee 和 Department @OneToMany 双向 关系.
I've Employee and Department @OneToMany bi-directional relationship.
员工.java
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMPLOYEE_ID")
private Long employeeId;
@Column(name = "FIRST_NAME")
private String firstName;
@Column(name = "LAST_NAME")
private String lastName;
@Column(name = "EMAIL_ID")
private String email;
@Column(name = "STATUS")
private String status;
@Column(name = "BIRTH_DATE")
private LocalDate birthDate;
@Column(name = "PROJECT_ASSOCIATION")
private Integer projectAssociation;
@Column(name = "GOAL_COUNT")
private Integer goalCnt;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "DEPT_ID", nullable = false)
@JsonIgnore
private Department department;
}
部门.java
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Department implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "DEPT_ID")
private Long departmentId;
@Column(name = "DEPT_NAME")
private String departmentName;
@Column(name = "DEPT_CODE")
private String departmentCode;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "department")
@JsonIgnore
private Set<Employee> employees;
}
我保存了如下数据.MyPaginationApplication.java
and I saved Data like below. MyPaginationApplication.java
@SpringBootApplication
public class MyPaginationApplication implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(MyPaginationApplication.class, args);
}
@Autowired
private EmployeeRepository employeeRepository;
@Autowired
private DepartmentRepository departmentRepository;
@Override
public void run(String... args) throws Exception {
saveData();
}
private void saveData() {
Department department1 = Department.builder()
.departmentCode("AD")
.departmentName("Boot Depart")
.build();
departmentRepository.save(department1);
Employee employee = Employee.builder().firstName("John").lastName("Doe").email("john.doe@gmail.com")
.birthDate(LocalDate.now())
.goalCnt(1)
.projectAssociation(2)
.department(department1)
.build();
Employee employee2 = Employee.builder().firstName("Neha").lastName("Narkhede").email("neha.narkhede@gmail.com")
.birthDate(LocalDate.now())
.projectAssociation(4)
.department(department1)
.goalCnt(2)
.build();
Employee employee3 = Employee.builder().firstName("John").lastName("Kerr").email("john.kerr@gmail.com")
.birthDate(LocalDate.now())
.projectAssociation(5)
.department(department1)
.goalCnt(4)
.build();
employeeRepository.saveAll(Arrays.asList(employee, employee2, employee3));
}
}
EmployeeController.java
EmployeeController.java
@GetMapping("/employees/{searchValue}")
public ResponseEntity<List<Employee>> findEmployees(@PathVariable("searchValue") String searchValue) {
List<Employee> employees = employeeService.searchGlobally(searchValue);
return new ResponseEntity<>(employees, HttpStatus.OK);
}
员工规范.java
public class EmployeeSpecification {
public static Specification<Employee> textInAllColumns(Object value) {
return (root, query, builder) -> builder.or(root.getModel().getDeclaredSingularAttributes().stream()
.filter(attr -> attr.getJavaType().equals(value.getClass()))
.map(attr -> map(value, root, builder, attr))
.toArray(Predicate[]::new));
}
private static Object map(Object value, Root<?> root, CriteriaBuilder builder, SingularAttribute<?, ?> a) {
switch (value.getClass().getSimpleName()) {
case "String":
return builder.like(root.get(a.getName()), getString((String) value));
case "Integer":
return builder.equal(root.get(a.getName()), value);
case "LocalDate":
return builder.equal(root.get(a.getName()), value);//date mapping
default:
return null;
}
}
private static String getString(String text) {
if (!text.contains("%")) {
text = "%" + text + "%";
}
return text;
}
}
当我点击 /employees/{searchValue} 时，我希望在 Department 表和 Employee 表中进行搜索(可能正在使用 Joins 类似的东西).那可能吗 ?如果是，我们该怎么做?
When I hit the /employees/{searchValue}, I want searching to be happened in Department Table along with Employee table (may be using Joins something like that). Is that possible ? If yes, how can we do that ?
或者:这将是像放在这里的好方法吗?从 使用@Query
Or: Will this be good approach to put like here? Got reference from Using @Query
@Query("SELECT t FROM Todo t WHERE " +
"LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
"LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm);
有什么指点吗?
推荐答案
如果你看看我的 post 其实我有一个加入的解决方案
If you take a look at my post actually I have a solution for join
@Override
public Specification<User> getFilter(UserListRequest request) {
return (root, query, cb) -> {
query.distinct(true); //Important because of the join in the addressAttribute specifications
return where(
where(firstNameContains(request.search))
.or(lastNameContains(request.search))
.or(emailContains(request.search))
)
.and(streetContains(request.street))
.and(cityContains(request.city))
.toPredicate(root, query, cb);
};
}
private Specification<User> firstNameContains(String firstName) {
return userAttributeContains("firstName", firstName);
}
private Specification<User> lastNameContains(String lastName) {
return userAttributeContains("lastName", lastName);
}
private Specification<User> emailContains(String email) {
return userAttributeContains("email", email);
}
private Specification<User> userAttributeContains(String attribute, String value) {
return (root, query, cb) -> {
if(value == null) {
return null;
}
return cb.like(
cb.lower(root.get(attribute)),
containsLowerCase(value)
);
};
}
private Specification<User> cityContains(String city) {
return addressAttributeContains("city", city);
}
private Specification<User> streetContains(String street) {
return addressAttributeContains("street", street);
}
private Specification<User> addressAttributeContains(String attribute, String value) {
return (root, query, cb) -> {
if(value == null) {
return null;
}
ListJoin<User, Address> addresses = root.joinList("addresses", JoinType.INNER);
return cb.like(
cb.lower(addresses.get(attribute)),
containsLowerCase(value)
);
};
}
private String containsLowerCase(String searchField) {
return "%" + searchField.toLowerCase() + "%";
}
在这里您可以看到我如何通过地址栏(城市和街道)搜索用户.
Here you can see how I search the users by their address columns (city and street).
您也不能使用 @Query 注释那么多(您以动态方式插入参数值，但不是参数.这就是 Specificationaion 方便的地方)
Also you cannot use the @Query annotation that much dinamically (you van insert parameter values dinamically, but not parameters. That's where Specificaion is handy)
我知道这不是 2.x.x Spring 版本，而是 1.5.x，但连接的想法是相同的.
I know this is not the 2.x.x Spring version, but 1.5.x, but the idea is the same for joins.
这篇关于在关联实体中使用规范 Spring Data Jpa 进行多列搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
