1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
| package com.example.pagination.specification;
import com.example.pagination.entity.User;
import com.example.pagination.entity.UserStatus;
import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
public class UserSpecification {
/**
* 根據使用者名稱模糊查詢
*/
public static Specification<User> usernameContains(String username) {
return (root, query, criteriaBuilder) -> {
if (username == null || username.trim().isEmpty()) {
return criteriaBuilder.conjunction();
}
return criteriaBuilder.like(
criteriaBuilder.lower(root.get("username")),
"%" + username.toLowerCase() + "%"
);
};
}
/**
* 根據郵箱模糊查詢
*/
public static Specification<User> emailContains(String email) {
return (root, query, criteriaBuilder) -> {
if (email == null || email.trim().isEmpty()) {
return criteriaBuilder.conjunction();
}
return criteriaBuilder.like(
criteriaBuilder.lower(root.get("email")),
"%" + email.toLowerCase() + "%"
);
};
}
/**
* 根據狀態查詢
*/
public static Specification<User> statusEquals(UserStatus status) {
return (root, query, criteriaBuilder) -> {
if (status == null) {
return criteriaBuilder.conjunction();
}
return criteriaBuilder.equal(root.get("status"), status);
};
}
/**
* 根據年齡範圍查詢
*/
public static Specification<User> ageBetween(Integer minAge, Integer maxAge) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
if (minAge != null) {
predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("age"), minAge));
}
if (maxAge != null) {
predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("age"), maxAge));
}
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
/**
* 根據城市查詢
*/
public static Specification<User> cityEquals(String city) {
return (root, query, criteriaBuilder) -> {
if (city == null || city.trim().isEmpty()) {
return criteriaBuilder.conjunction();
}
return criteriaBuilder.equal(root.get("city"), city);
};
}
/**
* 根據創建時間範圍查詢
*/
public static Specification<User> createdAtBetween(LocalDateTime startDate, LocalDateTime endDate) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
if (startDate != null) {
predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createdAt"), startDate));
}
if (endDate != null) {
predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createdAt"), endDate));
}
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
/**
* 複合查詢 - 根據多個條件
*/
public static Specification<User> withFilters(String username, String email,
UserStatus status, Integer minAge,
Integer maxAge, String city) {
return Specification.where(usernameContains(username))
.and(emailContains(email))
.and(statusEquals(status))
.and(ageBetween(minAge, maxAge))
.and(cityEquals(city));
}
/**
* 進階查詢 - 支援 OR 條件
*/
public static Specification<User> searchByKeyword(String keyword) {
return (root, query, criteriaBuilder) -> {
if (keyword == null || keyword.trim().isEmpty()) {
return criteriaBuilder.conjunction();
}
String likePattern = "%" + keyword.toLowerCase() + "%";
return criteriaBuilder.or(
criteriaBuilder.like(criteriaBuilder.lower(root.get("username")), likePattern),
criteriaBuilder.like(criteriaBuilder.lower(root.get("email")), likePattern),
criteriaBuilder.like(criteriaBuilder.lower(root.get("firstName")), likePattern),
criteriaBuilder.like(criteriaBuilder.lower(root.get("lastName")), likePattern),
criteriaBuilder.like(criteriaBuilder.lower(root.get("city")), likePattern)
);
};
}
}
|
留言討論